初见Python<7>:Python操作mysql

1、基本介绍：

python标准数据库接口为python DB-API，它为开发人员提供了数据库应用编程接口,可以支持mysql、Oracle、MSSQL、Sybase等多种数据库，不同的数据库需要下载不同的DB-API模块。

DBI-API是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。

python DB-API的使用流程是：

①引入API模块；②获取与数据库的连接；③执行SQL语句；④关闭数据库连接。

2、什么是MySQLdb?

MySQLdb-python是用于Python链接Mysql数据库的接口程序，python通过它实现对mysql数据库的各种操作。

使用之前必须保证MySQLdb已经在本地机上安装（当然，mysql程序也是必须要安装的）。

直接在网站上下载完MySQLdb-python安装包，安装完成即可。

通过检查MySQLdb模块是否正常导入来判断MySQLdb-python程序是否安装成功。

没有报错就代表安装成功。注意python是区分大小写的。

3、连接数据库：

说明：通过connect函数连接数据库，里面需要提供主机地址、端口、用户名、密码、数据库名称等信息。

上报错的原因是本地机上mysql服务没有开启。这时需要在命令行中通过net start mysql命令启动mysql服务。

这时候通过上面的conn1对象，已经连接到数据库上，但是要操作数据库，还需要创建游标。

通过数据库连接对象的cursor方法来创建游标。

 1 #导入MySQLdb模块
 2 import MySQLdb
 3 
 4 #建立与数据库的连接
 5 conn1=MySQLdb.connect(host='localhost'      #主机名
 6                       ,port=3306            #端口
 7                       ,user='root'          #用户名
 8                       ,passwd='admin'       #密码
 9                       ,db='test123'         #数据库名
10                       )
11 #也可以简写成conn1=MySQLdb.connect('localhost','root','admin','test123')
12 
13 #创建游标
14 cur1=conn1.cursor()
15 
16 #对游标对象使用execute方法就可以执行普通的SQL语句
17 #做一次查询
18 cur1.execute('select * from table_a limit 5')
19 
20 #通过fetchall方法获取全部查询结果
21 result1=cur1.fetchall()
22 print result1
23 
24 #通过fetchone方法获取一条查询结果
25 result2=cur1.fetchone()
26 print result2
27 
28 #关闭游标
29 cur1.close()
30 
31 #断开数据库连接
32 conn1.close()

执行结果：

说明：打印的result1结果应当是五条记录，可以看出，通过fetchall()方法接收的查询结果被保存为元组，而元组的每一个元素代表一条记录，各个字段之间也组成了一个元组。

为什么打印的result2结果为None，这是因为，通过fetchall()方法进行结果获取时，游标已移到结果集末尾，这时候通过fetchone()方法获取记录时自然是空值。

在进行完数据库操作后，应当通过close方法关闭游标、断开数据库连接。

上面的例子简单的介绍了导入模块、建立连接、创建游标、执行SQL、显示查询结果集、断开连接等一些基本的通过python对mysql进行操作的方法和步骤。

下面将具体进行介绍。

4、创建表：

 1 #导入MySQLdb模块
 2 import MySQLdb
 3 
 4 #建立与数据库的连接
 5 conn2=MySQLdb.connect('localhost','root','admin','test123')
 6 
 7 #创建游标
 8 cur2=conn2.cursor()
 9 
10 #创建表table_d，如果已经存在，则先删除
11 cur2.execute('drop table if exists table_d')
12 
13 #编写创建表的SQL语句
14 #当字符串很长，并且需要跨多行时，可以使用三个单引号/双引号括起来
15 sql1="""create table table_d(std_id int not null
16                             ,std_name char(20)
17                             ,age int
18                             ,sex char(1)
19                             )"""
20 
21 #创建表
22 cur2.execute(sql1)
23 
24 #查看是否创建成功
25 cur2.execute('show tables')
26 result1=cur2.fetchall()
27 print result1
28 
29 #提交数据库执行
30 conn2.commit()
31 
32 #关闭游标
33 cur2.close()
34 
35 #断开数据库连接
36 conn2.close()

执行结果：

说明：其中需要注意一点，通过游标对象的execute函数执行SQL时，有两种方式，一种是直接将SQL语句作为参数，另一种则是先创建一个字符串变量，其值为SQL语句，将该变量作为execute函数的参数。

5、查询操作：

fetchall()用于接收全部的查询结果集，返回一个元组，元组内一条元素代表一条记录，一个元素本身又是一个元组。

fetchone()用于接收一条结果记录。

rowcount()返回这次execute()操作影响的行数。

举例：下面要就那些这样一个查询操作：

在mysql里面的查询结果是：

下面在python里进行实现：

 1 #导入MySQLdb模块
 2 import MySQLdb
 3 
 4 #建立与数据库的连接
 5 conn3=MySQLdb.connect('localhost','root','admin','test123')
 6 
 7 #创建游标
 8 cur3=conn3.cursor()
 9 
10 #创建表
11 cur3.execute('select * from table_a where user_id=988917 and month_id=201503')
12 
13 #接收一条查询结果
14 result1=cur3.fetchone()
15 print result1
16 
17 #接收全部查询结果
18 result2=cur3.fetchall()
19 print result2
20 
21 #返回这次execute操作影响的记录数
22 print cur3.rowcount
23 
24 #循环遍历结果集记录
25 for row in result2:
26     user_id=row[0]
27     month_id=row[1]
28     brand_id=row[2]
29     rate=row[3]
30     print 'user_id=%d,month_id=%d,brand_id=%s,rate=%f' % \
31           (user_id,month_id,brand_id,rate)
32     
33 #关闭游标
34 cur3.close()
35 
36 #断开数据库连接
37 conn3.close()

