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

10-21-2021 12:44 PM
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:




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:


What am I missing on the python side of things?



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

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

WHERE TABLE_SCHEMA = 'yourDatabaseName';