Python中居然没有内置操纵MySQL数据库的包。

Python中操作MySQL的第三方包很多，本文使用的是MySQL-python 1.2.3。

此接口是符合PEP 249的，即Python通用的数据库操作标准(类似JDBC)。

1、下载、安装

# Download
wget http://downloads.sourceforge.net/project/mysql-python/mysql-python/1.2.3/MySQL-python-1.2.3.tar.gz?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fmysql-python%2Ffiles%2Fmysql-python%2F1.2.3%2F&ts=1339082819&use_mirror=cdnetworks-kr-1

# Decompress
tar -xzvf MySQL-python-1.2.3.tar.gz
cd MySQL-python-1.2.3

2、修改site.cfg

由于我得MySQL是编译安装的。如果你是apt或者yum安装的，直接装libdevmysql或者mysql-dev即可。

主要是mysql_config一行：

[options]
# embedded: link against the embedded server library
# threadsafe: use the threadsafe client
# static: link against a static library (probably required for embedded)

embedded = False
threadsafe = True
static = False

# The path to mysql_config.
# Only use this if mysql_config is not on your PATH, or you have some weird
# setup that requires it.
mysql_config = /usr/mysql/bin/mysql_config

# The Windows registry key for MySQL.
# This has to be set for Windows builds to work.
# Only change this if you have a different version.
registry_key = SOFTWARE\MySQL AB\MySQL Server 5.0

另外，如果你的libmysql.so不在系统的lib目录中，需要做一个软链接。

然后就可以安装了：

sudo python ./setup.py install

3、CRUD用法

连接、断开

import MySQLdb
# Conn
conn = MySQLdb.connect(host="127.0.0.1", 
                       port=3306,
                       user="liheyuan", 
                       passwd="password", 
                       db="test_db")
# Close
conn.close()

如果需要返回UTF8字符串，增加选项：use_unicode=True 且加上 charset="utf8"。

插入

插入后一定记着要commit，否则就丢了！

cursor = conn.cursor()
cursor.execute("insert into wb(wb_id, wb_msg) values(1, \"msgmsg\")")
conn.commit()

插入多条

注意此处坑爹的是，替代符号一律是%s，不是?，也不是对应数据类型！

cursor = conn.cursor()
params = [(i, "msg") for i in xrange(100, 200)]
cursor.executemany("insert into wb(wb_id, wb_msg) values(%s, %s)", params)
conn.commit()

查询

cursor.execute("select * from wb where wb_id>50")
for row in cursor.fetchall():
    print row[0]

查询并取出第一条

cursor.execute("select * from wb")
row = cursor.fetchone()

2013.11.25更新：

获取SELECT出的每一列的field_name：

num_fields = len(cursor.description)
field_names = [i[0] for i in cursor.description]