python3之pymysql模块

1、python3 MySQL数据库链接模块

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

PyMySQL模块下载地址：https://github.com/PyMySQL/PyMySQL

安装PyMySQL模块的方法：

#(1)pip方式安装
$pip install PyMySQL

#(2)克隆安装
$git clone https://github.com/PyMySQL/PyMySQL
　　$cd PyMySQL
　　$python3 setup.py install

#(3)源码安装
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
　　$ cd PyMySQL*
　　$python3 setup.py install

2、PyMySQL的API参考

（1）链接对象API：

classpymysql.connections.Connection(host=None, user=None, password='', database=None, port=0, unix_socket=None, charset='', sql_mode=None, read_default_file=None, conv=None, use_unicode=None, client_flag=0, cursorclass=<class 'pymysql.cursors.Cursor'>, init_command=None, connect_timeout=10, ssl=None, read_default_group=None, compress=None, named_pipe=None, no_delay=None, autocommit=False, db=None, passwd=None, local_infile=False, max_allowed_packet=16777216, defer_connect=False, auth_plugin_map={}, read_timeout=None, write_timeout=None, bind_address=None, binary_prefix=False)

使用MySQL服务器表示一个套接字，创建类实例的方法是调用connect()，将创建一个MySQL链接对象，建立连接可使用的参数如下：

host：数据库服务器主机名可以是IP或域名

user：用户登陆名

password：用户名密码
database：要使用的数据库名

port：端口号默认3306

bind_address：多网口是绑定网络接口，可以是主机名或IP

unix_socket：指定套接字链接

read_timeout：读取连接的超时时间

write_timeout：写入连接的超时时间

carset：指定字符集
sql_mode：指定SQL_MODE

read_defautl_file：指定my.cnf配置文件读取参数

init_command：建立连接时运行的初始SQL语句

 

链接对象使用的方法：

conn.close()   关闭链接

conn.commit()  提交更改到数据库服务器

conn.cursor(cursor=None)  创建一个新的游标来执行查询，cursor指定游标类型：Cursor、SSCursor、DictCursor或SSDictCursor，没有指定即使用光标

conn.open   如果链接处于打开状态则返回true

conn.ping(reconnect=True)  检查服务器是否存在，reconnect为True时重新链接

conn.rellback()   回滚当前事务

(2)游标对象API

class pymysql.cursors.Cursor(connection)  ：创建与数据库交换的对象，对象表示数据库游标，用于管理提取操作的上下文

游标的方法：

cursor.callproc(procname)  查看数据库存储过程

cursor.close() 关闭游标

cursor.execute(query,args=None) 执行查询，query查询参数为字符串，args可以是元组，列表或字典，用于查询的参数，返回类型为INT  

cursor.executemany(query,seq_of_parameters) 多次查询返回结果

cursor.fetchall()  获取所有行

cursor.fetchmany(size=None) 获取指定的行数

cursor.fetchone() 获取下一行

cursor.max_stmt_length=1024000  executemany()生成的最大语句大小

cursor.mogrify(query,args=None)  通过调用execute()方法返回发送到数据库的字符串

（3）其它对象API

class pymysql.cursors.SSCursor(connection)  ：用于返回大量数据的查询

class pymysql.cursors.DictCursor(connection)  ：用于将结果作为字典返回的游标

class pymysql.cursors.SSDictCursor(connection)  ：用于无缓冲的游标，它将结果作为字典返回

3、pymysql使用

（1）MySQL查询操作：

import pymysql

#创建数据库链接，分别指定主机、用户、密码和数据库名,必须保证用户有权限链接
db=pymysql.connect('10.0.1.198','test1','123.com','test')

#创建游标对象
cursor = db.cursor()

#使用execute()方法执行SQL语句
cursor.execute('select * from test1')

#获取单条数据
print(cursor.fetchone())
#获取N条数据
print(cursor.fetchmany(3))

