How to pretty print output for SqlAlChemy?

user_13004418 0
Viewed 1213

This is my code for SqlAlChemy after linking it to the database. I am trying to print the top IP address for the year 2017. However, i could not pretty print my results. 

from sqlalchemy import select, func, and_, desc
import math

Top2017 = session.query(Log.IP,func.count(Log.IP).label('count')).filter(and_(Log.date >= '2017-1-1',Log.date <= '2017-12-31')).group_by(Log.IP).order_by(desc('count'))

print("The Top IP Address is   ")
print("Year" , "IP Address")
for q in Top2017.limit(3):
    print("2017", q)

My output is:

The Top IP Address is   
Year IP Address
2020-03-06 10:21:03,811 INFO sqlalchemy.engine.base.Engine SELECT logfile."IP" AS "logfile_IP", count(logfile."IP") AS count 
FROM logfile 
WHERE logfile.date >= ? AND logfile.date <= ? GROUP BY logfile."IP" ORDER BY count DESC
 LIMIT ? OFFSET ?
2020-03-06 10:21:03,812 INFO sqlalchemy.engine.base.Engine ('2017-1-1', '2017-12-31', 3, 0)
2017 ('10.131.0.1', 1921)
2017 ('10.130.2.1', 1805)
2017 ('10.128.2.1', 1667)

The output is very messy as the result is not very clear with all the queries printed. Hence, my desired output will be:

The Top IP Address is 

Year IP Address Count 
2017 10.131.0.1 1921
2017 10.130.2.1 1805
2017 10.128.2.1 1667

Thank you for your help.

This is the output for dict. 

The Top IP Address is   
Year IP Address Count
OrderedDict([('10.131.0.1', 1921), ('10.130.2.1', 1805), ('10.128.2.1', 1667)])
sqlalchemy printing sql python
2 Answers

You can do like this:

from collections import OrderedDict

Top2017 = (
    session.query(Log.IP, func.count(Log.IP).label("count"))
    .filter(and_(Log.date >= "2017-1-1", Log.date <= "2017-12-31"))
    .group_by(Log.IP)
    .order_by(desc("count"))
    .limit(3)
)

dict = OrderedDict(Top2017)

Now, you have the query result in a dictionary and can print the desired output from it like this:

print("The Top IP Address is   ")
print("Year" , "IP Address", "Count")
for key, value in dict.items():
    print("2017", key, value)

Do let me know if you need more explanation.

user_79940740

If you want to print result as a table, use tabulate package.

pip install tabulate

from tabulate import tabulate

q = engine.execute('SHOW DATABASES;')
result = q.fetchall()
print(tabulate(result, headers=q.keys(), tablefmt='psql'))
user_117950040
Related