Python sql query insert data in columns

Question

I'm not so familiar with SQL. I have a table with over 100 columns. What is the best way to insert data from a dictionary into the corresponding column of my table (key and column names are identical)?

Example:

My SQL Table

Column1 Column2 Column3 Column4 Column5

My data which I get from a dictionary:

First Data set

Column2:apple, Column4:tree

Second Data set

Column1:banana, Column2:blue, Column3:green, Column4:house, Column5:red

So if the key of the dictionary is identical to the column name of the table, the data should be inserted. I know how to do this for a single query but how can I do this (clever) for a large number of queries?

Thanks!

Edit:

What I would do at a single level and some clarifications:

1) Create a SQL Table with all 120 columns but without any data. 2) I have about 500 word files which contain a part of the data which should go into the database (about 5-10 from the 120 columns). 3) So I have to read them, parse them and if the key of the data in the dictionary match any of the 120 column names, the value should be inserted. 3) Single mode:

if key == 'Column1':

sql = INSERT INTO Column1 valuexy

This is of course very stupid for 500 files and 120 columns. I hope this was more clear.

Could you share what you've got to do it for a single query? Then it'll be easier for people to show you how to adapt it for many queries. — Martin Atkins
– Martin Atkins, Commented Mar 28, 2013 at 15:07

bgusach · Accepted Answer · 2013-03-28 15:52:53Z

4

Let's say you have the following dict:

query_data = {
    'col1': 56,
    'col2': 100,
    'col3': 'lol'
}

Your sql query could be constructed as following:

sql = 'INSERT INTO table_name ({fields}) VALUES ({values})'

Now you have to construct the fields string

fields = query_data.keys()
fields_string = ', '.join(fields)

and for the values you do something similar, using the already created fields lists. (This could be done in another way if your dictionary was an OrderedDict).

values_string = ', '.join(['"{0}"'.format(query_data[key]) for key in fields])

Finally you can compose the string.

composed_sql = sql.format(fields=fields_string, values=values_string)

This is a very rough method, but very understandable if you are new to this topic. Most of frameworks allow for much refined methods to achieve this. Especially as to SQL injections.

Print it, play around, understand the concepts and with time you'll probably use other ways.

EDIT

A more succinct way would be:

sql = 'INSERT INTO table_name ({fields}) VALUES ({values})'
fields = ', '.join(query_data.keys())
values = ', '.join(['"{0}"'.format(value) for value in query_data.values()])
composed_sql = sql.format(fields=fields, values=values)

edited Mar 28, 2013 at 15:52

answered Mar 28, 2013 at 15:14

bgusach

15.4k15 gold badges59 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

honeymoon Over a year ago

Thank you for this suggestion, can you tell me where is the mistake?

bgusach Over a year ago

Sorry, Rodas was right. I thought since Dicts don't have order, keys() and values() could return in different order. But fact is, they don't. I'll edit to use that approach since it is a little bit cleaner. map() is anyway "deprecated", and you can't add quotes, so unless you are inserting only numbers, your query will crash.

A. Rodas · Accepted Answer · 2013-03-28 15:21:19Z

2

You can concatenate the keys and the values of the dictionary with join() and map():

d = {'Column1':'banana', 'Column2':'blue', 'Column3':'green', 'Column4':'house', 'Column5':'red'}
columns = ', '.join(map(str, d.keys()))
values = ', '.join(map(repr, d.values()))
cursor.execute("INSER INTO table ({}) VALUES ({})".format(columns, values))

answered Mar 28, 2013 at 15:21

A. Rodas

20.8k8 gold badges70 silver badges76 bronze badges

7 Comments

bgusach Over a year ago

This is a bad solution. Nobody guarantees that the .keys() and .values() are in the same order since dictionaries are not ordered. Besides, map() is dis-recommended, you should use generators instead. edit no, I was wrong. keys and values are returned in the same order.

honeymoon Over a year ago

Ok! One more question is it a good habit to leave the rest "empty" (NULL)?

koriander Over a year ago

columns = ', '.join(d.keys()) is good enough. @ikaros45: why is map "dis-recommended"? {}{} means {0}{1}, and 0,1 means 1st and 2nd argument, columns, and values, thus.

A. Rodas Over a year ago

@snowflake {} is used to be replaced with the arguments of format(). Keys and values are returned in the same order, and map is a built-in function as valid as any other. It may not the better solution, but at least these aren't the main problems ;)

bgusach Over a year ago

@koriander List comprehensions (not generators, sorry) provide a syntax cleaner than map(), reduce()..., without messing with lambda functions and easier nesting, etc. It's just more pythonic.

|

Collectives™ on Stack Overflow

Python sql query insert data in columns

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related