MySQL Python
最后修改于 2020 年 7 月 6 日
这是 MySQL Python 编程教程。它涵盖了使用 Python 进行 MySQL 编程的基础知识。本教程使用 MySQLdb 模块。示例在 Ubuntu Linux 上创建和测试。
MySQLdb 是一个用于 MySQL 的 Python 2 旧版数据库模块。对于现代的 Python 3 MySQL 编程，请使用 PyMySQL 模块。请参阅 PyMySQL 教程。
关于 MySQL 数据库
MySQL 是一个领先的开源数据库管理系统。它是一个多用户、多线程的数据库管理系统。MySQL 在 Web 上尤其流行。它是非常流行的 LAMP 平台的一部分，该平台由 Linux、Apache、MySQL 和 PHP 组成。目前 MySQL 由 Oracle 公司所有。
MySQL 数据库可在大多数主流操作系统平台上使用。它可以在 BSD Unix、Linux、Windows 或 Mac OS 上运行。维基百科和 YouTube 都在使用 MySQL。这些网站每天管理数百万次查询。MySQL 有两个版本：MySQL 服务器系统和 MySQL 嵌入式系统。
安装 MySQLdb
$ 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 命令来查找它。
$ sudo apt-get install python-mysqldb
这里我们安装用于 MySQL 数据库的 Python 接口；包括 _mysql 和 mysql 两个模块。
安装 MySQL
如果您尚未安装 MySQL，我们必须先安装它。
$ sudo apt-get install mysql-server
此命令会安装 MySQL 服务器及其他各种软件包。在安装软件包时，系统会提示我们为 MySQL root 账户输入密码。
接下来，我们将创建一个新的数据库用户和一个新的数据库。我们使用 mysql 客户端。
$ mysql -u root -p Enter password: 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 DB API 接口不兼容。通常，程序员更喜欢面向对象的 MySQLdb 模块。我们将关注后者。这里我们只展示一个使用 _mysql 模块的小例子。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import _mysql
import sys
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()
该示例将获取并打印 MySQL 数据库的版本。为此，我们使用 SELECT VERSION SQL 语句。
MySQLdb 模块
MySQLdb 是对 _mysql 的一个轻量级 Python 封装。它与 Python DB API 兼容，这使得代码更具可移植性。使用此模块是操作 MySQL 的首选方式。
MySQLdb 版本示例
在第一个示例中，我们将获取 MySQL 数据库的版本。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys
try:
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
cur = con.cursor()
cur.execute("SELECT VERSION()")
ver = cur.fetchone()
print "Database version : %s " % ver
except mdb.Error, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
finally:
if con:
con.close()
在此脚本中，我们连接到 testdb 数据库并执行 SELECT VERSION 语句。这将返回 MySQL 数据库的当前版本。我们将其打印到控制台。
import MySQLdb as mdb
我们导入 MySQLdb 模块。
con = mdb.connect('localhost', 'testuser',
'test623', 'testdb');
我们使用 connect 方法连接到数据库。我们传递四个参数：主机名、数据库用户名、密码和数据库名。
cur = con.cursor()
cur.execute("SELECT VERSION()")
从连接中，我们获取游标对象。游标用于遍历结果集中的记录。我们调用游标的 execute 方法并执行 SQL 语句。
ver = cur.fetchone()
我们获取数据。由于我们只检索一条记录，我们调用 fetchone 方法。
print "Database version : %s " % ver
我们将检索到的数据打印到控制台。
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.7.23-0ubuntu0.16.04.1
这是一个示例输出。
MySQLdb 创建并填充表
我们创建一个表并用一些数据填充它。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS Writers")
cur.execute("CREATE TABLE 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 表，并向其中添加五位作者。
with con:
使用 with 关键字，Python 解释器会自动释放资源。它还提供错误处理。
cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
Name VARCHAR(25))")
这条 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 表中查询所有数据。
MySQLdb fetchall
fetchall 方法获取查询结果集的所有（或所有剩余的）行，并返回一个元组列表。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
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 表中检索所有数据。
cur.execute("SELECT * FROM Writers")
这条 SQL 语句从 Writers 表中选择所有数据。
rows = cur.fetchall()
fetchall 方法获取所有记录。它返回一个结果集。从技术上讲，它是一个元组的元组。每个内部元组代表表中的一行。
for row in rows:
print row
我们将数据逐行打印到控制台。
$ ./fetch_all.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
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor()
cur.execute("SELECT * FROM Writers")
for i in range(cur.rowcount):
row = cur.fetchone()
print row[0], row[1]
我们再次将 Writers 表中的数据打印到控制台。这一次，我们逐行获取。
for i in range(cur.rowcount):
row = cur.fetchone()
print row[0], row[1]
我们使用 fetchone 方法逐行获取。rowcount 属性给出 SQL 语句返回的行数。
$ ./fetch_onebyone.py 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola 5 Truman Capote
这是示例的输出。
MySQLdb 字典游标
MySQLdb 模块中有多种游标类型。默认游标以元组的元组形式返回数据。当我们使用字典游标时，数据以 Python 字典的形式发送。这样我们就可以通过列名来引用数据。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor(mdb.cursors.DictCursor)
cur.execute("SELECT * FROM Writers LIMIT 4")
rows = cur.fetchall()
for row in rows:
print row["Id"], row["Name"]
在这个例子中，我们使用字典游标获取 Writers 表的前四行。
cur = con.cursor(mdb.cursors.DictCursor)
我们使用 DictCursor 字典游标。
cur.execute("SELECT * FROM Writers LIMIT 4")
我们从 Writers 表中获取四行数据。
for row in rows:
print row["Id"], row["Name"]
我们通过 Writers 表的列名来引用数据。
$ ./dictcur.py 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola
示例输出。
MySQLdb 列标题
接下来我们将展示如何将数据库表中的数据与列标题一起打印出来。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor()
cur.execute("SELECT * FROM Writers LIMIT 5")
rows = cur.fetchall()
desc = cur.description
print "%s %3s" % (desc[0][0], desc[1][0])
for row in rows:
print "%2s %3s" % row
我们再次将 Writers 表的内容打印到控制台。现在，我们也包含了列的名称。列名被认为是“元数据”。它是从游标对象中获取的。
desc = cur.description
游标的 description 属性返回有关查询结果中每一列的信息。
print "%s %3s" % (desc[0][0], desc[1][0])
这里我们打印并格式化表列名。
for row in rows:
print "%2s %3s" % row
在这里，我们遍历并打印数据。
$ ./columnheaders.py Id Name 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola 5 Truman Capote
这是输出。
MySQLdb 预处理语句
当我们编写预处理语句时，我们使用占位符而不是直接将值写入语句中。预处理语句可以提高安全性和性能。Python DB API 规范建议了五种不同的构建预处理语句的方法。MySQLdb 模块支持其中一种，即 ANSI printf 格式代码。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
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:", cur.rowcount
我们更改第四行作者的姓名。
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Guy de Maupasant", "4"))
我们使用由 %s 标记标识的两个占位符。在 SQL 语句执行之前，值会绑定到它们的占位符上。
$ ./prepared.py Number of rows updated: 1
我们更新了一行。
mysql> SELECT Name FROM Writers WHERE Id=4; +------------------+ | Name | +------------------+ | Guy de Maupasant | +------------------+ 1 row in set (0.00 sec)
第四行的作者已成功更改。
MySQLdb 插入图片
人们经常寻找将图片插入数据库的方法。我们将展示如何在 SQLite 和 Python 中完成此操作。请注意，有些人不建议将图片放入数据库。图片是二进制数据。MySQL 数据库有一个特殊的数据类型来存储二进制数据，称为 BLOB（二进制大对象）。TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 是二进制对象类型的变体。
mysql> CREATE TABLE Images(Id INT PRIMARY KEY, Data MEDIUMBLOB); Query OK, 0 rows affected (0.08 sec)
对于这个例子，我们创建一个名为 Images 的新表。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
def read_image():
with open("sid.jpg") as f:
img = f.read()
return img
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor()
data = read_image()
cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))
在上面的脚本中，我们从磁盘读取一个 JPG 图片，并将其插入到 Images 表中。
def read_image():
with open("sid.jpg") as f:
img = f.read()
return img
read_image 方法从位于当前工作目录的 JPG 文件中读取二进制数据。
cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))
我们将图片数据插入到 Images 表中。
MySQLdb 读取图片
在前面的例子中，我们已经将一张图片插入到数据库表中。现在我们将从表中把图片读回来。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
def writeImage(data):
fout = open('sid2.jpg', 'wb')
with fout:
fout.write(data)
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor()
cur.execute("SELECT Data FROM Images WHERE Id=1")
data = cur.fetchone()[0]
writeImage(data)
我们从 Images 表中读取一张图片。
cur.execute("SELECT Data FROM Images WHERE Id=1")
我们从表中选择一条记录。
fout = open('sid2.jpg', 'wb')
我们打开一个可写的二进制文件。
fout.write(data)
我们将数据写入磁盘。
现在我们当前目录下应该有一张名为 woman2.jpg 的图片。我们可以检查它是否与我们插入到表中的图片相同。
MySQLdb 事务支持
事务是针对一个或多个数据库中的数据执行的数据库操作的原子单元。事务中所有 SQL 语句的效果可以要么全部提交到数据库，要么全部回滚。
对于支持事务的数据库，Python 接口在创建游标时会静默地启动一个事务。commit 方法提交使用该游标所做的更新，而 rollback 方法则丢弃它们。每个方法都会启动一个新的事务。
MySQL 数据库有不同类型的存储引擎。最常见的是 MyISAM 和 InnoDB 引擎。自 MySQL 5.5 起，InnoDB 成为默认存储引擎。在数据安全性和数据库速度之间存在权衡。MyISAM 表处理速度更快，但不支持事务。commit 和 rollback 方法未实现，它们不起作用。另一方面，InnoDB 表在防止数据丢失方面更安全。它们支持事务，但处理速度较慢。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys
try:
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS Writers")
cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
Name VARCHAR(25)) ENGINE=INNODB")
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')")
cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")
con.commit()
except mdb.MySQLError, e:
if con:
con.rollback()
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
finally:
if con:
con.close()
我们重新创建 Writers 表。我们显式地处理事务。
cur = con.cursor()
在 Python DB API 中，我们不调用 BEGIN 语句来启动事务。事务在创建游标时启动。
cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \
Name VARCHAR(25)) ENGINE=INNODB")
我们正在处理一个 InnoDB 表类型。对于旧版本的 MySQL (<5.5)，我们需要使用 ENGINE=INNODB 选项指定引擎类型。
con.commit()
我们必须使用 commit 或 rollback 方法来结束一个事务。如果我们注释掉这行代码，表会被创建，但数据不会写入表中。
在本教程中，我们一直在使用事务而没有明确声明它们。我们使用了上下文管理器。上下文管理器处理进入和退出代码块执行所需的运行时上下文。上下文管理器通常通过 with 语句调用。
MySQLdb 模块中的连接对象可以用作上下文管理器。它们会自动提交或回滚事务。连接上下文管理器通过分解出 try、except 和 finally 语句来清理代码。
#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
con = mdb.connect('localhost', 'testuser', 'test623', 'testdb')
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS Writers")
cur.execute("CREATE TABLE 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')")
cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")
在我们的代码示例中，上下文管理器处理了所有必要的错误处理工作。它会自动提交或回滚事务。
这是 MySQL Python 教程。它使用了旧版的 MySQLdb 模块来操作 MySQL。要使用现代的 PyMySQL 模块，请参阅 PyMySQL 教程。