pymysql的基本使用方法

数据库开发

驱动

• MySQL基于TCP协议之上开发，但是网络连接后，传输的数据必须遵循MySQL的协议。封装好MySQL协议的包，就是驱动程序。
• MySQL的驱动
  • MySQLdb 最有名的库。对MySQL的C Client封装实现，支持Python 2，不更新了，不支持Python3
  • MySQL官方Connector
    • Mysql官网:https://dev.mysql.com/downloads/connector/
  • pymysql
  • 语法兼容MySQLdb，使用Python写的库，支持Python 3

pymysql使用

安装

pip install pymysql

创建数据库和表

CREATE DATABASE IF NOT EXISTS school;
SHOW DATABASES;
USE school;

CREATE TABLE `student` (  
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,  
    `age` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

连接Conncet

首先,必须建立一个连接数据库通道 --连接
pymysql.connect()方法返回的是Connections模块下的connection类实例,connection方法传参就是给Connection类的__init__提供参数

connect = Connection = Connect       #

def Connect(*args, **kwargs):
    from .connections import Connection
    return Connection(*args, **kwargs)

class Connection(object):
    def __init__(self, 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=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=16*1024*1024, 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):

建立连接如下:

import pymysql

IP ='192.168.176.134'
USERNAME = 'yu'
PASSWORD = '1234'
DATABASE = 'test'
PORT = 3306

conn = pymysql.connect(host=IP, user=USERNAME, password=PASSWORD,database=DATABASE, port=PORT)  # conn为一个连接对象

Connection初始化常用参数	说明
host	主机
user	用户名
password	密码
database	数据库
port	端口

Connection.ping()方法,测试数据库服务器是否还活着,有一个参数reconnect表示断开与服务器连接是否重连,连接关闭出现异常.(ping)并不能解决连接的问题,只是检查是否连接.

import pymysql
conn = None
try:
    conn = pymysql.connect('192.168.142.10','xw','xw','dbs1')
    conn.ping(False)
finally:
    if conn:
        conn.close()

游标Cursor
操作数据库,必须使用游标,需要先获取一个游标对象,
Connection.cursor(cursor=None)方法返回一个新的游标对象.
连接没有关闭前,游标对象可以反复使用.
cursor参数,可以指定一个Cursor类,如果为None,则使用默认的Cursor类.

操作数据库
数据库操作需要使用Cursor类实例,提供execute()方法,执行SQL语句,成功返回影响的行数

新增记录
使用insert into语句插入数据

import pymysql

IP ='172.198.11.11'
USERNAME = 'yu'
PASSWORD = '123'
DATABASE = 'test'
PORT = 3306

conn = None
cursor = None  #游标,对结果集的操作
try:
   conn = pymysql.connect(host=IP, user=USERNAME, password=PASSWORD,database=DATABASE, port=PORT)  # 建立连接对象(socket)
   cursor = conn.cursor() #建立游标对象

   # sql = "select * from reg"   #sql 语句
   # sql = "insert into reg (long_name,name,password) values('cc','cc','123456')"  #sql 插入语句
   sql = "update reg set name = 'xx' where id = 3"     #sql 更新语句

   row = cursor.execute(sql)   #结果集,影响的行数
   conn.commit()               #操作完后,一定要提交
   print(row)

finally:     # cursor 和conn 都是对象,都资源需要释放
   if conn:
       conn.close()
   if cursor:
       cursor.close()

事务管理

Connection类有三个方法：
begin 开始事务
commit 将变更提交
rollback 回滚事务批量增加数据

import pymysql

IP ='172.198.11.11'
USERNAME = 'yu'
PASSWORD = '123'
DATABASE = 'test'
PORT = 3306

conn = None
cursor = None  #游标,对结果集的操作
try:
   conn = pymysql.connect(host=IP, user=USERNAME, password=PASSWORD,database=DATABASE, port=PORT)  # 建立连接对象(socket)
   cursor = conn.cursor() #建立游标对象

   sql = "select id from reg where long_name = 'rose'"
   row = cursor.execute(sql)   #结果集,影响的行数

   if row ==1:
       userid = cursor.fetchone()[0]      #定位到ID字段
       sql1 = "update reg set name = 'rose' where id = '{}'".format(userid)
       
   conn.commit()               #操作完后,一定要提交
except:                         #中途有异常,则调用回滚操作
   conn.rollback()

finally:     # cursor 和conn 都是对象,都资源需要释放
   if conn:
       conn.close()
   if cursor:
       cursor.close()

pymysql一般流程

• 建立连接
• 获取游标
• 执行SQL
• 提交事务
• 释放资源

查询

Cursor类的获取查询结果集的方法有:fetchone()、fetchmany(size=None)、fetchall()

名称	说明
fetchone()	获取结果集的下一行
fetchmany(size=None)	size指定返回的行数,None则返回空元组
fetchall()	返回剩余所有行，如果走到末尾，就返回空元组，否则返回一个元组，其元素是每一行的记录封装的一个元组
cursor.rownumber	返回当前行号
cursor.rowccount	返回总行数

注意：fetch操作的是结果集，结果集是保存在客户端的，也就是说fetch的时候，查询已经结束了。

conn = None
cursor = None  #游标,对结果集的操作

conn = pymysql.connect(host=IP, user=USERNAME, password=PASSWORD,database=DATABASE, port=PORT)  # 建立连接对象(socket)
cursor = conn.cursor() #建立游标对象

sql = "select * from reg"   #sql 语句

print(cursor.fetchone(),111)
print(cursor.fetchmany(2))
print(cursor.fetchall())

带列名查询

Cursor类有一个Mixin的子类DictCursor
只需要cursor = conn.cursor(DictCursor)就可以了

from pymysql.cursors import DictCursor

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306) 
cursor = conn.cursor(DictCursor)
print(cursor.fetchone())
print(cursor.fetchmany(2))
print(cursor.fetchall())

