使用嵌套字典和自定义参数创建MySQL插入查询

Question

我在Python中有一个嵌套字典“sample_dict和几个参数：

import pandas as pd

#Parameters
Condition_Name='ID_0.5_365D'
Insert_Date='2021-05-19 00:00:00'

sample_dict=\
{pd.Timestamp('2015-01-02 00:00:00'): {'navi.o_efx': 180.0,
  'navi.o_qrvo.o': 180.0,
  'navi.o_stm.n': 180.0,
  'qrvo.o_efx': 1.0,
  'qrvo.o_stm.n': 1.0},
 pd.Timestamp('2014-12-31 00:00:00'): {'navi.o_efx': 179.0,
  'navi.o_stm.n': 179.0}}

对于给定的日期，具有不同的键和值

用于插入的MySQL表“sample_table”的结构如下：

CREATE TABLE sample_table (
  Date datetime DEFAULT NULL,
  Pair_Name varchar(255) DEFAULT NULL,
  Condition_Name varchar(255) DEFAULT NULL,
  Condition_Value double(16, 4) DEFAULT NULL,
  Insert_Date datetime DEFAULT NULL
)
ENGINE = INNODB,
AVG_ROW_LENGTH = 159,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci;

如何在Python中使用for循环，通过合并“示例dict”和“参数””来创建MySQL insert查询？例如，输出查询应以如下所示的格式创建：

INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2015-01-02 00:00:00','navi.o_efx','ID_0.5_365D',180,'2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2015-01-02 00:00:00', 'navi.o_qrvo.o','ID_0.5_365D',180,'2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2015-01-02 00:00:00',  'navi.o_stm.n','ID_0.5_365D',180,'2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2015-01-02 00:00:00',  'qrvo.o_efx','ID_0.5_365D',1,'2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2015-01-02 00:00:00',  'qrvo.o_stm.n','ID_0.5_365D',1,'2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2014-12-31 00:00:00','navi.o_efx','ID_0.5_365D',179,'2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name,Condition_Value,Insert_Date) VALUES ('2014-12-31 00:00:00','navi.o_stm.n','ID_0.5_365D',179,'2021-05-19 00:00:00');

“示例_表中的最终插入内容如下所示：

Answer 1

您甚至可以直接插入mysql：

import pandas as pd

import mysql.connector

from sqlalchemy import create_engine

result_dict, result_list = {}, []

#collect input

Condition_Name='ID_0.5_365D'

Insert_Date='2021-05-19 00:00:00'

sample_dict=\
{pd.Timestamp('2015-01-02 00:00:00'): {'navi.o_efx': 180.0,
  'navi.o_qrvo.o': 180.0,
  'navi.o_stm.n': 180.0,
  'qrvo.o_efx': 1.0,
  'qrvo.o_stm.n': 1.0},
 pd.Timestamp('2014-12-31 00:00:00'): {'navi.o_efx': 179.0,
  'navi.o_stm.n': 179.0}}

#populate result_dict
for key,value in sample_dict.items():
    for item_key, item_value in value.items():
        result_dict['condition_name']=Condition_Name
        result_dict['insert_date']=Insert_Date
        result_dict['date']=key
        result_dict['pair_name']=item_key
        result_dict['pair_value']=item_value
        result_list.append(result_dict.copy())

index_list = range(1, len(result_list)+1)

df1 = pd.DataFrame(result_list, columns = ['index', 'condition_name', 'insert_date', 'date', 'pair_name', 'pair_value'], index= index_list)

#dump into mysql
engine = create_engine('mysql+mysqldb://[user]:[pass]@[host]:[port]/[schema]', echo = False)
df1.to_sql(name = 'my_table', con = engine, if_exists = 'append', index = False)

Answer 2

如果需要简单的字符串构造，则应执行以下操作：

prefix = 'INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('
for timestamp, samples in sample_dict.items():
    for pair_name, condition_value in samples.items():
        print(f"{prefix}'{timestamp}', '{pair_name}', '{Condition_Name}', {condition_value}, '{Insert_Date}');")

输出：

INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2015-01-02 00:00:00', 'navi.o_efx', 'ID_0.5_365D', 180.0, '2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2015-01-02 00:00:00', 'navi.o_qrvo.o', 'ID_0.5_365D', 180.0, '2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2015-01-02 00:00:00', 'navi.o_stm.n', 'ID_0.5_365D', 180.0, '2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2015-01-02 00:00:00', 'qrvo.o_efx', 'ID_0.5_365D', 1.0, '2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2015-01-02 00:00:00', 'qrvo.o_stm.n', 'ID_0.5_365D', 1.0, '2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2014-12-31 00:00:00', 'navi.o_efx', 'ID_0.5_365D', 179.0, '2021-05-19 00:00:00');
INSERT INTO sample_table(Date, Pair_Name, Condition_Name, Condition_Value, Insert_Date) VALUES ('2014-12-31 00:00:00', 'navi.o_stm.n', 'ID_0.5_365D', 179.0, '2021-05-19 00:00:00');