def fetch_db_structure(conn: pymysql.Connection, db_name: str) -> dict: """""" """""" conn.select_db(db_name) cur = conn.cursor() cur.execute("SHOW TABLES") tables = [] for table in cur.fetchall(): tables.append(table[0]) desc = {} for table in tables: table_desc = {} cur.execute("SHOW FULL COLUMNS FROM " + str(table)) for row in cur.fetchall(): row_desc = { str(row[0]): { 'type': str(row[1]), 'collation': str(row[2]), 'null': str(row[3]), 'default': str(row[4]), 'extra': str(row[5]), 'privileges': str(row[6]), 'comment': str(row[7]), } } table_desc.update(row_desc) desc.update({table: table_desc}) return desc
def create_table(self, connection: pymysql.Connection = None): if not connection: connection = self.connection connection.select_db("test_task") connection.cursor().execute(""" CREATE TABLE IF NOT EXISTS `pricelist`\ (`name` varchar(20),\ `count` varchar(20),\ `price` varchar(20),\ `action` varchar(20))\ """)
def insert_in_table( self, name: str, count: Union[int, str], price: Union[int, str], connection: pymysql.Connection = None, ): if not connection: connection = self.connection connection.select_db("test_task") # TODO: добавить обработку sql-инъекции connection.cursor().execute( """ INSERT INTO `pricelist` \ (`name`, `count`, `price`) VALUES (%s, %s, %s); """, (name, count, price), ) connection.commit()