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

[JoeBorgione]

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?

@RandyBurton 

[DanPatterson]

FROM from

is this sql?

[JoeBorgione]

mySql

Took care of the double From and get a different error...

File "C:\Clones\Pro282\lib\site-packages\mysql\connector\connection.py", line 1169, in handle_unread_result
raise errors.InternalError("Unread result found")

InternalError: Unread result found

[BlakeTerhune]

What happens if you take out the semicolon at the end of the sql?

[JoeBorgione]

finally got this hack to give me what I want:

import mysql.connector as mysql

cn = mysql.connect(user = 'user', password = 'pwd', host = 'host', database = 'master_db')
cursor = cn.cursor()

cursor.execute("Show tables")
tables = cursor.fetchall()
for table in tables:
    cursor.execute(f"select * from master_db.{table[0]}")
    rows = cursor.fetchall()
    print(f'{table[0]}, {len(rows)}')

[DanPatterson]

so it was From from

[BlakeTerhune]

Wouldn't it be more efficient to use count() instead of reading all the data?

select COUNT(*) from owner.table_name

[JoeBorgione]

Yeah.  Perhaps.  Emphasis on HACK in my earlier post.  There comes a point where a guy says, "yep, good enough"....

[BlakeTerhune]

You do you, dude 😉

[JoeBorgione]

Among other things....

Soon I won't be able to say: