Why fetchall always results none?

Question

I just need help to solve this, when I execute my code, the result of the fetchall is 'None', but these are the expected results (https://i.stack.imgur.com/OZL5R.png)

this is my code

def verificar_saldo(ID_MATE_PERD):

    conn_str = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
    conn = pyodbc.connect(conn_str)
    
   
    cursor = conn.cursor()
    query = f"EXEC PRC_COME_ESTO_DISP @ID_MATE = {ID_MATE_PERD}"

    cursor.execute(sql + query)

    resultados = cursor.fetchall()
    print(resultados)  # Imprimir os resultados para depuração

    cursor.close()
    conn.close()

    return resultados

What is the value of `sql`? Also, you seem to be susceptible to SQL injection in your `query` string. — jarmod, Jul 21 '23 at 17:55
I suspect it's because `EXEC` calls the function but it does not return the results, so there is nothing for `fetchall()` to fetch. — John Gordon, Jul 21 '23 at 18:46
the value of sql = """ SET NOCOUNT ON; SET ANSI_WARNINGS OFF; DECLARE .@ret int; SELECT .@ret as ret; """ — Igor Moreira, Jul 21 '23 at 19:54

score 0 · Answer 1 · answered Jul 24 '23 at 13:15

Few problems with your code, For one you are subject to SQL injection by using formatted strings. Next, it looks like you have a variable called "SQL" in your execution function, I belive this is unneeded and you should just be calling your query string.

def verificar_saldo(ID_MATE_PERD):

    conn_str = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
    conn = pyodbc.connect(conn_str)
    
   
    cursor = conn.cursor()
    query = "EXEC PRC_COME_ESTO_DISP @ID_MATE = %s"%(ID_MATE_PERD)

    cursor.execute(query)

    resultados = cursor.fetchall()
    print(resultados)  # Imprimir os resultados para depuração

    conn.close()

    return resultados

Try something like this. I am not familiar with your SQL package so this could need some tweaking.

Your answer is just as vulnerable to SQL injection, changing it to using old-style formatting doesn't change that. Instead OP should use [parameter substitution](https://docs.python.org/3/library/sqlite3.html#how-to-use-placeholders-to-bind-values-in-sql-queries), for example: `query = "EXEC PRC_COME_ESTO_DISP @ID_MATE = ?"` and then `curson.execute(query, ID_MATE_PERD)` — Jasmijn, Jul 24 '23 at 13:24

Why fetchall always results none?

1 Answers1