Easy way to convert dictionary to SQL insert with Python

Python 3 convert dictionary to SQL insert

Insert multiple rows at once with Python and MySQL

* first one will create SQL like syntax into text file which can be used for DB import

mydict = {'user': 'Bot', 'version': 0.15, 'items': 43, 'methods': 'standard', 'time': 1536304833437, 'logs': 'no', 'status': 'completed'}

sql = "INSERT INTO %s ( %s ) VALUES ( %s );" % ('mytable', columns, values)
print(sql)

* second will do imports from the MySQL to DB directly(if you have corect access)

mydict = {'user': 'Bot1', 'version': 0.11, 'items': 23, 'methods': 'standard', 'time': 1536304833437, 'logs': 'no', 'status': 'completed'}

placeholders = ', '.join(['%s'] * len(mydict))
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % ('mytable', columns, placeholders)

---------------------------------------------------------------------------------------------------------------------------------------------------------------
Code store

Socials

[softhints]

If you need the reverse operation -
How to Convert MySQL Table to Pandas DataFrame / Python Dictionary

[jbmustaq]

I am getting a all stock name with stock code in dictionary and trying to put it in sql. I tried to use the same but I think python 3 person needs to use .format also I am getting a error in below statement. placeholders = ', '.join([f'{all_stock}'] * len(all_stock))
columns = ', '.join(all_stock.keys())
sql = "INSERT INTO stock ( {} ) VALUES ( {} )".format(columns, placeholders)
cursor.execute(sql, all_stock.values()) . please let me know what I am doing wrong here.

[s.m.ehsanulamin]

hello i have a one question. Can you please tell me how can i execute multiple row line (say 5 row lines) from a CSV file in one sql query with python? To be more precise i am telling you what do i want:
I want to have output as for 5 lines rows:

insert in to table x value ((q, w), (r, z), (l, m), (a, b), (c, d))

but i am getting output while running this code:
with open (x, 'rb') as csv_file:
csv_reader=csv.DictReader(csv_file, delimiter=', ')
i = 0
datalist=[]
for rows in csv_reader:
print rows
datalist=datalist+[rows]
if i== 4:
print datalist
for mydict in datalist:
columns = ', '.join("`" + str(y)+ "`" for y in mydict.keys())
values = ', '.join("'" + str(z)+ "'" for z in mydict.values())
print values
sql = "insert into %s ( %s ) values ( %s) ;" % ('data', columns, values)
print sql
db_cursor.execute(sql)
db_connection.commit()
and hence getting the output like below:
insert into table x value (q, w)
insert into table x value (r, z)
insert into table x value (l, m)
insert into table x value (a, b)
insert into table x value (c, d)


I will be really greatful if you give me a solution for this problem

[mediumshrimp]

How do you do the exact opposite, I want to convert my database into a dictionary.

[mannycalavera]

Thanks for making this video, it's great for beginners like me to learn his to apply some of the basic knowledge tutorials teach.

I have my first python project, collecting information from an exchange websocket and commiting it to a postgresql database.

One issue I have is some subscription data is only sent when it changes. So my functions expects argument a, b and c, but only receives b and c and throws an error. This videos explains a way around that, or the poor way I was writing the function.

Is there a way to carry forward a previous rows value if no key is received with. Corrospondig value?

[s.m.ehsanulamin]

Thanks for your helpful video. I want to know suppose if i want to insert 5 row lines at a time then how can i do that. Here you are inserting one by one . But for me if i want to insert 5 lines at a time then what can i do for this.

[leelavenkatesh]

good explanation ..
i need how to load mutliple text files into mutliple sql tables using python dictionaries please help me

[khan]

Hello everyone, anyone knows how to create dictionary of sql table ?

[s.m.ehsanulamin]

Hello concern,
finally i got the output and i want to share my code with this group




import csv
import sys
import os
import string
import MySQLdb
from array import *
db_connection = MySQLdb.connect(host='127.0.0.1', user= 'voice', passwd= 'ever', db='data')
db_cursor =db_connection.cursor()

for f in file_name:
x='/home/admin/CSV/'+f
print x
with open (x, 'rb') as csv_file:
csv_reader=csv.DictReader(csv_file, delimiter=', ')
i = 0
datalist=[]
for rows in csv_reader:
#print rows
datalist=datalist+[rows]
if i== 4:
print datalist
buffer_value=''
for mydict in datalist:
columns = ', '.join("`" + str(y)+ "`" for y in mydict.keys())
values = ', '.join("'" + str(z)+ "'" for z in mydict.values())
if buffer_value=="":

else:
buffer_value=buffer_value+", ("+values+")"
sql = "insert into data( "+columns+" ) values " + buffer_value
print sql
db_cursor.execute(sql)
db_connection.commit()
i=0
datalist=[]
continue
else:
i += 1
if datalist!='':
#print datalist
buffer_value=''
for mydict in datalist:
columns = ', '.join("`" + str(y)+ "`" for y in mydict.keys())
values = ', '.join("'" + str(z)+ "'" for z in mydict.values())
if buffer_value=="":

else:
buffer_value=buffer_value+", ("+values+")"
sql = "insert into data( "+columns+" ) values " + buffer_value
print sql
db_cursor.execute(sql)
db_connection.commit()