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

431
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 

 

can't wait to retire....
Tags (1)
0 Kudos
10 Replies
DanPatterson
MVP Esteemed Contributor
FROM from

is this sql?


... sort of retired...
0 Kudos
JoeBorgione
MVP Esteemed Contributor

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

 

can't wait to retire....
0 Kudos
BlakeTerhune
MVP Regular Contributor

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

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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)}')
can't wait to retire....
0 Kudos
DanPatterson
MVP Esteemed Contributor

so it was From from


... sort of retired...
BlakeTerhune
MVP Regular Contributor

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

select COUNT(*) from owner.table_name

 

JoeBorgione
MVP Esteemed Contributor

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

can't wait to retire....
0 Kudos
BlakeTerhune
MVP Regular Contributor

You do you, dude ;)

0 Kudos
JoeBorgione
MVP Esteemed Contributor

Among other things....

Soon I won't be able to say:

can't wait to retire....
0 Kudos