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模块,例如你需要访问Oracle数据库和Mysql数据,你需要下载Oracle和MySQL数据库模块。

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

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

Python DB-API使用流程:

1. 引入API模块。
2. 获取与数据库的连接。
3. 执行SQL语句和存储过程。
4. 关闭数据库连接。

1. MySQLdb

MySQLdb是用于Python链接Mysql数据库的接口，它实现了Python 数据库API规范V2.0，基于MySQL C API上建立的。

安装

直接使用pip进行安装，在此之前需要安装一些系统依赖包。

• CentOS

yum install gcc python-devel mysql-devel zlib-devel openssl-devel

• Ubuntu

sudo apt-get install libmysqlclient-dev libmysqld-dev python-dev python-setuptools

安装完依赖，直接使用pip安装，MySQLdb模块的名字在pip上叫MySQL-python。

pip install MySQL-python

常用函数

Python DB API 2.0 对事务提供了两个方法：

• commit() 提交
• rollback() 回滚

cursor用来执行命令的方法:

• callproc(self, procname, args) 用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数
• execute(self, query, args) 执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数
• executemany(self, query, args) 执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数
• nextset(self) 移动到下一个结果集

cursor用来接收返回值的方法:

• fetchall(self) 接收全部的返回结果行.
• fetchmany(self, size=None) 接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据.
• fetchone(self) 返回一条结果行.
• rowcount 这是一个只读属性,并返回执行execute() 方法后影响的行数。
• scroll(self, value, mode='relative') 移动指针到某一行; 如果mode='relative',则表示从当前所在行移动value条,如果 mode='absolute',则表示从结果集的第一行移动value条.

实例

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb

# 连接数据库
conn = mdb.connect('localhost', 'root', 'root')

# 也可以使用关键字参数
conn = mdb.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='test', charset='utf8')

# 也可以使用字典进行连接参数的管理
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'db': 'test',
    'charset': 'utf8'
}
conn = mdb.connect(**config)

# 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务conn.commit()
conn.autocommit(1)    # conn.autocommit(True) 

# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

try:
    # 创建数据库
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)
    
    #创建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)
    
    # 插入单条数据
    sql = 'INSERT INTO user values("%d","%s")' %(1,"jack")
    
    # 不建议直接拼接sql，占位符方面可能会出问题，execute提供了直接传值
    value = [2,'John']
    cursor.execute('INSERT INTO test values(%s,%s)',value)
    
    # 批量插入数据
    values = []
    for i in range(3, 20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)
    
    # 查询数据条目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print 'total records: %d' %count
    print 'total records:', cursor.rowcount
    
    # 获取表名信息
    desc = cursor.description
    print "%s %3s" % (desc[0][0], desc[1][0])
    
    # 查询一条记录
    print 'fetch one record:'
    result = cursor.fetchone()
    print result
    print 'id: %s,name: %s' %(result[0],result[1])
    
    # 查询多条记录
    print 'fetch five record:'
    results = cursor.fetchmany(5)
    for r in results:
        print r
        
    # 查询所有记录
    # 重置游标位置，偏移量:大于0向后移动;小于0向前移动，mode默认是relative
    # relative:表示从当前所在的行开始移动; absolute:表示从第一行开始移动
    cursor.scroll(0,mode='absolute')
    results = cursor.fetchall()
    for r in results:
        print r
 
    cursor.scroll(-2)
    results = cursor.fetchall()
    for r in results:
        print r
 
    # 更新记录
    cursor.execute('UPDATE %s SET name = "%s" WHERE id = %s' %(TABLE_NAME,'Jack',1))
    # 删除记录
    cursor.execute('DELETE FROM %s WHERE id = %s' %(TABLE_NAME,2))
    
    # 如果没有设置自动提交事务，则这里需要手动提交一次
    conn.commit()
except:
    import traceback
    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

查询时返回字典结构

MySQLdb默认查询结果都是返回tuple，通过使用不同的游标可以改变输出格式，这里传递一个cursors.DictCursor参数。

import MySQLdb.cursors

conn = MySQLdb.connect(host='localhost', user='root', passwd='root', db='test', cursorclass=MySQLdb.cursors.DictCursor)
cursor = conn.cursor()

cursor.execute('select * from user')
r = cursor.fetchall()
print r
# 当使用位置参数或字典管理参数时，必须导入MySQLdb.cursors模块

# 也可以用下面的写法
import MySQLdb as mdb
conn  = mdb.connect('localhost', 'root', 'root', 'test')
cursor = conn.cursor(cursorclass=mdb.cursors.DictCursor)

cursor.execute('select * from user')
r = cursor.fetchall()
print r

MySQLdb取回大结果集的技巧

普通的操作无论是fetchall()还是fetchone()都是先将数据载入到本地再进行计算，大量的数据会导致内存资源消耗光。解决办法是使用SSCurosr光标来处理。

• 解决mysqldb查询大量数据导致内存使用过高的问题
• MySQLdb取回大结果集的技巧

然而，在python3下，MySQLdb模块不再提供支持，此时可以使用另一个模块PyMySQL，它支持python2和python3。

2. PyMySQL

PyMySQL是一个纯Python写的MySQL客户端，它的目标是替代MySQLdb，可以在CPython、PyPy、IronPython和Jython环境下运行。PyMySQL在MIT许可下发布。

PyMySQL的性能和MySQLdb几乎相当，如果对性能要求
不是特别的强，使用PyMySQL将更加方便。

PyMySQL的使用方法和MySQLdb几乎一样。

安装

pip install pymysql

实例

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'passwd': 'root',
    'charset':'utf8mb4',
    'cursorclass':pymysql.cursors.DictCursor
    }
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()

try:
    # 创建数据库
    DB_NAME = 'test'
    cursor.execute('DROP DATABASE IF EXISTS %s' %DB_NAME)
    cursor.execute('CREATE DATABASE IF NOT EXISTS %s' %DB_NAME)
    conn.select_db(DB_NAME)
    
    #创建表
    TABLE_NAME = 'user'
    cursor.execute('CREATE TABLE %s(id int primary key,name varchar(30))' %TABLE_NAME)
    
    # 批量插入纪录
    values = []
    for i in range(20):
        values.append((i,'kk'+str(i)))
    cursor.executemany('INSERT INTO user values(%s,%s)',values)
    
    # 查询数据条目
    count = cursor.execute('SELECT * FROM %s' %TABLE_NAME)
    print 'total records:', cursor.rowcount
    
    # 获取表名信息
    desc = cursor.description
    print "%s %3s" % (desc[0][0], desc[1][0])

    cursor.scroll(10,mode='absolute')
    results = cursor.fetchall()
    for result in results:
        print result
        
except:
    import traceback
    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()

输出结果：

total records: 20
id name
{u'id': 10, u'name': u'kk10'}
{u'id': 11, u'name': u'kk11'}
{u'id': 12, u'name': u'kk12'}
{u'id': 13, u'name': u'kk13'}
{u'id': 14, u'name': u'kk14'}
{u'id': 15, u'name': u'kk15'}
{u'id': 16, u'name': u'kk16'}
{u'id': 17, u'name': u'kk17'}
{u'id': 18, u'name': u'kk18'}
{u'id': 19, u'name': u'kk19'}

参考：

• python操作mysql数据库
• MySQL Python tutorial
• python操作MySQL数据库
• python操作MySQL