Use a Python f-string to build an SQL query 'IN' clause

Question

I'm trying to use an f-string in Python to build my SQL query.

The query below seems fine:

code = ('123', '234')
print(f"select * from tbl where code in {code} ")

Output:

"select * from tbl where code in ('123', '234') "

However, if there is only one element in the tuple, it does not work:

code = ('123')
print(f"select * from tbl where code in {code} ")

Output:

select * from tbl where code in 123

Why does that happen? What's the best way to solve the issue? I can use a condition to check the tuple length, but I feel like there should be a better way...

Answer 1

('123') is not a tuple. Python interprets it simply as a string, '123'. In order to get a tuple with a single element you need to add a comma after the element: ('123',). Then the code

code = ('123',)
print( f"select * from tbl where code in {code} ")

gives:

select * from tbl where code in ('123',)

However, this is still not good since now the comma appears in the query. This can be fixed with some more formatting:

code = ('123',)
scode = ", ".join(f"'{s}'" for s in code)
print( f"select * from tbl where code in ({scode}) ")

This gives:

select * from tbl where code in ('123')

This will work for tuples of any size. For example,

code = ('123', '456')
scode = ", ".join(f"'{s}'" for s in code)
print( f"select * from tbl where code in ({scode}) ")

gives:

select * from tbl where code in ('123', '456')

If the additional comma in a tuple with a single element seems easy to miss, you can use the above code but with lists instead of tuples. So: code = ['123'] or code = ['123', '456'].

Answer 2

I normally do it to inject parameters and let PyMySQL, or whatever you use, inject the values.

code = ('123', '234')

# The below will produce a string %s,%s
format_strings = ','.join(['%s'] * len(code))

# The query will end up being:
# select * from tbl where code in(%s,%s)
sql_query = f"select * from tbl where code in({format_strings})"
with connection.cursor() as cursor:
    cursor.execute(sql_query, code)

For the second, as bb1 mentioned, it's not technically a tuple. You'll need to append a comma after the last element.

code = ('123',)

# The below will produce a string %s,%s
format_strings = ','.join(['%s'] * len(code))

# The query will end up being:
# select * from tbl where code in(%s)
sql_query = f"select * from tbl where code in({format_strings})"
with connection.cursor() as cursor:
    cursor.execute(sql_query, code)