mysql-API
事务
mysql事务：操作序列，这个操作要么执行，要么都不执行(工作单位)
支票表:支票账户 减少10000
储蓄表:储蓄账户 增加10000
特性：事务的4个属性 ACID
1.原子性Atomicity：一个事务是不可分割的最小的工作单位，整个事务要么全部提交成功，要么全部失败回滚
2.一致性Consistency：事务的开始和结束，数据库的完整性没有被破坏
3.隔离性Isolation：一个事务修改在最终提交以前，对其他事务是不可见的
4.持久性Durability：一旦事务提交，所做的修改永久保存到数据库当中。
?
开始事务：begin; # python 操作数据库默认是开始事物的。
执行操作：插入/修改数据(或删除数据)操作; 只要对数据库更改就需要执行事务
提交事务: commit 持久保存
回滚: rollback 都不执行
?
mysql配置文件, 如果是非本地主机连接mysql，就需要如下配置
# 1.跳转到/etc路径下的mysql路径下的配置文件夹下
cd /etc/mysql/mysql.conf.d
?
sudo vim mysqld.cnf
?
# 修改地址为 0.0.0.0 代表任何主机都可以连接
bind-address = 0.0.0.0
?
# 重启mysql
sudo service mysql restart
?
# 有的需要如下
sudo /etc/init.d/mysql restart
?
# 检查虚拟机端口转发
连接数据库api
import pymysql
?
# 连接方式一：
db_config = {
‘host‘: ‘127.0.0.1‘, # 主机 即ip
‘port‘: 3306, # 默认端口3306
‘user‘: ‘admin‘, # 用户
‘password‘: ‘Root110qwe‘,
‘db‘: ‘spiders‘, # 指定已有的数据库
‘charset‘: ‘utf8‘ # 设置字符集：注意这里是utf8
}
# 1.连接数据库（推荐使用）
db = pymysql.connect(**db_config)
?
# 方式二：以关键字参数连接； # 如mysql在windows本地host传入localhost
conn = pymysql.connect(host=‘localhost‘, port=3306, user=‘root‘, password=‘qwe123‘, db=‘mydb‘, charset=‘utf8‘)
?
# 2.获取操作游标
cur = db.cursor()
?
# 3.执行sql语句，调用execute()方法
sql = ‘select * from student‘ # 查询
# sql = ‘create database spiders [default character set utf8]‘ # [指定编码，我在连接时已经指定]
# sql1 = ‘desc user‘ 表结构
result = cur.execute(sql) # result会返回查询到的数量
cur.execute(sql1)
?
# 3.1 获取结果
cur.fetchone() # 每次只获取一条数据【类似生成器的next；因此可以for遍历】
for i in range(result):
cur.fetchone()
?
# 3.2 获取多条数据
cur.fetchmany(size=result) # 查询指定数量
cur.fetchall() # 查询全部,返回元组，如果数据特别大，会耗用整个内存，不推荐使用
?
# 4.关闭游标
cur.close()
?
# 5.关闭与数据库的连接
db.close()
python操作数据库
import pymyql
?
conn = pymysql.connect(**db_config)
# 用上下文管理器（游标存储自动关闭）
try:
with conn.cursor() as cur: # 建立游标对象，execute()方法执行sql语句：
sql = "create table stu(id int primary key auto_increment, name varchar(20), age int)"
cur.execute(sql) # 创建表
cur.execute("drop table stu") # 删除表
cur.execute(‘insert into stu values(1, "yelan", 18),(DEFAULT , "xiao", 20)‘) # 插入数据
cur.execute("UPDATE stu SET NAME=‘wang‘ WHERE NAME=‘xiao‘") # 修改数据
cur.execute(‘alter table stu modify tinyint ‘) # 修改字段类型
cur.execute(‘delete from stu where name="wang" ‘) # 删除操作只能在一张表上进行。
cur.execute(‘select * from stu‘) # 查询数据
pprint(cur.fetchall()) # 获取结果
conn.commit() # 默认开始事务！插入/修改或者删除需要提交，否则回滚。
?
except Exception as e:
print(e)
conn.rollback()
finally:
conn.close()
自定义插入数据
class Mymysql:
def __init__(self, **kwargs):
self.db = pymysql.connect(**kwargs)
self.cursor = self.db.cursor()
?
def process(self, sql:str) -> str:
self.cursor.execute(sql)
if ‘select‘ not in sql: # 如果不是查询操作才触发事务
try:
self.db.commit()
except:
self.db.rollback()
finally:
self.cursor.close()
?
def __exit__(self, exc_type, exc_val, exc_tb): # 运行结束时关闭数据库的连接
self.db.close()
?
?
db = Mymysql(**db_config)
# # 创建表
# sql = ‘create table if not exists student(id int primary key, name varchar(128) not null, age int DEFAULT 18)‘
# db.process(sql)
sql = """insert into student values(2001, ‘JACK‘, 38),(2002, ‘yelan‘, 18)"""
db.process(sql)
注意：只要是对数据库有更改就涉及了事务
# sql 语句中的占位符
?
sql = ‘insert into table_name (id, name, age) values(%s, %s, %s)‘
?
cur.execute(sql, (id, name, age)) # def execute(self, query, args=None):
# excute()方法的第一个参数接受sql语句，values的值用统一的元组传过来就好了
db.commit() # 提交才真正执行
?
*动态插入数据，通用方法
# 1 将我们插入的数据构造为一个字典,不用考虑字典的长度
data = {
‘id‘: 201901,
‘name‘: ‘jack‘,
‘age‘: 18
}
table = ‘students‘ # 指定表
keys = ‘, ‘.join(data.keys())
values = ‘, ‘.join([‘%s‘] * len(data))
?
sql = ‘insert into {}({}) values({})‘.format(table, keys, values)
# sql = ‘insert into {table}({keys}) values({values})‘.format(table=table, keys=keys, values=values)
try:
cur.execute(sql, tuple(data.values))
db.commit()
print(‘success‘)
except:
db.rollback()
print(‘faild‘)
finally:
db.close()
?
*更新数据(牛逼)；对增量爬虫很管用哦
这里实现一种去重的方法。如果数据存在，则更新数据；如果数据不存在，则插入数据。
data = {
‘id‘: 201901,
‘name‘: ‘jack‘,
‘age‘: 18
}
table = ‘students‘ # 指定表
keys = ‘, ‘.join(data.keys())
values = ‘, ‘.join([‘%s‘] * len(data))
?
sql = ‘insert into {table}({keys}) values({values}) on duplicate key update‘.format(table=table, keys=keys, values=values)
update = ‘,‘.join([" {key} = %s".format(key=key) for key in data])
sql = update # 拼接sql语句
print(sql)
try:
cur.execute(sql, tuple(data.values())*2)
print(‘Successful‘)
db.commit()
except:
db.rollback()
print(‘Failed‘)
finally:
db.close()
# sql中的 ON DUPLICATE KEY UPDATE 的意思是如果主键已经存在，就执行更新操作。
完整的sql构造出来是这样的：
insert into student(id, name, age) values(%s, %s, %s) on duplicate key update id=%s, name=%s, age=%s
这里就变成了双倍的%s, 因此execute()方法传入的元组就需要乘以2变为原来的2倍
?
删除数据
table = ‘student‘
condition = ‘age>18 and age<30‘ # 指定删除的条件
sql = ‘delete from {table} where {condition}‘.format(table=table, condition=condition)
执行sql commit()...
?
查询数据
sql = ‘select * from student where id<10 or age>=20‘
result = cur.execute(sql)
print(result) # 返回的也是查询的结果数
print("count：", cur.rowcount) # cursor的rowcount属性获取查询结果的条数
# 查询一条
print(cur.fetchone()) # 每次只获取一条数据(类似生成器中的next)
>> (2001, ‘JACK‘, 38) # 该方法返回的是数据库值的元组;
print(cur.fetchone()) # ！需要注意该方法存在指针偏移，也就是说我查询一次，这次的数据就在后续查询中不会在出现
for i in range(result): # 遍历
print(cur.fetchone())
# 查询多条(指定数量)
print(cur.fetchmany(3)) # 返回的是一个嵌套的元组
>> ((2001, ‘JACK‘, 38), (2002, ‘yelan‘, 18), (201901, ‘jack‘, 20))