async def create_order_table():
sql = """
CREATE TABLE IF NOT EXISTS orders(
orderid varchar(18) PRIMARY KEY,
createuser varchar(8) NOT NULL,
latitude decimal NOT NULL,
longitude decimal NOT NULL,
orderaddress varchar(30) NOT NULL,
orderphonenum varchar(11) NOT NULL,
taskmaster varchar(5),
masterphonenum varchar(11),
createdate varchar NOT NULL,
starttime varchar NOT NULL,
endtime varchar NOT NULL,
ordertype integer NOT NULL,
orderdesc varchar(20) NOT NULL,
orderprice decimal NOT NULL,
orderrate decimal,
orderpic TEXT[] NOT NULL
);
"""
try:
cursor.execute(sql)
print("order table created successfully")
conn.commit()
except Exception as err:
conn.rollback()
return err
def update_order(orderid: str, username: str, password: str):
sql = """UPDATE orders set username = %s and password = %s where orderid = %s"""
params = (username, password, orderid)
try:
cursor.execute(sql, params)
conn.commit()
except Exception as err:
conn.rollback()
return err
async def delete_order(orderid: str):
sql = """delete from orders where orderid = %s """
params = (orderid, )
try:
cursor.execute(sql, params)
conn.commit()
except Exception as err:
conn.rollback()
return err
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 create_user_table():
sql = """
CREATE TABLE users(
openid varchar(30) PRIMARY KEY,
username varchar(20),
region varchar(30),
address varchar(40)
);
"""
try:
cursor.execute(sql)
print("user table created successfully")
conn.commit()
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 delete_user(openid: str):
sql = """delete from users where openid = %s"""
params = (openid, )
try:
data = cursor.execute(sql, params)
conn.commit()
return data
except Exception as err:
conn.rollback()
return err
async def update_staff(staff: Staff):
sql = """UPDATE staff set username = %s and password = %s where id = %s"""
params = (staff.username, staff.password, staff.id)
try:
data = cursor.execute(sql, params)
conn.commit()
return data
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 update_user(user: User):
sql = """UPDATE users SET username=%(username)s, address=%(address)s, region=%(region)s WHERE openid=%(openid)s"""
params = {
"username": user.username,
"address": user.address,
"region": user.region,
"openid": user.openid
}
try:
data = cursor.execute(sql, params)
conn.commit()
return data
except Exception as err:
conn.rollback()
return err
async def create_staff_table():
sql = """CREATE TABLE IF NOT EXISTS staff(
id varchar(11) PRIMARY KEY,
username varchar(20),
password varchar(50)
);
"""
try:
data = cursor.execute(sql)
print("staff table created successfully")
conn.commit()
return data
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()