#获取所有数据，序列形式 
data = cursor.fetchall() 
print(data) 

#关闭游标 
cursor.close() 
#关闭链接 
db.close()

（2）获取字典类型数据：

import pymysql
#创建数据库链接，分别指定主机、用户、密码和数据库名,必须保证用户有权限链接
db=pymysql.connect('10.0.1.198','test1','123.com','test')
#创建游标对象，指定数据类型为字典，将打印key,value
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
#使用execute()方法执行SQL语句
cursor.execute('select * from test1')

#获取所有数据，字典形式
data = cursor.fetchall()
print(data)

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

（3）MySQL更新操作：

import pymysql

conn = pymysql.connect(host='10.0.1.198',port=3306,user='test1',passwd='123.com',db='test')
cursor = conn.cursor()
sql = "update test1 set age=28 where id=4"
cursor.execute(sql)
#提交语句到数据库
conn.commit()
cursor.close()
conn.close()

（4）插入多条语句：

import pymysql

conn = pymysql.connect(host='10.0.1.198',port=3306,user='test1',passwd='123.com',db='test')
cursor = conn.cursor()
l1 = [
    ('k1','aa',22,'2222'),
    ('k2','bb',23,'3333'),
    ('k3','cc',24,'4444'),
    ('k4','dd',25,'5555')
]
#定义数据库语句
sql = "insert into test1(name,sex,age,tel) values(%s,%s,%s,%s)"
#executemany()插入多条数据
cursor.executemany(sql,l1)
#获取新增数据自增ID
print(cursor.lastrowid)
#提交语句到数据库
conn.commit()
cursor.close()
conn.close()

（5）创建数据库表：

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2018/1/30 17:19
# @Author  : Py.qi
# @File    : create_database.py
# @Software: PyCharm

import pymysql

conn = pymysql.connect('10.0.1.198','test1','123.com')
#创建游标对象,字典输出
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute('drop database if exists student')
cursor.execute("create database student character set 'utf8' collate utf8_general_ci")

#如果表存在则删除
cursor.execute('drop table if exists student.test2')
#sql建表语句
sql = '''create table student.test2(
    id int not null auto_increment primary key,
    name char(8) not null,
    age int not null)'''
#执行建表语句
cursor.execute(sql)

cursor.close()
conn.close()

（6）数据回滚操作：

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2018/1/30 14:53
# @Author  : Py.qi
# @File    : mysqldb_clien.py
# @Software: PyCharm

import pymysql
#创建数据库链接，分别指定主机、用户、密码和数据库名,必须保证用户有权限链接
db=pymysql.connect('10.0.1.198','test1','123.com','test')
#创建游标对象
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
#使用execute()方法执行SQL语句

sql2 = "insert into test1(name,sex,age,tel) values('zz','ee',21,'8999')"
try:
    cursor.execute(sql2)
    #提交到数据库执行
    db.commit()
except:
    #发生错误时回滚操作
    db.rollback()

#获取所有数据，序列形式
data = cursor.fetchall()
print(data)

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

（7）错误处理：

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

异常	描述
Warning	当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类。
Error	警告以外所有其他错误类。必须是 StandardError 的子类。
InterfaceError	当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。 必须是Error的子类。
DatabaseError	和数据库有关的错误发生时触发。 必须是Error的子类。
DataError	当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。 必须是DatabaseError的子类。
OperationalError	指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、 数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。 必须是DatabaseError的子类。
IntegrityError	完整性相关的错误，例如外键检查失败等。必须是DatabaseError子类。
InternalError	数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。 必须是DatabaseError子类。
ProgrammingError	程序错误，例如数据表（table）没找到或已存在、SQL语句语法错误、 参数数量错误等等。必须是DatabaseError的子类。
NotSupportedError	不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上 使用.rollback()函数，然而数据库并不支持事务或者事务已关闭。 必须是DatabaseError的子类。

-----------------------------------------------------------------------------------------------------------------------------------------