Python mysql with variables

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?

Thanks guys, if figured out my other problem, db.commit().....

Some info on it here

Answer 1

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

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 :)

Answer 3

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:

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 for example python-sqlite).