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