PyMySQL的基本操作
官网文档链接：https://pymysql.readthedocs.io/en/latest/
1.PyMySQL 安装
1 pip install pymysql
2.数据库连接
注：MySQL数据库已创建数据库TESTDB，在TESTDB数据库中您已经创建了表 EMPLOYEE
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # 使用execute()方法执行SQL查询 18 sql = 'SELECT VERSION()' 19 cursor.execute(sql) 20 21 # 使用fetchone()获取单条数据 22 data = cursor.fetchone() 23 print("数据库版本：%s" % data) 24 25 # 关闭数据库 26 connection.close()
执行后的结果：
数据库版本：5.7.21-log
2.创建数据库表
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # 使用execute()方法执行SQL,如果表存在则删除 18 sql = 'DROP TABLE IF EXISTS EMPLOYEE' 19 cursor.execute(sql) 20 21 # 创建表 22 create_table_sql = """CREATE TABLE EMPLOYEE ( 23 ID INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT, 24 FIRST_NAME CHAR(20) NOT NULL, 25 LAST_NAME CHAR(20), 26 AGE INT, 27 SEX CHAR(1), 28 SALARY FLOAT)""" 29 cursor.execute(create_table_sql) 30 31 # 关闭数据库 32 connection.close()
3.数据库插入操作
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # SQL插入语句 18 insert_sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, SALARY) \ 19 VALUES ('%s','%s', %s, '%s', %s)" \ 20 % ('Mac', 'Mohan', 20, 'M', 2000.00) 21 try: 22 # 执行sql语句 23 cursor.execute(insert_sql) 24 # 提交 25 connection.commit() 26 except: 27 # 发生错误时回滚 28 connection.rollback() 29 30 # 关闭数据库 31 connection.close()
批量插入：
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # 插入语句 18 insert_sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, SALARY) VALUES (%s, %s, %s, %s, %s)" # 注意此处不要使用'%s'，使用s%站位 19 args_list = [('Mei', 'Han', 20, 'F', 3000.00), ('Obama', 'Qiao', 57, 'M', 2348.01), ('Jim', 'Cluse', 35, 'F', 2231.01), ('Macer', 'Jordan', 45, 'M', 2340.10)] 20 21 try: 22 # 执行sql语句 23 cursor.executemany(insert_sql, args_list) 24 # 提交 25 connection.commit() 26 except: 27 # 发生错误时回滚 28 connection.rollback() 29 30 # 关闭数据库 31 connection.close()
4.数据库查询操作
Python查询MySQL使用fetchone()获取单条数据，使用fetchmany(size=None)获取多条数据，使用fetchall()获取所有数据。
- fetchone()：获取下个一个查询结果集。结果集是一个对象（目前看到的事tuple格式）
- fetchmany(size=None)：从结果集中获取size个结果，如果size=None，则获取第一个
- fetchall()：获取全部的返回行。
- rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # 查询语句 18 select_sql = "SELECT * FROM EMPLOYEE WHERE SALARY>2000" 19 20 # fetchone()方法 21 try: 22 # 执行sql语句 23 cursor.execute(select_sql) 24 result_one = cursor.fetchone() 25 result_two = cursor.fetchone() 26 result_three = cursor.fetchone() 27 result_four = cursor.fetchone() 28 print(result_one) 29 print(result_two) 30 print(result_three) 31 print(result_four) 32 except: 33 print('fetchone获取数据失败') 34 35 # fetchmany(size=none) 36 try: 37 cursor.execute(select_sql) 38 result_many = cursor.fetchmany(size=None) 39 print(result_many) 40 except: 41 print('fetchmany获取数据失败') 42 43 # fetchall()和rowcount 44 try: 45 cursor.execute(select_sql) 46 result_all = cursor.fetchall() 47 row_count = cursor.rowcount 48 print(result_all) 49 print(row_count) 50 except: 51 print('fetchmany获取数据失败') 52 53 # 关闭数据库 54 connection.close()
以上脚本执行结果如下：
1 (2, 'Jim', 'Cluse', 32, 'M', 5000.78) 2 (3, 'Mary', 'Lily', 28, 'F', 3564.89) 3 (4, 'Tom', 'Kuke', 35, 'M', 4545.56) 4 None 5 ((2, 'Jim', 'Cluse', 32, 'M', 5000.78), (3, 'Mary', 'Lily', 28, 'F', 3564.89)) 6 ((2, 'Jim', 'Cluse', 32, 'M', 5000.78), (3, 'Mary', 'Lily', 28, 'F', 3564.89), (4, 'Tom', 'Kuke', 35, 'M', 4545.56)) 7 3
5.数据库更新操作
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # 更新语句 18 update_sql = "UPDATE EMPLOYEE SET SALARY = SALARY + 1000 WHERE SEX='%c'" % ('M') 19 20 try: 21 # 执行sql语句 22 cursor.execute(update_sql) 23 connection.commit() 24 except: 25 # 发生错误时回滚 26 connection.rollback() 27 28 # 关闭数据库 29 connection.close()
6.数据库删除操作
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个游标对象cursor 15 cursor = connection.cursor() 16 17 # 删除语句 18 delete_sql = "DELETE FROM EMPLOYEE WHERE AGE>'%s'" % (30) 19 20 # fetchone()方法 21 try: 22 # 执行sql语句 23 cursor.execute(delete_sql) 24 # 提交修改 25 connection.commit() 26 except: 27 # 发生错误时回滚 28 connection.rollback() 29 30 # 关闭数据库 31 connection.close()
7.Connection类和Cursor类
7.1 Connection类
class pymysql.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, autocommit=False, db=None, passwd=None, local_infile=False, max_allowed_packet=16777216, defer_connect=False, auth_plugin_map=None, read_timeout=None, write_timeout=None, bind_address=None, binary_prefix=False, program_name=None, server_public_key=None)
参数：
host：数据库服务器所在的主机
user：用户名
password：密码
database：数据库名, None to not use a particular one.
port：端口号，默认值3306
charset：编码（注：utf8不是utf-8）
begin()：开始事务
close()：关闭连接
Raise:如果该连接已关闭，则会引起异常
commit():提交事务
cursor(cursor=None)：创建一个游标
参数：Cursor，SSCursor，DictCursor和SSDictCursor中的任意一个，默认None的时候用的Cursor
open：如果连接打开，则返回True
ping(reconnect=True)：检查数据库服务连接是否正常
参数：reconnect 如果连接断开则重新连接
Raise：如果连接已经关闭，且此时reconnect=False时则会引起异常
rollback()：回滚事务
select_db()：选择数据库
参数：db 数据库名称
show_warnings()：发送“警告”SQL命令
7.2 Cursor类
用于数据库交互的对象。不要用Corsor自己创建游标实例，调用connections.Connect.cursor()。
close()：使用所有数据时关闭游标
execute(query, args=None)：执行一个query语句
参数：
query(str)：Query to execute
args(tuple,list or dict)可选参数
返回：受影响的行数（返回类型：int）
注：如果参数args是列表或元组，%s可以用作查询中的占位符。如果参数args是一个字典， %(name)s可以用作查询中的占位符。
executemany(query, args)：通过参数批量执行query语句
参数：
query： Query to execute on the server
args：序列的序列或者映射的序列。
返回：受影响的行数（返回类型：int）
注：该方法提高了多行INSERT和REPLACE的性能。否则，它相当于用execute()遍历所有参数。
fetchall()：获取所有行
fetchmany(size=None)：根据size值获取行数
fetchone()：获取下一行
max_stmt_length=1024000：executemany()生成的语句的最大大小。
允许语句的最大大小为max_allowed_packet - packet_header_size。max_allowed_packet的默认值是1048576。
mogrify(query, args=None)：
7.3 SSCursor类，DictCursor类和SSDictCursor类
SSCursor中的方法有：close()、fetchall()、fetchall_unbuffered()、fetchmany(size=None)、fetchone()、read_next
SSCursor，无缓冲游标，主要用于返回大量数据的查询，或在网速慢的时候连接远程服务器。
使用 SSCursor (流式游标)，避免客户端占用大量内存。(这个 cursor 实际上没有缓存下来任何数据，它不会读取所有所有到内存中，它的做法是从储存块中读取记录，并且一条一条返回给你。)，需要注意的：因为 SSCursor 是没有缓存的游标,结果集只要没取完，这个 conn 是不能再处理别的 sql，包括另外生成一个 cursor 也不行的。如果需要干别的，请另外再生成一个连接对象。
DictCursor的返回结果是字典类型：
1 # -*- coding:utf-8 -*- 2 import pymysql 3 4 # 打开数据库连接 5 connection = pymysql.connect( 6 host='localhost', # IP，MySQL数据库服务器IP地址 7 port=3306, # 端口，默认3306，可以不输入 8 user='root', # 数据库用户名 9 password='yxh1990', # 数据库登录密码 10 database='testdb', # 要连接的数据库 11 charset='utf8' # 字符集，注意不是'utf-8' 12 ) 13 14 # 使用cursor()方法创建一个字典游标对象DictCursor 15 dict_cursor = connection.cursor(pymysql.cursors.DictCursor) 16 17 # 查询语句 18 select_sql = "SELECT * FROM EMPLOYEE" 19 20 try: 21 # 执行sql语句 22 dict_cursor.execute(select_sql) 23 result_all = dict_cursor.fetchall() 24 print(result_all) 25 except: 26 print('查询发生错误！') 27 28 # 关闭数据库 29 connection.close()
执行结果：
[{'ID': 1, 'FIRST_NAME': 'Mac', 'LAST_NAME': 'Mohan', 'AGE': 20, 'SEX': 'M', 'SALARY': 3000.0},
{'ID': 3, 'FIRST_NAME': 'Mary', 'LAST_NAME': 'Lily', 'AGE': 28, 'SEX': 'F', 'SALARY': 3564.89},
{'ID': 4, 'FIRST_NAME': 'Mei', 'LAST_NAME': 'Han', 'AGE': 20, 'SEX': 'F','SALARY': 3000.0},
{'ID': 5, 'FIRST_NAME': 'Obama', 'LAST_NAME': 'Qiao', 'AGE': 57, 'SEX': 'M', 'SALARY': 2348.01},
{'ID': 6, 'FIRST_NAME': 'Jim', 'LAST_NAME': 'Cluse', 'AGE': 35, 'SEX': 'F', 'SALARY': 2231.01},
{'ID': 7, 'FIRST_NAME': 'Macer', 'LAST_NAME': 'Jordan', 'AGE': 45, 'SEX': 'M', 'SALARY': 2340.1}]
