#### pymysql
pymysql 的语法兼容MySQLdb,是使用Python写的
pymysql 是可以远程连接数据库并对数据库中的数据进行操作的工具
pymysql是使用
-
首先,必须先和数据库建立一个传输数据的连接通道,需要用到pymysql下的connect()方法
pymysql.connect() 方法返回的是Connections模块下的Connection 实例,connect() 方法传参就是在给Connection类的 _init_ 初始化魔术方法参数,也可以理解为 connect() 方法就是在创建新的 Connetion 类
connect() / Connection初始化 常用参数 说明 host 主机ip user 用户名 password 密码 database 数据库 port 端口号 charset 字符集 例如:
db = pymysql.connect(host='localhost', user='root', password='123456', database='userinfo', # 所用的数据库名 charset = 'utf-8')
- 在使用pymysql.connect() 方法与数据库建立连接后,想要操作数据库时,就需要使用游标 Cursor
通过连接好的数据库(此处为db)调用 cursor() 方法即可返回一个新的游标对象,在连接没有关闭之前,游标对象可以反复使用
cursor = db.cursor()
- 数据库操作需要使用Cursor类的实例(即通过 db.cursor() 创建的 cursor 游标对象)提供的 execute() 方法,执行SQL语句,成功则返回结果
例如:插入操作
sql = "insert into user_pwd(username,password) values('vera', '1234')"
cursor.execute(sql)
- 这里插入数据的时候插入的表名有个需要注意的地方,插入表名的时候最好带着库名,例如:
sql = "insert into database.table(username, password) values('xxx', '123')"
否则可能会出现如下错误:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax
具体可以参考我的另一篇文章:pymysql中cursor.execute()执行insert into的坑及解决方法
- 查询操作
sql = "select * from table"
response1 = cursor.execute(sql)
response2 = cursor.fetchall()
这里单独说一下查询操作
这里有两个response返回,
response1是对cursor.execute(sql) 的返回数据接收,这个返回可能并不是你想要的返回值,因为它返回的是查询到的个数,是个int类型的数字
而我想response2才是你想要的返回
response2是调用了fetchall方法:查询时获取结果集中的所有行,一行构成一个元组,然后再将这些元组返回(即嵌套元组)
还有一些别的方法如下
||| (这是个箭头)
V
-
获取查询结果集的方法
当调用 cursor.execute(sql) 方法查询数据的时候,如果执行成功,获取查询的结果集的方法有
名称 说明 fetchone() 获取结果集的下一行 fetchmany(size=None) size指定返回的行数,None则返回空元组 fetchall() 返回剩下的所有行,如果走到末尾,就返回空元组,否则返回一个元组,其元素是每一行的记录封装的一个元组 cursor.rownumber 返回当前行号.可以修改,支持负数 cursor.rowcount 返回的总行数 注意: fetch操作的是结果集,结果集是保存在客户端的,也就是说fetch的时候,查询已经结束了
-
Connection类提供了三个方法: begin 开始事务, commit 提交事务, rollback 回滚事务,如果通过 sql 语句对数据库中的数据进行了修改, 则需要提交事务。
db.commit()
- rollback()也是个很重要的方法,正确的使用rollback可以避免commit提交事务的时候发生错误导致程序中断。主要使用方式:结合try except捕获异常,将事务进行rollback回滚
try: db.commit() except Exception as e: db.rollback()
- rollback()也是个很重要的方法,正确的使用rollback可以避免commit提交事务的时候发生错误导致程序中断。主要使用方式:结合try except捕获异常,将事务进行rollback回滚
- 释放资源,在程序结束时需要建立的连接即建立的游标资源释放掉,避免资源的浪费,可以调用close() 方法
连接查询的数据为历史数据(获取实时数据的方法)
-- 如果对数据即时性要求很高,或者说数据随时可能发生变化,那么close的使用将会很重要。close是将当前连接关闭。注意,这里和sqlalchemy的close不太相同,cur.close()是关闭游标,但是仅仅关闭游标是不够的,创建新的游标查询的依旧是历史数据,只有将当前db.connect数据库连接关闭,即db.close()后在重新创建连接才有效,才可以得到最新的数据;研究了一下,其实和mysql的事务有关,所以另外一种方法就是:db.commit()提交事务,即时只是查询,也将当前事务提交,开启新的事务,连接就会重新获取当前最新的内容。否则查询到的数据依旧是历史数据,而不是最新的数据。 -- 在sqlalchemy中,是通过创建引擎以及连接池的方式实现的,一个连接池中可以有多个连接,调用session.close()则是将当前连接关闭,和这里的db.close()一样,但是sqlalchemy有连接池的存在,关闭后若想继续使用连接,连接池会自动创建好连接,直接使用session即可,不需要重新创建连接,但pymysql不行,pymysql是单独创建一个连接,没有连接池的概念
if cursor:
cursor.close()
db.close()
-
在执行查询操作的时候要尽量避免 使用%s 或者 format() 的字符串替换操作,这样是为了避免sql注入的发生
sql 注入指:字符串替换操作中如果出现了 如下操作时,返回的结果将是全部数据
sql = "select * from table where id={}".format('5 or 1=1')
字符串替换的方式将会让攻击者有机可乘,攻击者猜测后台数据库的查询语句使用的拼接字符串等方式,从而经过设计为服务端传参,令其拼接处特殊字符串的SQL语句,返回攻击者想要的结果,所有,永远不要相信客户端传来的数据是规范及安全的!
解决此问题的方法:
参数化查询,不仅可以有效的防止注入攻击,还可以提高查询的效率
cursor.execute(query,args=None)
args必须是元组 列表或字典,如果查询字符串使用%(name)s,就必须使用字典.
例如:
sql = "select * from table where name like %s and age < %s" cursor.execute(sql,('tom%',25)) 或者 sql = "select * from table where name like %(name)s and age < %(age)s" cursor.execute(sql,{'name':'tom%','age':25})
参数化查询提高了效率,原因是–SQL语句缓存.
数据库服务器一般会对SQL语句编译和缓存,编译只对SQL语句部分,所以参数就算有SQL指令也不会被当作指令执行.
编译过程,需要词法分析、语句分析、生成AST、优化、生成执行计划等过程,比较耗费资源。
服务端会先查找是否对同一条查询语句进行了缓存,如果缓存未失效,则不需要再次编译,从而降低了编译的成 本,降低了内存消耗。
可以认为SQL语句字符串就是一个key,如果使用拼接方案,每次发过去的SQL语句都不一样,都需要编译并缓存。 大量查询的时候,首选使用参数化查询,以节省资源。
- 上下文管理: 不关闭游标对象cursor 和 数据库对象db,就可以接着用,连接(db)应该不需要反反复复创建销毁,应该是多个cursor共享一个db.
例如:
import pymysql
db = pymysql.connect(ip,user,password,database,port)
with db.cursor() as cursor:
sql = "select * from table where id=%s"
cursor.execute(sql,5)
print(cursor.fetchall())
cursor.close()
db.close()