python - pymysql(11)

淩亂°似流年 2022-05-11 00:22 196阅读 0赞

前面讲过对文件或简单的持久化存储可以满足一些小应用的需求，而大型服务器或高数据容量的应用则需要更加成熟的数据库系统。但是现在是大数据时间，除了关系型数据外，还会有非关系数据库的支持，才可以在“三高“的系统上，体现性能的优势。本文主要讲述 Python 与关系型数据库（mysql）进行通信。

## 主要内容 ##

1 需要掌握的技术点  
2 安装pymysql  
3 pymysql.cursors和 pymysql.connections介绍  
4 实现简单的CRUD案例

## 需要掌握的技术点 ##

1 必须对mysql数据的基本操作要知道，比如说基本的sql 语句的编写，数据库的概念，如果有不了解的同学可 以去 [菜鸟教程mysql教程][mysql].  
2 要有python基础的知语法，毕竟pymysql是通过python来编写程序来处理数据数据的。

## 安装pymysql ##

[pymysql安装详解][pymysql].

## pymysql.connections ##

在面向对象的世界里万事万物皆对象，connections代表的是应用与数据库之间进行通信需要建立数据库连接。它是最基本的机制，只有通过数据库连接才能把命令传递到服务器，并得到返回的结果。当一个连接（或一个连接池）建立后，可以创建一个游标，向数据库发送请求，然后从数据库中接收回应。  
connections 类常见方法详解：

<table> 
 <thead> 
  <tr> 
   <th>方法名</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>close（）</td> 
   <td>关闭数据库连接</td> 
  </tr> 
  <tr> 
   <td>commit()</td> 
   <td>提交当前事务</td> 
  </tr> 
  <tr> 
   <td>rollback()</td> 
   <td>取消当前事务</td> 
  </tr> 
  <tr> 
   <td>cursor()</td> 
   <td>使用该连接创建（并返回）一个游标或类游标的对象</td> 
  </tr> 
  <tr> 
   <td>errorhandler (cxn, cur, errcls, errval )</td> 
   <td>作为给定连接的游标的处理程序</td> 
  </tr> 
 </tbody> 
</table>

## pymysql.cursors ##

cursors 游标可以让用户提交数据库命令，并获得查询的结果行

cursors 类常见方法详解

<table> 
 <thead> 
  <tr> 
   <th>方法名或属性</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>arraysize</td> 
   <td>使用 fetchmany()方法时，一次取出的结果行数，默认为 1</td> 
  </tr> 
  <tr> 
   <td>connection</td> 
   <td>创建此游标的连接（可选）</td> 
  </tr> 
  <tr> 
   <td>description</td> 
   <td>返回游标活动状态（7 项元组）：(name, type_code, display_size, internal_ size, precision, scale, null_ok)，只有 name 和 type_code 是必需的</td> 
  </tr> 
  <tr> 
   <td>lastrowid</td> 
   <td>上次修改行的行 ID（可选；如果不支持行 ID，则返回 None）</td> 
  </tr> 
  <tr> 
   <td>rowcount</td> 
   <td>上次 execute*()方法处理或影响的行数</td> 
  </tr> 
  <tr> 
   <td>callproc( func [,args])</td> 
   <td>调用存储过程</td> 
  </tr> 
  <tr> 
   <td>close()</td> 
   <td>关闭游标</td> 
  </tr> 
  <tr> 
   <td>execute (op[,args])</td> 
   <td>执行数据库查询或命令</td> 
  </tr> 
  <tr> 
   <td>executemany (op，args)</td> 
   <td>类似 execute()和 map()的结合，为给定的所有参数准备并执行数据库查询或命令</td> 
  </tr> 
  <tr> 
   <td>fetchone()</td> 
   <td>获取查询结果的下一行</td> 
  </tr> 
  <tr> 
   <td>fetchmany([size=cursor. arraysize])</td> 
   <td>获取查询结果的下面 size 行</td> 
  </tr> 
  <tr> 
   <td>fetchall()</td> 
   <td>获取查询结果的所有（剩余）行</td> 
  </tr> 
  <tr> 
   <td><strong>iter</strong>()</td> 
   <td>为游标创建迭代器对象（可选，参考 next()）</td> 
  </tr> 
  <tr> 
   <td>messages</td> 
   <td>游标执行后从数据库中获得的消息列表（元组集合，可选）</td> 
  </tr> 
  <tr> 
   <td>next ()</td> 
   <td>被迭代器用于获取查询结果的下一行（可选，类似 fetchone()，参考__iter__()）</td> 
  </tr> 
  <tr> 
   <td>nextset()</td> 
   <td>移动到下一个结果集合（如果支持）</td> 
  </tr> 
  <tr> 
   <td>rownumber</td> 
   <td>当前结果集中游标的索引（以行为单位，从 0 开始，可选）</td> 
  </tr> 
  <tr> 
   <td>setinputsizes(sizes)</td> 
   <td>设置允许的最大输入大小（必须有，但是实现是可选的）</td> 
  </tr> 
  <tr> 
   <td>setoutputsize(size[,col])</td> 
   <td>设置大列获取的最大缓冲区大小（必须有，但是实现是可选的）</td> 
  </tr> 
 </tbody> 
