Error while executing mysql query via python script

Question

i'm trying to execute following script in python3, which suppose to connect to database, get list of tables, and then return describe statement ,but whole time python seems to change `` for '' and returns me following error:

File "apka_baz.py", line 17, in <module>
 for result in cursor.execute(describe, smthn):
 File "/usr/local/lib/python3.7/site-packages/mysql/connector/cursor.py", line 559, in execute
 self._handle_result(self._connection.cmd_query(stmt))
 File "/usr/local/lib/python3.7/site-packages/mysql/connector/connection.py", line 494, in cmd_query
 result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
 File "/usr/local/lib/python3.7/site-packages/mysql/connector/connection.py", line 396, in _handle_result
 raise errors.get_exception(packet)
mysql.connector.errors.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 ''test_table'' at line 1

Code:

# Open database connection
db = conn.connect(user="sb",password="smtn",host="172.blabla",database="blabla")
# prepare a cursor object using cursor() method
cursor = db.cursor()
describe = ("show create table %s")
cursor.execute("SHOW TABLES")
for row in cursor.fetchall():
 smthn = (row[0],)
 print(describe % smthn)
 for result in cursor.execute(describe, smthn):
 print(result)
 for rows in cursor.fetchall():
 print(rows)

It's probably fault of changes described earlier, but still i can't get solution

• Viggo Lundén: Your SQL syntax is incorrect, as the message hints at. Remove the symbols before and after %s
• harcotlupus: @ViggoLundén even without those, it returns same error
• Viggo Lundén: Have you tried using ' instead of `?
• harcotlupus: @ViggoLundén Yes, after this change it's just adding one more " ' " to the error message
• harcotlupus: @HarlyHallikas Same error... it says, that query is bad, but : print(describe % smthn) returns me legit query

Answer 1

# prepare a cursor object using cursor() method
cursor = db.cursor()
cursor.execute("SHOW TABLES")
for row in cursor.fetchall():
 # it seems like you want to iterate through all tables?
 # either way, I renamed smthn to "table" - look below:
 # smthn = (row[0],) 
 for table in row: # row contains one or more table names
 print("Found table: %s" % table)
 Q = "SHOW CREATE TABLE %s" % table # here we compile the query
 print("Executing: '%s;' ...\n" % Q) #
 # It's not a good practice to query (write into) the same cursor 
 # that you're reading at the same time. May cause issues. Use separate cursor:
 cursor2 = db.cursor() 
 # THE FOLLOWING WAS THE ROOT OF ALL EVIL:
 # for result in cursor.execute(describe, smthn): 
 # we only GET a single result (either query succeeded or not)
 result = cursor2.execute(Q)
 # go through all and display
 for row in cursor2.fetchall():
 print(" -- " + " \n ".join(row))

Output from the sample above on my test table:

[~]$ python3.6 python-mysql-test.py
Found table: test
Executing: 'SHOW CREATE TABLE test;' ...
-- test
CREATE TABLE `test` (
 `id` smallint(5) unsigned NOT NULL,
 `string` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# [~]$

• Harly Hallikas: refresh before copying, I just fixed the formatting here
• harcotlupus: Worked like a charm!
• Harly Hallikas: Yep. Also, I'd like to make one remark. When working with SQL QUERIES IN UPPERCASE. It would be a nice habit + it will make the queries easily readable, as commonly table and column names are either lowercase, or mixed case (such as with title case)