Ubuntu wordpress Windows Firefox linux命令 java php google apache Python 微软 开源 centos 程序员 Android nginx linux HTML5 mysql shell

Python3连接MySQL数据库

Python 2.x 上连接mysql的库倒是不少的,其中比较著名就是MySQLdb(Django项目都使用它;我也在开发测试系统时也使用过),见:http://sourceforge.net/projects/mysql-python/

不过,目前MySQLdb并不支持python3.x,网上找了一些方法,后来我还是偶然发现MySQL官方已经提供了MySQL连接器,而且已经有支持Python3.x的版本了。MySQL Connector/Python, a self-contained Python driver for communicating with MySQL servers. 这个用起来还是感觉比较顺手的。

关于MySQL Connector/Python的各种介绍、安装、API等文档,还是参考官网吧:http://dev.mysql.com/doc/connector-python/en/index.html(注意:安装程序将关于MySQL Connnector的python2的源文件复制到了python3库的位置(运行时会报语法错误),我就直接手动复制了其中python3/目录下的文件过去就解决。)

另外,Python3.x连接MySQL的其他方案有:oursql, PyMySQL, myconnpy 等,参考如下链接:

  • http://packages.python.org/oursql/
  • https://github.com/petehunt/PyMySQL/
  • https://launchpad.net/myconnpy

下面只是贴一个试用 MySQL Connector/Python 的Python脚本吧(包括创建表、插入数据、从文件读取并插入数据、查询数据等):

#!/usr/bin/python3
# a sample to use mysql-connector for python3
# see details from   http://dev.mysql.com/doc/connector-python/en/index.html
 
import mysql.connector
import sys, os
 
user = 'root'
pwd  = '123456'
host = '127.0.0.1'
db   = 'test'
 
data_file = 'mysql-test.dat'
 
create_table_sql = "CREATE TABLE IF NOT EXISTS mytable ( 
                    id int(10) AUTO_INCREMENT PRIMARY KEY, 
		    name varchar(20), age int(4) ) 
		    CHARACTER set utf8"
 
insert_sql = "INSERT INTO mytable(name, age) VALUES ('Jay', 22 ), ('杰', 26)"
select_sql = "SELECT id, name, age FROM mytable"
 
cnx = mysql.connector.connect(user=user, password=pwd, host=host, database=db)
cursor = cnx.cursor()
 
try:
    cursor.execute(create_table_sql)
except mysql.connector.Error as err:
    print("create table 'mytable' failed.")
    print("Error: {}".format(err.msg))
    sys.exit()
 
try:
    cursor.execute(insert_sql)
except mysql.connector.Error as err:
    print("insert table 'mytable' failed.")
    print("Error: {}".format(err.msg))
    sys.exit()
 
if os.path.exists(data_file):
    myfile = open(data_file)
    lines = myfile.readlines()
    myfile.close()
 
    for line in lines:
        myset = line.split()
        sql = "INSERT INTO mytable (name, age) VALUES ('{}', {})".format(myset[0], myset[1])
        try:
            cursor.execute(sql)
        except mysql.connector.Error as err:
            print("insert table 'mytable' from file 'mysql-test.dat' -- failed.")
            print("Error: {}".format(err.msg))
            sys.exit()
 
try:
    cursor.execute(select_sql)
    for (id, name, age) in cursor:
        print("ID:{}  Name:{}  Age:{}".format(id, name, age))
except mysql.connector.Error as err:
    print("query table 'mytable' failed.")
    print("Error: {}".format(err.msg))
    sys.exit()
 
cnx.commit()
cursor.close()
cnx.close()

另外,最后再贴一个使用MySQLdb的python2.x代码示例吧:

#!/usr/bin/python2.7
# coding=utf-8
 
import MySQLdb
import sys
 
host = 'localhost'
user = 'root'
pwd  = '123456'   # to be modified.
db   = 'test'
 
 
if __name__ == '__main__':
    conn = MySQLdb.connect(host, user, pwd, db, charset='utf8');
    try:
        conn.ping()
    except:
        print 'failed to connect MySQL.'
    sql = 'select * from mytable where id = 2'
    cur = conn.cursor()
    cur.execute(sql)
    row = cur.fetchone()
#    print type(row)
    for i in row:
        print i
    cur.close()
    conn.close()
    sys.exit()

延伸阅读

评论