Bring bank result and put in a list to use with variables

Asked

Viewed 53 times

0

I’m working with Python 3.7.

I am trying to define variables with data from a list[], the values of the list[] is a result coming from the database, but is giving error.

Code:

conn = sqlite3.connect('db/dataBase.db')  
cursor = conn.cursor()

cursor.execute("select * from configs")

lista = []
lista = cursor.fetchall()

id = lista[0]
valorOrdem = lista[1]
tipoOrdem = lista[2]
qtdOrdens = lista[3]
...

cursor.close
conn.close

Returns the following error:

Ocorreu uma exceção: IndexError
list index out of range

I know this error is because I am trying to access a field that does not exist in the list[].

I’m not being able to distribute the bank’s results on the list[]. I need to define variables with list values[0], list1, list[2]...

Follow the result that returns from the database (the data is correct):

0:(1, 2.0, 0, 3, 1, 5.0, 0, 1, 10.0, 0, 1, 0, 0, 3, ...)
len():1

Follow the print return of the database in the list[] and the error when I try to set the variable valueOrder with the list field1:

Erro

python list arraylist sqlite3 fetchall

  • Take a look at the image on the line above special variables [(1, 2.0, 0, 3, 1, 5.0, 0, 1, 10.0,...] . Your list consists of a tuple of values. If this query returns only a single line do lista = cursor.fetchall()[0]

  • Thanks. Solved for this query that returns only one row from the database, but when returning more than one row how could I do ? I will fall into the same problem described in the post.

  • Each tuple returned by fetchall() is a table row so create an iteration loop and work with each row of the table individually.

  • There are people who turn the result of fetchall() into an array of pandas/numpy. It depends a lot

  • 1

    I tried to close the question with the @Augustovasques answer, but I couldn’t because I was in a comment. But it solved the problem. Thank you.

1 answer

0


Interpreting the comments attached to the question...

Take a look at the image, in the line above special variables: inserir a descrição da imagem aqui

Your list consists of a tuple of values.
If this query returns only a single line do:

lista = cursor.fetchall()[0]

In his example:

conn = sqlite3.connect('db/dataBase.db')  
cursor = conn.cursor()

cursor.execute("select * from configs")    

lista = cursor.fetchall()[0]              #Aqui pega a primeira tupla retornada pela consulta

id = lista[0]
valorOrdem = lista[1]
tipoOrdem = lista[2]
qtdOrdens = lista[3]
...

cursor.close
conn.close

Each tuple returned by fetchall() is a table row whose query was performed so create an iteration loop[for/while] and work with each row of the table individually:

conn = sqlite3.connect('db/dataBase.db')  
cursor = conn.cursor()

cursor.execute("select * from testes")

for linha in cursor.fetchall():
      ...realize alguma alguma ação...

Or reading some table with Pandas, according to this answer given in the SOEN:

import sqlite3
import pandas as pd

cnx = sqlite3.connect('file.db')

df = pd.read_sql_query("SELECT * FROM table_name", cnx)

Browser other questions tagged python list arraylist sqlite3 fetchall

You are not signed in. Login or sign up in order to post.