pymysql模块的使用

ゝ一世哀愁。 2021-12-23 03:07 448阅读 0赞

# pymysql模块的使用 #

### 文章目录 ###

*  pymysql模块的使用
 *   *  数据库开发
     *  pymysql的使用
     *   *  链接Connect
         *  游标Cursor
     *  操作数据库
     *   *  新增记录
         *  查询
         *  带列名查询
         *  SQL注入攻击
         *  批量执行executemany()
         *  上下文支持

## 数据库开发 ##

*  **驱动**  
    Mysql基于TCP协议之上开发，但是网络连接后，传输的数据必须遵循MySQL的协议。封装好MySQL协议的包，就是驱动程序。

1.  MySQL的驱动
    
     *  MySQLdb:有名的库，对MySQL的client封装实现，支持Python2，不更新了，不支持Python3
     *  MySQL官方Connector
        
         *  Mysql官网[https://dev.mysql.com/downloads/connector/][https_dev.mysql.com_downloads_connector]
     *  pymysql:语法兼容MySQLdb,使用Python写的库，支持Python3

## pymysql的使用 ##

**安装**: `pip install pymysql`

*  创建数据库和表

CREATE DATABASE IF NOT EXISTS xdd;
    USE xdd;
    
    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;

### 链接Connect ###

*  第一步：先建立连接数据的传输数据通道–链接
    
    1.  pymysql.connect()方法返回的是Connections模块下的Connection类实例。
    2.  connect方法传参就是给Connection类的\_\_init\_\_提供参数

<table> 
 <thead> 
  <tr> 
   <th align="left">Connection初始化方法常用参数</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">host</td> 
   <td align="left">主机，ip地址</td> 
  </tr> 
  <tr> 
   <td align="left">user</td> 
   <td align="left">用户名</td> 
  </tr> 
  <tr> 
   <td align="left">password</td> 
   <td align="left">密码</td> 
  </tr> 
  <tr> 
   <td align="left">database</td> 
   <td align="left">数据库名称</td> 
  </tr> 
  <tr> 
   <td align="left">port</td> 
   <td align="left">数据库端口</td> 
  </tr> 
 </tbody> 
</table>

Connection.ping()方法，测试数据库服务器是否活着。有一个参数reconnect表示断开与服务器连接是否重连。连接关闭抛出异常。

import pymysql
    
    conn = None
    try:
        ip = "127.0.0.1"
        username = "xdd"
        password = "xdd"
        database = "xdd"
        port = 3306
        conn = pymysql.connect(ip,username,password,database,port=port)
        conn.ping(False) #ping不通则抛出异常
    finally:
        if conn:
            conn.close()

### 游标Cursor ###

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

## 操作数据库 ##

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

### 新增记录 ###

使用insert int 语句插入数据库。

*  **事务管理**
    
    1.  Connection类有三个方法：
        
         *  begin 开始事务
         *  commit将变更提交
         *  rollback回滚事务

### 查询 ###

*  一般流程
    
    1.  建立连接
    2.  获取游标
    3.  执行SQL
    4.  提交事务
    5.  释放资源
 *  Cursor类的获取查询结果集的方法由
    
    1.  fetchone() \#获取当前游标所在行数据，使游标下移一位。如果已经是末尾，返回None
    2.  fetchmany(size=None) \#是游标向下移动size行，返回移动中所有行的数据。如果已经是末尾，返回空元组
    3.  fetchall() \#获取当前游标游标所在行，到最后一行数据。游标移动到末尾。如果已经是末尾，返回空元组

<table> 
 <thead> 
  <tr> 
   <th align="left">名称</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">fetchone()</td> 
   <td align="left">获取结果集的下一行</td> 
  </tr> 
  <tr> 
   <td align="left">fetchmany(size=None)</td> 
   <td align="left">size指定返回的行数的行，None则返回空元组</td> 
  </tr> 
  <tr> 
   <td align="left">fetchall()</td> 
   <td align="left">返回剩余所有行，如果走到末尾，就返回空元组，否则返回一个元组，其元素是每一行的记录封装的一个元组</td> 
  </tr> 
  <tr> 
   <td align="left">cursor.rownumber</td> 
   <td align="left">返回当前行号。可以修改，支持负索引</td> 
  </tr> 
  <tr> 
   <td align="left">cursor.rowcount</td> 
   <td align="left">返回总行数</td> 
  </tr> 
 </tbody> 
</table>

import pymysql
    
    conn = None
    cursor = None
    try:
        ip = "127.0.0.1"
        username = "xdd"
        password = "xdd"
        database = "xdd"
        port = 3306
        #创建数据库连接对象
        conn = pymysql.connect(ip,username,password,database,port=port)
        #连接数据库
        cursor = conn.cursor()
    
        insert_sql = "select * from student"
        # 执行sql语句
        rows = cursor.execute(insert_sql) #返回受影响的行
        print("- "*10,cursor.rownumber, cursor.rowcount)
        print(cursor.fetchone())
        print(cursor.fetchone())
        print("- "*10,cursor.rownumber, cursor.rowcount)
        print(cursor.fetchmany(2))
        print("- " * 10, cursor.rownumber, cursor.rowcount)
        print(cursor.fetchmany(2))
        print("- " * 10, cursor.rownumber, cursor.rowcount)
        print(cursor.fetchall())
        print("- " * 10, cursor.rownumber, cursor.rowcount)
        print(cursor.fetchone())
        print(cursor.fetchmany(1))
        print(cursor.fetchall())
    
        #修改游标索引
        cursor.rownumber = 0 #正负都支持
        for x in cursor.fetchall():
            print(x,"~~~~~~~")
    except:
        conn.rollback() #如果提交失败回滚
    finally:
        if conn:
            conn.close()
        if cursor:
            cursor.close()

![pymysql\_001][pymysql_001]

### 带列名查询 ###

*  Cursor类有一个Mixin的子类DictCursor。只需要`cursor = conn.cursor(DictCursor)就可以了`
 *  返回一行，是一个字典
 *  返回多行，放在列表中，元素是字典，代表一行。

# 返回结果
    {'name': 'tom', 'age': 18}
    {'name': 'tom0', 'age': 18}

*  简单示例

![pymysql\_002][pymysql_002]

### SQL注入攻击 ###

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

import pymysql
    
    ip = "127.0.0.1"
    username = "xdd"
    password = "xdd"
    database = "xdd"
    port = 3306
    #创建数据库连接对象
    conn = pymysql.connect(ip,username,password,database,port=port)
    
    cursor = None
    try:
    
        #连接数据库
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        # insert_sql = "select * from student where name like %(name)s and age > %(age)s;"
        # # 执行sql语句
        # rows = cursor.execute(insert_sql,{"name":"tom%","age":17}) #返回受影响的行
    
        insert_sql = "select * from student where name like %s and age > %s;"
        rows = cursor.execute(insert_sql,("tom%",17))
    
        for x in cursor.fetchall():
            print(x)
    except:
        conn.rollback() #如果提交失败回滚
    finally:
        if conn:
            conn.close()
        if cursor:
            cursor.close()

![pymysql\_003][pymysql_003]

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

### 批量执行executemany() ###

*  需要批量执行可以使用executemany()

import pymysql
    
    ip = "127.0.0.1"
    username = "xdd"
    password = "xdd"
    database = "xdd"
    port = 3306
    #创建数据库连接对象
    conn = pymysql.connect(ip,username,password,database,port=port)
    cursor = None
    try:
        #连接数据库
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        insert_sql = "insert into student (name,age) values (%s,%s);"
        rows = cursor.executemany(insert_sql,(("jerry{}".format(i),18+i) for i in range(5)))
        print(rows)
    
        conn.commit()
    except:
        conn.rollback() #如果提交失败回滚
    finally:
        if conn:
            conn.close()
        if cursor:
            cursor.close()

![pymysql\_004][pymysql_004]

### 上下文支持 ###

*  查看链接类和游标类的源码

# 链接对象类
    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()
    
    #游标类
    class Cursor(object):
        def __enter__(self):
        return self
    
        def __exit__(self, *exc_info):
            del exc_info
            self.close()

*  链接类进入上下文的时候会返回一个游标对象，退出时如果没有异常会提交更改。
 *  游标类也使用上下文，在退出时关闭游标对象。
 *  conn的with进入是返回一个新的cursor对象，退出时，只是提交或者回滚了事务。并没有关闭cursor和conn。不关闭cursor就可以接着用，防止多次构建链接对象。
 *  简单示例：

import pymysql
    
    ip = "127.0.0.1"
    username = "xdd"
    password = "xdd"
    database = "xdd"
    port = 3306
    #创建连接对象
    conn = pymysql.connect(ip,username,password,database,port = port)
    try:
        with conn as cursor:
            with cursor as cur:
                sql = "select * from student"
                cursor.execute(sql)
                print(cursor.fetchall())
    finally:
        if conn:
            conn.close()

*  通过上面的实验，我们知道，链接 应该不需要反反复复创建销毁，应该是过个cursor共享一个conn。

[https_dev.mysql.com_downloads_connector]: https://dev.mysql.com/downloads/connector/
[pymysql_001]: /images/20211223/2e5f94fef4044e2ca82ee83f893e2110.png
[pymysql_002]: /images/20211223/c3462d79be494ce9b8a6942cfc8171bd.png
[pymysql_003]: /images/20211223/1ef2b55c3a074fea9718b2bde816cf94.png
[pymysql_004]: /images/20211223/986e5ef5a1654b08aecdcce7234a16fc.png