python操作mysql数据库

        python标准数据库接口为 python DB-API，python DB-API为开发人员提供了数据库应用编程接口。

        python数据库接口支持非常多的数据库，可以选择适合项目的数据库：

  • GadFly
  • mSQL
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Informix
  • Interbase
  • Oracle
  • Sybase

        可以访问python数据库接口及API查看详细的支持数据库列表。

        不同的数据库需要下载不同的DB API模块。

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

        python的DB-API，为大多数的数据库实现了接口，使用它连接各种数据库后，就可以用相同的方式操作各数据库。

        python DB-API使用流程：

  • 引入API模块。
  • 获取数据库的连接。
  • 执行SQL语句和存储过程。
  • 关闭数据库连接。

安装MySQLdb

        MySQLdb是用与python连接mysql数据库的接口，他实现了python数据库API规范，基于MySQL C API上建立的。

        为了使用DB-API编写mysql脚本，必须确保已经安装了mysql。

1
2
3
4
5
6
import MySQLdb
Traceback (most recent call last):
  File "test.py", line 3, in <module>
    import MySQLdb
ImportError: No module named MySQLdb

        如果执行后报错，以为着没有安装MySQLdb模块。

        安装MySQLdb，访问： https://sourceforge.net/projects/mysql-python/ ，linux平台可以访问： https://pypi.python.org/pypi/MySQL-python ，从这里可以选择适合的平台安装包，分为预编译的二进制文件和源代码安装包。

        如果选择二进制文件发行版本的话，安装过程基本安装提示即可完成。如果从源代码进行安装的话，则需要切换到MySQLdb发行版本的顶级目录，并键入命令：

1
2
3
4
5
$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

        注意：确保有root权限来安装上述模块。

数据库连接

        连接数据库前，先确认以下事项：

  • 已经创建了数据库TESTDB
  • 在TESTDB数据哭中已经创建了表EMPLOYEE
  • EMPLOYEE表字段为FIRST_NAME,LASE_NAME,AGE,SEX和INCOME
  • 连接数据库TESTDB使用的用户名为“testuser”，密码为“test123”，可以自己设定或者直接使用root用户名机及其密码，mysql数据库用户授权使用Grant命令。
  • 在机子上已经安装了Python MySQLdb模块。

        连接mysql的TESTDB数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print "Database version : %s " % data
# 关闭数据库连接
db.close()

        也可以这样连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect(host="localhost",port=3306,user="testuser",passwd="test123",db="TESTDB",charset="utf8")
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print "Database version : %s " % data
# 关闭数据库连接
db.close()

        还可以使用函数连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
import MySQLdb
# 打开数据库连接
def connect_mysql():
    db_config = {
        "host": "localhost",
        "port": 3306,
        "user": "testuser",
        "passwd": "test123",
        "db": "TESTDB",
        "charset": "utf8"
    }
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()
print "Database version : %s " % data
# 关闭数据库连接
db.close()

        以上脚本输出结果如下

1
Database version : 5.0.45

执行事务

        事务机制可以确保数据一致性。

        事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常成为ACID特性。

  • 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
  • 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
  • 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  • 持久性（durability）。持续性也称为持久性（prtmanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

        python DB API的 事务提供了两个方法commit或rollback。

1
2
3
4
5
6
7
8
9
10
# SQL删除记录语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

        对于支持事务的数据库，在python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。

        commit()方法游标的所有更新操作，rollback()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。

创建数据库表

        如果数据库连接存在，那么可以使用execute()方法来为数据库创建表，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
cursor.execute(sql)
# 关闭数据库连接
db.close()

        也可以在后边加入异常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
import MySQLdb
# 打开数据库连接
def connect_mysql():
    db_config = {
        "host": "localhost",
        "port": 3306,
        "user": "testuser",
        "passwd": "test123",
        "db": "TESTDB",
        "charset": "utf8"
    }
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
try:
    cursor.execute(sql)
    # 关闭数据库连接
    db.close()
    cursor.commit()
except Exception as e:
    raise e
    cursor.rollback()
finally:
    db.close()

游标（Cursor）

        游标是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在集中一次以行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

        游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。

        用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由python进一步处理，一组主编辆一次只能存放一条记录。仅使用主编辆并不能完全满足SQL语句向应用程序输出数据的要求。

游标的优点

        在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当巨鼎对结果集进行处理时，必须声明一个指向该结果集的游标。

游标常用方法

  • cursor()：创建游标对象
  • close()：关闭此游标对象
  • fetchone()：得到结果集的下一行
  • fetchmany([size = cursor.arraysize])：得到结果集的下几行
  • fetchall()：得到结果集中剩下的所有行
  • excute(sql[, args])：执行一个数据库查询或命令
  • excutemany(sql, args)：执行多个数据库查询或命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
import MySQLdb
def connect_mysql():
    db_config={
        'host':'192.168.48.128',
        'port':3306,
        'user':'yan',
        'passwd':'123456',
        'db':'python',
        'charset':'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx
    
if __name__ == '__main__':
    cnx = connect_mysql()
    cus = cnx.cursor()
    sql = "select * from employees;"
    try:
        cus.execute(sql)
        result1 = cus.fetchone()
        print('result1:')
        print(result1)
        result2 = cus.fetchmany(1)
        print('result2:')
        print(result2)
        result3 = cus.fetchall()
        print('result3:')
        print(result3)
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        raise e
    finally:
        cnx.close()

        说明

  1. 先通过MySQLdb.connect(**db_config)建立mysql连接对象
  2. 再通过 = cnx.cursor()创建游标
  3. fetchone()：在最终搜索的数据中去一条数据
  4. fetchmany(1)在接下来的数据中再取1行的数据，这个数字可以自定义，定义多少就是在结果集中取多少条数据
  5. fetchall()是在所有的结果中取所有的数据

数据库连接池

        python编程中可以使用MySQLdb进行数据库的连接及诸如查询、插入、更新等操作，但是每次连接mysql数据库请求时，都是独立的去请求访问，相当浪费资源，而且访问数量达到一定数量时，对mysql的性能会产生较大的影响。因此，实际使用中，通常会使用数据库的连接池技术，来访问数据库达到资源复用的目的。

        python的数据库连接池包DBUtils

        DBUtils是一套python数据库连接池包，并允许对非县城安全的数据库接口进行线程安全包装。DBUtils来自Webware for Python。

        DBUtils提供两种外部接口：

  • PersistentDB：提供线程专用的数据库连接，并自动管理连接
  • PooledDB：提供线程间可共享的数据库连接，并自动管理连接

        下载地址：https://pypi.python.org/pypi/DBUtils/

        下载解压后，使用python setup.py install或者pip install DBUtils命令进行安装或者使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import MySQLdb
from DBUtils.PooledDB import PooledDB
def connect_mysql():
    db_config={
        'host':'192.168.48.128',
        'port':3306,
        'user':'yan',
        'passwd':'123456',
        'db':'python',
        'charset':'utf8'
    }
pool = PooledDB(MySQLdb, 5, **db_config)    # 5为连接池里的最少连接数
conn = pool.connection()      # 以后每次需要数据库连接就是用connection()函数获取连接就好了
cur = conn.cursor()
SQL = "select * from tmp;"
r = cur.execute(SQL)
r = cur.fetchall()
print(r)
cur.close()
conn.close()

        PooledDB的参数

  1. mincached：最少的空闲连接数，如果空闲连接数小于这个数，pool会创建一个新的连接
  2. maxcached：最大的空闲连接数，如果空闲连接数大于这个数，pool会关闭空闲连接
  3. maxconnection：最大的连接数
  4. blocking：当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，如果这个值是False，会报错
  5. maxshared：当连接数达到这个数，新请求的连接会分享已经分配出去的连接

        在uwsgi中，每个http请求都会分发给一个进程，连接池中配置的连接数都是一个进程为单位的（即上面的最大连接数，都是在一个进程中的连接数），而如果业务中，一个http请求中需要的sql连接数不是很多的话（其实大多数都只需要创建一个连接），配置的连接数配置都不需要太大。

        连接池对性能的提升表现在

  1. 在程序创建连接的时候，可以从一个空闲的连接中获取，不需要重新初始化连接，提升获取连接的速度
  2. 关闭连接的时候，把连接放回连接池，而不是真正的关闭，所以可以减少频繁地打开和关闭连接

设计表结构

        在操作设计数据库之前，先要设计数据库表结构。先分析个主题之间的关系，它们之间直接有什么属性，并确定表结构，在实际开发过程中，根据自己的业务需要和属性，设计不同的表结构。

        有了表结构，就可以开始创建表。

数据库插入操作

        使用执行 SQL INSERT语句想表EMPLOYEE插入记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
# 关闭数据库连接
db.close()

        也可以写成如下形式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
# 关闭数据库连接
db.close()

        例：以下代码使用变量想SQL语句中传递参数

1
2
3
4
5
6
7
..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

数据库查询操作

        python查询mysql使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据。

  • fetchone()：该方法获取下一个查询结果集。结果集是一个对象。
  • fetchall()：接收全部的返回结果行。
  • rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。

        例：查询EMPLOYEE表中salary（工资）字段大于1000的所有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 打印结果
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"
# 关闭数据库连接
db.close()

        脚本执行结果

1
fname=Mac, lname=Mohan, age=20, sex=M, income=2000

删除操作

        删除操作用于删除数据表中的数据。

        例：删除数据表EMPLOYEE中AGE大于20的所有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# SQL 删除语句
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
# 关闭连接
db.close()

数据库更新操作

        更新操作用于更新数据表的数据。

        例：将EMPLOYEE表中SEX字段为‘M’的AGE字段递增1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import MySQLdb
# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()
# SQL 更新语句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()
# 关闭数据库连接
db.close()

索引

MySQL索引的概念

        索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引类别

  1. 普通索引

        普通索引（由关键字 KEY 或 INDEX 定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

  1. 唯一索引

        普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

        如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一索引。这么做的好处：一是简化了 MySQL 对这个索引的管理工作，这个索引也因此而变得更有效率；二是 MySQL 会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL 将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

  1. 主索引

        在前面已经反复多次强调过：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。

4.外键索引

        如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

  1. 复合索引

        索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。

mysql主键和索引的区别:

  1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。

        所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。

        主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

  1. 一个表中可以有多个唯一性索引，但只能有一个主键。

  2. 主键列不允许空值，而唯一性索引列允许空值。

  3. 索引可以提高查询的速度。

        创建Course的CouID的字段为主键 Score的SID字段为主键 Student的StdID字段为主键 Teacher的TID字段为主键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
import codecs
import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        'db': 'python',
        'charset': 'utf8'
    }
    cnx = MySQLdb.connect(**db_config)
    return cnx
if __name__ == '__main__':
    cnx = connect_mysql()
    sql1 = '''alter table Teacher add primary key(TID);'''
    sql2 = '''alter table Student add primary key(StdID);'''
    sql3 = '''alter table Score add primary key(SID);'''
    sql4 = '''alter table Course add primary key(CouID);'''
    sql5 = '''alter table Score add index idx_StdID_CouID(StdID, CouID);'''
    # sql6 = '''alter table Score drop  index idx_StdID_CouID;'''   删除索引
    sql7 = '''explain select * from Score where StdID = 16213;'''
    try:
        cus = cnx.cursor()
        cus.execute(sql1)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql2)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql3)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql4)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql5)
        cus.close()
        cus = cnx.cursor()
        cus.execute(sql7)
        result = cus.fetchall()
        print(result)
        cus.close()
        cnx.commit()
    except Exception as e:
        cnx.rollback()
        print('error')
        raise e
    finally:
        cnx.close()

        结果：

1
((1L, u'SIMPLE', u'Score', u'ref', u'idx_StdID_CouID', u'idx_StdID_CouID', u'4', u'const', 4L, None),)

        说明：Sql1， sql2， sql3， sql4是添加主键，sql5是增加一个索引，我们也可以在mysql的客户端上执行sq7，得到如下的结果：

1
2
3
4
5
6
7
mysql> explain select * from Score where StdID = 16213;               
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
|  1 | SIMPLE      | Score | ref  | idx_StdID_CouID | idx_StdID_CouID | 4       | const |    4 | NULL  |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+
1 row in set (0.00 sec)

        这个说明，我们在搜索StdID的时候，是走了idx_StdID_CouID索引的。