json数据自动生成MySQL建表语句

最新推荐文章于 2025-06-20 22:56:13 发布

huayanqiaq

最新推荐文章于 2025-06-20 22:56:13 发布

阅读量4.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： python

本文链接：https://blog.youkuaiyun.com/huayanqiaq/article/details/82705517

python 专栏收录该内容

17 篇文章

订阅专栏

本文介绍如何使用Python的pymysql库连接MySQL数据库，创建数据表并插入字典数据。通过定义函数实现数据插入操作，展示了从字典到元组的转化，以及根据元组生成MySQL语句的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#_*_coding:utf-8_*_
#author: cike
#date: 18-9-13
import pymysql.cursors


connection = pymysql.connect(host='localhost',
                            port = 3306,
                            user='root',
                            passwd='root',
                            db ='jsontest',
                            charset='utf8mb4',
                            cursorclass=pymysql.cursors.DictCursor,)

#这里设置表名
table_name = "test12"
#这里设置需要的字典数据
dict1 = {
            "id": "971",
            "payout_id": "10602",
            "payin_id": "0",
            "pay_nums": "500.00",
            "pay_state": "0",
            "pay_time": "1536829856",
            "pay_no": "PAY2018091348534951",
            "card_id": "664",
            "trade_notes": "null",
            "trans_type": "0",
            "trans_img": "null",
            "get_moneytime": "null",
            "fee_nums": "100.00",
            "out_card": "null"
}

#数据插入函数
def insert1(sql):
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql)
        connection.commit()
        print(u"创建表%s成功" %(table_name))
    finally:
        connection.close()
#根据元祖数据生成mysql语句
def make_sql(tuple2):
    str1 = "CREATE TABLE `%s`" %(table_name)
    str2 = "ENGINE=InnoDB DEFAULT CHARSET=utf8;"
    str4=""
    for i in tuple2:
        str3 = "`"+i+"`"+" "+"varchar(255) DEFAULT NULL,"
        str4 = str4+str3
    str5 = "("+str4.rstrip(",")+")"
    str6 = str1+" "+str5+str2
    return str6
#把字典数据转化为元祖数据
def tuple1(dict1):
    list1 = []
    print(u"总字段数:"+str(len(dict1)))
    for i in dict1.keys():
        list1.append(i)
    m = tuple(list1)
    return m

def main():
    tuple2 = tuple1(dict1)
    sql_str = make_sql(tuple2)
    insert1(sql_str)

if __name__ == '__main__':
    main()