时间戳

首页 > 解决方案 > 如何使用列获取数据类型并创建字典

python - 如何使用列获取数据类型并创建字典

问题描述

Mysql 表student有 2 个表students和teach

代码如下

def tbl_col_names(table):
    db = {}
    for i in table:
        cursor.execute("select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= '%s'" % (i))
        tabledescription = cursor.fetchall()
        print ('tableDesc1', tabledescription)
        tableDescription1 = [i[0] for i in tabledescription]
        print (tableDescription1)
        db[i] = tableDescription1
        with open('data.json','w') as f:
            db1 = json.dumps(db)
            test = (f"data ='[{db1}]'")
            f.write(test)
    return db
cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema='databasename'")
ecords = cursor.fetchall()
print (records)
member = [i[0] for i in records]
print ('member',member)
allcolumnvalues = tbl_col_names(member)
print ('all',allcolumnvalues )

data.json 输出如下

data = '[{"students": ["student_no"], "teach": ["last_name", "course_no"]}]'

如何获取数据类型以及列名

下面是获取列名和数据类型的mysql查询

select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where table_name='student';

cursor.execute("select COLUMN_NAME,DATA_TYPE  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= '%s'" % (i))

我的预期结果data.json如下

data = '[{"students": {"student_no":"int"}, "teach": {"last_name":"varchar", "course_no":"int"}}]'

标签： pythondictionary

解决方案

cursor.execute('show columns from databasename.students')
records = cursor.fetchall()
d = {record[0]: record[1] for record in records}
# or d = {record['Field']: record['Type'] for record in records} if the cursor.execute returns a dictionary

这将返回每个字段类型的详细描述，包括精度，例如int(11)or varchar(32)。如果您不希望包含精度，则：

d = {record[0]: record[1].split('(')[0] for record in records}

对于您感兴趣的每个数据库/表，您可以将如上例中计算的字典附加到列表中，以获得所需的结果：

def get_table_attributes(cursor, database, table):
    cursor.execute(f'show columns from {database}.{table}')
    records = cursor.fetchall()
    return {record[0]: record[1] for record in records}
    # return {record[0]: record[1].split('(')[0] for record in records}

data = []
data.append({'students': get_table_attributes(cursor, 'databasename', 'students')})
data.append({'teach': get_table_attributes(cursor, 'databasename', 'teach')})

推荐阅读