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?
FROM from
is this sql?
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
What happens if you take out the semicolon at the end of the sql?
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)}')
so it was From from
Wouldn't it be more efficient to use count() instead of reading all the data?
select COUNT(*) from owner.table_name
Yeah. Perhaps. Emphasis on HACK in my earlier post. There comes a point where a guy says, "yep, good enough"....
You do you, dude 😉
Among other things....
Soon I won't be able to say: