mysql api 查询_mysql-API

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