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?

Answer 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: