I am running an SQL Oracle query through Python with cx.Oracle. However, the execution time is very long and I would like to have some progress bar (either with a live counter or a visual progress bar) that shows the run rate of the query.
I have found that this is possible with the tqdm library, but I am unsure how to integrate it in my cx.Oracle query.
This is how I run the queries now, which only displays start time, end time and run time after the query has completed, not during which is what I want.
q2 = """SELECT col1, col2
FROM TABLE1
WHERE col3 = (to_date('2024-03-31 00:20:00', 'yyyy-mm-dd HH24:MI:SS'))
AND
col2 >= (to_date('2024-03-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS'))"""
start_time = datetime.datetime.today()
print("Started at", start_time.strftime("%H:%M:%S"))
# Put it all into a data frame
b = pd.DataFrame(cursor.execute(q2).fetchall())
b.columns = [i[0] for i in cursor.description]
end_time = datetime.datetime.today()
elapsed_time = end_time - start_time
print("Ended in", elapsed_time)
print("Fetched", len(b), "rows...")
The time it takes to execute the SQL query depends on your D.B. and it can't be tracked with a progress bar. 😬
The progress bar will update as each row is fetched from the cursor, not during the execution of the SQL query itself. 🤓
This is your code with a progress bar:
start_time = datetime.datetime.today()
print("Started at", start_time.strftime("%H:%M:%S"))
cursor.execute(q2)
row = cursor.fetchone()
pbar = tqdm.tqdm(total=cursor.rowcount)
data = []
while row is not None:
data.append(row)
row = cursor.fetchone()
pbar.update()
b = pd.DataFrame(data)
b.columns = [i[0] for i in cursor.description]
end_time = datetime.datetime.today()
elapsed_time = end_time - start_time
print("Ended in", elapsed_time)
print("Fetched", len(b), "rows...")
pbar.close()