def get_all_column_by_table_name(table_name):
conn = dbpool.connection()
sqlstr = """SELECT `COLUMN_NAME` ,`DATA_TYPE` , `COLUMN_TYPE`,
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_COMMENT, IS_NULLABLE, COLUMN_COMMENT
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA= DATABASE() AND TABLE_NAME = '%s' """ % table_name
cursor = conn.cursor(cursorclass=DictCursor)
cursor.execute(sqlstr)
result = cursor.fetchall()
cursor.close()
conn.close()
return result
def get_table_constraints(table_name):
sql = """SELECT k.COLUMN_NAME, t.CONSTRAINT_TYPE, t.CONSTRAINT_NAME
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING ( constraint_name, table_schema, table_name )
WHERE t.table_schema = DATABASE()
AND t.table_name = '%s'
""" % table_name
conn = dbpool.connection()
cursor = conn.cursor(cursorclass=DictCursor)
cursor.execute(sql)
result = cursor.fetchall()
cursor.close()
conn.close()
return result
def get_all_table():
conn = dbpool.connection()
sqlstr = '''
SELECT
TABLE_NAME, TABLE_COMMENT
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = DATABASE()
and Table_type = 'BASE TABLE'
'''
cursor = conn.cursor(cursorclass=DictCursor)
cursor.execute(sqlstr)
result = cursor.fetchall()
cursor.close()
conn.close()
return result
