def search_all_user():
# sql语句 建表
sql = """SELECT * FROM users;"""
try:
# 执行语句
cursor.execute(sql)
# 抓取
rows = cursor.fetchall()
# 事物提交
conn.commit()
return rows
except Exception as err:
conn.rollback()
return err
async def find_staff(staff: FindStaff):
# sql语句 建表
sql = """SELECT * FROM staff where username = %s and password = %s;"""
# 执行语句
params = (staff.username, staff.password)
try:
cursor.execute(sql, params)
# 抓取
rows = cursor.fetchall()
# 事物提交
conn.commit()
return rows
except Exception as err:
conn.rollback()
return err
def find_order(orderid: str):
# sql语句 建表
sql = """SELECT * FROM orders where orderid = %s;"""
# 执行语句
params = (orderid, )
try:
cursor.execute(sql, params)
# 抓取
rows = cursor.fetchall()
# 事物提交
conn.commit()
return rows
except Exception as err:
conn.rollback()
return err
def find_user(openid):
# sql语句 建表
sql = """SELECT * FROM users where openid = %s;"""
# 执行语句
# params = (username, address)
params = (openid, )
try:
cursor.execute(sql, params)
# 抓取
rows = cursor.fetchall()
# 事物提交
conn.commit()
return rows
except Exception as err:
conn.rollback()
return err
async def create_staff(staff: Staff):
idsql = """SELECT * FROM staff where id = %s"""
idparams = (staff.id,)
cursor.execute(idsql, idparams)
conn.commit()
rows = cursor.fetchall()
print(rows)
if rows:
return -1
else:
sql = """INSERT INTO staff (id, username, password) VALUES (%(id)s,%(username)s, %(password)s)"""
params = {'id': staff.id, 'username': staff.username, 'password': staff.password}
try:
data = cursor.execute(sql, params)
conn.commit()
return data
except Exception as err:
conn.rollback()
return err
async def create_user(user: User):
idsql = """SELECT * FROM users where openid = %s"""
idparams = (user.openid, )
cursor.execute(idsql, idparams)
conn.commit()
rows = cursor.fetchall()
if rows:
return -1
else:
sql = """INSERT INTO users (openid, username, region, address) VALUES (%(openid)s, %(username)s, %(region)s, %(address)s)"""
params = {
'openid': user.openid,
'username': user.username,
'region': user.region,
'address': user.address
}
try:
cursor.execute(sql, params)
conn.commit()
except Exception as err:
conn.rollback()
return err
async def create_order(order: Order):
idsql = """SELECT * FROM orders where orderid = %s"""
idparams = (order.orderid, )
cursor.execute(idsql, idparams)
conn.commit()
rows = cursor.fetchall()
print(rows)
# if rows:
# return -1
# else:
sql = """
INSERT INTO orders (
orderid,
createuser,
latitude,
longitude,
orderaddress,
orderphonenum,
taskmaster,
masterphonenum,
createdate,
starttime,
endtime,
ordertype,
orderdesc,
orderprice,
orderrate,
orderpic
) VALUES (
%(orderid)s,
%(createuser)s,
%(latitude)s,
%(longitude)s,
%(orderaddress)s,
%(orderphonenum)s,
%(taskmaster)s,
%(masterphonenum)s,
%(createdate)s,
%(starttime)s,
%(endtime)s,
%(ordertype)s,
%(orderdesc)s,
%(orderprice)s,
%(orderrate)s,
%(orderpic)s)
"""
params = {
'orderid': order.orderid,
'createuser': order.createuser,
'latitude': order.latitude,
'longitude': order.longitude,
'orderaddress': order.orderaddress,
'orderphonenum': order.orderphonenum,
'taskmaster': order.taskmaster,
'masterphonenum': order.masterphonenum,
'createdate': order.createdate,
'starttime': order.starttime,
'endtime': order.endtime,
'ordertype': order.ordertype,
'orderdesc': order.orderdesc,
'orderprice': order.orderprice,
'orderrate': order.orderrate,
'orderpic': order.orderpic,
}
print(params)
# try:
cursor.execute(sql, params)
conn.commit()