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:
What am I missing on the python side of things?
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';