执行结果：

说明：这个查询的结果集应该是4条记录，由于首先使用了fetchone方法，因此，获取一条记录后游标移动到下一条记录的开头，所以再使用fetchall方法时就只能获取剩下的全部三条记录。

注意，不论是fetchone方法还是fetchall方法，最后返回的都是元组。

还有另外两种方法，fetchmany()和scroll()，前者可以指定获取多少条查询结果，后者可以用来移动游标。

 1 #导入MySQLdb模块
 2 import MySQLdb
 3 
 4 #建立与数据库的连接
 5 conn6=MySQLdb.connect('localhost','root','admin','test123')
 6 
 7 #创建游标
 8 cur6=conn6.cursor()
 9 
10 #创建表
11 cur6.execute('select * from table_a where user_id=988917 and month_id=201503')
12 
13 #返回这次execute操作影响的记录数
14 print cur6.rowcount
15 
16 #接收全部查询结果
17 result1=cur6.fetchall()
18 print result1
19 
20 #再次接收全部查询结果
21 result2=cur6.fetchall()
22 print result2
23 
24 #游标移动到结果集开始位置
25 cur6.scroll(0,'absolute')       #绝对移动
26 
27 #取出一条记录
28 result3=cur6.fetchone()
29 print result3
30 
31 #游标向后移动一条记录
32 cur6.scroll(1,'relative')       #相当移动，也是缺省移动方式，可以缩写为cur6.scroll(1)
33 
34 #取出两条记录
35 result4=cur6.fetchmany(2)
36 print result4
37 
38 #关闭游标
39 cur6.close()
40 
41 #断开数据库连接
42 conn6.close()

执行结果：

说明：查询出4条记录，第一次使用fetchall()取出了全部记录，游标也相应的从结果集开始位置移动到结果集末尾，所以第二次使用fetchall()取出的就是空值，使用scroll()方法将游标移动到文件开始位置（位置0处，第一条查询结果记录编号为0，第二条编号为1，以此类推），再使用fetchone()就可以获取一条记录，这时候游标就移动到位置1处。再使用scroll()方法向后移动一个位置，游标到位置2处，这时候再使用fetchmany()方法取出2条记录。

注意scroll方法有两个参数，scroll(value,mode)，mode缺省值为'relative'，代表相对移动。当mode='relative'时，value就是移动的长度，value>0向后移动（从位置0移动到位置2），value<0向前移动（比如从位置2移动到位置0），当mode='absolute'时，代表绝对移动，value就代表移动的绝对位置，value=0就代表移动到位置0处，就是结果集开头，value=3就是移动到位置3处，也就是第4条记录处。

6、插入操作：

 1 #导入MySQLdb模块
 2 import MySQLdb
 3 
 4 #建立与数据库的连接
 5 conn4=MySQLdb.connect('localhost','root','admin','test123')
 6 
 7 #创建游标
 8 cur4=conn4.cursor()
 9 
10 #创建表table_e，如果已经存在，则先删除
11 cur4.execute('drop table if exists table_e')
12 
13 #创建表table_e
14 sql1="""create table table_e(user_id int not null
15                             ,month_id int
16                             ,brand_id varchar(20)
17                             ,rate float
18                             )"""
19 cur4.execute(sql1)
20 
21 #插入一条数据
22 cur4.execute("insert into table_e values(999999,201505,'202B303',0.6752)")
23 
24 #验证是否插入成功
25 cur4.execute('select * from table_e')
26 result1=cur4.fetchall()
27 print result1
28 
29 #再次插入一条记录[使用%s占位]
30 sql2="insert into table_e values(%s,%s,%s,%s)"
31 cur4.execute(sql2,(888888,201505,'303B202',0.6565))
32 
33 #验证是否插入成功
34 cur4.execute('select * from table_e')
35 result2=cur4.fetchall()
36 print result2
37 
38 #批量插入多条数据[每一个values作为一个元组，values共同组成一个列表，作为executemany的参数]
39 cur4.executemany(sql2,[
40                     (666666,201505,'101B101',0.5000)
41                     ,(666666,201505,'202B202',0.7656)
42                     ,(666666,201505,'303B303',0.8965)
43                     ]
44                     )
45 
46 #验证是否插入成功
47 cur4.execute('select * from table_e')
48 result3=cur4.fetchall()
49 print result3
50 
51 #提交到数据库执行
52 conn4.commit()
53 
54 #关闭游标
55 cur4.close()
56 
57 #断开数据库连接
58 conn4.close()

执行结果：

说明：插入单条数据有两种写法，但都使用的是execute方法，而插入批量数据使用的是executemany方法。

一定要使用数据库连接对象的commit方法来提交插入，否则不能真正的插入数据。

7、更新操作：

 1 #导入MySQLdb模块
 2 import MySQLdb
 3 
 4 #建立与数据库的连接
 5 conn5=MySQLdb.connect('localhost','root','admin','test123')
 6 
 7 #创建游标
 8 cur5=conn5.cursor()
 9 
10 #更新前
11 sql1="select * from table_e where user_id=888888"
12 cur5.execute(sql1)
13 result1=cur5.fetchall()
14 print result1
15 
16 #update语句
17 sql2="update table_e set rate=0.9988 where user_id=888888"
18 try:
19     cur5.execute(sql2)
20     print cur5.rowcount
21     conn5.commit()           #向数据库中提交
22 except:
23     conn5.rollback()         #发生错误时回滚
24 
25 #验证是否更新成功
26 sql3="select * from table_e where user_id=888888"
27 cur5.execute(sql3)
28 result2=cur5.fetchall()
29 print result2
30 
31 #关闭游标
32 cur5.close()
33 
34 #断开数据库连接
35 conn5.close()