CODEWITHSUNDEEP
mysqlpython-3.x
harcotlupus
harcotlupus

Reputation: 209

Error while executing mysql query via python script

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

Upvotes: 0

Views: 1095

Answers (1)

Harly Hallikas
Harly Hallikas

Reputation: 620

# 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

# [~]$

Upvotes: 1

Related Questions