数据库开发-优快云博客

驱动

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

MySQLdb：最有名的库。对MySQL的Client封装实现，支持Python2，不更新，不支持Python3
MySQL官方Connector
pymysql：语法兼容MySQLdb，使用Python写的库，支持Python3

pymysql

安装

$ pip install pymysql

连接Connect

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

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

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

import pymysql
ip='192.168.163.130'
user='xiaobai'
password='xiaobai'
database='school'
con=None
try:
    con=pymysql.connect(ip,user,password,database)
    con.ping(False)# ping不通则抛异常
    print(con)
finally:
    if con:
        con.close()

在这里插入图片描述

游标Cursor

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

操作数据库

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

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

import pymysql
ip='192.168.163.130'
user='xiaobai'
password='xiaobai'
database='school'
con=None
try:
    con=pymysql.connect(ip,user,password,database)
    con.ping(False)# ping不通则抛异常

    cursor=con.cursor()
    insert_sql="insert into student (name,age) values ('tom',20)"
    rows=cursor.execute(insert_sql)
    print(rows)
finally:
    if con:
        con.close()

在这里插入图片描述
发现数据库中没有数据提交成功
原因在于，在Connection类的__init__方法的注释中有这么一句话

autocommit: Autocommit mode. None means use server default. (default: False)

管理书屋

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

import pymysql
ip='192.168.163.130'
user='xiaobai'
password='xiaobai'
database='school'
con=None
cursor=None
try:
    con=pymysql.connect(ip,user,password,database)
    con.ping(False)# ping不通则抛异常

    cursor=con.cursor()
    insert_sql="insert into student (name,age) values ('tom',20)"
    rows=cursor.execute(insert_sql)
    print(rows)
    con.commit() #提交
except:
    con.rollback()
finally:
    if con:
        con.close()
    if cursor:
        cursor.close()

在这里插入图片描述

一般流程
- 建立连接
- 获取游标
- 执行SQL
- 提交事务
- 释放资源
查询
Cursor类的获取查询结果集方法有fetchone(),fetchmany(size=None),fetchall()

import pymysql
ip='192.168.163.130'
user='xiaobai'
password='xiaobai'
database='school'
con=None
cursor=None
try:
    con=pymysql.connect(ip,user,password,database)
    con.ping(False)# ping不通则抛异常

    cursor=con.cursor()
    insert_sql="select * from student"
    rows=cursor.execute(insert_sql)
    print(cursor.fetchone(),1)
    print(cursor.fetchmany(2),2)
    print(cursor.rownumber, cursor.rowcount,'`````````````')
    print(cursor.fetchall(),3)
    print(cursor.rownumber,cursor.rowcount)
    #con.commit() #提交
except:
    con.rollback()
finally:
    if con:
        con.close()
    if cursor:
        cursor.close()

在这里插入图片描述

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

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

带列名查询
Cursor类有一个Mixin的子类DictCursor。
只需要cursor=con.cursor(DictCursor)就可以了

import pymysql
from pymysql.cursors import DictCursor
ip='192.168.163.130'
user='xiaobai'
password='xiaobai'
database='school'
con=None
cursor=None
try:
    con=pymysql.connect(ip,user,password,database)
    con.ping(False)# ping不通则抛异常

    cursor=con.cursor(DictCursor)
    insert_sql="select * from student"
    rows=cursor.execute(insert_sql)
    print(cursor.fetchone(),1)
    print(cursor.fetchmany(2),2)
    print(cursor.rownumber, cursor.rowcount,'`````````````')
    print(cursor.fetchall(),3)
    print(cursor.rownumber,cursor.rowcount)
    #con.commit() #提交
except:
    con.rollback()
finally:
    if con:
        con.close()
    if cursor:
        cursor.close()

在这里插入图片描述
返回的是一个字典

SQL 注入攻击
找出用户id为6的用户信息的SQL语句

"select * from student where id={}".format('5')

在这里插入图片描述
将搜索条件变成

"select * from student where id={}".format('5 or 1=1')

在这里插入图片描述
返回的结果是全部数据

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

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

参数化查询，可以有效防止注入攻击，并提高查询的效率

import pymysql
from pymysql.cursors import DictCursor
ip='192.168.163.130'
user='xiaobai'
password='xiaobai'
database='school'
con=None
cursor=None
try:
    con=pymysql.connect(ip,user,password,database)
    con.ping(False)# ping不通则抛异常

    cursor=con.cursor(DictCursor)
    insert_sql="select * from student where id=%s"
    rows=cursor.execute(insert_sql,'5 or 1=1')
    print(cursor.fetchall())
    #con.commit() #提交
except:
    con.rollback()
finally:
    if con:
        con.close()
    if cursor:
        cursor.close()

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