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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt   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  lt  5

User · Answer

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

[python] MySQL parameterized queries

Incorrect (with security issues)

Correct (with escaping)

Examples related to python

Examples related to mysql

Examples related to bind-variables