Adding SQL Queries in For Loop in Python

Question

Is there a way to add SQL queries in a For Loop in python where the table names and database names are variable? Something like this:

database = []
tables= []
column = []

for x in database: 
    for y in tables:
        for z in column:
            SQL = "select * from x.y where z is NOT NULL;"
            cursor.execute(sql)`enter code here`

Yes, this should be possible, at least in theory. What is your actual question? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 18, 2019 at 6:11
...although this kind of problem can be symptomatic of poorly designed databases/tables/columns — Strawberry
– Strawberry, Commented Feb 18, 2019 at 6:12
Yes its possible. For examples, Check out this python sqlite documentation. docs.python.org/2/library/sqlite3.html — Jim Todd
– Jim Todd, Commented Feb 18, 2019 at 6:20

Lich · Accepted Answer · 2019-02-18 09:07:13Z

3

Just use string formatting. In your example:

database = []
tables= []
column = []

for x in database: 
    for y in tables:
        for z in column:
            SQL = "select * from {x}.{y} where {z} is NOT NULL;".format(x=x, y=y, z=z)
            cursor.execute(sql)

It's a single example of python string formatting, but you either can use string concatenation, % formatting or f-strings.

answered Feb 18, 2019 at 9:07

Lich

4912 silver badges12 bronze badges

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

Comments

Novak · Accepted Answer · 2019-02-18 07:36:07Z

3

Just use .format() method of string object to get the sql query string:

SQL = "select * from {}.{} where {} is NOT NULL;".format(x, y, z)

Or append values like this:

SQL = "select * from " + str(x) + "." + str(y) + " where " + str(z) + " is NOT NULL;"

I recommend the first solution.

answered Feb 18, 2019 at 7:36

Novak

2,1611 gold badge13 silver badges22 bronze badges

1 Comment

John S Over a year ago

Both methods worked great especially the first one, thank you!

Collectives™ on Stack Overflow

Adding SQL Queries in For Loop in Python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related