MYSQL Python 入门教程

Posted on 2012-03-29 17:37  少林  阅读(...)  评论(... 编辑 收藏

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()回滚,通过验证我们发现数据库并没有更改。

 

 

posts - 70, comments - 306, trackbacks - 0, articles - 4

Copyright © 2019 少林
Powered by .NET Core 3.1.0 on Linux