分享一个纯 Python 完成的 MySQL 客户端操纵库【MySQL教程】,pymysql,mysql,python

PyMySQL 是一个纯 Python 完成的 MySQL 客户端操纵库，支撑事件、存储历程、批量实行等。PyMySQL 遵照 Python 数据库 API v2.0 范例，并包含了 pure-Python MySQL 客户端库。

装置

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网别的相干文章！

标签：pymysqlmysqlpython