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)
)
Improve this question
10
  • 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 instead of string formatting. For example, cursor.execute('INSERT INTO activity_analyzed VALUES (%s)', columns) Commented Sep 26, 2015 at 16:41
  • Also see: How do I use SQL parameters with python? Commented Sep 26, 2015 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". Commented Sep 26, 2015 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) and a dictionary instead of a list, and a an approach similar to this to build the column names and value placeholder strings. Commented Sep 26, 2015 at 17:04
  • Correction: (%s, %s, %s, %s) (4 placeholders). Apparently, I'm really bad at counting arguments. Commented Sep 26, 2015 at 17:10
Related questions
1
Python string formating using values from multiple lists
1
String formatting with a list - more Pythonic solution to String formatting
2
Calling format function with a list
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.