pymysql使用

数据库开发

驱动

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

MySQL的驱动

• MySQLdb
  最有名的库. 对MySQL的c Clien封装实现, 支持Python2, 不更新了, 不支持Python3
• MySQL官方Connector
  MySQL官网https://dev.mysql.com/downloads/connector/
• pymysql
  语法兼容MySQLdb, 使用Python写的库, 支持Python3

pymysql使用

安装

$ pip install  pymsql

创建数据库和表

CREATE DATEBASE IF NOT EXTSTS school;-创建库
SHOW DATABASES;    
USE school  ## 选择表   

MariaDB [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;

连接Connect

首先, 必须建立一个传输数据通道–连接.

• pymysql.connect() 方法返回的是Connection模块下的Connection类实例. connect放啊传参就是给Connection类的__init__提供参数

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

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

import pymysql

conn = None
try:
    conn = pymysql.connect(host='192.168.0.106', user='root', password='admin1',database='school')
    conn.ping(False)   # ping不通抛异常
finally:
    if conn:
        conn.close()

游标

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

cursor参数, 可以指定一个Cursor类. 如果None, 则使用默认Cursor类.

操作数据库

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

新增记录

使用insert into语句插入数据

事务管理

Connection类的__init__方法中, aotocommit缺省是False表示是否开启自动提交事务
不用开启, 一般手动提交.

Connection类有三个方法:
begin 开始事务
commit 将变更提交
rollback 回滚事务

实验批量增加数据

import pymysql

conn = None
cursor = None
try:
    conn = pymysql.connect('192.168.0.106', 'root', 'admin1', 'school')
    conn.ping(False)   # ping不通抛异常,Ture 表示不通重连

    cursor = conn.cursor()   ##获取游标
    for i in range(10):
        insert_sql = "insert into student (name, age) values('tom',20)"   ##直接用sql语句
        rows = cursor.execute(insert_sql)

    conn.commit() #提交
except Exception as e:
    conn.rollback()  # 回滚
    print(e)
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

一般流程

1. 建立连接: conn = pymysql.connect(host等参数)
2. 获取游标: cursor = conn.cursor()
3. 执行SQL cursor.execute(sql语句)
4. 提交事务 conn.commit()
5. 释放资源 cursor.close(), conn.close()

查询

Cursor类的查询结果集的方法有fetchone(), fetchmany(), fetchall().

import pymysql

conn = None
cursor = None
try:
    conn = pymysql.connect('192.168.0.106', 'root', 'admin1', 'school')
    conn.ping(False)   # ping不通抛异常,Ture 表示不通重连
#
    cursor = conn.cursor()   ##获取游标
    
    sql = 'select * from student'
    rows = cursor.execute(sql)   ## 返回影响的行数
    print(rows)
    print(cursor.fetchone())
    print(cursor.rownumber,cursor.rowcount)
    print(cursor.fetchmany(2))
    print(cursor.rownumber, cursor.rowcount)   ##这里看出, rownumber 表示游标位置
    for x in cursor.fetchall():
        print(x)
    cursor.rownumber = 0   ## 改变游标位置, 支持正负
    for x in cursor.fetchall():
        print(x)

finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

每一行返回元组, 多行则是大的元组中每一项都是元组

名称	说明
fetchone()	获取结果集的下一行
fetchmany(size=None)	size指定返回的行数,None表示返回空元组
fetchall()	返回剩余所有行, 如果走到末尾, 就返回空元祖, 否则返回一个元组 , 七元素是每一行的记录封装的一个元组
cursor.rownumber	返回当前行号. 可以修改, 支持负数
cursor.rowcount	返回的总行数

注意: fetch操作的是结果集, 结果集是保留在客户端的, 也就是说fetch的时候, 查询已经结束了.

带列名查询

Cursor类有一个Minxin的 子类DictCursor.
只需要 cursor = conn.cursor(pymysql.cursors.DictCursor)

cursor = conn.cursor(pymysql.cursors.DictCursor)
## 返回结果
{'name':'tom','age':20, 'id': 4}

每一行返回的是字典.
多行时, 放在列表中, 元素是字典, 代表一行.

SQL注入攻击

例如找到用户ID为6的用户信息SQL语句如下

SELECT * FROM student WHERE id=6

现在要用pymysql代码实现:

userid = 5 ## 用户ID可变
sql = 'SELECT *FROM student WHERE id={}'.format('userid')
``
客户端request请求中获取的字符串拼接到字符串中
假如是如下的字符串呢?
```python
sql =  'SELECT *FROM student WHERE id={}'.format('5 or 1=1')

运行的结果竟然返回了全部数据.

SQL注入攻击
猜测后台数据库的查询语句使用拼接字符串等方式，从而经过设计为服务端传参，令其拼接出特殊字符串的SQL语句，返回攻击者想要的结果。

永远不要相信客户端传来的数据是规范及安全的！！！

如何解决注入攻击？
参数化查询，可以有效防止注入攻击，并提高查询的效率。

Cursor.execute(sql, args=None)
args, 必须是元组,列表.或者字典, 如果查询字符串使用%(name)s, 就必须使用字典.

userid = '5 or 1=1'
sql = 'SELECT * from student WHERE id = %s'
cursor.execute(sql, (userid,)) # 参数化查询   这样查不到, 因为id没有5 or 1=1


sql = 'SELECT * from student WHERE name like %(name)s and age > %(age)s'
cursor.execute(sql, {'name':'tom%', 'age':25}) # 参数化查

参数化查询中, 占位符使得参数只能作为某一个参数, 而不是字符串.例如id中没有’5 or 1=1’这个id.

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

上下文支持

# 连接类
class Connection(object):
   def __enter__(self):
       """Context manager that returns a Cursor"""
       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就可以接着用，省的反复创建它。

连接应该不需要反反复复创建销毁，应该是多个cursor共享一个conn。