Create a MySQL insert query using a nested dictionary and custom parameters

Question

I have a nested dictionary "sample_dict and a few Parameters in Python :

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}}

which, for a given date, has varying keys and values.

The structure of the MySQL table "sample_table" intended for insertion is as follows:

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;

How can a MySQL insert query be created by using for loops in Python by merging the "sample_dict" and "Parameters"? For example, the output query should be created in the below shown format:

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');

The final inserts in the "sample_table" would look like this:

The final inserts in the "sample_table" would like this: 1) Post as text, not as a picture (with proper formatting); 2) Show the output which matches shown source data strictly (not "like this"). — Akina
– Akina, Commented May 19, 2021 at 5:06
@Akina: I have posted the inserts and their EXACT corresponding output. Please take the time to go thought it, before criticizing ! :) — Prateek Daniels
– Prateek Daniels, Commented May 19, 2021 at 5:15
Oops... sorry. I was confused by the 12 hour value (and the non-standard datetime format for MySQL) — Akina
– Akina, Commented May 19, 2021 at 5:21

Jan Wilamowski · Accepted Answer · 2021-05-19 05:06:55Z

If you want simple string construction, this should do:

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}');")

output:

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');

Prateek Daniels · Accepted Answer · 2021-05-20 20:15:08Z

1

you can even directly insert into 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)

edited May 20, 2021 at 20:15

Prateek Daniels

4831 gold badge6 silver badges20 bronze badges

answered May 19, 2021 at 5:41

mohit nathani

213 bronze badges

5 Comments

Prateek Daniels Over a year ago

Thanks for the solution. The value in result_dict gets overwritten after every loop. Could you correct the solution

mohit nathani Over a year ago

@PrateekDaniels yep, my bad. changes done cheers!

Prateek Daniels Over a year ago

Thanks for making the change, but it's still wrong. The list "result_list" produces duplicate entries of date:'2014-12-31 00:00:00'

Prateek Daniels Over a year ago

The result_list should have seven unique entries

Prateek Daniels Over a year ago

I figured out the error, the corrected line of code should be : result_list.append(result_dict.copy())

Collectives™ on Stack Overflow

Create a MySQL insert query using a nested dictionary and custom parameters

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related