MYSQL Python 入门教程

MYSQL Python 入门教程

（一）关于本教程

这是一本关于Python中使用Mysql编程的入门教程，它讲述了Python中使用Mysql进行编程的基本知识。主要使用MYSQLdb模块。本教程中的所有实例是在ubuntu上开发和测试的。

（二）关于MYSQL数据库

MYSQL是一个领先的开源数据管理系统，是一个多用户、多线程的数据库系统。MYSQL在web应用中特别流行，是非常流行的LAMP（L-linux, A-apache, M-mysql, P-php）平台中的一部分。MYSQL最早是由瑞典的MYSQL AB公司所开发， 这家公司以及Trolltech 是非常有名的开放源代码公司。MYSQL兼容大多数操作系统平台，包括BSD Unix、Linux、Windows以及MAC。维基百科和YouTube使用MYSQL，这些网站每天处理上百万的查询请求。MYSQL包括两个版本：MYSQL服务端版本和MYSQL嵌入式版本。

 

（三）开始前的准备

在本教材开始之前，我们先搭建开发环境。

如果你没有安装mysql数据库，使用下面命令进行安装：

sudo apt-get install mysql-server

该命令安装Mysql数据库以及各种依赖库，在安装过程中，我们可以先想好Mysql数据库的root密码。

$ apt-cache search MySQLdb

python-mysqldb - A Python interface to MySQL

python-mysqldb-dbg - A Python interface to MySQL (debug extension)

bibus - bibliographic database

eikazo - graphical frontend for SANE designed for mass-scanning

如果我们不太清楚MYSQLdb模块在哪个安装包中，可以使用apt-cache search命令找到要具体的安装包，如上所示，我们要安装的是python-mysqldb模块。使用下面命令安装：

sudo apt-get install python-mysqldb

接下来，我们使用mysqlclient建立一个新的数据库以及数据库用户，首先我们连接到myql数据库中，如下所示：

$ mysql -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 30

Server version: 5.0.67-0ubuntu6 (Ubuntu)

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema | 

| mysql              | 

+--------------------+

2 rows in set (0.00 sec)

然后创建数据库testdb，命令如下：

mysql> CREATE DATABASE testdb;

Query OK, 1 row affected (0.02 sec)

最后我们创建一个'testuser'的测试用户，并予以相应的权限，命令如下：

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623';

Query OK, 0 rows affected (0.00 sec)

 

mysql> USE testdb;

Database changed

 

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';

Query OK, 0 rows affected (0.00 sec)

 

mysql> quit;

Bye

至此，我们完成了开发环境的搭建。

（四）_Mysql 模块

_Mysql模块实现了Mysql C API库，但是它和python的DB API接口不兼容。一般来说，程序员更喜欢面向对象的Mysqldb模块，Mysqldb后面的章节会详细的讲述。本节只给出_Mysql模块的一个例子，代码如下：

#!/usr/bin/python

# -*- coding: utf-8 -*-

import _mysql

import sys

 

con = None

try:

    con = _mysql.connect('localhost', 'testuser', 

        'test623', 'testdb')     

    con.query("SELECT VERSION()")

    result = con.use_result()

    print "MySQL version: %s" % \

        result.fetch_row()[0]

except _mysql.Error, e:

 

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

    sys.exit(1)

 

finally:

    if con:

        con.close()

 

（五）Mysqldb 模块

MyqslDb是_Mysql模块的轻度封装，它兼容Python DB API接口，因此具有更好的移植性。使用MyqlDb进行mysql编程是非常方便。

（六）第一个实例

在这个示例中，我们会打印Mysql的数据库版本，代码如下：

#!/usr/bin/python

# -*- coding: utf-8 -*-

import MySQLdb as mdb

import sys

con = None

 

try:

    con = mdb.connect('localhost', 'testuser', 

        'test623', 'testdb');

 

    cur = con.cursor()

    cur.execute("SELECT VERSION()")

    data = cur.fetchone()

    print "Database version : %s " % data

 

except mdb.Error, e:

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

    sys.exit(1)

finally:    

    if con:    

        con.close()

在上面的脚本中，我们连接到mysql数据库中，并执行select version() 语句，该语句将返回mysql数据库的当前版本，我们把它输出到控制台。

首先我们引入MysqlDB模块，代码如下：

import MySQLdb as mdb

然后初始化con变量为None，接着链接到mysql数据库，代码如下：

con = mdb.connect('localhost', 'testuser', 

    'test623', 'testdb');

Connect方法有四个参数，第一个参数是主机名，第二个参数是数据库的用户名，第三个参数是用户名的密码，最后一个参数是要链接的数据库名。

cur = con.cursor()

cur.execute("SELECT VERSION()")

从con链接变量，我们获取一个cur游标变量。游标是用来遍历结果集中的记录，我们调用游标的execute方法执行sql语句。

data = cur.fetchone()

我们取回数据，因为该结果集只有一行数据，所以为我们调用facthone方法。

print "Database version : %s " % data

调用print把结果输出到屏幕上。

except mdb.Error, e:

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

    sys.exit(1)

检查数据库错误是非常重要的，因为数据库易于出现问题的。

finally:    

    if con:    

        con.close()

最后关闭链接。

执行本脚本，可以得到类似下面的输出：

$ ./version.py

Database version : 5.5.9 

 

（七）新建表并插入数据

示例代码如下：

#!/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("CREATE TABLE IF NOT EXISTS \

        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")

    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")

    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

我们建立一个Writers的表，并且插入了5条记录。相关代码如下：

