装置
pip install PyMySQL
竖立数据库衔接
import pymysql connection = pymysql.connect(host='localhost', port=3306, user='root', password='root', db='demo', charset='utf8')
参数列表:
参数 | 形貌 |
---|---|
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) |
实行 SQL
cursor.execute(sql, args) 实行单条 SQL
# 猎取游标 cursor = connection.cursor() # 竖立数据表 effect_row = cursor.execute(''' CREATE TABLE `users` ( `name` varchar(32) NOT NULL, `age` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ''') # 插进去数据(元组或列表) effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18)) # 插进去数据(字典) info = {'name': 'fake', 'age': 15} effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info) connection.commit()
executemany(sql, args) 批量实行 SQL
# 猎取游标 cursor = connection.cursor() # 批量插进去 effect_row = cursor.executemany( 'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [ ('hello', 13), ('fake', 28), ]) connection.commit()
注重:INSERT、UPDATE、DELETE 等修正数据的语句需手动实行connection.commit()
完成对数据修正的提交。
猎取自增 ID
cursor.lastrowid
查询数据
# 实行查询 SQL cursor.execute('SELECT * FROM `users`') # 猎取单条数据 cursor.fetchone() # 猎取前N条数据 cursor.fetchmany(3) # 猎取一切数据 cursor.fetchall()
游标掌握
一切的数据查询操纵均基于游标,我们能够经由过程cursor.scroll(num, mode)
掌握游标的位置。
cursor.scroll(1, mode='relative') # 相对当前位置挪动 cursor.scroll(2, mode='absolute') # 相对相对位置挪动
设置游标范例
查询时,默许返回的数据范例为元组,能够自定义设置返回范例。支撑5种游标范例:
Cursor: 默许,元组范例
DictCursor: 字典范例
DictCursorMixin: 支撑自定义的游标范例,需先自定义才可运用
SSCursor: 无缓冲元组范例
SSDictCursor: 无缓冲字典范例
无缓冲游标范例,适用于数据量很大,一次性返回太慢,或许服务端带宽较小时。源码解释:
Unbuffered Cursor, mainly useful for queries that return a lot of data, or for connections to remote servers over a slow network.Instead of copying every row of data into a buffer, this will fetch rows as needed. The upside of this is the client uses much less memory, and rows are returned much faster when traveling over a slow network
or if the result set is very big.There are limitations, though. The MySQL protocol doesn't support returning the total number of rows, so the only way to tell how many rows there are is to iterate over every row returned. Also, it currently isn't possible to scroll backwards, as only the current row is held in memory.
竖立衔接时,经由过程 cursorclass 参数指定范例:
connection = pymysql.connect(host='localhost', user='root', password='root', db='demo', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
也能够在竖立游标时指定范例:
cursor = connection.cursor(cursor=pymysql.cursors.DictCursor)
事件处理
开启事件
connection.begin()
提交修正
connection.commit()
回滚事件
connection.rollback()
防 SQL 注入
转义特别字符
connection.escape_string(str)
参数化语句
支撑传入参数举行自动转义、格式化 SQL 语句,以防止 SQL 注入等安全问题。
# 插进去数据(元组或列表) effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%s, %s)', ('mary', 18)) # 插进去数据(字典) info = {'name': 'fake', 'age': 15} effect_row = cursor.execute('INSERT INTO `users` (`name`, `age`) VALUES (%(name)s, %(age)s)', info) # 批量插进去 effect_row = cursor.executemany( 'INSERT INTO `users` (`name`, `age`) VALUES (%s, %s) ON DUPLICATE KEY UPDATE age=VALUES(age)', [ ('hello', 13), ('fake', 28), ])
参考资料
Python中操纵mysql的pymysql模块详解
Python之pymysql的运用
相干引荐:
python完成telnet客户端的要领
MemCached的PHP客户端操纵类二
数据库mysql视频教程
以上就是分享一个纯 Python 完成的 MySQL 客户端操纵库的细致内容,更多请关注ki4网别的相干文章!