</table>

## 实现简单的CRUD案例 ##

查询数据

import  pymysql
    #获取连接对象
    connection = pymysql.connect("localhost","root","root","users",charset="utf8")
    #通过连接对象获取游标
    cursor = connection.cursor()
    #操作数据
    sql = "select * from my_user"
    cursor.execute(sql)
    #返回影响的行数
    #print(cursor.rowcount)
    
    #获取查询数据
    #fetchone 获取单条数据   返回是元组类型
    # result = cursor.fetchone()
    # for message in result:
    #     print(message)
    
    #获取全部数据
    for  id,name,address,sex,phone,age,pw in cursor.fetchall():
        print(id,name,address,phone,age,pw)
    
    #带参数查询
    #操作数据   （查询id＝　３　　这条记录）
    #================1 参数和写死在str中====================
    #   sql = "select * from my_user where u_id = 3"
    #cursor.execute(sql)
    
    #=============2 str动态拼接=  (解决不了sql注入问题)====================
    # sql = "select * from my_user where u_id = %d " % (4)
    # cursor.execute(sql)
    
    #=======3  通过cursor.execute(sql，arge) 参数来实现=====(可以解决sql注入问题)==============
    sql = "select * from my_user where u_id = %s "
    cursor.execute(sql,[4])
    
    #关闭资源
    cursor.close()
    connection.close()

sql注入问题

#sql注入问题
    import  pymysql
    #获取连接对象
    connection = pymysql.connect("localhost","root","root","users",charset="utf8")
    #通过连接对象获取游标
    cursor = connection.cursor()
    
    
    name = "'admin' or 1=1 -- "
    pw = "123"
    
    #=============2 str动态拼接=  (解决不了sql注入问题)====================
    # sql = "select * from my_user where u_name=%s and u_password=%s" % (name,pw)
    # cursor.execute(sql)
    
    #=======3  通过cursor.execute(sql，arge) 参数来实现=====(可以解决sql注入问题)==============
    sql = "select * from my_user where u_name=%s and u_password=%s"
    cursor.execute(sql,[name,pw])
    
    
    #fetchone 获取单条数据   返回是元组类型
    print(cursor.fetchone())
    
    #关闭资源
    cursor.close()

插入数据

import  pymysql
    # 1 Connection 连接  事务 默认是手动提交
    message ={"host":"localhost","user":"root","password":"root","database":"test","charset":"utf8"}
    connection  =  pymysql.connect(**message)
    # 2 通过connectin对象获取游标
    cursor = connection.cursor()
    #3  执行语句
    #sql ="insert into dept values (null,'研发')"
    #添加动态的数据，但是避免不了sql注入
    sql ="insert into dept values (null,'%s')" % "行政"
    #print(sql)
    #添加动态的数据 可以防止sql注入
    #sql ="insert into dept values (null,%s)"
    #print(sql)
    #print(cursor.execute(sql,["人事"]))
    print(cursor.execute(sql))
    #手动提交事务
    connection.commit()
    #关闭数据连接
    cursor.close()
    connection.close()

删除数据

# pymysql 连接数据(删除数据)
    import  pymysql
    
    #根据地址 ，端口  ，用户  密码，获取连接
    message= {"host":"localhost","user":"root","password":"root","database":"test","charset":"utf8"}
    #connection = pymysql.connect("localhost","root","root","test")
    connection = pymysql.connect(**message)
    # 通过连接对象 去获取游标对象  （执行sql）
    cursor = connection.cursor()
    
    sql = "delete from myuser where u_id =%s"
    #cursor.execute(sql,["李四2","深圳市xx","男",22,12121])
    number = cursor.execute(sql,[70])
    print(number)
    #提交事务
    connection.commit()
    #关闭资源
    cursor.close()
    connection.close()

修改数据

# pymysql 连接数据(修改数据)
    import  pymysql
    
    #根据地址 ，端口  ，用户  密码，获取连接
    message= {"host":"localhost","user":"root","password":"root","database":"test","charset":"utf8"}
    #connection = pymysql.connect("localhost","root","root","test")
    connection = pymysql.connect(**message)
    # 通过连接对象 去获取游标对象  （执行sql）
    cursor = connection.cursor()
    
    sql = "update myuser set u_name=%s where  u_id=%s"
    #cursor.execute(sql,["李四2","深圳市xx","男",22,12121])
    number = cursor.execute(sql,["张三2",70])
    print(number)
    #提交事务
    connection.commit()
    #关闭资源
    cursor.close()
    connection.close()

如果每次执行操作都需要编写获取连接，执行sql， 关闭资源的代码肯定不合理，那么怎么才可以把代码重用起来，请看后期更新。谢谢！

[mysql]: http://www.runoob.com/mysql/mysql-tutorial.html
[pymysql]: https://blog.csdn.net/weixin_41287692/article/details/82414360