topic Re: MySql: Loop through list of tables and return number of rows in Python Questions

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

JoeBorgione — Thu, 21 Oct 2021 19:44:11 GMT

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

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

DanPatterson — Thu, 21 Oct 2021 20:06:19 GMT

FROM from

is this sql?

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

JoeBorgione — Thu, 21 Oct 2021 20:10:37 GMT

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

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

BlakeTerhune — Thu, 21 Oct 2021 20:57:36 GMT

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

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

JoeBorgione — Thu, 21 Oct 2021 21:43:50 GMT

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)}')

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

DanPatterson — Thu, 21 Oct 2021 22:01:07 GMT

so it was From from

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

JoeBorgione — Thu, 21 Oct 2021 22:03:23 GMT

Among other things....

Soon I won't be able to say:

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

BlakeTerhune — Thu, 21 Oct 2021 22:24:43 GMT

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

select COUNT(*) from owner.table_name

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

JoeBorgione — Thu, 21 Oct 2021 22:28:12 GMT

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

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

BlakeTerhune — Thu, 21 Oct 2021 22:29:50 GMT

You do you, dude 😉

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

RandyBurton — Fri, 22 Oct 2021 04:15:37 GMT

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';