返回一行，是一个字典。返回多行，放在列表中，元素是字典，代表一行。

SQL注入攻击

SQL注入攻击

• 猜测后台数据库的查询语句,使用拼接字符串等方式，从而经过设计为服务端传参，令其拼接出特殊字符串的SQL语句，返回攻击者想要的结果,就是注入攻击。
• 永远不要相信客户端传来的数据是规范及安全的！！！
• 如何解决注入攻击？
  • 参数化查询，可以有效防止注入攻击，并提高查询的效率。
  • Cursor.execute(query, args=None), args必须是元组、列表或字典。如果查询字符串使用%s,就使用元组,如果查询字符串使用%(name)s，就必须使用字典

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306) 
cursor = conn.cursor(DictCursor)

sql = "select * from reg where id = {}".format('2 or 1 = 1')   #当攻击者通过1=1拼好字符串后,就可以看到所有记录
cursor.execute(sql)

for i in cursor.fetchall() :
    print(i)
------------------------------------------------------------------------
{'id': 2, 'long_name': 'tom', 'name': 'bb', 'password': '123'}
{'id': 3, 'long_name': 'jurry', 'name': 'xx', 'password': '12345'}
{'id': 4, 'long_name': 'jake', 'name': 'bend', 'password': '12345'}
{'id': 5, 'long_name': 'jake', 'name': 'divd', 'password': '12345'}
{'id': 6, 'long_name': 'dbs', 'name': 'dbs', 'password': '2345'}
{'id': 7, 'long_name': 'rose', 'name': 'rose', 'password': '12345'}
{'id': 8, 'long_name': 'wx', 'name': 'wx', 'password': '1234567'}

解决的方法如下:

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306)
cursor = conn.cursor(DictCursor)

id = '3 or 1=1'
sql = "select * from reg where id=%s"
cursor.execute(sql,(id,))
print(cursor.fetchall())
------------------------------------------------------------------------
F:\pycharm\venv1\lib\site-packages\pymysql\cursors.py:329: Warning: (1292, "Truncated incorrect DOUBLE value: '3 or 1=1'")   #结果产生了一个警告
  self._do_get_result()
