数据库开发
驱动
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;
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor()
# sql = "select * from reg"
sql = "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;"
r = currsor.execute(sql)
print(r)
conn.commit()
except:
conn.rollback()
print('roll back')
finally:
if currsor:
currsor.close()
if conn:
conn.close()
连接Connect
首先，必须建立一个传输数据通加粗样式道——连接。
pymysql.connect()方法返回的是Connections模块下的Connection类实例- connect方法传参就是给Connection类的
__init__提供参数
| Connection初始化常用参数 | 说明 |
|---|---|
| host | 主机 |
| user | 用户名 |
| password | 密码 |
| database | 数据库 |
| port | 端口 |
Connection.ping()方法，测试数据库服务器是否活着。有一个参数reconnect表示断开与服务器连接是否重连。连接关闭抛出异常
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
conn.ping(False) # ping不通则抛异常
print(conn)
finally:
if conn:
conn.close()
# 执行结果
<pymysql.connections.Connection object at 0x000002370C8996D8>
游标Cursor
操作数据库，必须使用游标，需要先获取一个游标对象
Connection.cursor(cursor=None) 方法返回一个新的游标对。
连接没有关闭前，游标对象可以反复使用
cursor参数，可以指定一个Cursor类。如果为None，则使用默认Cursor类
操作数据库
数据库操作需要使用Cursor类的实例，提供execute() 方法，执行SQL语句，成功返回影响的行数
新增记录
使用insert into语句插入数据
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor()
sql = "insert into student (name,age) values('tom', 20)"
r = currsor.execute(sql)
print(r)
conn.commit()
except:
conn.rollback()
print('roll back')
finally:
if currsor:
currsor.close()
if conn:
conn.close()
提交时需提供commit()方法，原因在于，在Connection类的 __init__方法的注释中有这么一句话
autocommit: Autocommit mode. None means use server default. (default: False)
一般不用开启自动提交功能，使用手动管理事务
事务管理
Connection类有三个方法：
- begin 开始事务
- commit 将变更提交
- rollback 回滚事务
批量增加数据
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor()
for i in range(5):
sql = "insert into student (name,age) values('ben{}', {})".format(i,20+i)
r = currsor.execute(sql)
conn.commit() # 提交
except:
conn.rollback() # 回滚
print('roll back')
finally:
if currsor:
currsor.close()
if conn:
conn.close()
一般流程
- 建立连接
- 获取游标
- 执行SQL
- 提交事务
- 释放资源
查询
- Cursor类的获取查询结果集的方法有
fetchone()、fetchmany(size=None)、fetchall() - 查询会根据Cursor游标移动
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor()
sql = 'select * from student'
rows = currsor.execute(sql) # 返回影响的行数
print(currsor.fetchone())
print(currsor.fetchone())
print(currsor.rownumber, currsor.rowcount)
print('1 ~~~~~~~~~~~~')
print(currsor.fetchmany(2))
print(currsor.rownumber, currsor.rowcount)
print('2 ~~~~~~~~~~~~~')
print(currsor.fetchall())
print(currsor.rownumber, currsor.rowcount)
for x in currsor.fetchall():
print(x, '~~~')
currsor.rownumber = 0
for x in currsor.fetchall():
print(x, '===')
finally:
if currsor:
currsor.close()
if conn:
conn.close()
- 执行结果
(1, 'tom', 20)
(2, 'ben0', 20)
2 6
1 ~~~~~~~~~~~~
((3, 'ben1', 21), (4, 'ben2', 22))
4 6
2 ~~~~~~~~~~~~~
((5, 'ben3', 23), (6, 'ben4', 24))
6 6
(1, 'tom', 20) ===
(2, 'ben0', 20) ===
(3, 'ben1', 21) ===
(4, 'ben2', 22) ===
(5, 'ben3', 23) ===
(6, 'ben4', 24) ===
Process finished with exit code 0
| 名称 | 说明 |
|---|---|
| fetchone() | 获取结果集的下一行 |
| fetchmany(size=None) | size指定返回的行数的行，None则返回空元组 |
| fetchall() | 返回剩余所有行，如果走到末尾，就返回空元组，否则返回一个元组，其元素是每一行的记录封装的一个元组 |
| cursor.rownumber | 返回当前行号。可以修改，支持负数。 负向超界，拉到起始位置；正向超界，拉到末位位置 |
| cursor.rowcount | 返回的总行数 |
注意：fetch操作的是结果集，结果集是保存在客户端的，也就是说fetch的时候，查询已经结束了
先查后改
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor()
sql = 'select * from student'
rows = currsor.execute(sql) # 返回影响的行数
userid = currsor.fetchone()[0]
print(userid)
sql = "update student set name='jerry' where id='{}'".format(userid)
currsor.execute(sql)
conn.commit()
except:
conn.rollback()
finally:
if currsor:
currsor.close()
if conn:
conn.close()
带列名查询
- Cursor类有一个Mixin的子类DictCursor
- 只需要
- 导入
from pymysql.cursors import DictCursor库 cursor = conn.cursor(DictCursor)就可以了
- 具体实现
import pymysql
from pymysql.cursors import DictCursor
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor(DictCursor)
sql = 'select * from student where id={}'.format('2')
currsor.execute(sql)
print(currsor.fetchall())
except:
conn.rollback()
finally:
if currsor:
currsor.close()
if conn:
conn.close()
# 执行结果
[{'id': 2, 'name': 'ben0', 'age': 20}]
返回一行，是一个字典
返回多行，放在列表中，元素是字典，代表一行
SQL注入攻击
- 找出用户id为3的用户信息的SQL语句如下
SELECT * from student WHERE id = 3
- 现在，要求可以找出某个id对应用户的信息，代码如下
userid = 5 # 用户id可以变
sql = 'SELECT * from student WHERE id = {}'.format(userid)
- userid可以变，例如从客户端request请求中获取，直接拼接到查询字符串中
如果userid = '5 or 1=1'
sql = 'SELECT * from student WHERE id = {}'.format('5 or 1=1')
- 运行的结果竟然是返回了全部数据
SQL注入攻击
- 猜测后台数据库的查询语句使用拼接字符串等方式，从而经过设计为服务端传参，令其拼接出特殊字符串的SQL语句，返回攻击者想要的结果
- 永远不要相信客户端传来的数据是规范及安全的！！！
如何解决注入攻击
- 参数化查询
- 可以有效防止注入攻击，并提高查询的效率
Cursor.execute(query, args=None)
args，必须是元组、列表或字典。- 如果查询字符串使用
%(name)s，就必须使用字典
import pymysql
from pymysql.cursors import DictCursor
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor(DictCursor)
userid = '5 or 1=1'
sql = 'SELECT * from student WHERE id = %s'
currsor.execute(sql, (2,)) # 参数化查询
print(currsor.fetchall())
currsor.execute(sql, (userid,))
print(currsor.fetchall())
finally:
if currsor:
currsor.close()
if conn:
conn.close()
- 参数化查询为什么提高效率？
原因就是——SQL语句缓存 - 数据库服务器一般会对SQL语句编译和缓存，编译只对SQL语句部分，所以参数中就算有SQL指令也不会被当做指令执行
- 编译过程，需要词法分析、语法分析、生成AST、优化、生成执行计划等过程，比较耗费资源
- 服务端会先查找是否对同一条查询语句进行了缓存，如果缓存未失效，则不需要再次编译，从而降低了编译的成本，降低了内存消耗
- 可以认为SQL语句字符串就是一个key，如果使用拼接方案，每次发过去的SQL语句都不一样，都需要编译并缓存
大量查询的时候，首选使用参数化查询，以节省资源
开发时，应该使用参数化查询
注意：这里说的是查询字符串的缓存，不是查询结果的缓存
- 批量执行executemany()
import pymysql
from pymysql.cursors import DictCursor
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = None
currsor = None # 对结果集的操作
try:
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
currsor = conn.cursor()
sql = "insert into student (name,age) values(%s, %s)"
currsor.executemany(sql, (
('ben{}'.format(i), 20+i) for i in range(5)
))
conn.commit()
except Exception as e:
print(e)
conn.rollback()
finally:
if currsor:
currsor.close()
if conn:
conn.close()
上下文支持
- 查看连接类和游标类的源码
# 连接类
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()
- 连接类进入上下文的时候会返回一个游标对象，退出时如果没有异常会提交更改
- 游标类也使用上下文，在退出时关闭游标对象
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
try:
with conn.cursor() as cursor:
sql = "insert into student (name,age) values('tom'%s, %s)"
r = cursor.executemany(sql, (("{}".format(i), 20+i) for i in range(2)))
print(r) # 插入行数
with conn as cursor:
cursor.execute('select * from student')
print(cursor.fetchall())
except Exception as e:
print(e)
finally:
if conn:
conn.close()
执行结果
2
((1, 'jerry', 20), (2, 'ben0', 20), (3, 'ben1', 21),\
(4, 'ben2', 22), (5, 'ben3', 23), (6, 'ben4', 24),\
(50, "tom'0", 20), (51, "tom'1", 21))
Process finished with exit code 0
换一种写法，使用连接的上下文
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
with conn as cursor:
for i in range(2):
sql = "insert into student (name,age) values('tom{0}', 20+{0})".format(i)
rows = cursor.execute(sql)
cursor.execute('select * from student')
print(cursor.fetchall())
cursor.close()
conn.close()
conn的with进入是返回一个新的cursor对象，退出时，只是提交或者回滚了事务。并没有关闭cursor和conn。
不关闭cursor就可以接着用，省的反复创建它
- 如果想关闭cursor对象，这样写
import pymysql
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
conn = pymysql.connect(IP, USERNAME, PASSWORD, DBNAME, PORT)
with conn as cursor:
with cursor:
sql = "select * from student"
cursor.execute(sql)
print(cursor.fetchall())
# 关闭
conn.close()
通过上面的实验，我们应该知道，连接应该不需要反反复复创建销毁，应该是多个cursor共享一个conn
扫一扫
1044
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
