CODEWITHSUNDEEP
pythonmysqlpython-3.xmysql-pythonmysql-connector
tmdag
tmdag

Reputation: 529

passing dictionary as parameter for mysql.connector

I had semi working, unsafe set of functions for mysql.connector that can accept data in a form like one below. It is flexible as I could design any size of dictionary i need.

db_data={
"column01" : 'test',
"column02" : 51,
"column03" : None
}
columns = db_data.keys()
values = db_data.values()
tablename = "mytable"

It "works" fine but it is unsafe (although it is only for internal use so I did not care about safety here) but I am also not able to pass "None" (Null object) to mysql.

unsafe_query = "INSERT INTO {0:s} ({1:s}) VALUES ({2:s})".format(tablename, ', '.join(map(str, columns)), ','.join(map(repr, values)))
cursor.execute(unsafe_query)

I will get an error:

Something went wrong: 1054 (42S22): Unknown column 'None' in 'field list'

Following suggestion of how it should be properly designed, I've tried this way but I cannot get string formatting to pass dictionary same way anymore and I'm little stuck here:

safe_query = "INSERT INTO %s (%s) VALUES (%s)"
cursor.execute(safe_query, (tablename, ', '.join(map(repr, columns)), ', '.join(map(repr, values))) )
# cursor.execute(safe_query, (tablename, ', '.join(map(str, columns)), ', '.join(map(str, values))) ) # this won't work either

Getting error:

Something went wrong: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''mytable' ('\'column01\', \'column02\', \'column03\'') VALUES ('\'test\', ' at line 1

Maybe someone could suggest how to get around it and get mysql Null object to work. Ideally would be to have a proper, safe query but that's not the most important issue for me.

Upvotes: 0

Views: 1843

Answers (1)

josoler
josoler

Reputation: 1423

You probably want your values to be handled by the driver and averything else composed as a string:

query = "INSERT INTO {} ({}) VALUES ({})".format(tablename, ', '.join(columns), ','.join(['%s']*len(values)))
cursor.execute(query, values)

Even better, you could use the dict directly:

query = "INSERT INTO {} ({}) VALUES ({})".format(tablename, ', '.join(columns), ','.join(['%({})s'.format(colname) for colname in columns]))
cursor.execute(query, db_data)

Anyway, I would not encourage such string formatting in the first place. If you now the tablename and columnnames beforehand and that's not going to change, you'll probably have a more readable and reliable code by hardcoding everything but the values.

Upvotes: 1

Related Questions