CODEWITHSUNDEEP
pythonmysqlsql
delex
delex

Reputation: 211

MySQL Commands out of sync Python

I am trying to initialize my database with MySQL and python mysql connector. After I run the code bellow I receive this exception:

mysql.connector.errors.DatabaseError: 2014 (HY000): Commands out of sync; you can't run this command now

I tried several thing including commit the connection object although nothing as worked for me.

   def initialization():
        cnx = mysql.connector.connect(user="root", password="xxxxxxxxx", host='127.0.0.1')
        cursor = cnx.cursor()
        cursor.execute("CREATE DATABASE IF NOT EXISTS izugitdb;")
        cursor.execute("USE izugitdb;")
        cursor.execute("CREATE TABLE IF NOT EXISTS employee_users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(40) NOT NULL, password VARCHAR(40) NOT NULL,isadmin TINYINT(1) DEFAULT 0 NOT NULL);")
        cursor.execute("CREATE TABLE IF NOT EXISTS clients (client_id INT AUTO_INCREMENT PRIMARY KEY,client_name VARCHAR(255) NOT NULL); ENGINE = InnoDB;")
        cursor.execute("CREATE TABLE IF NOT EXISTS clients_workers (id INT AUTO_INCREMENT PRIMARY KEY, worker_id INT ,worker_name VARCHAR(255) NOT NULL, client_id INT);")

Upvotes: 1

Views: 7966

Answers (2)

delex
delex

Reputation: 211

I Found out that this line

cursor.execute("CREATE TABLE IF NOT EXISTS clients (client_id INT AUTO_INCREMENT PRIMARY KEY,client_name VARCHAR(255) NOT NULL); ENGINE = InnoDB;")

Is making all the trouble, the ENGINE = InnoDB; can't be at the same execute Thanks

Upvotes: 0

DirtyBit
DirtyBit

Reputation: 16782

Commands out of sync

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

Perhaps, the commands in your case, have you tried using them directly in MySQL? and be able to replicate the error?

Or try closing the cursor after each command and then and open it again before using it to execute another statement:

cursor.close() 

cursor = cnx.cursor()

PS. Good catch by @Nikos Steiakakis, I have edited and shaded your password and lets hope that wasn't a real one anyway.

Upvotes: 4

Related Questions