Using while loop with SQL Query and Pandas

Question

I am running a SQL query in python and storing the results of the table in a Pandas DataFrame. I'd like run the query until a condition in met. Here's some sample data to work with:

sql table1

name      val       
post      10
sutter    15
oak       20

import pandas as pd
# mysql connection
import pymysql
from sqlalchemy import create_engine
user = 'user1'
pwd = 'pwd'
host =  'xxxx.1.rds.amazonaws.com'
port = 3306
database = 'db1'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))


# Readdata
con = engine.connect()

v = 12

query = '''
        SELECT *
        FROM table1
        WHERE val < {}
        '''.format(v)

df = pd.read_sql(query, con)

I'd like to run this query until a condition is met. Condition is the size of the dataframe or number of rows returned. Above query returns 1 row. I need the query to update the value of v until the condition is satisfied. So, a while-loop would work.

While df.shape[0] => 2:

   run query

How do I run the query in a while-loop and check of # of rows returned or size of the dataframe?

If the condition is simply the # of rows returned then there's absolutely no need for the while condition. ORDER BY VAL and then SELECT TOP N rows. Or you can modify that slightly to return more rows if you want all rows tied for the val that sets you over the N threshold — ALollz
– ALollz, Commented Apr 12, 2022 at 15:48
The condition is # of rows, but there are a couple of caveats and while-loop would be ideal implementation. I have simplified for SO post. Could you recommend how I can do this within a while loop? — kms
– kms, Commented Apr 12, 2022 at 16:07
if you really need loop then maybe use while True:, next get data, next use if to check number of rows and use break to exit loop, and use v += 1 to run loop with bigger value. — furas
– furas, Commented Apr 13, 2022 at 9:14

furas · Accepted Answer · 2022-04-13 09:16:42Z

1

If you really need to do it with loop then maybe use while True:, next get data, next use if to check number of rows and use break to exit loop, and use v += 1 to run loop with bigger value

Something like

v = 12

query = '''
        SELECT *
        FROM table1
        WHERE val < {}
        '''

while True:

    df = pd.read_sql(query.format(v), con)

    if df.shape[0] >= 2:
        break

    v += 1

but if there is only one row in database then it may work forever and it may need other condition to stop it. ie. while v < 10000:

answered Apr 13, 2022 at 9:16

furas

149k12 gold badges121 silver badges171 bronze badges

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

2 Comments

kms Over a year ago

can you explain how while True works? Does it execute until it hits the break statement?

furas Over a year ago

while True is endless loop - it runs forever - and break can exit it. It is popular method when you have to run some function in loop before you check its result.

Collectives™ on Stack Overflow

Using while loop with SQL Query and Pandas

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related