CODEWITHSUNDEEP
pythonmysqlmysql-python
Jayakumar Bellie
Jayakumar Bellie

Reputation: 9558

MySQLdb.cursors.Cursor.execute does not work

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?

Upvotes: 4

Views: 16569

Answers (4)

Ritesh Naik
Ritesh Naik

Reputation: 1

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()

Upvotes: 0

Mr. Pundir
Mr. Pundir

Reputation: 557

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

Upvotes: 15

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

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?

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52070

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.

Upvotes: 0

Related Questions