[{'id': 3, 'long_name': 'jurry', 'name': 'xx', 'password': '12345'}]  # 参数化查询,最后只查到一条记录,id = '3 or 1=1',只截取了前面的3

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306)
cursor = conn.cursor(DictCursor)

sql = "select * from reg where id=%(a)s and password=%(b)s"  # 参数化查询,解决注入攻击问题
cursor.execute(sql,{'a':'3','b':'12345'})
print(cursor.fetchall())
------------------------------------------------------------------------
[{'id': 3, 'long_name': 'jurry', 'name': 'xx', 'password': '12345'}]

参数化查询为什么提高效率?

• 原因就是——SQL语句缓存。数据库服务器一般会对SQL语句编译和缓存，编译只对SQL语句部分，所以参数中就算有SQL指令也不会被当做指令执行。编译过程，需要词法分析、语法分析、生成AST、优化、生成执行计划等过程，比较耗费资源。服务端会先查找是否对同一条查询语句进行了缓存，如果缓存未失效，则不需要再次编译，从而降低了编译的成本，降低了内存消耗。可以认为SQL语句字符串就是一个key，如果使用拼接方案，每次发过去的SQL语句都不一样，都需要编译并缓存。大量查询的时候，首选使用参数化查询，以节省资源。开发时，应该使用参数化查询。注意：这里说的是查询字符串的缓存，不是查询结果的缓存

---

批量执行executemany()

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306)
cursor = conn.cursor(DictCursor)

sql = "insert into reg (long_name,name,password) values ('jake',%s,%s)"
cursor.executemany(sql,(("jake{}".format(i),'axc{}'.format(i)) for i in range(5)))
conn.commit()

上下文支持

查看连接类和游标类的源码

# 连接类
class Connection(object)
    def __enter__(self):
        """Context manager that returns a Cursor"""
        warnings.warn(
            "Context manager API of Connection object is deprecated; Use conn.begin()",
            DeprecationWarning)
        return self.cursor()    #返回一个游标对象 ,占用资源

    def __exit__(self, exc, value, traceback):
        """On successful exit, commit. On exception, rollback"""
        if exc:
            self.rollback()    #退出有异常,回滚操作
        else:
            self.commit()      #退出没有异常,提交OK

# 游标类
class Cursor(object):
    def __enter__(self):
        return self

    def __exit__(self, *exc_info):
        del exc_info
        self.close()      # 退出时会关闭游标对象,释放资源

连接类进入上下文的时候会返回一个游标对象，退出时如果没有异常会提交更改。游标类也使用上下文，在退出时关闭游标对象。

---

• 下面是使用游标的上下文

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306)

try:
    with conn.cursor(DictCursor) as cursor:  # 此with是操作新产生的cursor 当with结束时,新产生的cursor会关闭
        for i in range(5):
            insert_sql = "insert into reg (long_name,name,password) values ('rose','rose{}','abx{}')".format(i,uuid.uuid4())
            cursor.execute(insert_sql)
    conn.commit()
except Exception as er:
    conn.rollback()
finally:
    conn.close()

• 下面是用连接的上下文
  conn的with进入是返回一个新的cursor对象，退出时，只是提交或者回滚了事务。并没有关闭cursor和conn。不关闭cursor就可以接着用，省的反复创建它(如果要关闭cursor,则在for循环外面再套一个with即可,后面就不需要再关闭cursor)。

conn = pymysql.connect(host='172.198.11.11', user='yu', password='123',database=dbas, port=3306)

with conn as cursor:  #源码中有return self.cursor()  ==>即conn.cursor() ==>得到cursor游标对象
    for i in range(3):
        insert_sql = "insert into reg (name,password) values('tom{0}',20+{0})".format(i)
        rows = cursor.execute(insert_sql)
sql = "select * from reg"
cursor.execute(sql)
print(cursor.fetchall())

cursor.close()  #最后关闭cursor和conn对象
conn.close()