# 获得插入id
cursor.execute("SELECT LAST_INSERT_ID()")
id = cursor.fetchall()
return id[0]['LAST_INSERT_ID()']
# 导入Flask类
import json
import os
from sqlalchemy import create_engine, text
import pymysql
import traceback
current_dir_path = os.getcwd()
# print(current_dir_path)
with open(f"{current_dir_path}/configs/setting.json", 'r') as file:
datas = json.load(file)
class Db(object):
# 连接库
def base(database):
engine = create_engine(
f"mysql+pymysql://{datas['myuser']}:{datas['mypwd']}@{datas['myip']}:{datas['myport']}/{database}",
# f"mysql+pymysql://root:root@127.0.0.1:3306/{database}",
max_overflow=0, # 超过连接池大小外最多创建的连接
pool_size=5, # 连接池大小
pool_timeout=30, # 池中没有线程最多等待的时间,否则报错
pool_recycle=-1, # 多久之后对线程池中的线程进行一次连接的回收(重置)
isolation_level="READ UNCOMMITTED", # 查询缓存更新,查询不缓存
)
# conn = engine.connect()
conn = engine.raw_connection()
return conn
# 查询数据
def query(conn,sql):
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
res = cursor.fetchall()
# print(sql)
return res
# 新增,table表,data数据
def inserts(conn, table, data):
# 组装数据
sql = ''
for v1 in data:
if data[v1] != None:
if sql == "":
sql = f"`{v1}`='{data[v1]}'"
else:
sql = f"{sql},`{v1}`='{data[v1]}'"
insert = f"INSERT INTO {table} set {sql}"
print(insert)
cursor = conn.cursor(pymysql.cursors.DictCursor)
req = cursor.execute(insert)
# 获得插入id
cursor.execute("SELECT LAST_INSERT_ID()")
id = cursor.fetchall()
return id[0]['LAST_INSERT_ID()']
# 修改,table表,data数据,where条件
def updates(conn, table, data, where):
# 组装数据
sql = ''
for v1 in data:
if data[v1] != None:
if sql == "":
sql = f"`{v1}`='{data[v1]}'"
else:
sql = f"{sql},`{v1}`='{data[v1]}'"
update = f"UPDATE {table} set {sql} where {where}"
if table != 'state':
print(update)
cursor = conn.cursor(pymysql.cursors.DictCursor)
req = cursor.execute(update)
return 1
#删除,table表,where条件
def deletes(conn, table, where):
delete = f"delete from {table} where {where}"
print(delete)
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(delete)
return 1
# 关闭连接
def close(conn):
conn.close()