获取多表计数的Python脚本

Question

我正试图编写一个python脚本来获取一些用于监视的表的计数，这看起来有点像下面的代码。我试图获得如下输出，并尝试使用python多维数组，但没有任何运气

预期产出：

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

脚本：

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

电流输出：

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

感谢您的帮助

谢谢

Answer 1

我有点不愿意这样说，因为最佳实践建议永远不要构建动态SQL字符串，而是始终使用常量字符串和参数，但这是一个计算字符串是合法的用例：

• 表名不能是SQL中的参数
• 输入仅来自程序本身，完全掌握

可能代码：

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

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

Answer 2

我相信如下所示，由于证书问题，无法测试代码

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)