Python mysql with variables

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

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

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

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