Python MySQL 动态插入

本文介绍了Python MySQL 动态插入 - 列名作为变量，值作为变量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试创建一个填充 MySQL 表的函数.该函数接收多个变量作为输入:

I am trying to create a function that populates a MySQL table. The function receives several variables as inputs:

Symbol Var_Date Stat_Name Stat_Value

Stat_Name 的值随着代码运行而变化，例如 Enterprise_Value、Profit_Margin、EBITDA - 这些是表中的列名称.Stat_Value 是进入行的数据.

The value of Stat_Name changes as the code runs, eg Enterprise_Value, Profit_Margin, EBITDA - these are the column names within the table. Stat_Value is the data that goes into the rows.

此代码需要一个列名作为变量，三个值作为变量.

This code requires one Column Name as a variable, and three values as variables.

我发现之前的这篇文章看起来很有希望，但我还没有能够让它发挥作用.python中的动态mysql插入语句

I found this previous article which looks promising, but I haven't been able to make it work as of yet. dynamic mysql insert statement in python

我已将我的尝试粘贴到以下代码中:

I have pasted my attempt at the code below:

dbname = "mydb" var_table = "exc" symbol = 'ABC' var_date = '20200629' stat_name = 'Enterprise_Value' stat_value = '12345' def funPopulateTables(symbol, var_date, stat_name, stat_value): conn = pymysql.connect(host="localhost", user="root", passwd=mypass, db=dbname) my_cursor = conn.cursor() query_placeholders = ', '.join(['%s'] * len(stat_value)) query_columns = ', '.join(stat_name) add_word = ("INSERT INTO {table} " "(Symbol, Date, (%s)) " "VALUES (%s, %s, %s)") %(query_columns, query_placeholders) data_word = (symbol, var_date, stat_value) my_cursor.execute(add_word.format(table=var_table), data_word) connmit() conn.close() funPopulateTables(symbol, var_date, stat_name, stat_value)

任何帮助将不胜感激.提前致谢.

Any help would be greatly appreciated. Thanks in advance.

推荐答案

在遵循 khelwood 的建议后，此代码现在可以工作了:

After following the advice from khelwood, this code is now working:

dbname = "mydb" var_table = "exc" var_date = '20200629' symbol = 'AAB' dict_stats = {'Enterprise_Value' : '12345', 'EBITDA' : '67890'} def funPopulateTables(symbol, var_date, key, stat): conn = pymysql.connect(host="localhost", user="root", passwd=mypass, db=dbname) my_cursor = conn.cursor() add_word_temp = str("INSERT INTO {table} " "(Symbol, Date, ") + key + str(") " " VALUES (%s, %s, %s)") print(add_word_temp) add_word = str(add_word_temp) data_word = (symbol, var_date, stat) my_cursor.execute(add_word.format(table=var_table), data_word) connmit() conn.close() for key in dict_stats: print(key) stat = dict_stats[key] print(stat) funPopulateTables(symbol, var_date, key, stat)