Cursor.Fetchone() Returns None But Row in the Database Exists

cursor.fetchone() returns None but row in the database exists

Okay, db.autocommit(True) solved my problem.

cursor.fetchone() returns None even though a value exists

When you call execute, the results are computed and fetched, and you use fetchone/fetchmany/fetchall to retrieve them.

In your case, your query returns a single row as the result, so calling cursor.fetchone in the if causes the result to be fetched and subsequently thrown away, so another call to fetchone will yield None as the pointer has already advanced past the only row in the result set.

The idiomatic sqlite way of checking if data exists, is to query a row, and test its truthiness -

result = cursor.fetchone()
if result:
    print(result)

Python sqlite connection.fetchone() returns none when their is data in the database

You have forgot to add connection.execute("select * from data")

Here is the correct code

def getDatabase():
    conn = sqlite3.connect('data.db')
    connection = conn.cursor()
    connection.execute('''CREATE TABLE IF NOT EXISTS data(CK, CSK, ATK, ATSK, DAK)''')
    conn.commit()
    connection.execute("select * from data")#you forgot to add this
    row = connection.fetchone()
    print(row)
    if row:
        print("Database Has  Rows")
        #ShowOptionScreen()

    else:
        print("Database Has No Rows")
        #getKEYSfromuser()

sqlite cursor fetchone() returns 'NoneType'?

After executing a query with execute, the query results will be available in a query result set, which you can then iterate over with the c.fetch* methods.

The thing to note here, is that fetch* will iterate over the result set, exhausting it. This works similar to a generator, which you can only iterate over once.

It works something like this. Imagine a pointer pointing to the next row in the result set to be queried. Running c.execute gives a result set looking like this -

 head
   |____ Result 1
         Result 2
         .
         .
         Result N

After calling c.fetchone, head will return the next row, and advance by one row -

 head    Result 1 (returned)
   |____ Result 2
         .
         .
         Result N

Since you have only one row in your table, another call to fetchone returns an empty list.

Why would MySQL execute return None?

Query executions have no return values.

The pattern you need to follow is:

cursor creation;
cursor, execute query;
cursor, *fetch rows*;

Or in python:

c = d.cursor()

c.execute(query)    # selected rows stored in cursor memory

rows = c.fetchall()    # get all selected rows, as Barmar mentioned
for r in rows:
    print(r)

Also some db modules allow you to iterate over the cursor using the for...in pattern, but triple-check that regarding mysql.

python cursor.execute returning empty

The problems:

1. As @pvg mentioned, you need to escape your input values when querying database;
2. If you want to fetch a dictionary-like result, passing dictionary=True when you initialize the cursor;
3. In your original code, you didn't return the variable json_output;
4. To fetch only one result, use fetchone instead fetchall;
5. After cursor.close() got called, you can obtain nothing from that cursor no matter you fetched before or not;
6. Use try-finally to ensure that cursor always get closed (at last).

Here's the fixed code:

@app.route('/data/active_predicted/<int:ticketId>', methods=['GET'])
def search_db_tickId_act(ticketId):
    try:
        cursor = db.cursor(dictionary=True)
        db_query = cursor.execute("select * from active_predicted where ticketId=%s LIMIT 1", ticketId)
        row = cursor.fetchone()
        if row:
            return json.dumps(row)
        else:
            return "Nothing found \n SQL Query: " + "select * from active_predicted where ticketId=" + str(ticketId)
    finally:
        cursor.close()

---

Related Topics