Python MySQLdb passing a list into an insert statement

Question

I am trying to pass some values from a list into an insert statement and thought it would be straightforward enough but I find myself wasting far too much time on this:

please could someone explain what's going wrong here?

WORKS WITH HARDCODED LIST of values

cursor=db.cursor()
cursor.execute(" INSERT INTO a (a,b) VALUES (%s,%s)", ('abc', 'abc'))
db.commit()
cursor.close()

DOESN'T WORK with list of values as a variable (output_insert)

output = ('abc','abc')
output_insert = str(output)
cursor=db.cursor()
sql = '" INSERT INTO a (a,b) VALUES (%s,%s)",' +  output_insert
cursor.execute(sql)
db.commit()
cursor.close()

if I print the sencond statement, I can see that it is identical to the first that fails:

print sql

" INSERT INTO a (a,b) VALUES (%s,%s)",('abc', 'abc')

same as:

" INSERT INTO a (a,b) VALUES (%s,%s)", ('abc', 'abc')

The error returned is:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_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 \'" INSERT INTO a (a,b) VALUES (%s,%s)",(\'abc\', \'abc\')\' at line 1')

Thanks!

Answer 1

Don't use string interpolation or string formatting to create SQL queries. You are making your code vulnerable to SQL injection attacks and getting type conversion and quote-handling problems (as you can already see).

Instead, parameterize your query. The query parameters should be passed as a separate argument to execute():

output = ('abc','abc')
sql = """
    INSERT INTO 
        a 
        (a, b) 
    VALUES 
        (%s, %s)"""
cursor.execute(sql, output)

Answer 2

If you print the sql you give to mysql:

" INSERT INTO a (a,b) VALUES (%s,%s)",('abc', 'abc')

It's not a right sql for MySQL, so you got error.

Or You can use string format method to format sql, example as:

cursor=db.cursor()
vars_sql = ('abc', 'abc')
sql_format = "INSERT INTO a (a,b) VALUES ('{0}','{1}')"
real_sql = sql_format.format(*vars_sql)
# print out see if sql is correct
print real_sql
# not use format string avoid dangerous
cursor.execute("INSERT INTO a (a,b) VALUES ('%s','%s')", ('abc', 'abc'))
db.commit()
cursor.close()

in this way, you can print out the real sql and execute in MySQL to see if anything wrong with the sql.