CODEWITHSUNDEEP
pythondatabase
ribbit
ribbit

Reputation: 79

Python Script to get multi table counts

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!

Upvotes: 0

Views: 1011

Answers (2)

Serge Ballesta
Serge Ballesta

Reputation: 149125

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, ")")

Upvotes: 2

SUN
SUN

Reputation: 189

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)

Upvotes: 0

Related Questions