CODEWITHSUNDEEP
pythonmysqlpython-2.7dictionaryinsert
Veljko
Veljko

Reputation: 1808

Python 2.7 - insert multiple values in MySql in one execution from dictionary key values

I am trying to make dynamic insert command into mysql, from dictionary values in one transaction but getting an error. Also I was wondering what is the most efficient way to perform this specific case (maybe my code is not optimal)? I am using FOR since in some cases dictionary can be empty. Thanks

    import mysql.connector
    mydb = mysql.connector.connect(..........
    mycursor = mydb.cursor()
    varStatic="test"
    cust={'74.2': '54', '172.26': '76', '7': 'B9'}
    insertStatement='"""INSERT INTO customers (id,number,desc) VALUES (%s,%s,%s)"""'
    for key in cust:
          insertStatement+=',('+key+','+cust[key]+','+varStatic+')'
    mycursor.execute(insertStatement)
    mydb.commit()

Upvotes: 0

Views: 197

Answers (1)

KC.
KC.

Reputation: 3107

You may do something like this, but a little confused about how to optimize for-loop and value. If i can get rid of append or replace to List Comprehensions, then you can use insertStatement += ("(%s,%s,%s),"*len(cust.items()))[:-1]

import mysql.connector
mydb = mysql.connector.connect(user="k",passwd="k",db="k")
mycursor = mydb.cursor()
varStatic="test"
cust={'74.2': "54'", '172.26': '76', '7': 'B9'}
insertStatement= """INSERT INTO customers (id,number,desc) VALUES """
value = []
for k,v in cust.items():
    insertStatement += "(%s,%s,%s),"
    value.append(k)
    value.append(v)
    value.append(varStatic)

print(insertStatement[:-1],value)
try:
    mycursor.execute(insertStatement[:-1],value)
    mydb.commit()
except Exception as e:
    print(e)
    mydb.rollback()

Upvotes: 1

Related Questions