分享一个纯 Python 完成的 MySQL 客户端操纵库【MySQL教程】,pymysql,mysql,python
作者:搜教程发布时间:2019-12-01分类:MySQL教程浏览:31评论:0
装置
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网别的相干文章!
相关推荐
- python数据类型有哪几种?_Python教程,python
- 如何修改mysql的默认时区_MySQL教程,mysql,时区
- mysql数据库如何创建数据表_MySQL教程,mysql,数据表
- python针对Excel表格的操作_Python教程,python,excel
- 实例解析Python单元测试及unittest框架用法_Python教程,python,单元测试,unittest框架
- Python如何使用xlrd实现读取合并单元格_Python教程,python,xlrd
- 手把手教你在python中如何使用while True语句_Python教程,python,while true
- 给大家分享一下日常学习python的心得(详解)_Python教程,python
- linux源码安装mysql5.7_MySQL教程,linux,mysql,mysql5.7
- python如何另起一行?_Python教程,python
你 发表评论:
欢迎- MySQL教程排行
-
- 1Mysql如何挂盘_MySQL教程,Mysql
- 2mysql中key 、primary key 、unique key 与index区别详解_MySQL教程,mysql
- 3关于一道mysql查询面试题的思考解决过程_MySQL教程,mysql
- 4关于linux下mysql去除严格模式_MySQL教程,linux
- 5分离数据库和附加数据库的区别_MySQL教程,数据库
- 6利用mysql生成唯一序号_MySQL教程,mysql
- 7如何修改mysql的默认时区_MySQL教程,mysql,时区
- 8看看MySQL 5.6, 5.7, 8.0的新特性!_MySQL教程,MySQL
- 9mysql数据库如何创建数据表_MySQL教程,mysql,数据表
- 最新文章
- 广而告之