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

Your SQL syntax is incorrect, as the message hints at. Remove the symbols before and after %s — Viggo Lundén
– Viggo Lundén, Commented Aug 27, 2018 at 12:23
@ViggoLundén Yes, after this change it's just adding one more " ' " to the error message — harcotlupus
– harcotlupus, Commented Aug 27, 2018 at 12:29
@HarlyHallikas Same error... it says, that query is bad, but : print(describe % smthn) returns me legit query — harcotlupus
– harcotlupus, Commented Aug 27, 2018 at 12:35

Harly Hallikas · Accepted Answer · 2018-08-27 13:25:05Z

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

# [~]$

edited Aug 27, 2018 at 13:25

answered Aug 27, 2018 at 13:14

Harly Hallikas

6184 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Harly Hallikas Over a year ago

refresh before copying, I just fixed the formatting here

harcotlupus Over a year ago

Worked like a charm!

Harly Hallikas Over a year ago

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)

Collectives™ on Stack Overflow

Error while executing mysql query via python script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related