def mysql_conn(username,phone,email,mess):
# print(username, "\n", phone, "\n", email, "\n", mess, "\n")
#连接数据库
conn=Connection(host='localhost',user='******',password='******',port=3306,database='ly')
cursor=conn.cursor()
# ------------
data = {
'username': username,
'phone': phone,
'email': email,
'mess':mess
}
table = 'ly.talk'
# 获取到一个以键且为逗号分隔的字符串，返回一个字符串
keys = ', '.join(data.keys())
print("keys\n")
print(keys)
values = ', '.join(['%s'] * len(data))
print("values\n ")
print(values)
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
# 这里的第二个参数传入的要是一个元组
# 执行
if cursor.execute(sql, tuple(data.values())):
print('Successful')
# 提交
conn.commit()
except:
print('Failed')
conn.rollback()
# ------------
cursor.close()
conn.close()
return render_template("auth/index.html")
def generate(mysql: dict, table_name: str, model_name: str, model_path: str):
model_path = model_path % model_name
connection = Connection(**mysql)
try:
cursor = connection.cursor()
cursor.execute(
query="SELECT TABLE_COMMENT FROM information_schema.TABLES "
"WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
args=(mysql['database'], table_name))
TABLE_COMMENT, = cursor.fetchone()
cursor.close()
cursor = connection.cursor()
cursor.execute(
query=
"SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT FROM information_schema.COLUMNS "
"WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
args=(mysql['database'], table_name))
fields = cursor.fetchall()
cursor.close()
if os.path.exists(model_path):
print(f"File {model_path}:1 was overwrite.")
with open(model_path, 'w', encoding='utf8') as f:
f.write("from ._BaseModel import BaseModel\n\n\n")
f.write(f"class {model_name}(BaseModel):\n")
TABLE_COMMENT = ' '.join(TABLE_COMMENT.split())
if TABLE_COMMENT:
f.write(f' """{TABLE_COMMENT}"""\n\n')
f.write(" def __init__(self, row: dict = None):\n")
f.write(" row = dict() if row is None else row\n\n")
for COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT in fields:
if COLUMN_TYPE == 'bit(1)':
f.write(
f" self.{COLUMN_NAME} = (None"
f" if '{COLUMN_NAME}' not in row"
f" else bool(row['{COLUMN_NAME}'] == b'\\x01')"
f" if type(row['{COLUMN_NAME}']) is bytes and len(row['{COLUMN_NAME}']) == 1"
f" else row['{COLUMN_NAME}'])\n")
else:
f.write(
f" self.{COLUMN_NAME} = row['{COLUMN_NAME}'] if '{COLUMN_NAME}' in row else None\n"
)
COLUMN_COMMENT = ' '.join(COLUMN_COMMENT.split())
if COLUMN_COMMENT:
f.write(f' """{COLUMN_COMMENT}"""\n\n')
else:
f.write('\n')
except Exception as e:
connection.rollback()
print(type(e), e)
finally:
connection.close()
def execute_sql_command(connection: mariadb.Connection, statement: str):
# Warning! that is insecure and musn't be used as GUI function!
"""
executes any sql command
:param connection: Connection class on wich query will operate
:param statement: String to execute sql query
:return:
"""
isinstance(connection, mariadb.Connection.__class__)
cursor = connection.cursor()
try:
cursor.execute(statement)
connection.commit()
return cursor.fetchall()
except Exception as e:
connection.rollback()
print(e)
def save_dishes_to_sql(conn: pymysql.Connection, dishes: [Dish]):
"""
Saves the given dishes to the database
:param conn: The pymysql connection
:param dishes: The dishes to be saved
"""
if len(dishes) < 1:
return
cur = conn.cursor()
dish_tuples = []
dish_availability_tuples = []
for dish in dishes:
if len([item for item in dish_tuples if item[1] == dish.name]) > 0:
continue
dish_tuples.append((dish.dish_id, dish.name, dish.comparable_name))
dish_availability_tuples.append(
(dish.dish_id, dish.date, dish.price, dish.category))
insert_dish = \
'INSERT INTO dishes (dish_id, name, comparable_name) ' \
'VALUES (%s, %s, %s) ' \
'ON DUPLICATE KEY UPDATE ' \
' dish_id = VALUES(dish_id),' \
' name = VALUES(name)'
# Duplicate key can in theory be ignored but update also works
insert_dish_availability = \
'INSERT INTO dish_availability (dish, available_date, price, category) VALUES (%s, %s, %s, %s) ' \
'ON DUPLICATE KEY UPDATE available_id = VALUES(available_id)'
dishes_success = cur.executemany(insert_dish, dish_tuples)
dish_availabilities_success = cur.executemany(insert_dish_availability,
dish_availability_tuples)
if dishes_success and dish_availabilities_success:
conn.commit()
else:
conn.rollback()
raise Exception('Error saving menu to database')
cur.close()
