CODEWITHSUNDEEP
pythonsqlpython-3.x
Anastasia Quart
Anastasia Quart

Reputation: 33

How to apply multiple SQL query using Python?

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])

Upvotes: 0

Views: 71

Answers (1)

flaschbier
flaschbier

Reputation: 4177

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.

Upvotes: 1

Related Questions