转：MySQL Python 教程（二）

原文链接：http://zetcode.com/databases/mysqlpythontutorial/
翻译作者：kodango dangoakachan at foxmail dot com
译文链接：
a. http://kodango.com/mysql-python-tutorial-part-one
b. http://kodango.com/mysql-python-tutorial-part-two

译者注：本文的前半部分请看上一篇文章。

字典游标

在MySQL模块中有许多种游标类型。默认的游标类型以元组的元组形式返回数据。当我们使用字典游标时，这些数据是以Python字典的形式返回。这样一来，我们就可以通过列名来访问数据。

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

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print "%s %s" % (row["Id"], row["Name"])

在这个例子中，我们使用字典游标来打印Writers表的内容。

cur = con.cursor(mdb.cursors.DictCursor)

我们创建字典游标。

rows = cur.fetchall()

然后我们返回所有的数据。

for row in rows:
        print "%s %s" % (row["Id"], row["Name"])

接着，我们使用Writers表的列名来引用相应的数据。

列标题

接下来，我们会展示如何将列标题同表数据一同打印出来。

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

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()
    desc = cur.description

    print "%s %3s" % (desc[0][0], desc[1][0])

    for row in rows:    
        print "%2s %3s" % row

预处理语句

译者注：关于Prepared Statement我了解的并不是很多，起初我还以为仅仅简单的一个API层次的变量替换，为了编程方便而已，后来上网一搜好像并不是我理解的那么简单，这篇文章讲得比较容易理解。数据库的知识比较匮乏，应该好好学习~。Prepared Statement到底应该翻译成什么，我不是该领域的专业人士，就暂时译成“预处理语句”吧。

现在我们开始把重心放到预处理语句上。当写预处理语句时，我们会在语句中使用占位符，而不是直接将值填到语句当中去。预处理语句提高了安全性和性 能。在Python的标准数据库API文档中提供了五种不同的方式来构建预处理语句。MySQLdb支持其中的一种形式——符合ANSI标准的 printf格式串。

译者注：在PySqlite学习笔记我也提到过这五种形式，有兴趣的可以看看。

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

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:    

    cur = con.cursor()

    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))        

    print "Number of rows updated: %d" % cur.rowcount

修改第4行作者的名字。

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Guy de Maupasant", "4"))

以上代码展示了如何使用预处理语句，我们用到了两个%s占位符。在SQL语句被执行之前，相应的占位符会被替换成实际的值。

mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name             |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)

可见，位于表第四行的作者的名字被成功更改了。

写入图片

一些人喜欢将他们的图片放到数据库中，也有一些人是将图片保留在文件系统中。当处理数以百万计的图片时，我们往往会遇到一些技术的困难。图片是一种 二进制数据。MySQL中有一种特殊的数据类型用来存储二进制数据，它和名称叫做BLOB（Binary Large Object，二进制大对象）。

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)

为此，我们创建一个新的数据库表名称为Images。

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

import MySQLdb as mdb
import sys

try:
    fin = open("chrome.png")
    img = fin.read()
    fin.close()

except IOError, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

try:
    conn = mdb.connect(host='localhost',user='testuser',
       passwd='test623', db='testdb')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Images SET Data='%s'" % \
        mdb.escape_string(img))

    conn.commit()

    cursor.close()
    conn.close()

except mdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

在上面的例子中，我们读取一张png格式的图片并将其插入到Images表中。

fin = open("chrome.png")
img = fin.read()

我们打开图片文件并读入，read()函数返回字符串形式的数据。

cursor.execute("INSERT INTO Images SET Data='%s'" % \
    mdb.escape_string(img))

字符串数据被插入到表中，不过在此之前，它需要经过escape_string()方法处理。转义字符串作为查询参数使用。这是常见的做法，以避免恶意的SQL注入攻击。

读取图片

在上一个例子中，我们已经将一张图片插入到数据库表中。现在，我准备将图片从数据库中重新取回来。

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

