MySQLdb.cursors.Cursor.execute does not work

Question

I have done the following:

import MySQLdb as mdb
con = mdb.connect(hostname, username, password, dbname)
cur = con.cursor()
count = cur.execute(query)
cur.close()
con.close()

I have two queries, I execute them in the mysql console I can view the results.
But when I give the same through python one query works and the other one does not.
I am sure it is not problem with mysql or query or python code. I suspect cur.execute(query) function.

Have anyone come through similar situation? Any solutions?

Answer 1

Use conn.commit() after execution, to commit/finish insertion and deletion based changes.

Answer 2

I have two queries, I execute them in the mysql console I can view the results.

But I only see one query:

import MySQLdb as mdb
con = mdb.connect(hostname, username, password, dbname)
cur = con.cursor()
count = cur.execute(query)
cur.close()
con.close()

My guess is query contains the both queries separated by a semin-colon and is an INSERT statement? You probably need to use executemany().

See Executing several SQL queries with MySQLdb

---

On the other hand, if both of your queries are SELECT statements (you say "I see the result"), I'm not sure you can fetch both results from only one call to execute(). I would consider that as bad style, anyway.

Answer 3

This is a function and the query is passed to this function. When I execute one query after the other. I dont get the result for few queries, there is no problem with the queries because I have crossed checked them with the mysql console.

As you clarified your question in a comment, I post an other answer -- completely different approach.

Are you connected to your DB in autocommit mode? If no, for changes to be permanently applied, you have to COMMIT them. In normal circumstances, you shouldn't create a new connection for each request. That put excessive load on the DB server for almost nothing:

# Open a connection once
con = mdb.connect(hostname, username, password, dbname)


# Do that *for each query*:
cur = con.cursor()
try:
    count = cur.execute(query)
    conn.commit() # don't forget to commit the transaction
else:
    print "DONE:", query # for "debug" -- in real app you migth have an "except:" clause instead
finally:
    cur.close() # close anyway


# Do that *for each query*:
cur = con.cursor()
try:
    count = cur.execute(query)
    conn.commit() # don't forget to commit the transaction
else:
    print "DONE:", query # for "debug" -- in real app you migth have an "except:" clause instead
finally:
    cur.close() # close anyway


# Close *the* connection
con.close()

The above code is directly typed into SO. Please forgive typos and other basic syntax errors. But that's the spirit of it.

A last word, while typing I was wondering how you deal with exceptions? By any chance could the MySQLdb error be silently ignored at some upper level of your program?

Answer 4

Use this query, this will update multiple rows of column in one query

sql=cursor.executemany("UPDATE `table` SET `col1` = %s WHERE `col2` = %s",
            [(col1_val1, col2_val1),(col2_val2, col_val2)])

and also commit with database to see the changes.

conn.commit()