pymysql（第三方库）：
首先，必须先和数据库建立一个传输数据的连接通道，需要用到pymysql下的connect()方法
pymysql.connect() 方法返回的是Connections类下的Connection 实例，connect() 方法传参就是在给Connection类的 _init_ 初始化不定长参数，也可以理解为 connect() 方法就是在创建新的 Connetion 对象
| connect() / Connection初始化 常用参数 | 说明 |
|---|---|
| host | 主机ip |
| user | 用户名 |
| password | 密码 |
| database | 数据库 |
| port | 端口号 |
| charset | 字符集 |
import pymysql # 建立传输数据的链接通道 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", charset="utf8") print(conn) # <pymysql.connections.Connection object at 0x000001ADB3CD4E50>
在使用pymysql.connect() 方法与数据库建立连接后，想要操作数据库时，就需要使用游标 Cursor
通过连接好的数据库（此处为conn）调用 cursor() 方法即可返回一个新的游标对象，在连接没有关闭之前，游标对象可以反复使用
# 获取游标，用于操作数据库 cursor = conn.cursor() print(cursor) # <pymysql.cursors.Cursor object at 0x000001ADB3ED4FD0>
常用基础方法：
execute()：发送指令
fetchone()：取出下一条记录
fetchchall()：取出所有数据
commit()：提交变化到稳定存储（在对数据库进行增删改的时候需要做的操作）
import pymysql # 建立传输数据的链接通道 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", charset="utf8") print(conn) # <pymysql.connections.Connection object at 0x000001C3C6687FD0> # 获取游标，用于操作数据库 cursor = conn.cursor() print(cursor) # <pymysql.cursors.Cursor object at 0x000001C3C6687F70> # 执行一个查询(即：发送指令) cursor.execute("show databases") # 取出下一条记录(有点像可迭代对象(iterable)，具有next方法，可以逐一获取每行数据，但不是一个可迭代对象) result = cursor.fetchone() print(result) # ('db1',) # 取出所有行，由于上面代码去了一条记录，所以fetchall获取的数据没有上面的那条数据 result = cursor.fetchall() print(result) # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',)) # 创建数据库 cursor.execute("create database db3 default charset utf8 collate utf8_general_ci") # 增、删、改 都需要使用commit (提交变化到稳定存储)。 conn.commit() cursor.execute("show databases") print(cursor.fetchall()) # (('db1',), ('db3',), ('information_schema',), ('mysql',), ('performance_schema',), ('sys',)) # 删除数据库 cursor.execute("drop database db3") conn.commit() cursor.execute("show databases") print(cursor.fetchall()) # (('db1',), ('information_schema',), ('mysql',), ('performance_schema',), ('sys',)) cursor.execute("use db1") print(cursor.fetchall()) # () cursor.execute("show tables") result = cursor.fetchall() print(result) # (('123',), ('emp',), ('employee',), ('student',), ('student_1',))
import pymysql # 输入用户名和密码 user = input("请输入用户名：") # ' or 1=1 -- pwd = input("请输入密码：") # 123 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8",db='usersdb') cursor = conn.cursor() # 基于字符串格式化来 拼接SQL语句 # sql = "select * from users where name='root' and password='123'" # sql = "select * from users where name='' or 1=1 -- ' and password='123'" sql = "select * from users where name='{}' and password='{}'".format(user, pwd) cursor.execute(sql) result = cursor.fetchone() print(result) # None，不是None cursor.close() conn.close()
为什么呢？
因为在SQL拼接时，拼接后的结果是：
select * from users where name='' or 1=1 -- ' and password='123'
注意：在MySQL中 -- 表示注释。
切记，SQL语句不要在使用python的字符串格式化，而是使用pymysql的execute方法。
# SQL语句不在用python的字符串格式化，而是使用pymsql的execute方法 import pymysql # 输入账号密码 user = input("输入账号") pw = input("输入密码") # 创建数据库链接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", charset="utf8") # 创建游标 cursor = conn.cursor() # 方式一：发送指令，execute会自动帮助我们检测，user、pw中是否存在特殊字符并帮助处理。 cursor.execute("select * from user where name=%s and password=%s", [user, pw]) # 方式二： # cursor.execute("select * from user where name=%(name)s and password=%(passwd)s", {"name": user, "passwd": pw}) result = cursor.fetchall() print(result) # 关闭游标、关闭链接 cursor.close() conn.close()
什么是数据库事务
数据库事务是访问并可能操作各种数据项的一个数据库操作（包括增删改，不包括查询）序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。
import pymysql # 创建链接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", charset="utf8") # 这样写可以直接进入到数据库shool中 # conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", charset="utf8", db="shool") # 创建游标 cursor = conn.cursor() # 进入数据库 cursor.execute("use shool") # 开启事务 conn.begin() # 尝试执行事务 try: cursor.execute("update student set sname=%s where sid=8", ["某人飞"]) except Exception as e: print("发生异常，进行回滚") conn.rollback() else: print("提交事务") conn.commit() # 关闭游标、关闭链接 cursor.close() conn.close()
锁
在用MySQL时，不知你是否会疑问：同时有很多做更新、插入、删除动作，MySQL如何保证数据不出错呢？
MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：
-
表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
-
行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。
MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。
即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。
在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。
所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。
而select则默认不会申请锁 select * from xxx;
如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法来实现。
- for update，排它锁，加锁之后，其他不可以读写。
begin; select * from L1 where name="飞某人" for update; -- name列不是索引（表锁） commit;
begin; -- 或者 start transaction; select * from L1 where id=1 for update; -- id列是索引（行锁） commit;
当行锁是id（索引）时,其他事务也需要有单独的索引才不会被阻塞，否则数据会通过id列就导致了阻塞
- lock in share mode，共享锁，加锁之后，其他可读但不可写。
begin; select * from L1 where name="飞某人" lock in share mode; -- 假设name列不是索引（表锁） commit;
begin; -- 或者 start transaction; select * from L1 where id=1 lock in share mode; -- id列是索引（行锁） commit;
排它锁
排它锁（ for update），加锁之后，其他事务不可以读写。
应用场景：总共100件商品，每次购买一件需要让商品个数减1 。
A: 访问页面查看商品剩余 100 B: 访问页面查看商品剩余 100 此时 A、B 同时下单，那么他们同时执行SQL： update goods set count=count-1 where id=3 由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。 但是，当商品剩余 1个时，就需要注意了。 A: 访问页面查看商品剩余 1 B: 访问页面查看商品剩余 1 此时 A、B 同时下单，那么他们同时执行SQL： update goods set count=count-1 where id=3 这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？ 这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行： begin; -- start transaction; select count from goods where id=3 for update; -- 获取个数进行判断 if 个数>0: update goods set count=count-1 where id=3; else: -- 已售罄 commit;
基于Python代码示例：
import pymysql import threading def task(): # 创建链接 conn = pymysql.connect(host="localhost", port=3306, user="root", passwd="root", charset="utf8", db="db1") # 创建游标 # 参数的作用是将游标接收到的数据整理成字典的形式 cursor = conn.cursor(pymysql.cursors.DictCursor) # 开启事务 conn.begin() # 尝试执行事务 try: cursor.execute("select id,age from tran where id=1 for update") # fetchall 有参：( {"id":1,"age":10},{"id":2,"age":10}, ) 无参获取的结果：((1,10),(2,10)) # fetchone 有参{"id":1,"age":10} 无参获取的结果：(1,10) result = cursor.fetchone() print(result) current_age = result['age'] if current_age > 0: cursor.execute("update tran set age=age-1 where id=1") else: print("已售罄") except Exception as e: print("异常，进行回滚") conn.rollback() else: print("提交事务") conn.commit() cursor.close() conn.close() def fun(): thread = threading.Thread(target=task) thread.start() if __name__ == '__main__': for x in range(11): fun()
假设在用户使用时，每个用户都会获取一个数据库连接，如果有很多用户同时申请获取连接，然后使用后连接也都断开了，如果又要连接......这样的过程很明显用上面的代码是行不通的，数据库顶不住啊！
这时候就需要一个数据库连接池，来管理用户对数据库连接的操作。
在操作数据库时需要使用数据库连接池
pip3.9 install pymysql
pip3.9 install dbutils
# 导入连接池工具包 import threading import pymysql from dbutils.pooled_db import PooledDB MYSQL_DB_POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块,因为dbutils并不能链接数据库，真正连接数据库还是需要pymsql maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=3, # 链接池中最多闲置的链接，0和None不限制 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。 # 如：0 = None = never, 1 = default = whenever it is requested, # 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root', database='shool', charset='utf8' ) def task(): # 获取连接池中的连接 conn = MYSQL_DB_POOL.connection() # 获取连接中的游标 cursor = conn.cursor(pymysql.cursors.DictCursor) # cursor = conn.cursor() # 执行命令 cursor.execute("show tables") # 接受数据 result = cursor.fetchall() print(result) cursor.close() # 把链接交还给数据库连接池 conn.close() def run(): for x in range(10): thread = threading.Thread(target=task) thread.start() if __name__ == '__main__': run()
因为数据库连接池只有一个，所以我们需要基于数据库连接池开发一个公共的SQL操作，方便以后操作数据库。
# 数据库连接池_单例.py import pymysql from dbutils.pooled_db import PooledDB # 模块的方式实现单例 class DBHelper(object): # 初始化数据库连接池 def __init__(self): # TODO 此处配置，可以去配置文件中读取。 self.pool = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=3, # 链接池中最多闲置的链接，0和None不限制 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root', database='shool', charset='utf8' ) # 获取连接和游标 def get_conn_cursor(self): conn = self.pool.connection() cursor = conn.cursor(pymysql.cursors.DictCursor) return conn, cursor # 关闭游标，把链接交还给连接池 def close_conn_cursor(self, *args): for item in args: item.close() # 发送指令 def exec(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) conn.commit() self.close_conn_cursor(conn, cursor) # 获取一条数据 def fetch_one(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) result = cursor.fetchone() self.close_conn_cursor(conn, cursor) return result # 获取所有数据 def fetch_all(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) result = cursor.fetchall() self.close_conn_cursor(conn, cursor) return result db = DBHelper()
# 数据库连接池测试.py # 测试模块的方式实现的单例 from 数据库连接池_单例 import db db.exec("insert into student(sname) values(%(name)s)", name="飞某人") ret = db.fetch_one("select * from student") print(ret) ret = db.fetch_one("select * from student where sid=%(nid)s", nid=3) print(ret) ret = db.fetch_all("select * from student") print(ret) ret = db.fetch_all("select * from student where sid>%(nid)s", nid=2) print(ret)
with 获取连接：
执行SQL（执行完毕后，自动将连接交还给连接池）
# 数据库连接池_单例.py import pymysql from dbutils.pooled_db import PooledDB # 上下文管理方式实现单例 # 创建一个连接池 POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块,因为dbutils并不能链接数据库，真正连接数据库还是需要pymsql maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=3, # 链接池中最多闲置的链接，0和None不限制 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。 # 如：0 = None = never, 1 = default = whenever it is requested, # 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root', database='shool', charset='utf8' ) class Connect(object): # 初始化数据库连接与游标 def __init__(self): self.conn = POOL.connection() self.cursor = self.conn.cursor(pymysql.cursors.DictCursor) # 上文管理 def __enter__(self): return self # 下文管理 def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() # 发送指令 def exec(self, sql, **kwargs): self.cursor.execute(sql, kwargs) self.conn.commit() # 获取一条数据 def fetch_one(self): result = self.cursor.fetchone() return result # 获取所有数据 def fetch_all(self): result = self.cursor.fetchall() return result
# 数据库连接池测试.py # 上下文管理测试 from 数据库连接池_单例 import Connect with Connect() as obj: obj.exec("select * from student") result = obj.fetch_one() print(result) result = obj.fetch_all() print(result) obj.exec("select * from student where sname=%(name)s or sid=%(tid)s", name="某人飞", tid=4) result = obj.fetch_all() print(result)