Python操作MySQL数据库

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]

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *