Python学习之13.连接mysql

Python标准数据库接口为Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。Python数据库接口支持非常多的数据库，可以选择适合自己的数据库：

GraFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

DB-API是一个规范，它定义了一系列必须的对象和数据库存取方式，以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口。不同的数据库需要下载不同的DB-API模块，例如你需要访问Oracle数据库和MySQL数据库，你需要下载Oracle和MySQL模块。

下面介绍如何用DB-API编写MySQL脚本，必须确保已经安装了MySQL，我用的是Windows，下载MySQLdb的exe文件安装即可。

根据Python多少位下载对应版本：

32位：https://pypi.python.org/pypi/MySQL-python/1.2.5

64位：http://arquivos.victorjabur.com/python/modules/MySQL-python-1.2.3.win-amd64-py2.7.exe

实例：

以下实例链接MySQL的testdb数据库：

import MySQLdb

#打开数据库连接(localhost为主机名，testuser为用户名，test123为数据库密码，testdb为要连接的数据库)
db=MySQLdb.connect("hostlocal","testuser","test123","testdb")

#使用cursor()方法获取操作游标：通过游标执行SQL查询并检查结果
cursor=db.cursor()

#使用execute执行SQL语句
cursor.execute("select version()")

#使用fetchone方法获取一个数据库
data=cursor.fetchone()

print "Database version : %s " % data

#关闭数据库
db.close()

执行以上脚本输出结果如下：

Database version : 5.7.19-log

创建数据库表

如果数据库连接存在可以使用execute()方法来为数据库创建表，以下所示创建表EMPLOYEE：

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()

数据库插入操作

以下实例使用执行SQL INSERT语句向表EMPLOYEE插入记录:

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)VLAUES('%s','%s','%d','%c','%d')" % ('Mac','Mohan',20,'M',2000))
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()

数据库查询操作

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

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

实例：

查询EMPLOYEE表中salary(工资)字段大于1000的左右数据：

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=cusor.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,lanme,age,sex,income)
except:
    print "Error:unable to fetch data."

#关闭数据库连接
db.close()

数据库更新操作

更新操作用于更新数据库表的数据库，以下实例将EMPLOYEE表中的SEX字段为'M'的AGE字段递增1：

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()

删除操作

删除操作用于删除数据库中的数据，以下实例演示删除数据表EMPLOYEE中AGE大于20的左右数据：

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()

执行事务

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

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

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

Python DB API 2.0的事务提供了两个方法commit和rollback。

实例：

import MySQLdb

#打开数据库连接
db=MySQLdb.connect("localhost","testuser","test123","testdb")

#使用cursor()方法获取操作游标
cursor=db.cursor()

#SQL删除记录语句
sql="DELETE FROM EMPLOYEE WEHRE AGE > '%d'" % (20)
try:
    #执行SQL语句
    cursor.execute(sql)
    #向数据库提交
    db.commit()
except:
    #发生错误时回滚
    db.rollback()

对于支持事务的数据库，在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。commit()方法游标的所有更新操作，rollback()方法回滚当前游标的所有操作。每个方法都开始了一个新的事务。

错误处理

DB API中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常：