How to apply multiple SQL query using Python?

Question

I need to apply more than one query to my SQL database. Every time I need to fetch all to get the result but I feel it's boring and not smart.

Is there any gentle way to shore the code and apply the following?

cursor = mydb.cursor()

updated_query = cursor.execute(
    'select count(*) from event where update_count > 0 and date > now() - interval 24 hour')
result = cursor.fetchall()
for x in result:
    print('Updated records in the last 24 hours:', x[0])

deleted_query = cursor.execute(
    'select count(*) from event where deleted > 0 and date > now() - interval 24 hour')
result = cursor.fetchall()
for x in result:
    print('Deleted records in the last 24 hours:', x[0])

total_last_24_hours = cursor.execute(
    'select count(*) from event where date > now() - interval 24 hour')
result = cursor.fetchall()
for x in result:
    print('Total records in the last 24 hours:', x[0])

total_events = cursor.execute('select count(*) from event')
result = cursor.fetchall()
for x in result:
    print('Total records:', x[0])

I've removed the conflicting RDBMS tags; if your question is about one of those, then edit your question and (re)tag the appropriate one. — Thom A
– Thom A ♦, Commented Sep 6, 2022 at 11:10

flaschbier · Accepted Answer · 2022-09-06 11:19:42Z

Using the SQL from the question, you can fire something like this to the database

select 'Updated records in the last 24 hours' as txt, count(*) as cnt from event where update_count > 0 and date > now() - interval 24 hour
union
select 'Deleted records in the last 24 hours:' as txt, count(*) as cnt from event where deleted > 0 and date > now() - interval 24 hour
union
...

and then

result = cursor.fetchall()
for x in result:
    print(f"{x[0]}: {x[1]}")

The trick is that the result sets of the union produce the same columns and, thus, can be processed in one. Neat approach also for SQL consoles when you have quite some things to look for at the same time and want your result condensed in one tabular output.

Collectives™ on Stack Overflow

How to apply multiple SQL query using Python?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related