pymysql
Pymysql
https://pymysql.readthedocs.io/en/latest/user/examples.html
官方文档
INSTALL
$ python3 -m pip install PyMySQL
#Requirements
Python – one of the following:
CPython >= 2.7 or >= 3.5
Latest PyPy
MySQL Server – one of the following:
MySQL >= 5.5
MariaDB >= 5.5
示例:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8_bin NOT NULL,
`password` varchar(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;
游标使用的是字典模式
import pymysql.cursors
# Connect to the database
# 设置了db
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()
with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
finally:
connection.close()
示例2
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': '123123',
'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[0], result[1])
except:
import traceback
traceback.print_exc()
# 发生错误时会滚
conn.rollback()
finally:
# 关闭游标连接
cursor.close()
# 关闭数据库连接
conn.close()
示例3:数据量比较大的时候操作
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
import time
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': '123123',
'charset':'utf8mb4',
'db': 'employees',
'cursorclass': pymysql.cursors.SSCursor, ##数据量比较大的时候操作
# 默认游标为元组
#'cursorclass':pymysql.cursors.DictCursor # 游标为字典
}
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()
try:
TABLE_NAME = 'titles'
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()
# print(results)
for result in results:
# 右边默认使用元组
time.sleep(1)
print(result)
except:
import traceback
traceback.print_exc()
# 发生错误时会滚
conn.rollback()
finally:
# 关闭游标连接
cursor.close()
# 关闭数据库连接
conn.close()
数据库常用操作
创建数据库表
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': '123123',
'charset':'utf8mb4',
'db': 'test',
'cursorclass': pymysql.cursors.SSCursor,
# 默认游标为元组
#'cursorclass':pymysql.cursors.DictCursor # 游标为字典
}
conn = pymysql.connect(**config)
conn.autocommit(1)
cursor = conn.cursor()
res = cursor.execute("DROP TABLE IF EXISTS employee1") #执行成功后返回0
sql = """CREATE TABLE `employee1` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`first_name` char(20) NOT NULL,
`last_name` char(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`income` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""
ress = cursor.execute(sql) #执行成功后返回0
print("Created table Successfull.")
# disconnect from server
db.close()
插入数据
sql = "INSERT INTO employee1(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('%s', '%s', '%d', '%c', '%d' )" % ('Max', 'Su', 25, 'F', 2800)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
conn.commit() ##conn为创建的数据库连接
except:
# Rollback in case there is any error
conn.rollback() ##conn为创建的数据库连接
另外一种插入方式
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' %(user_id, password))
读取操作
读取操作的形式与连接数据库是指定的结果集的方式有关,默认为元组,
# 未测试
#!/usr/bin/python3
#coding=utf-8
import pymysql
# Open database connection
db = pymysql.connect("localhost","root","123456","test" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# 按字典返回
# cursor = db.cursor(pymysql.cursors.DictCursor)
# Prepare SQL query to select a record from the table.
sql = "SELECT * FROM EMPLOYEE WHERE INCOME > %d" % (1000)
#print (sql)
try:
# Execute the SQL command
cursor.execute(sql)
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
#print (row)
fname = row[1]
lname = row[2]
age = row[3]
sex = row[4]
income = row[5]
# Now print fetched result
print ("name = %s %s,age = %s,sex = %s,income = %s" % \
(fname, lname, age, sex, income ))
except:
import traceback
traceback.print_exc()
print ("Error: unable to fetch data")
# disconnect from server
cursor.excute(query,args = None)#执行数据库操作(查询或命令)
参数：
query：str 表示要执行的SQL语句
args：(元组，列表或字典) - 与查询一起使用的参数。用于给SQL传入参数，是可选的。
返回：
int受影响的行数
注意：
# 如args是列表或元组用%s作为SQL 语句中的占位符，args是dict用%(name)s作为占位符。
# 重复操作最好用.setinputsizes()提前指定参数类型和大小。参数与预定义信息不匹配是合法的;
# 参数也可为元组列表，如在单个操作中插入多行，不推荐：应用.executemany()
str_insert= "INSERT INTO Cu_table (id) VALUES (%s)"# 参数是元组时
cursor.execute(str_insert,('123'))
arg = {'id':'id'}# 参数是字典时
str_insert= "INSERT INTO Cu_table (id) VALUES (%(id)s)"
cursor.execute(str_insert,arg)
cursor.excutemany(query, args)#执行多个数据库查询或命令
参数：
query - 要在服务器上执行的查询
args - 序列或映射的序列。它用作参数。
返回：
受影响的行数(如果有)
说明：
提高多行INSERT和REPLACE的性能。否则它等同于使用execute()循环遍历args
executemany()生成的最大语句大小为max_allowed_packet - packet_header_size
cursor.max_stmt_length #1024000
con.max_allowed_packet#16777216
实例：
str_insert = "INSERT INTO Cu_table (id) VALUES (%s)"
cursor.executemany(str_insert,['A','B','C','D']) #一次插入A B C D 四个值
cursor.fetchmany(size=None)#获取查询结果前size行，默认cursor.arraysize=1行
返回：
序列的序列(例如元组的元祖)
实例：
str_insert = "SELECT * FROM Cu_table"
cursor.execute(str_insert)
result = cursor.fetchmany(3) # 获取前三行
print(result)
https://blog.csdn.net/tcy23456/article/details/86665211 看这里
更新操作
删除操作
执行事物
事务是确保数据一致性的一种机制。事务具有以下四个属性 -
- 原子性 - 事务要么完成，要么完全没有发生。
- 一致性 - 事务必须以一致的状态开始，并使系统保持一致状态。
- 隔离性 - 事务的中间结果在当前事务外部不可见。
- 持久性 - 当提交了一个事务，即使系统出现故障，效果也是持久的
# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
错误和异常
1
Warning
用于非致命问题，是StandardError的子类。
2
Error
错误的基类，是StandardError的子类。
3
InterfaceError
用于数据库模块中的错误，但不是数据库本身，是Error的子类。
4
DatabaseError
用于数据库中的错误，是Error的子类。
5
DataError
DatabaseError的子类引用数据中的错误。
6
OperationalError
DatabaseError的子类，涉及如丢失与数据库的连接等错误。 这些错误通常不在Python脚本程序的控制之内。
7
IntegrityError
DatabaseError 子类错误，可能会损害关系完整性，例如唯一性约束和外键。
8
InternalError
DatabaseError的子类，指的是数据库模块内部的错误，例如游标不再活动。
9
ProgrammingError
DatabaseError的子类，它引用错误，如错误的表名和其他安全。
10
NotSupportedError
DatabaseError的子类，用于尝试调用不支持的功能
连接参数
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
参数
解释
host
数据库服务器地址，默认 localhost
user
用户名，默认为当前程序运行用户
password
登录密码，默认为空字符串
database
默认操作的数据库
port
数据库端口，默认为 3306
bind_address
当客户端有多个网络接口时，指定连接到主机的接口。参数可以是主机名或IP地址。
unix_socket
unix 套接字地址，区别于 host 连接
read_timeout
读取数据超时时间，单位秒，默认无限制
write_timeout
写入数据超时时间，单位秒，默认无限制
charset
数据库编码
sql_mode
指定默认的 SQL_MODE
read_default_file
Specifies my.cnf file to read these parameters from under the [client] section.
conv
Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshaling of types.
use_unicode
Whether or not to default to unicode strings. This option defaults to true for Py3k.
client_flag
Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass
设置默认的游标类型
init_command
当连接建立完成之后执行的初始化 SQL 语句
connect_timeout
连接超时时间，默认 10，最小 1，最大 31536000
ssl
A dict of arguments similar to mysql_ssl_set()’s parameters. For now the capath and cipher arguments are not supported.
read_default_group
Group to read from in the configuration file.
compress
Not supported
named_pipe
Not supported
autocommit
是否自动提交，默认不自动提交，参数值为 None 表示以服务器为准
local_infile
Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet
发送给服务器的最大数据量，默认为 16MB
defer_connect
是否惰性连接，默认为立即连接
auth_plugin_map
A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental)
server_public_key
SHA256 authenticaiton plugin public key value. (default: None)
db
参数 database 的别名
passwd
参数 password 的别名
binary_prefix
Add _binary prefix on bytes and bytearray. (default: False)
游标的类型
Cursor: 默认，元组类型 普通的游标对象，默认创建的游标对象DictCursor: 字典类型 以字典的形式返回操作结果SSCursor: 无缓冲元组类型 不缓存游标，主要用于当操作需要返回大量数据的时候SSDictCursor: 无缓冲字典类型 不缓存游标，将结果以字典的形式进行返回
无缓冲游标类型，适用于数据量很大，一次性返回太慢，或者服务端带宽较小
connection = pymysql.connect(host='localhost',
user='root',
password='root',
db='db',
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
#创建时指定
# 或者创建游标时指定
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
class pymysql.cursors.Cursor(connection)#游标结果作为元祖的元祖返回
说明：
这是您用于与数据库交互的对象。
不要自己创建Cursor实例。调用connections.Connection.cursor()
class pymysql.cursors.SSCursor(connection)#无缓冲游标结果作为元祖的元祖返回，
用途：
用于返回大量数据查询，或慢速网络连接到远程服务器
不将每行数据复制到缓冲区，根据需要获取行。客户端内存使用少
在慢速网络上或结果集非常大时行返回速度快
限制：
MySQL协议不支持返回总行数，判断有多少行唯一方法是迭代返回的每一行。
目前无法向后滚动，因为只有当前行保存在内存中。
class pymysql.cursors.DictCursor(connection) #将结果作为字典返回游标
class pymysql.cursors.SSDictCursor(connection)#无缓冲游标结果作为字典返回
游标属性
# PEP 249 - Python数据库API规范v2.0
name #必需
type_code#必需
display_size
internal_size
precision
scale
null_ok
cursor.description#返回游标活动状态 #(('VERSION()', 253, None, 24, 24, 31, False),)
包含7个元素的元组：
(name, type_code, display_size, internal_size, precision, scale, null_ok)
##################################################################################
cursor.max_stmt_length#1024000
cursor.rownumber#5 #当前结果集中游标所在行的索引(起始行号为 0)
cursor.arraysize#1 #此读/写属性指定用.fetchmany()一次获取的行数。
# 默认1表示一次获取一行；也可以用于执行.executemany()
cursor.lastrowid#None #只读属性提供上次修改行的rowid
# DB仅在执行单个INSERT 操作时返回rowid 。
# 如未设rowid或DB不支持rowid应将此属性设置为None
# 如最后执行语句修改了多行，例如用INSERT和.executemany()时lastrowid语义是未定义
cursor.rowcount #5 #最近一次 execute() 创建或影响的行数
# 如无cursor.execute()或接口无法确定最后一个操作的rowcount则该属性为-1
# 该行数属性可以在动态更新其值的方式来编码。
# 这对于仅在第一次调用.fetch()方法后返回可用rowcount值的 数据库非常有用。
游标的移动
所有的数据查询操作均基于游标，我们可以通过cursor.scroll(num, mode)控制游标的位置
cursor.scroll(1, mode='relative') # 相对当前位置移动
cursor.scroll(2, mode='absolute') # 相对绝对位置移动
游标移动后,下次获取的是游标的下一条数据
防止sql 注入
#避免注入，使用pymysql提供的参数化语句
user="u1' or '1'-- "
passwd="u1pass"
#执行参数化查询
row_count=cursor.execute("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
# 内部执行参数化生成的SQL语句，对特殊字符进行了加\转义，避免注入语句生成。
sql=cursor.mogrify("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
print(sql)
# 被转义的语句
select user,pass from tb7 where user='u1\' or \'1\'-- ' and pass='u1pass'
浙公网安备 33010602011771号