import MySQLdb as mdb 
import sys

try:
    conn = mdb.connect(host='localhost',user='testuser', 
        passwd='test623', db='testdb')

    cursor = conn.cursor()

    cursor.execute("SELECT Data FROM Images LIMIT 1")

    fout = open('image.png','wb')
    fout.write(cursor.fetchone()[0])
    fout.close()

    cursor.close()
    conn.close()

except IOError, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

我们从Images表中读取一张图片。

cursor.execute("SELECT Data FROM Images LIMIT 1")

通过select语句从表中查询一条记录。

fout = open('image.png','wb')

然后，打开一个可写入的二进制文件。

fout.write(cursor.fetchone()[0])

我们将刚通过SQL语句从数据库表中取妯的数据写入到文件中。现在我们已经在当前目录保存了一张名为image.png的图片，可以对比下和原来插入表中的图片是否一致。

事务支持

事务是指在一个或者多个数据库中对数据的原子操作。在一个事务中，所有SQL语句的影响要么全部提交到数据库，要么就全部回滚。

对于支持事务机制的数据库，Python接口在创建游标时，默默地开始一个事务。可以通过游标对象的commit()方法提交游标操作引起的所有更新操作，或者使用rollback()方法丢弃这么更新操作。每个方法执行完后都会开始一个新的事务。

MySQL数据库有多种不同的存储引擎。其中最常见的是MyISAM和InnoDB引擎，而MyISAM是默认的一个。需要在数据的安全性和数据库 的速度之间作一个权衡。MyISAM表处理速度比较快，但是它们不支持事务，所以commit()和rollback()方法也没有实现，这时调用这些方 法不会做任何事情。另外一方面，InnoDB在预防数据丢失方面比较安全，它们支持事务，但是处理速度相对比较慢。

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

import MySQLdb as mdb
import sys

try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cursor = conn.cursor()

    cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Leo Tolstoy", "1"))       
    cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Boris Pasternak", "2"))
    cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 
        ("Leonid Leonov", "3"))   

    conn.commit()

    cursor.close()
    conn.close()

except mdb.Error, e:

    conn.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

在这个脚本中，我们试图更新三行数据。表存储引擎为MyISAM。

cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Leo Tolstoy", "1"))       
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Boris Pasternak", "2"))

这里我们更改了第一行和第二行的作者名字。

cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 
    ("Leonid Leonov", "3"))

上面的SQL语句会执行出错，因为不存在名为Writer的表。

conn.rollback()

SQL语句执行出错后抛出异常，在异常处理中调用rollback()方法，但是事实上它并没有起作用。

$ ./isam.py
Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Leo Tolstoy       |
|  2 | Boris Pasternak   |
|  3 | Lion Feuchtwanger |
|  4 | Guy de Maupasant  |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

通过运行这个脚本，结果正如我们预期的一样，首两行已经被更改。

译者注： 我在我的电脑上试了，最终没有被修改？我不确定是哪的问题。

在本教程的最后一个例子中，我们打算重新创建Writers表。这一次，表的类型将会是InnoDB。MySQL InnoDB数据库表支持事务。

DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');

将以上内容保存到writers.sql文件中，它被用来重新创建Writers表。

mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.10 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)

并在MySQL命令行中使用source命令加载并执行这些语句。

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

import MySQLdb as mdb
import sys

try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cursor = conn.cursor()

    cursor.execute("DELETE FROM Writers WHERE Id = 5")  
    cursor.execute("DELETE FROM Writers WHERE Id = 4") 
    cursor.execute("DELETE FROM Writer WHERE Id = 3") 

    conn.commit()

except mdb.Error, e:

    conn.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

cursor.close()
conn.close()

这次我们执行以上的脚本，脚本中会从数据库表中删除三行数据，当执行到第三行时会出错。

$ ./innodb.py
Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

错误发生在我们提交更改之前，rollback()方法被调用，从而实际上没有删除操作发生。