python MySQLdb 学习小结

之前写过一篇sqlite3的博客,好久就想也写一写MySQLdb的,由于自己不是专业开发(懒、不上进)的缘故,迟迟未能动笔,最近工作上不是太忙,就趁着这个机会整理了下。

忘记了这个 import MySQLdb as mdb

一: 新建(create)数据库表

连接我本地的数据库,创建author表:

#创建author表
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("CREATE TABLE `author` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8")
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

登入数据库,可以看到author表正确创建:

mysql> show create table author\G
*************************** 1. row ***************************
       Table: author
Create Table: CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

二:插入(insert)数据

先使用execute插入一条数据,接着使用executemany插入多条数据,如下:

#插入
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("insert into author(name) values(%s)",("lee0",))
    con.commit()
    data = [("lee"+str(i),) for i in range(1,10)]
    cur.executemany("insert into author(name) values(%s)",data)
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

登录数据库,查看插入成功:

mysql> select * from author;
+----+------+
| id | name |
+----+------+
|  1 | lee0 |
|  2 | lee1 |
|  3 | lee2 |
|  4 | lee3 |
|  5 | lee4 |
|  6 | lee5 |
|  7 | lee6 |
|  8 | lee7 |
|  9 | lee8 |
| 10 | lee9 |
+----+------+
10 rows in set (0.00 sec)

三:更新(update)数据

#更新
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("update author set name=%s where id=%s;",("test",1))
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

登录到数据库,看到id=1的那条记录已经更新:

mysql> select * from author where id=1;
+----+------+
| id | name |
+----+------+
|  1 | lee0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from author where id=1;
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+
1 row in set (0.00 sec)

四:删除(delete)数据

#删除
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("delete from author where id=%s",(1,))
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

登录到数据库,id=1那条数据已经被删除:

mysql> select * from author;
+----+------+
| id | name |
+----+------+
|  2 | lee1 |
|  3 | lee2 |
|  4 | lee3 |
|  5 | lee4 |
|  6 | lee5 |
|  7 | lee6 |
|  8 | lee7 |
|  9 | lee8 |
| 10 | lee9 |
+----+------+
9 rows in set (0.00 sec)

五:使用MySQLdb的The dictionary cursor模式

其实这个和sqlite3的Row模式类似,就是使用cursorfetch*函数时,他默认返回的是tuple,你只能使用row[0]、row[1]这种方式去访问数据,很不方便;The dictionary cursor模式就是解决这个问题的,返回的数据变成了字典,可以直接用row[“id”]、row[“name”]访问数据。

默认返回的结果是tuple:

#查询
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    print "取多行数据"
    cur.execute("select * from author")
    rows = cur.fetchall()
    for row in rows:
        print row
    print "取单行数据"
    cur.execute("select * from author where id=10")
    row = cur.fetchone()
    print row
except mdb.Error,e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

结果:

取多行数据
(2L, 'lee1')
(3L, 'lee2')
(4L, 'lee3')
(5L, 'lee4')
(6L, 'lee5')
(7L, 'lee6')
(8L, 'lee7')
(9L, 'lee8')
(10L, 'lee9')
取单行数据
(10L, 'lee9’)

使用cursors.DictCursor返回的是字典:

#查询使用mdb.cursors.DictCursor模式
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("select * from author where id=10")
    row = cur.fetchone()
    print row,row["id"],row["name"]
except mdb.Error,e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

结果:

{'id': 10L, 'name': 'lee9'} 10 lee9

看到网友自己写的模拟返回字典的函数,供参考:

#查询使用cursor.description对返回的结果做了一个简单处理,模拟上面的cursors.DictCursor
def FetchOneAssoc(cursor) :
    data = cursor.fetchone()
    if data == None :
        return None
    desc = cursor.description
    dict = {}
    for (name, value) in zip(desc, data) :
        dict[name[0]] = value
    return dict

try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("select * from author where id=10")
    row = FetchOneAssoc(cur)
    print row,row["id"],row["name"]
except mdb.Error,e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

结果:

{'id': 10L, 'name': 'lee9'} 10 lee9

最后:

1.执行sql时使用MySQLdb的占位符,不要使用python的字符串插入形式

推荐 cur.execute("insert into author(name) values(%s)",("lee0",))
不建议 cur.execute("insert into author(name) values(%s)” % ("lee0",))

2.对于非事务引擎mysiam,con.commit()无用;对于事务引擎innodb,insert/update/delete操作需要con.commit(),否则sql不生效,测试create不加con.commit()也可以,select语句不涉及到修改数据库可以不加con.commit()

3.如果觉得try/except/finally太麻烦,可以使用with语句,with语句会自动commit、释放mysql资源

In [9]: with mdb.connect('localhost','root','budong','test') as cur:
   ...:     cur.execute("insert into author(name) values(%s)",("lee0",))
   ...:

In [10]:

附,测试代码:

#!/usr/bin/env python
#coding: utf-8

import MySQLdb as mdb
import sys

#创建author表
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("CREATE TABLE `author` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8")
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

#插入
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("insert into author(name) values(%s)",("lee0",))
    con.commit()
    data = [("lee"+str(i),) for i in range(1,10)]
    cur.executemany("insert into author(name) values(%s)",data)
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

#更新
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("update author set name=%s where id=%s;",("test",1))
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

#删除
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("delete from author where id=%s",(1,))
    con.commit()
except mdb.Error,e:
    if con:
        con.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

#查询
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    print "取多行数据"
    cur.execute("select * from author")
    rows = cur.fetchall()
    for row in rows:
        print row
    print "取单行数据"
    cur.execute("select * from author where id=10")
    row = cur.fetchone()
    print row
except mdb.Error,e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

#查询使用mdb.cursors.DictCursor模式
try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("select * from author where id=10")
    row = cur.fetchone()
    print row,row["id"],row["name"]
except mdb.Error,e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

#查询使用cursor.description对返回的结果做了一个简单处理,模拟上面的mdb.cursors.DictCursor
def FetchOneAssoc(cursor) :
    data = cursor.fetchone()
    if data == None :
        return None
    desc = cursor.description
    dict = {}
    for (name, value) in zip(desc, data) :
        dict[name[0]] = value
    return dict

try:
    con = mdb.connect('localhost','root','budong','test')
    cur = con.cursor()
    cur.execute("select * from author where id=10")
    row = FetchOneAssoc(cur)
    print row,row["id"],row["name"]
except mdb.Error,e:
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
finally:
    if con:
        con.close()

参考资料:

MySQL for Python : https://sourceforge.net/projects/mysql-python/

MySQLdb1: https://github.com/farcepest/MySQLdb1

MySQLdb User’s Guide:http://mysql-python.sourceforge.net/MySQLdb.html

Writing MySQL Scripts with Python DB-API:http://www.kitebird.com/articles/pydbapi.html

MySQLdb Frequently Asked Questions: http://mysql-python.sourceforge.net/FAQ.html

A Quick Guide to Using MySQL in Python: http://ianhowson.com/a-quick-guide-to-using-mysql-in-python.html

Python MySQL Database Access:https://www.tutorialspoint.com/python/python_database_access.htm

Python MySQL Documentation:http://www.mikusa.com/python-mysql-docs/index.html

Context manager for Python’s MySQLdb : http://stackoverflow.com/questions/8067690/context-manager-for-pythons-mysqldb#comment23020320_8074341