Python Mysql CREATE and INSERT in one query

Question

I am using Python to connect to MySQL in XAMPP.I am creating a string and passing it to a new process through queue.The string contains the MySQL query which on execution will create a table and insert into the table.following is my code:-

from MySQLdb import connect
from os import _exit
from multiprocessing import Process,Queue
q=Queue()

def pkt():
    conn=connect(user='root',passwd='257911.',host='localhost',unix_socket="/opt/lampp/var/mysql/mysql.sock")
    cursor=conn.cursor()
    conn.select_db("try")
    while True:
        y=q.get()
        if y=="exit":
            break
        else:
            cursor.execute(y)
            conn.commit()
    cursor.close()
    conn.close()
    _exit(0)


if __name__=="__main__":
    a=Process(target=pkt)
    a.start()
    query="CREATE TABLE hello(id varchar(10) NOT NULL,name varchar(20)); INSERT INTO hello(id,name) VALUES('1234','sujata'); "
    q.put(query)
    q.put("exit")

Upon executing the code, I am getting the following error:-

Traceback (most recent call last):
  File "/usr/lib64/python2.7/multiprocessing/process.py", line 258, in _bootstrap
    self.run()
  File "/usr/lib64/python2.7/multiprocessing/process.py", line 114, in run
    self._target(*self._args, **self._kwargs)
  File "try.py", line 16, in pkt
    conn.commit()
ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

I am getting the same error on inserting into multiple tables in one query.Is it not possible to combine create and insert in one statement?? Thanks.

sgp · Accepted Answer · 2015-06-17 12:03:03Z

1

No, you cannot give multiple semicolon separated queries in the same line. This is because MySQLdb is a wrapper around _mysql which makes it compatible with the Python DB API interface (Read more about the API here). The API works via PREPARE/EXECUTE statements and according to this,

The text must represent a single statement, not multiple statements.

answered Jun 17, 2015 at 12:03

sgp

1,7787 gold badges17 silver badges32 bronze badges

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

1 Comment

sgp Over a year ago

@sujata Did that answer your question?

wonder · Accepted Answer · 2016-01-18 06:31:31Z

I need to close the cursor after execution. This works for me.

from MySQLdb import connect
from os import _exit
from multiprocessing import Process,Queue
q=Queue()

def pkt():
    conn=connect(user='root',host='localhost',unix_socket="/home/mysql/mysql/mysql.sock")
    cursor=conn.cursor()
    conn.select_db("try")
    while True:
        y=q.get()
        if y=="exit":
            break
        else:
            cursor.execute(y)
            cursor.close()
            conn.commit()
    conn.close()
    _exit(0)


if __name__=="__main__":
    a=Process(target=pkt)
    a.start()
    query="CREATE TABLE hello(id varchar(10) NOT NULL,name varchar(20)); INSERT INTO hello(id,name) VALUES('1234','sujata'); "
    q.put(query)
    q.put("exit")

Collectives™ on Stack Overflow

Python Mysql CREATE and INSERT in one query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related