Using a Python dict for a SQL INSERT statement

python insert dictionary into database
how to insert user input into database in python
create an html form and insert data into the database using python
python mysql insert variable
python insert dictionary into postgres
python dictionary
how to insert integer value in mysql using python
how to automate sql queries using python

I am trying to use a dict to do a SQL INSERT. The logic would basically be:

INSERT INTO table (dict.keys()) VALUES dict.values()

However, I am having a tough time figuring out the correct syntax / flow to do this. This is what I currently have:

# data = {...}
sorted_column_headers_list = []
sorted_column_values_list = []
for k, v in data.items():
    sorted_column_headers_list.append(k)
    sorted_column_values_list.append(v)
sorted_column_headers_string = ', '.join(sorted_column_headers_list)
sorted_column_values_string = ', '.join(sorted_column_values_list)

cursor.execute("""INSERT INTO title (%s) 
            VALUES (%s)""", 
            (sorted_column_headers_string, sorted_column_values_string))

From this I get a SQL exception (I think related to the fact that commas are also included in some of the values that I have). What would be the correct way to do the above?

You want to add parameter placeholders to the query. This might get you what you need:

qmarks = ', '.join('?' * len(myDict))
qry = "Insert Into Table (%s) Values (%s)" % (qmarks, qmarks)
cursor.execute(qry, myDict.keys() + myDict.values())

Python 3 convert dictionary to SQL insert, Python 3 convert dictionary to SQL insert. first one will create SQL like syntax into text file which can be used for DB import. second will do imports from the MySQL to DB directly(if you have corect access) In this article you can see how to convert any python dictionary easily into SQL insert statements. This is useful when you need to provide SQL inserts to DBA guy who needs to apply them to DB. In this example we are testing the resulted query in MySQL 5.7 and output the queries to a external file. We will see two examples: * first one will create SQL like syntax into text file which can be

I think the comment on using this with MySQL is not quite complete. MySQLdb doesn't do parameter substitution in the columns, just the values (IIUC) - so maybe more like

placeholders = ', '.join(['%s'] * len(myDict))
columns = ', '.join(myDict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
# valid in Python 2
cursor.execute(sql, myDict.values())
# valid in Python 3
cursor.execute(sql, list(myDict.values()))

You're not getting escaping on the columns though, so you might want to check them first....

See http://mail.python.org/pipermail/tutor/2010-December/080701.html for a more complete solution

Python MySQL Insert Into, mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "record inserted.") Run example �. Important!: Notice the statement: mydb.commit() . Insert Into Table. To fill a table in MySQL, use the "INSERT INTO" statement.

Always good answers here, but in Python 3, you should write the following:

placeholder = ", ".join(["%s"] * len(dict))
stmt = "insert into `{table}` ({columns}) values ({values});".format(table=table_name, columns=",".join(dict.keys()), values=placeholder)
cur.execute(stmt, list(dict.values()))

Don't forget to convert dict.values() to a list because in Python 3, dict.values() returns a view, not a list.

Also, do NOT pour the dict.values() in stmt because it tears a quote out of a string by joining it, which caused MySQL error in inserting it. So you should always put it in cur.execute() dynamically.

Insert into mysql with python dict, How to use python dict as data source for mysql insert-into statement. len( dict_data)) columns = ', '.join(dict_data.keys()) sql = "INSERT INTO� First, connect to the SQLite database by creating a Connection object. Second, create a Cursor object by calling the cursor method of the Connection object. Third, execute an INSERT statement. If you want to pass arguments to the INSERT statement, you use the question mark (?) as the placeholder for each argument.

table='mytable'    
columns_string= '('+','.join(myDict.keys())+')'    
values_string = '('+','.join(map(str,myDict.values()))+')'    
sql = """INSERT INTO %s %s
     VALUES %s"""%(table, columns_string,values_string)

Easy way to convert dictionary to SQL insert with Python, Insert multiple rows at once with Python and MySQL first one will create SQL like syntax Duration: 4:50 Posted: 16-Sep-2018 The end goal is to insert new values into the dbo.Person table using Python.. Once you have your data ready, proceed to the next step. Step 2: Establish a connection between Python and SQL Server

