SQL in python to include a where clause

Question

Here are the SQL code as string in python:

sql_code="""
SELECT VAR
    VAR2
FROM TABLE
WHERE VAR in ('A','B')
"""

And I would like to create a variable for the list of selection in the WHERE clause, this is what we can do:

sql_code_arg1="""
SELECT VAR
VAR2
FROM TABLE
WHERE VAR in {}
"""

Then

lst=["A","B"]
print(sql_code_arg1.format(tuple(lst)))

Now, I would like to parameter the entire condition in WHERE clause:

sql_code_arg2="""
SELECT VAR
VAR2
FROM TABLE
WHERE {}
"""

I tried someting like this:

print(sql_code_arg2.format("VAR in "+tuple(list)))

But it doesn't work.

Does this answer your question? Using variables in the format() function in Python — Jim G.
– Jim G., Commented Jan 6, 2023 at 15:23

Talha Tayyab · Accepted Answer · 2023-01-06 15:26:09Z

2

You were trying to add a string with a tuple.

format("VAR in "+tuple(list)))

Rather, this should work:

sql_code_arg2.format("VAR in "+str(tuple(lst)))

Disclaimer: This method is not recommended because it allows SQL injection

edited Jan 6, 2023 at 15:26

answered Jan 6, 2023 at 15:20

Talha Tayyab

30.6k54 gold badges139 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ljmc Over a year ago

Please do not recommend methods that allow SQL injection.

Talha Tayyab Over a year ago

@Ijmc yes you are right!

Yasha Akimov · Accepted Answer · 2023-01-06 15:44:14Z

Avoid generating sql queries from raw string. its not flexible, unsafe and unmaintainable. For generating sql queries use library https://github.com/kayak/pypika

With this library you can generate sql queries from objects like

customers = Table('customers')
q = Query.from_(customers).select(
    customers.id, customers.fname, customers.lname, customers.phone
).where(
    customers.fname == 'Max'
).where(
    customers.lname == 'Mustermann'
)

and when you need sql string you just cast query to string and get this

SELECT id,fname,lname,phone FROM customers WHERE fname='Max' AND lname='Mustermann'

Collectives™ on Stack Overflow

SQL in python to include a where clause

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related