Forums

MySQL query partially working

I'm trying to figure out why the folder(item) function is not working the 'correct" way. It is working if I use the for loop and doing a search. I'm getting the ID. But I like to do is to use MySQL query, but it returns 'None'

Just to make sure, if I type in SELECT * FROM grocery WHERE item = "Test 1"; in MySQL it finds the ID.

The line I can't get to work is the index = cursor.execute("SELECT * FROM grocery WHERE item =%s", (item))

def showAll():
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM grocery")
    result = cursor.fetchall()
    return result

def insert(item, path):
    cursor = conn.cursor()
    cursor.execute("INSERT INTO grocery (item, path) VALUES (%s, %s)", (item, path))
    conn.commit()

def delete(index):
    cursor = conn.cursor()
    cursor.execute("DELETE FROM grocery WHERE id=%s", (index,))
    conn.commit()

def folder(item):
    display = showAll()
    for d in display:
        print(d)
        if(item in d):
            print("ID = ", d[0])
    cursor = conn.cursor()
    index = cursor.execute("SELECT * FROM grocery WHERE item =%s", (item))
    return index

print(folder("Test 1"))

#SELECT * FROM grocery WHERE item = "Test 1";
tonynsx | 8 posts | March 12, 2025, 7:40 p.m. | permalink

This is the result when I do print(folder("Test 1")). This is correct.

As you can se, it is showing ID = 127 from the for loop, but None from index = cursor.execute("SELECT * FROM grocery WHERE item =%s", (item)). This should also return 127, but returns None

(127, 'Test 1', '')
ID =  127
(129, 'Test 3 attachment', '/uploads/IMG_0611.jpeg')
(131, 'sdffff', './uploads/New Rich Text Format.rtf')
(132, 'ffffffsdfsf', '/home/tonynsx/grocery_list/uploads/New Rich Text Format.rtf')
None
>>>
tonynsx | 8 posts | March 12, 2025, 7:43 p.m. | permalink

In general we can only give tech support on PythonAnywhere-related issues here, and not for general programming-related queries -- we recommend Stack Overflow for those.

However, in this case... check out the difference between your showAll and folder functions. In the first, you're using cursor.fetchAll:

    cursor.execute("SELECT * FROM grocery")
    result = cursor.fetchall()
    return result

In the second, though, you're not calling fetchAll and you're just returning the result of the cursor.execute function:

    index = cursor.execute("SELECT * FROM grocery WHERE item =%s", (item))
    return index
Staff giles | 12766 posts | PythonAnywhere staff | March 12, 2025, 8:12 p.m. | permalink

Thank you for the reply.

tonynsx | 8 posts | March 13, 2025, 12:28 a.m. | permalink

I hope you were able to fix it.

Staff nkahr | 727 posts | PythonAnywhere staff | March 13, 2025, 9:40 a.m. | permalink