I tried @furicle's solution but it still inputs everything as a string - if your dict is a mixed one then this may not work as you would want it to. I had a similar issue and this is what I came up with - this is only a query builder and you could use it (with changes) to work with any database of your choice. Have a look!

def ins_query_maker(tablename, rowdict):
    keys = tuple(rowdict)
    dictsize = len(rowdict)
    sql = ''
    for i in range(dictsize) :
        if(type(rowdict[keys[i]]).__name__ == 'str'):
            sql += '\'' + str(rowdict[keys[i]]) + '\''
        else:
            sql += str(rowdict[keys[i]])
        if(i< dictsize-1):
            sql += ', '
    query = "insert into " + str(tablename) + " " + str(keys) + " values (" + sql + ")"
    print(query) # for demo purposes we do this
    return(query) #in real code we do this

This is crude and still needs sanity checks, etc, but it works as intended. for a dict:

tab = {'idnumber': 1, 'fname': 'some', 'lname': 'dude', 'dob': '15/08/1947', 'mobile': 5550000914, 'age' : 70.4}

running the query I get the following output

results of query generated by the suite

Generate SQL for insertation into table from dictionary object , Generate SQL for insertation into table from dictionary object (Python .com/ questions/9336270/using-a-python-dict-for-a-sql-insert-statement� This is a possible duplicate of, or can be answered by this post Using a Python dict for a SQL INSERT statement However, to answer your question, I posted this over on the other question and i'm reposting it here as well:

5.3 Inserting Data Using Connector/Python, Both INSERT statements are stored in the variables called add_employee and add_salary . Note that the second INSERT statement uses extended Python format� Best practice for SQL statements in Python Thanks to a compulsory interface for database connectors, the "Python Database API Specification v2.0, PEP249", all current connectors have been developed so that database connections and the SQLs for data retrieval and data transactions can be started using the same commands.

Using Databases and SQL - Textbooks, Databases require more defined structure than Python lists or dictionaries. The SQL INSERT command indicates which table we are using and then defines a� SQLAlchemy provides a robust expression language for generating SQL from Python. Like every other well-designed abstraction layer, however, the queries it generates insert data through bind variables rather than through attempting to mix the query language and the data being inserted into a single string.

How to Insert Values into SQL Server Table using Python, I'll use a simple example to demonstrate this concept. To start, here is the general syntax that you may use to insert values into a table created in SQL Server: # insert data cols = "`,`".join([str(i) for i in df.columns.tolist()]) # insert dict records . for i,row in data_dict: sql = "INSERT INTO `test` (`" +cols + "`) VALUES (" + "%s,"*(len(row)-1) + "%s)" cursor.execute(sql, tuple(row)) connection.commit()

Comments
  • fyi: this did not work for me, but @furicle answer did
  • Using myDict.keys() in this way cannot guarantee any specific order between the keys. myDict.values() might not even produce values in the corresponding order as returned by myDict.keys().
  • @PatrikIselind - Incorrect. See stackoverflow.com/questions/835092/…. As long as there's no intervening changes (which there can't be with the above) they will always return in the same order.
  • this one gets error for the fields since it will detect it as a string although SQL wants it as a variable
  • The line cursor.execute(qry, columns, myDict.values()) has a syntax error. cursor.execute(qry, myDict.values()) is the correct form.
  • is there any ordering problem about dict ?
  • The best answer ever!
  • @zs1986 Per python's documentation, there shouldn't be an ordering problem from the dict: If items(), keys(), values(), iteritems(), iterkeys(), and itervalues() are called with no intervening modifications to the dictionary, the lists will directly correspond.
  • Use columns = "`%s`" % '`,`'.join(myDict.keys()) if your column names contain hyphens or other special/reserved characters
  • Seems like a good answer - thank you. But using dict as the variable name may not be ideal... Also I get a AttributeError: 'dict_values' object has no attribute 'translate'error from pymysql. Need to do more testing.
  • I had to do this stackoverflow.com/a/46590820/2075003 to get rid of the AttributeError
  • That's great help, how would you include 'ON DUPLICATE KEYS UPDATE'? tx!
  • Nice answer. Buy you need to scape a single quote in the join, to be more practical. '(\''+'\',\''.join(myDict.keys())+'\')' or better "('"+"','".join(myDict.keys())+"')"