0

I was trying to use python to connect to Mysql db and execute sql queries to count total rows of multiple tables. The part of code that raised error is:

mysql = {}
for i in df_mysql:
    cnx.cursor.execute("SELECT COUNT(*) FROM s%", (i,))
    num = cox_cursor.fetchall()
    mysql[i] = num

print(mysql)

df_mysql is a list of table names: ['schema.a', 'schema.b',...]

I wanted to store the result in a dictionary, the keys will be the table names, and values are the counts.

The error is:

ProgrammingError: 1064 (42000): 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 ''schema.a'' at line 1

I think it's because it automatically adds ' ' around the table names. Do you guys have suggestions on this? Or are there any other ways to achieve this? Thank you!

Stella
  • 65
  • 4
  • 1
    You can't use table names as query parameters. You have to put them in the sql itself. – khelwood Jan 19 '21 at 22:15
  • To add to @khelwood's comment - in traditional relational, SQL-style databases, if you have to pass the names of your tables or columns as parameters, **your database design is undoubtedly flawed**. You should research and ensure your current design meets the definition of *normal form*, more specifically "*3rd normal form*". – esqew Jan 19 '21 at 22:17

0 Answers0