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

Can you use single quotes around table names? I've only used backticks. https://dev.mysql.com/doc/refman/5.0/en/identifiers.html (ctrl+f "identifier quote character") — David Ehrmann, Jun 19 '15 at 19:22

score 0 · Answer 1 · edited May 23 '17 at 12:22

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.

unfortunately removing the single quotes didnt do the trick. I'll look into the alternatives to SQL injection though, thanks — Blythe Simmons, Jun 19 '15 at 19:32

Jmills · Answer 2 · 2015-06-19T20:25:05.567

0

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

edited Jun 19 '15 at 20:25

answered Jun 19 '15 at 20:19

Jmills

2,414
2
19
22

I tried this 2 step method, and I got a TypeError, not sure whats going on. I included this in the question update – Blythe Simmons Jun 19 '15 at 20:36
@BlytheSimmons What is in "dbMap[key]"? Are any of the contents tuples? You have a single %s to replace, but if you pass it more than one value to replace via a tuple with a length greater than 1, you will see "TypeError: not all arguments converted during string formatting" – Jmills Jun 19 '15 at 20:46
I returned the type of each value, and it returned: – Blythe Simmons Jun 19 '15 at 20:52
I used str(key) and str(dbMap[key]) and still got a type error – Blythe Simmons Jun 19 '15 at 20:55

score 0 · Accepted Answer · edited May 23 '17 at 11:58

0

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

Python and MySQLdb: substitution of table resulting in syntax error

edited May 23 '17 at 11:58

Community

1
1

answered Jun 22 '15 at 15:26

Blythe Simmons

143
3
10

score -1 · Answer 4 · answered Dec 15 '21 at 22:20

-1

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...'

answered Dec 15 '21 at 22:20

Marcelo Alvarez

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 15 '21 at 23:01

Python MySQLdb "INSERT INTO" Issue

4 Answers4