MySQL parameterized queries

Question

I find it difficult to use the MySQLdb module to insert information into my database. I need to insert 6 variables into a 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?

Answer 1

Beware of using string interpolation for SQL queries, as it will not correctly remove input parameters and will leave your application open for SQL injection vulnerabilities. The difference may seem trivial, but in fact it is huge .

Wrong (with security issues)

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

Fix (with escaping)

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

This adds to the confusion that the modifiers used to bind parameters in an SQL expression differ between different database API implementations and that the mysql client library uses printf syntax instead of the more common '?' marker (used, for example, python-sqlite ).

Answer 2

You have several options. You will want to enjoy pernon string iterpolation. What term can you have more success in the future when you want to know such things.

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) 

Given that you probably already have all of your data in an object or dictionary, the second format is best for you. Also, it sucks so that the count "% s" appears on the line when you should return and update this method in a year :)

Answer 3

Related documents provide 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 4

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

To execute 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 use% s in a row? Because MySQLdb converts it to an SQL literal value, which is the string "5". When it finishes, the request will truly say: "... WHERE price <5".

Answer 5

As an alternative to the chosen answer and with Marcel's same safe semantics, here is a compact way to use the Python dictionary to specify values. This is useful for ease of editing when adding or removing 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 a dictionary containing the values ​​to insert. The update can be performed as follows:

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

Answer 6

Here is another way to do this. This is documented on the official MySQL site. https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

In the spirit, he uses the same @Trey Stout answer mechanics. Nevertheless, I believe that it is more beautiful 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 that the escape is 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) 

Answer 7

The first solution works well. I want to add one small detail here. Make sure that the variable you are trying to replace / update must be of type str. My MySQL type is decimal, but I had to make the parameter variable like str in order to be able to fulfill the query.

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