CODEWITHSUNDEEP
pythonmysqlmysql-pythonjupyter-notebook
Ivan Pereira
Ivan Pereira

Reputation: 2229

Python MySQLdb doesn't wait for the result

I am trying to run some querys that needs to create some temporary tables and then returns a result set, but i am unable to do that with MySQLdb api.

I already dig something about this issue like here but without success.

My query is like this:

 create temporary table tmp1
 select * from table1;

 alter tmp1 add index(somefield);

 create temporary table tmp2
 select * from table2;

 select * from tmp1 inner join tmp2 using(somefield);

This returns immediatly an empty result set. If i go to the mysql client and do a show full processlist i can see my queries executing. They take some minutes to complete. Why cursor returns immediatly and don't wait to query to run.

If i try to run another query i have a "Commands out of sync; you can't run this command now"

I already tried to put my connection with autocommit to True

db = MySQLdb.connect(host='ip',
                 user='root',
                 passwd='pass',
                 db='mydb',
                 use_unicode=True
    )
db.autocommit(True)

Or put every statement in is own cursor.execute() and between them db.commit() but without success too.

Can you help me to figure what is the problem? I know mysql don't support transactions for some operations like alter table, but why the api don't wait until everything is finished like it does with a select?

By the way i'm trying to do this on a ipython notebook.

Upvotes: 0

Views: 3886

Answers (1)

Austin Phillips
Austin Phillips

Reputation: 15756

I suspect that you're passing your multi-statement SQL string directly to the cursor.execute function. The thing is, each of the statements is a query in its own right so it's unclear what the result set should contain.

Here's an example to show what I mean. The first case is passing a semicolon set of statements to execute which is what I presume you have currently.

def query_single_sql(cursor):
    print 'query_single_sql'
    sql = []
    sql.append("""CREATE TEMPORARY TABLE tmp1 (id int)""")
    sql.append("""INSERT INTO tmp1 VALUES (1)""")
    sql.append("""SELECT * from tmp1""")

    cursor.execute(';'.join(sql))
    print list(cursor.fetchall())

Output:

query_single_sql
[]

You can see that nothing is returned, even though there is clearly data in the table and a SELECT is used.

The second case is where each statement is executed as an independent query, and the results printed for each query.

def query_separate_sql(cursor):
    print 'query_separate_sql'
    sql = []
    sql.append("""CREATE TEMPORARY TABLE tmp3 (id int)""")
    sql.append("""INSERT INTO tmp3 VALUES (1)""")
    sql.append("""SELECT * from tmp3""")
    for query in sql:
        cursor.execute(query)
        print list(cursor.fetchall())

Output:

query_separate_sql
[]
[]
[(1L,)]

As you can see, we consumed the results of the cursor for each query and the final query has the results we expect.

I suspect that even though you've issued multiple queries, the API only has a handle to the first query executed and so immediately returns when the CREATE TABLE is done. I'd suggest serializing your queries as described in the second example above.

Upvotes: 2

Related Questions