Can tqdm be used with Database Reads?

Question

While reading large relations from a SQL database to a pandas dataframe, it would be nice to have a progress bar, because the number of tuples is known statically and the I/O rate could be estimated. It looks like the tqdm module has a function tqdm_pandas which will report progress on mapping functions over columns, but by default calling it does not have the effect of reporting progress on I/O like this. Is it possible to use tqdm to make a progress bar on a call to pd.read_sql?

Answer 1

Edit: Answer is misleading - chunksize has no effect on database side of the operation. See comments below.

You could use the chunksize parameter to do something like this:

chunks = pd.read_sql('SELECT * FROM table', con=conn, chunksize=100)

df = pd.DataFrame()
for chunk in tqdm(chunks):
    df = pd.concat([df, chunk])

I think this would use less memory as well.

Answer 2

I wanted to provide an alternative answer, given the previous solutions really only provide status to the concat operations. There is still a bit of "best guess" work, but think it's a bit closer to a viable solution.

Summary

The general concept is to check the status of a function execution every second and populate a status bar up to an arbitrarily defined max number of seconds. YMMV on these values. (If the execution takes longer than the total defined, it simply removes the status bar)

• Create a manual tqdm progress bar and adjust the settings as you see fit.
• Use asyncio.create_task to spawn a task. Wrap the pandas read method in asyncio.to_thread to create as a Coroutine.
• Check the task status in a while loop, updating the progress bar if the the execution is not done - or returning the results if complete.

async def sql_handler(query, conn):
    progress = tqdm(desc="Executing SQL Query",total=8)
    task = asyncio.create_task(
        asyncio.to_thread(pd.read_sql, sql=query, con=connection)
    )

    while True:
        status = task.done()
        progress.update()
        if status:
            return task.result()
        await asyncio.sleep(1)

async def main():
    await self.sql_handler(query=..., conn=...)

if __name__ == "__main__":
    asyncio.run(main())

Full Example

Here's a more complete example using sqlalchemy to read from a snowflake database and including some settings to help get the desired output

import asyncio
import pandas as pd
from pandas import DataFrame
from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
from sqlalchemy.engine import Engine
from tqdm import tqdm

async def sql_handler(query: str, connection: Engine) -> DataFrame:
    progress = tqdm(
        desc="Executing SQL Query",
        ncols=100,
        bar_format="{desc}: {bar}[{elapsed}]",
        total=8,
        )
    task = asyncio.create_task(
        asyncio.to_thread(pd.read_sql, sql=query, con=connection)
    )
    while True:
        status = task.done()
        progress.update()
        if status:
            return task.result()
        # With progress.total = 8 -> Check status every 1 second for 8 seconds
        await asyncio.sleep(1)

async def main() -> None:
    conn = create_engine(
        URL(account=...,
            user=...,
            password=...,
            warehouse=...,
            database=...,
            schema=...,
        )
    )
    query = "SELECT * FROM table"
    results_data_frame = await self.sql_handler(query, conn)
    print(results_data_frame.to_dict(orient="records")

if __name__ == "__main__":
    asyncio.run(main())

Output

Executing SQL Query: █████████████████████████████████████████      [00:03]

Answer 3

yes! you can!

expanding the answer here, and Alex answer, to include tqdm, we get:

# get total number or rows
q = f"SELECT COUNT(*) FROM table"
total_rows = pd.read_sql_query(q, conn).values[0, 0]
# note that COUNT implementation should not download the whole table. 
# some engine will prefer you to use SELECT MAX(ROWID) or whatever...

# read table with tqdm status bar
q = f"SELECT * FROM table"
rows_in_chunk = 1_000
chunks = pd.read_sql_query(q, conn, chunksize=rows_in_chunk)
df = tqdm(chunks, total=total_rows/rows_in_chunk)
df = pd.concat(df)

output example:

39%|███▉      | 99/254.787 [01:40<02:09,  1.20it/s]