MySQL Python 教程
(1)下面是在Python中使用MySql数据库的教程,涵盖了Python对MySql的基础操作,主要采用了MySQLdb模块,下面的代码都是基于Ubuntu Linux测试的。
(2)MySQL 数据库
MySQL是一套领先的开源的数据库管理系统,它是多用户,多线程的数据库系统,尤其是是在web领域非常著名,它是非常流行的LAMP的一部分, Linux, Apache, MySQL, PHP。当前,MySQL已经归属为Oracle,MySQL可以有效的运行在各个操作系统平台上, BSD Unix, Linux, Windows or Mac. Wikipedia 和 YouTube 业主使用MySQL,这些站点每天都支撑着数以亿计的查询,MySQL来源于MySQL server system 和MySQL embedded system.
(3)准备工作
首先要安装一些软件包,应用于我们接下来的工作。
#
sudo apt-get install mysql-server
上面的命令用于安装 mysql-server,在这个过程中会弹出提示要求输入管理员密码。
安装mysql以后,来安装mysql的python模块,
#
apt-cache search MySQLdb
如果不知道 MySQLdb 对于 Python 的模块,可以搜索下 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
接下来是安装 python-mysqldb
#
apt-get install python-mysqldb
这样我们就安装了 python 访问 mysql 的接口,包含了 _mysql 和 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帐号登录了数据库,接下来创建一个数据库
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)
为此数据库添加一个可访问的用户,用户名是 testuser,密码是 test623,并且给该用户授予了这个数据所有的操作权限
mysql> USE testdb;
Database changed
mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
退出
(4)_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()
上面这个例子展示了通过 _mysql 模块,获得数据库版本的一段代码。并且用了 SELECT VERSION() 这个语句。
(5)MySQLdb 模块
MySQLdb 是对 _mysql 的一个轻量级的包装。兼容了 Python DB API,让代码更加便捷,利用该模块与 mysql 一起是首选。
1、 获得数据的版本
#!/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()
在这个例子中,我们连接了 testdb 数据库,并且执行了 SELECT VERSION() 这个语句,并且返回了数据的版本,把它输出显示在屏幕上。
#
import MySQLdb as mdb
import 模块 MySQLdb
#
con = None
初始化变量 con, 并且赋值为 None
在有些情况下,我们可能不能创建一个连接去连接数据库,例如磁盘满了的情况下,那么我们没有连接被定义,这样将会在 finally 语句中产生错误。
#
con = mdb.connect('localhost', 'testuser',
'test623', 'testdb');
连接数据库,在这里 mdb 是 MySQLdb 的别名,在开始 import 模块的时候命名的,connect 方法有四个参数,第一个是数据库的地址,第二个是用户名,第三个是密码,第四个是要访问的数据库名称。
#
cur = con.cursor()
cur.execute("SELECT VERSION()")
从 con 我们获得了 cursor 对象,该对象用于从结果集(result set)中萃取记录,最后我们调用了改对象的 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、创建一个表,并且填充数据
#!/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')")
我们创建了一个表,并且添加了五条记录
#
cur.execute("CREATE TABLE IF NOT EXISTS \
Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
这个语句创建了一个数据表叫 Writers,该表有两列 ID 和 Name
#
cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
……
向数据表插入数据。
#
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 命令行工具,来查看下当前数据表中的数据
3、获取数据(retrieving data)
上面的例子中,我们已经向表中添加了一些数据,下面我们把他们取出来
#!/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
在上面的代码中,我们取出来所有的数据
#
cur.execute("SELECT * FROM Writers")
这个语句检索了 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')
4、有时候一次取出全部数据并不可取,我们可以一行一行的来取出,下面这个例子就是
#!/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]
我们还是和第3个例子一样,取出表中数据并输出,这次我们并没有采用 fetchall 方法,而是一步一步的取出来。
#
numrows = int(cur.rowcount)
获得返回结果集的记录数量
#
for i in range(numrows):
row = cur.fetchone()
print row[0], row[1]
通过 fetchone 来一步一步的取出数据,结果如下
$ ./retrieve2.py
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote
5、The dictionary cursor (这个我也不知道怎么翻译好,姑且翻译为 字典型游标 吧)
在 MySQLdb 里有多种 游标(cursor) 类型,默认返回的是由元组构成的元组,如果我们采用西典型游标,数据将会以字典的形式返回,这样返回的数据可以关联到数据库表的字段名
#!/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"])
关联到字段名称输出
6、Column headers
接下来的例子是通过 Column headers 的方式来从数据表中输出数据
#!/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
我们还是把整个表的数据输出出来,现在我们也包含了列的名字,列明被认为是 “meta data”,也是可以通过 cursor 对象获得的。
#
desc = cur.description
这个 cursor对象的[描述属性]返回了每个查询结果集列的信息。
#
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
7、Prepared statements (预编译语句?)
现在我们要来关注 prepared statements ,我们用占位符来替换一些语句中的变量,prepared statements 方式提高了性能和安全性,PYTHON DB API 规定了五种不同的方式来构建 prepared statements。MySQLdb 支持了其中的一种,ANSI printf format
#!/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改变数据库记录中一个人的名字
#
cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s",
("Guy de Maupasant", "4"))
%s 就是占位符
mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)
名字被成功的改写了。
8、写图片
有的人喜欢把图片存储到数据库中,有的人喜欢把图片存储在文件系统中,MYSQL 有一种数据类型就是用于存储二进制数据的,叫 BLOB
#
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)
上面的例子中,我们读取了一个图片,并且存储到了表中。
#
fin = open("chrome.png")
img = fin.read()
打开并且读取图片, read 方法返回一个字符串
#
cursor.execute("INSERT INTO Images SET Data='%s'" % \
mdb.escape_string(img))
将字符串插入到表中,在这个语句中调用了 escape_string() 方法,主要是为了方式 SQL 注入
9、读取图片
在前面的例子中,我们已经插入了一个图片到数据表中,下面将读取图片
#!/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])
写入数据
现在就可以看到当前的目录下多了一个 image.png 文件
10、事务支持
事务是数据库操作的一个原子单元,它影响的是数据库操作的一组记录,整体提交或者整体回滚,如果一个一组中的一个操作失败,那么就是整体回滚。MYSQL 中 MYISAM 引擎不支持事务操作,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"))
将两个ID分别为1和2的记录换Name
#
cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s",
("Leonid Leonov", "3"))
这是一个错误的语句,表名不是 Writers ,少了一个 s,
#
conn.rollback()
调用了 rollback 方法,但是并没有触发回滚操作,下面来看下操作结果
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 引擎,支持事务。
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');
来看下下面的脚本
#!/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()
执行上面的脚本,删除三条记录,但是第三条语句是错误的。所以执行了数据库回滚的操作,来看下最终的结果集
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)
(完)