Python MySQLdb "INSERT INTO" Issue

Question

My code attempts to iterate through dictionary, "dbMap", and execute MySQL INSERT INTO statements based on the keys and values of dbMAP:

for key in dbMap:
    try:
        cursor.execute("INSERT INTO '%s' (id, additional_details) VALUES (123, '%s')", (key, dbMap[key]))
    except UnicodeEncodeError:
        pass

I get the following error when I run the above code:

_mysql_exceptions.ProgrammingError: (1064, "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 '''random_key'' (id, additional_details) VALUES (123, 'random_value' at line 1")

I don't see what MySQL syntax I'm violating, I am following the following resources for help:

http://www.mikusa.com/python-mysql-docs/query.html

http://www.w3schools.com/sql/sql_insert.asp

UPDATE:When I try this:

for key in dbMap:
    try:
        cursor.execute("INSERT INTO {} (id, additional_details) VALUES (123, '{}')".format(key, dbMap[key]))
    except UnicodeEncodeError:
        pass

I get a different error:

_mysql_exceptions.OperationalError: (1054, "Unknown column 'id' in 'field list'")

UPDATE 2:

for key in dbMap:
    try:
        query = "INSERT INTO `%s` (id, additional_details) VALUES(123, %%s)" % key
        cursor.execute(query, dbMap[key])
    except UnicodeEncodeError:
        pass

Got a new error: TypeError: not all arguments converted during string formatting

Any assistance in helping me figure out what's going wrong is much appreciated

Answer 1

The mysqldb library only lets you substitute values, not tables:

Python and MySQLdb: substitution of table resulting in syntax error

Answer 2

I just jumped in an interpreter to play with this and it looks like you can't substitute a table name in the MySQLdb API, because it will put your replace in quotes (table names can be backticked but not quoted), so you must do it in two steps. iceberg is also correct, you do not provide your own quotes.

My sample:

>>> query = "INSERT INTO %s (id, login) VALUES (1972, %%s)" % 'users'
>>> cursor.execute(query, ('TEST-LOGIN123',))
>>> 1L

Try

for key in dbMap:
    try:
        query = "INSERT INTO `%s` (id, additional_details) VALUES (123, %%s)" % key
        cursor.execute(query, (dbMap[key],))
    except UnicodeEncodeError:
        pass

Answer 3

When using Python's MySQL library, I think you don't want to use ' around the variables. I believe they make the %s be interpreted literally, instead of having them be substituted. It's been awhile, since I've used this method, though, so I could be off-base. Try this:

try:
    cursor.execute("INSERT INTO %s (id, additional_details) VALUES (123, %s)", (key, dbMap[key]))

Python will do the substitution for you.

Also, considering using an abstraction to prevent SQL injection. Check out - this post - one of SO's most frequently-viewed.

Separately, since I'm looking closer at your Python line, and your actual error, I think your line doesn't do what you think it does.

for key in dbMap will produce the values of dbMap, not the key values - so when you call dbMap[key], since key isn't an index, it's getting an error. That might also mean you're not getting the right table that you're looking for with INSERT INTO key. Food for thought. Try:

for idx, value in enumerate(dbMap):
    cursor.execute("INSERT INTO %s (id, additional_details) VALUES (123, %s)", (idx, dbMap[idx]))

This will allow you to access the key index (idx) AND the mapDB value.

Answer 4

def insert_jugador():
if request.method == 'POST':
    apellidos = request.form['apellidos']
    nombres = request.form['nombres']
    direccion = request.form['direccion']

    SqlConsulta = mysql.connection.cursor()
    SqlConsulta.execute("INSERT INTO JUGADORES (APELLIDOS, NOMBRES, 
    DIRECCION, ID_LOCALIDAD) VALUES (%s, %s, %s, 5)", (apellidos, 
    nombres, direccion))

    mysql.connection.commit()

    return 'Jugador Insertado Exitosamente...'