CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
NewBeeProgrammer
NewBeeProgrammer

Reputation: 27

Pyodbc -Iterating over results of query and get counts

I am trying to get count from results of tables which is from another query execution but I am getting error:

import pandas.io.sql
import pyodbc
import pandas as pd
server = '<Sample server>'
database = 'ABC'
username = 'scott'
password = 'tiger'
driver= '{ODBC Driver 17 for SQL Server}'

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password,autocommit=False)
cursor = cnxn.cursor()

query = "select TABLE_NAME from information_schema.tables where table_catalog='ABC' and table_type='BASE TABLE'"

cursor.execute(query)

for row in cursor:
print(row.TABLE_NAME)
a=str(row.TABLE_NAME)
print(a)
res=cursor.execute('select count(*) from  '+a)
for i in res:
       print(i)

Getting error

I am trying to get all table_names and their row counts 

TABLE_NAME , COUNT
-------------------
EMPLOYEE,2213
DEPARTMENTS,12
LOCATION,10

Can you please help how can I achieve this?

Upvotes: 0

Views: 568

Answers (1)

Parfait
Parfait

Reputation: 107707

Consider one query joining to sys tables:

query = """SELECT 
             i.TABLE_NAME,
             p.[Rows] AS [COUNT]
           FROM 
             sys.tables t
           INNER JOIN 
             sys.partitions p ON t.object_id = p.OBJECT_ID
           INNER JOIN
             information_schema.tables i ON t.[NAME] = i.TABLE_NAME 
                                        AND i.table_catalog = 'ABC'
                                        AND i.table_type = 'BASE TABLE';
        """

cursor.execute(query)

Upvotes: 2

Related Questions