SQL in python to include a where clause-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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'