【www.bbyears.com--python】
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
和数据库建立连接
conn=MySQLdb.connect(host="localhost",user="root",passwd="sa",db="mytable",charset="utf8")
提供的connect方法用来和数据库建立连接,接收数个参数,返回连接对象.
比较常用的参数包括
host:数据库主机名.默认是用本地主机.
user:数据库登陆名.默认是当前用户.
passwd:数据库登陆的秘密.默认为空.
db:要使用的数据库名.没有默认值.
port:MySQL服务使用的TCP端口.默认是3306.
charset:数据库编码.
下面只是贴一个试用 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()
使用python3操作MySQL
以下是一个python3操作MySQL5.5的一个示例，其中包括连接MySQL，创建数据库，创建表格，插入/查询数据功能：
复制代码
# -*- coding: utf-8 -*-
# author: txw1958
# website: http://www.cnblogs.com/txw1958/
import MySQLdb
#连接
cxn = MySQLdb.Connect(host = "127.0.0.1", user = "root", passwd = "Welcome123")
#游标
cur = cxn.cursor()
try:
cur.execute("DROP DATABASE txw1958")
except Exception as e:
print(e)
finally:
pass
#创建数据库
cur.execute("CREATE DATABASE txw1958")
cur.execute("USE txw1958")
#创建表
cur.execute("CREATE TABLE users (id INT, name VARCHAR(8))")
#插入
cur.execute("INSERT INTO users VALUES(1, "www"),(2, "cnblogs"),(3, "com"),(4, "txw1958")")
#查询
cur.execute("SELECT * FROM users")
for row in cur.fetchall():
print("%s\t%s" %row)
#关闭
cur.close()
cxn.commit()
cxn.close()
运行结果如下：
C:\>python py3-mysql.py
1 www
2 cnblogs
3 com
4 txw1958
C:\>