CODEWITHSUNDEEP
pythonmysqlmysql-python
jpou
jpou

Reputation: 1995

Python MySQL cursor fails to fetch rows

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.

Upvotes: 2

Views: 4436

Answers (2)

guest12345
guest12345

Reputation: 1

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.

Upvotes: 0

Quentin
Quentin

Reputation: 700

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

Upvotes: 1

Related Questions