Python Script to get multi table counts

Question

I'm trying to write a python script to get a count of some tables for monitoring which looks a bit like the code below. I'm trying to get an output such as below and have tried using python multi-dimensional arrays but not having any luck.

Expected Output:

('oltptransactions:', [(12L,)])
('oltpcases:', [(24L,)])

Script:

import psycopg2

# Connection with the DataBase
conn = psycopg2.connect(user = "appuser", database = "onedb", host = "192.168.1.1", port = "5432")
cursor = conn.cursor()

sql = """SELECT COUNT(id) FROM appuser.oltptransactions"""
sql2 = """SELECT count(id) FROM appuser.oltpcases"""

sqls = [sql,sql2]
for i in sqls:
    cursor.execute(i)
    result = cursor.fetchall()
    print('Counts:',result)

conn.close()

Current output:

[root@pgenc python_scripts]# python multi_getrcount.py 
('Counts:', [(12L,)])
('Counts:', [(24L,)])

Any help is appreciated.

Thanks!

Do you want to print the table names instead of the keyword 'Counts' in the print statements ? — Resham Wadhwa
– Resham Wadhwa, Commented Feb 19, 2020 at 12:10
IF you want to print the count along with table name, then you have to modify your select statement and include your table name as well. cursor.fetachall returns a list of each row as tuples so rather than using fetchall, iterate each row and format your print statement. Such as for name, count in cursor: Print ("") — SUN
– SUN, Commented Feb 19, 2020 at 12:18

Serge Ballesta · Accepted Answer · 2020-02-19 14:18:54Z

2

I am a bit reluctant to show this way, because best practices recommend to never build a dynamic SQL string but always use a constant string and parameters, but this is one use case where computing the string is legit:

a table name cannot be a parameter in SQL
the input only comes from the program itself and is fully mastered

Possible code:

sql = """SELECT count(*) from appuser.{}"""
tables = ['oltptransactions', 'oltpcases']

for t in tables:
    cursor.execute(sql.format(t))
    result = cursor.fetchall()
    print("('", t, "':,", result, ")")

edited Feb 19, 2020 at 14:18

answered Feb 19, 2020 at 12:42

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ribbit Over a year ago

Thanks, @Serge Ballesta I like the table name array and prefer this to hard coding the SELECT statements within the variable. I amended the script to include this change but encountered an error. There's a lot of references to panda when googling for sql.format(table) ` Traceback (most recent call last): File "a.py", line 12, in <module> cursor.execute(sql.format(table)) NameError: name 'table' is not defined

Serge Ballesta Over a year ago

@ribbit: my bad... I wrote t in for and table in format :-( Now fixed...

ribbit Over a year ago

Thank you! That's done the trick and appreciate your help. [root@pgenc python_scripts]# python a.py ("('", 'oltptransactions', "':,", [(12L,)], ')') ("('", 'oltpcases', "':,", [(24L,)], ')')

SUN · Accepted Answer · 2020-02-19 12:26:36Z

0

I believe something as below, Unable to test code because of certificate issue.

sql = """SELECT 'oltptransactions', COUNT(id) FROM appuser.oltptransactions"""
sql2 = """SELECT 'oltpcases', COUNT(id) FROM appuser.oltpcases"""

sqls = [sql,sql2]
for i in sqls:
    cursor.execute(i)
    for name, count in cursor:
        print ("")

Or

sql = """SELECT 'oltptransactions :'||COUNT(id) FROM appuser.oltptransactions"""
sql2 = """SELECT 'oltpcases :'||COUNT(id) FROM appuser.oltpcases"""

sqls = [sql,sql2]
for i in sqls:
    cursor.execute(i)
    result = cursor.fetchall()
    print(result)

answered Feb 19, 2020 at 12:26

SUN

1895 bronze badges

Collectives™ on Stack Overflow

Python Script to get multi table counts

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related