1

I am trying to format a Python array as a string to be inserted into an SQL query. I want to be able to handle string and integer data but I cannot figure out how to to format the array. This is what I have right now

columns = ['owner_id', 23, 'activity', 'rest']
val_str = ', '.join("%s" % (v) for k, v in enumerate(columns))
print "INSERT INTO %s VALUES (%s)" % (table_name, val_str)

Results in:

INSERT INTO activity_analyzed VALUES (owner_id, 23, activity, rest)

I am trying to insert an if structure so that I can check the type of the variable and surround it with quotes if it is a string using typeof(). However I am new to Python and I cannot seem to get the syntax right so that it works and does not give me an error. Something like this would be ideal:

val_str = ', '.join(
    if type(v) is str:
        "'%s'" % (v)
    else if type(v) is int:
        "%s" % (v)
    for k, v in enumerate(columns)
)
Erik Berkun-Drevnig
  • 2,306
  • 23
  • 38
  • 1
    There's no `typeof` in Python. You probably meant `type`, but that's still the wrong approach for this problem. Use the [DBAPI to create parameterized SQL statements](https://www.python.org/dev/peps/pep-0249/#id16) instead of string formatting. For example, `cursor.execute('INSERT INTO activity_analyzed VALUES (%s)', columns)` – Lukas Graf Sep 26 '15 at 16:41
  • Also see: [How do I use SQL parameters with python?](http://stackoverflow.com/questions/3410455/how-do-i-use-sql-parameters-with-python) – Lukas Graf Sep 26 '15 at 16:46
  • Thanks, yep I meant `type`. That seems to be what I am after but when I use `cursor.execute('INSERT INTO daily_activity VALUES (%s)', columns)` I get "TypeError: not all arguments converted during string formatting". – Erik Berkun-Drevnig Sep 26 '15 at 16:58
  • Ah yes, that should have been `(%s, %s, %s)` - the number of placeholders needs to match the number of arguments. If you want a generic approach with a variable number of arguments, you should probably use named placeholders (see the available [`paramstyle` options](https://www.python.org/dev/peps/pep-0249/#paramstyle)) and a dictionary instead of a list, and a an approach similar to [this](http://stackoverflow.com/a/32751768/1599111) to build the column names and value placeholder strings. – Lukas Graf Sep 26 '15 at 17:04
  • Correction: `(%s, %s, %s, %s)` (4 placeholders). Apparently, I'm really bad at counting arguments. – Lukas Graf Sep 26 '15 at 17:10
  • Yeah unfortunately I need a generic method. I have already is actually a dictionary containing the values I need, so I set `paramstyle` doing `db.paramstyle = "named"` and then when I do `cursor.execute(...)` it will expect a dictionary? – Erik Berkun-Drevnig Sep 26 '15 at 17:14
  • No, you don't need to set `paramstyle` - that's just a documentation attribute to indicate what sort of parameter style your DBAPI conformant database driver supports. In order to use a particular parameter style you just have to use the [corresponding syntax](https://www.python.org/dev/peps/pep-0249/#paramstyle). So in order to use the named style, you'd use `INSERT INTO activity_analyzed (owner_id_col, ...) VALUES (:OWNER_ID_KEY, ...)`, where `owner_id_col` is the actual name of the column in the database schema, and `OWNER_ID_KEY` the name of the key in your dictionary that should be used. – Lukas Graf Sep 26 '15 at 17:20
  • (I just used different spellings to make the distinction clear - they may very well be named exactly the same). And yes, if you use the `named` (or `pyformat`) style, you just pass a dictionary as the second argument to `.execute(operation [, parameters ]))`. – Lukas Graf Sep 26 '15 at 17:22
  • And passing a dictionary will let me automatically use those values to insert? I cannot specify the keys for each one because those will change. How would I do that? It only shows how to shows how to use a single parameter here: https://www.python.org/dev/peps/pep-0249/#paramstyle – Erik Berkun-Drevnig Sep 26 '15 at 17:32
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/90693/discussion-between-lukas-graf-and-eberkund). – Lukas Graf Sep 26 '15 at 17:33

0 Answers0