cur.execute("CREATE TABLE IF NOT EXISTS \

        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")

cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")

通过执行脚本后，数据库testdb中的输入如下所示：

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)

 

（八）取回数据

上节我们在数据库testddb中插入了5条记录，本节将演示如何把这些数据取回来。本节所有代码如下所示：

#!/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()

    for row in rows:

        print row

 

以下代码把表Writers中的所有数据都取回来。

rows = cur.fetchall()

Fetchall()函数将取回表中的所有记录，返回一个结果集。它是一个元组的元组。元组内嵌的每个元组代表数据库中的一行数据。

for row in rows:

    print row

我们一行行的把数据打印到屏幕上。执行脚本，可以得到类似一下的输出：

$ ./retrieve.py

(1L, 'Jack London')

(2L, 'Honore de Balzac')

(3L, 'Lion Feuchtwanger')

(4L, 'Emile Zola')

(5L, 'Truman Capote')

一下子把所有数据全部取回来扩展性不是很好，我们改下程序，一行一行的从数据库中读取，代码如下：

#!/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")

    numrows = int(cur.rowcount)

    for i in range(numrows):

        row = cur.fetchone()

        print row[0], row[1]

首先我们游标的rowcount属性获取结果集中记录的条数，然后使用fetchone方法一行行的把数据取回，并输出到屏幕上。执行本脚本，可以得到一下类似的输出：

$ ./retrieve2.py

1 Jack London

2 Honore de Balzac

3 Lion Feuchtwanger

4 Emile Zola

5 Truman Capote

 

（九）字典游标

Mysqldb模块中有很多种游标类型，默认的游标以元组的元组方式返回数据。当我们使用字典游标时，数据将以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"])

在上面代码中，我们使用字典游标来操纵数据：

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

我们获取所有数据，并且使用列名的方式获取数据，输出到屏幕上：

rows = cur.fetchall()

for row in rows:

        print "%s %s" % (row["Id"], row["Name"])

 

（十）列名

下面示例代码演示了如何打印表的列名，代码如下：

#!/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

本例中，我们再次把writer表中的数据输出到屏幕上，不过这次我们输出了列名，列名是元数据，是cursor类型的属性：

desc = cur.description

Cursor类型的description属性查询中的每一列的相关信息，我们使用以下语句打印出列名：

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

整个程序输出结果如下：

$ ./columnheaders.py

 Id Name

 1 Jack London

 2 Honore de Balzac

 3 Lion Feuchtwanger

 4 Emile Zola

 5 Truman Capote

 

（十一）格式化语句

在本节我们使用格式化语句，Python的Mysql DB接口支持5中不同的格式化方式，但MysqlDB模块只支持其中的一种，即ANSI的printf格式化方式，代码如下：

#!/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

通过如下代码，我们id=4的作者的名字，

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 

    ("Guy de Maupasant", "4"))   

 

（十二）插入图片

有人喜欢使用mysql来存储图片，而有的人喜欢把图片存储在文件系统中。而当我们要处理成千上万的图片时，会引起技术问题。图片时二进制数据，mysql有种特殊的数据类型，用来存储二进制数据，叫做BLOB（Binary Large Ojbect）。

开始之前，我们创建一个images表用来存储图片数据，代码如下：

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);

Query OK, 0 rows affected (0.06 sec)

接着，我们读取图片数据，并把它插入到数据库中，示例代码如下：

#!/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)

首先，我们打开一个图片，并读取图片数据，代码如下：

fin = open("chrome.png")

img = fin.read()

接着，我们把图片数据插入到数据库中，并使用escape_string进行特殊字符串转义。代码如下：

cursor.execute("INSERT INTO Images SET Data='%s'" % \

    mdb.escape_string(img))

 

（十三）读取图片

上一节中，我们把图片存储到数据库中了，在本节，我们将取回并保存为图片文件。本节示例如下：

#!/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)

首先，我们从数据库中读取一张图片数据：

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

接着，我们以二进制的写方式打开一个文件，写入图片数据：

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

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

执行程序，我们当前目录应该有一张图片，验证一下是否和写入数据库之前的图片一致。

 

（十四）事务支持

事务是数据库中的原子操作，不管要操作的数据在一个或者多个数据中。事务中的所有sql语句的结果要么全部提交，要么全部回滚。

对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。Commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。

Mysq数据库有不同的存储引擎，其中最知名的引擎是MyISAM和 InnoDb引擎。MyISAM是Mysql的默认引擎。这是在数据安全和性能之前寻求一个平衡。MysqlISAM引擎的表处理性能很高，但是不支持事务。而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)

以上代码我们尝试更新三条记录，记住，Writers表的存储引擎是MyISAM，开始我们更新id等于1和2 的记录，代码如下：

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"))

然后我们更新id等于3的记录，但是我们相应的sql语句有词法错误，代码如下：

cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 

    ("Leonid Leonov", "3")) 

出现错误时，我们调用rollback（），但是它没有做任何事情：

conn.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)

但是Id=1和2的记录已经更新了，说明rollback并没有回滚。

在本章最后一个示例中，我们将重建writers表，这次我们使用InnoDB作为writers的存储引擎，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');

我们执行下面的脚本，用来验证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("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()

我们删除表中的三行数据，为了验证事务机制，我们的第三个sql语句存在语法错误，执行结果类似下面输出：

$ ./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)

Sql错误在我们commit之前发生，错误处理程序调用rollback（）回滚，通过验证我们发现数据库并没有更改。