Python操作MySQL
Python操作MySQL
关于MySQL数据库
开始之前
_mysql模块
MySQLdb模块
1. 第一个例子
2. 创建并填充表
3. 取回数据
4. 字典游标
5. 列标题
6. 预处理语句
7. 写入图片
8. 读取图片
9. 事务支持
10. 使用SQLAlchemy
介绍用Python语言操作MySQL数据库，覆盖了基础的Python MySQL编程内容。使用MySQLdb模块。
关于MySQL数据库
MySQL是一个领先的开源数据库管理系统。它是一个多用户，多线程的数据库管理系统。MySQL在网上非常流行，它与Linux、Apache、PHP一起组成LAMP平台，这是一个非常流行的Web平台。目前，MySQL是由甲骨文拥有。MySQL数据库可用于最重要的几个操作系统上。它能够运行在BSD UNIX，Linux，Windows或Mac操作系统之上。
Wikipedia和YouTube均使用MySQL数据库。这些站点每天处理数百万的查询次数。MySQL包含服务器系统和嵌入式系统两个版本。
开始之前
需要安装几个软件包，才能确保可以正确地执行。如果没有安装MySQL，必须先安装它：
$ sudo apt-get install mysql-server
以上命令会安装MySQL服务器，还包括一些相关的软件包。
装好MySQL数据库的Python编程接口，MySQLdb模块。
接下来，我们会为本文的例子创建一个新的数据库和数据库用户，我们使用MySQL客户端来操作：
$ 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)
使用root账号连接到数据库上，并用通过SHOW DATABASES语句列出所有数据库名称。
mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)
创建一个新的数据库testdb，在本文的例子中都会用到这个数据库。
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
创建一个新的数据库用户，并且将testdb数据库中的所有表的操作权限全部授权给这个用户。
_mysql模块
_mysql模块直接封装实现了MySQL的C语言API函数。它与Python标准的数据库API接口不兼容。通常，程序员都更加喜欢面向对象的MySQLdb模块。我们也会更加关注后一个模块。在这里，只是给出一个使用_mysql模块编程的小例子。
my-1.py
获取并打印MySQL数据库的版本，为此使用SELECT VERSION()这条SQL语句。
MySQLdb模块
MySQLdb是在_mysql模块的基础上进一步进行封装，并且与Python的标准数据库API接口兼容，这使得代码更易于移植。使用这个模块是操作MySQL数据库的首选方法。
1. 第一个例子
在第一个例子中，我们来获取MySQL数据库的版本。
my-2.py
连接到testdb数据库并执行 SELECT VERSION()语句。该脚本返回MySQL数据库的版本号，并打印到终端。
import MySQLdb as mdb
我们导入MySQLdb模块。
con = None
我们将con变量初始化成None。在无法创建数据库连接的时候（例如硬盘空间已满），我们不能定义该数据库连接变量。这将会导致在finally语句中执行出错。
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');
我们连接到数据库中，其中connect()方法有四个参数。第一个是MySQL数据库所在的主机地址，在我们的例子中为'localhost'；第二个参数是数据库的用户名，其实是该用户的密码；最后一个参数是数据库的名称。
cur = con.cursor()
cur.execute("SELECT VERSION()")
一旦连接成为，我们将会得到一个cursor（游标）对象。这个cursor对象用来遍历结果集中的记录。我们通过调用该curor对象的execute()方法来执行SQL语句。
data = cur.fetchone()
我们开始获取数据，由于我们只取回一个记录，因此我们调用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()
最后一步，我们释放连接的资源。
2. 创建并填充表
创建一个表，并在其中填入一些数据。
my-3.py
在这里使用了with..的语法，在退出with语句之前，会执行con对象的__exit__方法，猜测在该方法中会调用commit方法提交事务。
创建一张记录作家名字的数据库表，并往其中添加五位作者。
cur.execute("CREATE TABLE IF NOT EXISTS \
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25)) ENGINE=MyISAM;")
这条SQL语句创建了一个名为Writers的新数据库，它包含Id和Name两列。
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...
使用INSERT语句插入作者到表中。
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)
在脚本执行之后，我们通过MySQL的客户端工具来查询Writers表中的所有数据。
3. 取回数据
现在，在往表中插入一些数之后，我们想要将数据取回。
my-4.py
从Writers表中取回所有数据。
cur.execute("SELECT * FROM Writers")
这条SQL语句从Writers表中查询所有的数据。
rows = cur.fetchall()
fetchall()方法获取所有记录。它返回一个结果集，该结果集其实是一个包含元组的元组。在元组内部的第一个元组代码表中的一行。
for row in rows:
print row
我们以行的方式将数据打印到终端。
一次性返回所有的数据未必可行，我们可以取逐行依次取回。
my-5.py
我们仍然从Writers表中取回数据，并打印到终端。这次我们逐行获取数据。
numrows = int(cur.rowcount)
这里我们首先确认SQL语句查询结果中包含的行数。
for i in range(numrows):
row = cur.fetchone()
print row[0], row[1]
使用fetchone()方法逐行获取数据。
4. 字典游标
在 MySQLdb 模块中有许多种游标类型。默认的游标类型以元组的元组形式返回数据。当我们使用字典游标时，这些数据是以Python字典的形式返回。这样一来，我们就可以通过列名来访问数据。
my-6.py
使用字典游标来打印Writers表的内容。
cur = con.cursor(mdb.cursors.DictCursor)
创建字典游标。
rows = cur.fetchall()
然后返回所有的数据。
for row in rows:
print "%s %s" % (row["Id"], row["Name"])
接着，使用Writers表的列名来引用相应的数据。
5. 列标题
接下来，展示如何将列标题同表数据一同打印出来。
my-7.py
6. 预处理语句
现在我们开始把重心放到预处理语句上。当写预处理语句时，我们会在语句中使用占位符，而不是直接将值填到语句当中去。预处理语句提高了安全性和性能。在Python的标准数据库API文档中提供了多种不同方式来构建预处理语句。MySQLdb支持其中的一种形式——符合ANSI标准的printf格式串。
my-8.py
修改第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)
可见，位于表第四行的作者的名字被成功更改了。
7. 写入图片
一些人喜欢将他们的图片放到数据库中，也有一些人是将图片保留在文件系统中。当处理数以百万计的图片时，我们往往会遇到一些技术的困难。图片是一种二进制数据。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。
my-9.py
读取一张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注入攻击。
8. 读取图片
在上一个例子中，我们已经将一张图片插入到数据库表中。现在，我准备将图片从数据库中重新取回来。
my-10.py
从Images表中读取一张图片。
cursor.execute("SELECT Data FROM Images LIMIT 1")
通过select语句从表中查询一条记录。
fout = open('image.png','wb')
然后，打开一个可写入的二进制文件。
fout.write(cursor.fetchone()[0])
将刚通过SQL语句从数据库表中取的数据写入到文件中。现在我们已经在当前目录保存了一张名为image.png的图片，可以对比下和原来插入表中的图片是否一致。
9. 事务支持
事务是指在一个或者多个数据库中对数据的原子操作。在一个事务中，所有SQL语句的影响要么全部提交到数据库，要么就全部回滚。
对于支持事务机制的数据库，Python接口在创建游标时，默默地开始一个事务。可以通过游标对象的commit()方法提交游标操作引起的所有更新操作，或者使用rollback()方法丢弃这么更新操作。每个方法执行完后都会开始一个新的事务。
MySQL数据库有多种不同的存储引擎。其中最常见的是MyISAM和InnoDB引擎，而MyISAM是默认的一个。需要在数据的安全性和数据库的速度之间作一个权衡。MyISAM表处理速度比较快，但是它们不支持事务，所以commit()和rollback()方法也没有实现，这时调用这些方法不会做任何事情。另外一方面，InnoDB在预防数据丢失方面比较安全，它们支持事务，但是处理速度相对比较慢。
my-11.py
在这个脚本中，我们试图更新三行数据。表存储引擎为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()方法，但是事实上它并没有起作用。
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命令加载并执行这些语句。
my-12.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()方法被调用，从而实际上没有删除操作发生。
10. 使用SQLAlchemy
数据库表是一个二维表，包含多行多列。把一个表的内容用Python的数据结构表示出来的话，可以用一个list表示多行，list的每一个元素是tuple，表示一行记录，比如，包含id和name的user表：
[
('1', 'Michael'),
('2', 'Bob'),
('3', 'Adam')
]
Python的DB-API返回的数据结构就是像上面这样表示的。
但是用tuple表示一行很难看出表的结构。如果把一个tuple用class实例来表示，就可以更容易地看出表的结构来：
class User(object):
def __init__(self, id, name):
self.id = id
self.name = name
[
User('1', 'Michael'),
User('2', 'Bob'),
User('3', 'Adam')
]
这就是传说中的ORM技术：Object-Relational Mapping，把关系数据库的表结构映射到对象上。是不是很简单？
但是由谁来做这个转换呢？所以ORM框架应运而生。
在Python中，最有名的ORM框架是SQLAlchemy。我们来看看SQLAlchemy的用法。
首先通过pip安装SQLAlchemy：
$pip install sqlalchemy
然后，在MySQL的testdb数据库中创建user表：
create table user (id varchar(20) primary key, name varchar(20));
insert into user (id, name) values ('1','Liao');
insert into user (id, name) values ('2','Zhao');
insert into user (id, name) values ('3','Qian');
select * from user;
使用SQLAlchemy：my-13.py
第一步，导入SQLAlchemy，并初始化DBSession：
# 导入:
from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 创建对象的基类:
Base = declarative_base()
# 定义User对象:
class User(Base):
# 表的名字:
__tablename__ = 'user'
# 表的结构:
id = Column(String(20), primary_key=True)
name = Column(String(20))
# 初始化数据库连接:
engine = create_engine('mysql+mysqldb://root:liaoxiaofei999@localhost:3306/testdb')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
以上代码完成SQLAlchemy的初始化和具体每个表的class定义。如果有多个表，就继续定义其他class，例如School：
class School(Base):
__tablename__ = 'school'
id = ...
name = ...
create_engine()用来初始化数据库连接。SQLAlchemy用一个字符串表示连接信息：
'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
你只需要根据需要替换掉用户名、口令等信息即可。
下面，我们看看如何向数据库表中添加一行记录。
由于有了ORM，我们向数据库表中添加一行记录，可以视为添加一个User对象：
# 创建session对象:
session = DBSession()
# 创建新User对象:
new_user = User(id='5', name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()
可见，关键是获取session，然后把对象添加到session，最后提交并关闭。Session对象可视为当前数据库连接。
如何从数据库表中查询数据呢？有了ORM，查询出来的可以不再是tuple，而是User对象。SQLAlchemy提供的查询接口如下：
创建Session:
session = DBSession()
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter(User.id=='5').one()
# 打印类型和对象的name属性:
print 'type:', type(user)
print 'name:', user.name
# 关闭Session:
session.close()
运行结果如下：
type: <class '__main__.User'>
name: Bob
可见，ORM就是把数据库表的行与相应的对象建立关联，互相转换。
由于关系数据库的多个表还可以用外键实现一对多、多对多等关联，相应地，ORM框架也可以提供两个对象之间的一对多、多对多等功能。
例如，如果一个User拥有多个Book，就可以定义一对多关系如下：
class User(Base):
__tablename__ = 'user'
id = Column(String(20), primary_key=True)
name = Column(String(20))
# 一对多:
books = relationship('Book')
class Book(Base):
__tablename__ = 'book'
id = Column(String(20), primary_key=True)
name = Column(String(20))
# “多”的一方的book表是通过外键关联到user表的:
user_id = Column(String(20), ForeignKey('user.id'))
当我们查询一个User对象时，该对象的books属性将返回一个包含若干个Book对象的list。
因此，ORM框架的作用就是把数据库表的一行记录与一个对象互相做自动转换。
正确使用ORM的前提是了解关系数据库的原理。
