MySQL parameterized queries

Question

I am having a hard time using the MySQLdb module to insert information into my database. I need to insert 6 variables into the table.

cursor.execute ("""
    INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
    VALUES
        (var1, var2, var3, var4, var5, var6)

""")

Can someone help me with the syntax here?

Idiom

Answer 1

Beware of using string interpolation for SQL queries, since it won't escape the input parameters correctly and will leave your application open to SQL injection vulnerabilities. The difference might seem trivial, but in reality it's huge.

Incorrect (with security issues)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s" % (param1, param2))

Correct (with escaping)

c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2))

It adds to the confusion that the modifiers used to bind parameters in a SQL statement varies between different DB API implementations and that the mysql client library uses printf style syntax instead of the more commonly accepted '?' marker (used by eg. python-sqlite).

Territory

@Idiom IMO it makes sense to always stay with right and secure implementation ways. It creates right habits and a good culture of programming. Also nobody knows how you code will be used in the future; somebody can use it later for other system or website. – Alex

@BryanHunt You can turn on the use of ? with an argument somewhere, but it is discouraged because it doesn't tell you much about which argument goes where. (The same could be said of %s, of course, which is discouraged for the same reason.) More information here: python.org/dev/peps/pep-0249/#paramstyle – Stickybeak

Coming from php/pdo I was mega-confused if the printf style %s marker, and I was sorta terrified that I was writing vulnerable queries. Thanks for clearing that worry up! :) – Whiz

Do you have any docs that go through the difference between % and ,? – Jackass

@Jackass The % operator puts variables into a string, this is general python code. The , simply separates arguments in a function/method call, so your variable list becomes a second argument. This execute method apparently has an optional second argument. – Turpitude

When it's a single parameter remember to keep the comma: c.execute("SELECT * FROM foo WHERE bar = %s", (param1,)) – Reinforce

For context on the parameters in the cursor execute (and why %s is still needed), the api reference for the MySQLdb cursor is at mysql-python.sourceforge.net/MySQLdb-1.2.2/public/… – Rinse

If I am not wrong this is also the way we insert into string of logger functions like info, error etc.... – Varipapa

Answer 2

You have a few options available. You'll want to get comfortable with python's string iterpolation. Which is a term you might have more success searching for in the future when you want to know stuff like this.

Better for queries:

some_dictionary_with_the_data = {
    'name': 'awesome song',
    'artist': 'some band',
    etc...
}
cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%(name)s, %(artist)s, %(album)s, %(genre)s, %(length)s, %(location)s)

        """, some_dictionary_with_the_data)

Considering you probably have all of your data in an object or dictionary already, the second format will suit you better. Also it sucks to have to count "%s" appearances in a string when you have to come back and update this method in a year :)

Mazza

Looks like the dictionary approach works better in case a given bind variable has to be used in more than one place in the SQL statement. With the positional approach, we need to pass the variable as many times as it is referenced, which is not very desirable. – Carob

Answer 3

The linked docs give the following example:

   cursor.execute ("""
         UPDATE animal SET name = %s
         WHERE name = %s
       """, ("snake", "turtle"))
   print "Number of rows updated: %d" % cursor.rowcount

So you just need to adapt this to your own code - example:

cursor.execute ("""
            INSERT INTO Songs (SongName, SongArtist, SongAlbum, SongGenre, SongLength, SongLocation)
            VALUES
                (%s, %s, %s, %s, %s, %s)

        """, (var1, var2, var3, var4, var5, var6))

(If SongLength is numeric, you may need to use %d instead of %s).

Pangaro

will this work where the var1 and var2 have charecters like " or ' . – Pigeonwing

AFAIK you have to use %s there no matter which type. @sheki: Yes – Geez

Answer 4

Actually, even if your variable (SongLength) is numeric, you will still have to format it with %s in order to bind the parameter correctly. If you try to use %d, you will get an error. Here's a small excerpt from this link http://mysql-python.sourceforge.net/MySQLdb.html:

To perform a query, you first need a cursor, and then you can execute queries on it:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".

Dynamometer

Yeah this is a strange one alright ... you think that "printf" format would mean... actually printf format and not just using %s everywhere. – Chaparajos

Answer 5

As an alternative to the chosen answer, and with the same safe semantics of Marcel's, here is a compact way of using a Python dictionary to specify the values. It has the benefit of being easy to modify as you add or remove columns to insert:

  meta_cols = ('SongName','SongArtist','SongAlbum','SongGenre')
  insert = 'insert into Songs ({0}) values ({1})'.format(
      ','.join(meta_cols), ','.join( ['%s']*len(meta_cols)))
  args = [ meta[i] for i in meta_cols ]
  cursor = db.cursor()
  cursor.execute(insert,args)
  db.commit()

Where meta is the dictionary holding the values to insert. Update can be done in the same way:

  meta_cols = ('SongName','SongArtist','SongAlbum','SongGenre')
  update='update Songs set {0} where id=%s'.
        .format(','.join([ '{0}=%s'.format(c) for c in meta_cols ]))
  args = [ meta[i] for i in meta_cols ]
  args.append(songid)
  cursor=db.cursor()
  cursor.execute(update,args)
  db.commit()

Sucrose

I am impressed ... you managed to make python code unreadable! – Underworld

Answer 6

The first solution works well. I want to add one small detail here. Make sure the variable you are trying to replace/update it will has to be a type str. My mysql type is decimal but I had to make the parameter variable as str to be able to execute the query.

temp = "100"
myCursor.execute("UPDATE testDB.UPS SET netAmount = %s WHERE auditSysNum = '42452'",(temp,))
myCursor.execute(var)

Selfrealization

Answer 7

Here is another way to do it. It's documented on the MySQL official website. https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

In the spirit, it's using the same mechanic of @Trey Stout's answer. However, I find this one prettier and more readable.

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

And to better illustrate any need for variables:

NB: note the escape being done.

employee_id = 2
first_name = "Jane"
last_name = "Doe"

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (employee_id, conn.escape_string(first_name), conn.escape_string(last_name), datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

Obey