Create multiple strings inside a string using python

Question

I am new to using python to write sql strings and was wondering if someone could help me. So currently I am writing a sql statement like this,

 sql_statement = """SELECT * from some_database WHERE FirstName IN (%(first_names)s)"""
 first_names = ['fn1', 'fn2', 'fn3']

And I want the string to end up like this SELECT * from some_database WHERE FirstName IN ('fn1', 'fn2', 'fn3'), where each element in the list becomes its own string in the sqlstatement. Is this possible in Python?

• coldspeed95: If you are calling cur.execute, try: cur.execute("""SELECT * from some_database WHERE FirstName IN (?)""", first_names)

Answer 1

You can use str.format to accomplish this:

>>> sql_statement = """SELECT * from some_database WHERE FirstName IN {}"""
>>> first_names = ['fn1', 'fn2', 'fn3']
>>> 
>>> sql_statement.format(repr(tuple(first_names)))
"SELECT * from some_database WHERE FirstName IN ('fn1', 'fn2', 'fn3')"
>>> 

Caveat: While this is fine as just a plain string, be very careful using this as a SQL statement due to SQL injection. A better idea would most likely be to cursor.execute instead or the equivalent in your SQL API library.

• coldspeed95: This is fine on its own but remember SQL injection is always a problem when dealing with SQL statements.
• Chris: @cᴏʟᴅsᴘᴇᴇᴅ Yes, that is a valid point and I'm aware of that fact. I was simply providing what the OP asked for.
• Jugurtha Hadjar: @ChristianDean: May I suggest adding a caveat to your reply given the nature of the question (not just "any" string substitution, it's a SQL query)? A lot of people do Stack Overflow Driven Development (implement first answer on SO that "works"). Your answer will probably be accepted because it will work and then others will search for something similar, see your reply, and then go with it not bothering to read the comments and, worst, the docs. It may be tempting to consider their fate as natural selection. I would've been dead were it not for charitable souls.
• Chris: @JugurthaHadjar Alright, you've convinced me. I'll add one.
• Jugurtha Hadjar: Thanks.. Sorry to bother you again: the caveat isn't in using cursor.execute to avoid SQL injection (we need to execute the query to have an injection in the first place), it's about handling parameters properly. For further information, see PEP249 (Python Database Specification v2.0) One attribute to pay attention to is paramstyle. Whether you use sqlite3 or psycopg2, etc: sqlite3.paramstyle returns 'qmark' and psycopg2.paramstyle returns 'pyformat'.