MySql: Loop through list of tables and return number of rows

2394
10
10-21-2021 12:44 PM
JoeBorgione
MVP Esteemed Contributor

Back in 2020 I was messing around with python and mySql. (see this post) and today I'm back to it.

I can get a list of tables in my database like this:

 

import mysql.connector as mysql

cn = mysql.connect(user = 'myUserName', password = 'myPassword', host = 'myHost', database = 'master_db')
cursor = cn.cursor()

cursor.execute("Show tables;")
myresult = cursor.fetchall()

 

myresult is a list of tuples that looks a little like this:

 

[('accessmoderules',),
 ('accessmodes',),
 ('activitylogs',),
  ...
]

 

What I want to do is loop through myresult and return the table name and the number of records right next to it, but can't quite seem to get it right.  If I try this:

 

 

for table in myresult:
    query = f""" SELECT * FROM from master_db.{table[0]};"""
    number_of_rows = cursor.execute(query)

 

 

I get this error:

 

 

ProgrammingError: You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server 
version for the right syntax to use near 'from master_db.accessmoderules' 
at line 1

 

I can run that very query in a MySql Workbench window and get what I want:

JoeBorgione_0-1634845293557.png

What am I missing on the python side of things?

@RandyBurton 

 

That should just about do it....
Tags (1)
0 Kudos
10 Replies
RandyBurton
MVP Regular Contributor

You might try this MySQL query which should return both table names and associated row counts for a database:

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'yourDatabaseName';