3

I can't get ? instead of %s to work on an insert with python mysqldb.

I try 

cur.execute("""INSERT INTO project (project) VALUES (?) """, ('ham'))

but I get

query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

works fine if I simply substitute ? with %s

What am I doing wrong?

user1170304
  • 315
  • 1
  • 3
  • 12

1 Answers1

5

You can't. The mysqldb adapter only supports the format and pyformat parameter styles.

The Python DB API 2.0 specification supports 5 different paramstyle parameter styles but it is up to the exact database adapter implementation what they support. The mysqldb project documentation tells you what styles are supported by that adapter, and qmark is not one of them.

If you are trying to write SQL suitable for multiple database adapters, you'll most likely have to deal with more issues than just the parameter style. Consider using SQLAlchemy instead, which can generate SQL for you that is more database agnostic (up to a point).

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • Is there any way of automatically sanitizing the input like with ? for the python mysqldb? Or just python in general? – user1170304 Jan 22 '14 at 11:38
  • @user1170304: Why do you ask? Are you trying to write SQL suitable for different database adapters? – Martijn Pieters Jan 22 '14 at 11:40
  • @user1170304: You *can* run a `.replace('?', '%s')` on your SQL string, but if there are any `?` characters in the query that are *not* placeholders, you'll have a harder parsing job on your hand. – Martijn Pieters Jan 22 '14 at 11:44
  • 1
    The `%s` parameter style will sanitize the input just like the `?` in the call - provided, of course, that you pass your SQL parameters as parameters to the `execute` method: do not pre-format the string with the `%s` with the `%` operator before making the method call. – jsbueno Jan 22 '14 at 11:46
  • @jsbueno: The OP is not doing that; the exception comes from the MySQLdb adapter, which *internally* implements the parameter handling using string formatting. – Martijn Pieters Jan 22 '14 at 11:48
  • I just want to avoid errors when inputting something like 'don't' @jsbueno I'm not sure what you mean by "that you pass your SQL parameters as parameters to the execute method" – user1170304 Jan 22 '14 at 12:49
  • 1
    @user1170304: Then why not just use `%s`? Those are SQL parameters. Don't be fooled by the fact that the syntax is the same as string interpolation in Python; it's not the same functionality here. – Martijn Pieters Jan 22 '14 at 12:51
  • I have used %s but I get an error when i try and input something like 'don't' because it isn't sanitizing the input. – user1170304 Jan 22 '14 at 12:52
  • 2
    @user1170304: Use them as SQL parameters; `cur.execute("""INSERT INTO project (project) VALUES (%s) """, ('ham',))` will work just fine. – Martijn Pieters Jan 22 '14 at 12:52
  • 1
    @user1170304: Note that that doesn't use `execute(sql % values)`; it uses `execute(sql, values)`. – Martijn Pieters Jan 22 '14 at 12:53
  • @user1170304: The only difference is the *style* of the SQL parameter placeholder. The meaning is *exactly the same*. – Martijn Pieters Jan 22 '14 at 12:53
  • yes it will but something like 'don't' which is a value taken from a form will fail becuase it is not sanitized. I want to input values that are automatically sanitized. Is this possible? – user1170304 Jan 22 '14 at 12:53
  • 1
    @user1170304: That is what SQL parameters **do**, they sanitize the input and quote it correctly. MySQLdb uses SQL parameters in exactly that fashion, provided you use them correctly. – Martijn Pieters Jan 22 '14 at 12:55
  • 1
    @user1170304: You didn't share what you tried. It works just fine when used correctly. – Martijn Pieters Jan 22 '14 at 12:58
  • sql = """ INSERT INTO conditions (conditions) VALUES %s """ cur.execute(sql, (string)) where string = 'don't' – user1170304 Jan 22 '14 at 13:06
  • 1
    @user1170304: There are two errors there; you need parenthesis: `VALUES (%s)` and you need to make the second parameter a *tuple* (requiring a comma). – Martijn Pieters Jan 22 '14 at 13:08
  • @user1170304: `sql = """INSERT INTO conditions (conditions) VALUES (%s)"""`, then `cur.execute(sql, (string,))`. – Martijn Pieters Jan 22 '14 at 13:08
  • @user1170304: For the latter problem, see [sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied](http://stackoverflow.com/q/16856647) – Martijn Pieters Jan 22 '14 at 13:09
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/45804/discussion-between-user1170304-and-martijn-pieters) – user1170304 Jan 22 '14 at 13:25