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