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))

mysql-API

标签：class   获取   管理   fail   etc   exp   database   varchar   student