fetchone() is returning None when results are available [fetchone, mysql, return, none]

[CinnamonDonkey]

Hi All,

I have a situation which is confusing me. Using PyODBC and connecting
to a MySQL backend I have some code which seems to work one minute and
then stop the next.

I'm basically doing:

1. Create a DB connection
2. Create a cursor and cursor.execute ("select * from MyTable")
3. cursor.fetchone()
...
go back to 2.

There are other steps after 3. Which include creating another cursor
and doing update and commit on the same table. But the issue is at
point 2/3.

The loop seems to work a few times and then at 3 when the fetchone is
called I am returned None when I know looking at the DB manually that
there are results to be returned.

Can someone please provide some insight into why this may be happening
and where I should be looking to try and debug/resolve this problem.

Thank you!

Regards,
-Shaun

[CinnamonDonkey]

Better example:

db_issue.py:
------------------

import pyodbc

# connect once
db_cxn = pyodbc.connect( 'DSN=mysqlServer' )

# No rows exist, will be added later...
cur = db_cxn.cursor()
cur.execute("select * from a_table where status is NULL")

# expect None
row = cur.fetchone()

cur.close()

# OK, We got none as expected... now ***manually create rows in DB
and continue***
cur = db_cxn.cursor()
cur.execute("select * from forced_jobs where status is NULL")

# Expect tuple with row recently added
row = cur.fetchone()

cur.close()

#row returned is None type?
db_cxn.close()


If I single step through the above code pausing after the first fetch
to manually add rows to the db when I continue the result for the
second fetch is None even though I have just added fresh rows to the
DB.

When I restart the rows are returned on the first fetch call as
expected.

I've had this problem before and fixed it by simply closing the
connection and re-opening before I interact with the DB but surely
there is a better way of doing this!

Does anyone know what I am doing wrong? I'm sure it must be obvious
but I can't see it!

[Michael Kleehammer]

Two things:

1) Make sure there aren't any commits missing.  (Unlikely)

2) Are you using multiple connections or cursors at the same time?  If so, the visibility of newly inserted rows from existing connections depends on whether you've committed them and the transaction isolation settings.

[CinnamonDonkey]

RE> 1) Make sure there aren't any commits missing. (Unlikely)

I thought that commits are only required for calls that that modify
the DB, i.e. update, insert, delete...

Is this not the case?

RE> 2) Are you using multiple connections or cursors at the same
time?...

The provided code example is complete (my second post), it exhibits
the problem. I run it through eclipse with pydev so that I can break
point before the second fetch allowing me to manually create an entry
in the DB. I imagine the same problem would occur at the python
command line (I may try it).

I put the break point on the second occurrence of this line: cur =
db_cxn.cursor()

As you can see from the example code I'm connecting once at the start
and closing the connection once at the end.

I do create a new cursor, but the old one should be released at this
point and since I am only reading from the DB I don't think it is
necessary to perform a commit, or am I wrong?


I'm using Python 2.6 and PyODBC 2.1.8.

[CinnamonDonkey]

Hm, it seems a commit() is needed. Inserting the line
'db_cxn.commit()' after the first 'row=cur.fetchone()' in my example
fixes the problem.

Out of interested, the MySQLdb exhibits this same behavior.

According to: http://www.python.org/dev/peps/pep-0249/

" .commit() - Commit any pending transaction to the database."

Where is the "Pending transaction" is it normal to consider reading
form a DB a transaction?