Python MySQL cursor fails to fetch rows

Question

I am trying to fetch data from AWS MariaDB:

cursor = self._cnx.cursor()
stmt = ('SELECT * FROM flights')

cursor.execute(stmt)

print(cursor.rowcount)
# prints 2

for z in cursor:
    print(z)
# Does not iterate

row = cursor.fetchone()
# row is None

rows = cursor.fetchall()
# throws 'No result set to fetch from.'

I can verify that table contains data using MySQL Workbench. Am I missing some step?

EDIT: re 2 answers:

res = cursor.execute(stmt)
# res is None

EDIT:

I created new Python project with a single file:

import mysql.connector

try:
    cnx = mysql.connector.connect(
        host='foobar.rds.amazonaws.com',
        user='devuser',
        password='devpasswd',
        database='devdb'
    )

    cursor = cnx.cursor()
    #cursor = cnx.cursor(buffered=True)

    cursor.execute('SELECT * FROM flights')

    print(cursor.rowcount)
    rows = cursor.fetchall()

except Exception as exc:
    print(exc)

If I run this code with simple cursor, fetchall raises "No result set to fetch from". If I run with buffered cursor, I can see that _rows property of cursor contains my data, but fetchall() returns empty array.

Quentin · Accepted Answer · 2017-04-26 19:47:48Z

1

Your issue is that cursor.execute(stmt) returns an object with results and you're not storing that.

results = cursor.execute(stmt) print(results.fetchone()) # Prints out and pops first row

answered Apr 26, 2017 at 19:47

Quentin

7004 silver badges10 bronze badges

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

2 Comments

jpou Over a year ago

execute resturns None, so this throws exception about calling method on the None type

Quentin Over a year ago

Hmmm... I think I found some details on what your cursor is possibly returning. Maybe it's not setup correctly? dev.mysql.com/doc/connector-python/en/…

guest12345 · Accepted Answer · 2017-08-31 07:56:47Z

For the future googlers with the same Problem I found a workaround which may help in some cases:

I didn't find the source of the problem but a solution which worked for me.

In my case .fetchone() also returned none whatever I did on my local(on my own Computer) Database. I tried the exact same code with the Database on our companies server and somehow it worked. So I copied the complete server Database onto my local Database (by using database dumps) just to get the server settings and afterwards I also could get data from my local SQL-Server with the code which didn't work before.

I am a SQL-newbie but maybe some crazy setting on my local SQL-Server prevented me from fetching data. Maybe some more experienced SQL-user knows this setting and can explain.

Collectives™ on Stack Overflow

Python MySQL cursor fails to fetch rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related