A query in SQLite3 with python

Question

I'm testing a query with python and sqlite3. First method works fine, but second is not working. It is about the defined type of variable containing the resgisters in DB:

import sqlite3

def insertar():

    db1=sqlite3.connect('tabla.db')
    print("Estas en la funcion insertar")

    nombre1=raw_input("Escribe el titulo de la novela: ")
    autor1=raw_input("Escribe el autor de la novela: ")
    year1=str(input("Digita el any de la novela: "))

    consulta=db1.cursor()

    strConsulta = "insert into tabla(nombre, autor, year) values\
    ('"+nombre1+"','"+autor1+"','"+year1+"')"
    print(strConsulta)

    consulta.execute(strConsulta)
    consulta.close()
    db1.commit()
    db1.close()

def consultar():
    db2 = sqlite3.connect("tabla.db")
    print("Estas en la funcion insertar")
    db2row_factory = sqlite3.Row
    consulta = db2.cursor()
    consulta.execute("select * from tabla")
    filas = consulta.fetchall()
    lista = []
    for fila in filas:
        s = {}
        s['nombre'] = fila['nombre']
        s['autor'] = fila['autor']
        s['year'] = str(fila['year'])
        lista.append(s)

    consulta.close()
    db2.close()
    return(lista)


#consultar()

def menu():
    Opcion= input("\nIngresa la opcion deseada\n1.Inserta un valor en la tabla\n2.Consultar los valores de la tabla\n")
    if Opcion==1:
        insertar()
        menu()
    elif Opcion==2:
        ListaNovelas = consultar()
        for novela in ListaNovelas:
            print(novela['nombre'],novela['autor'],novela['year'])
        menu()

menu()

I get this error while testing the second method consultar().

$ python file.py

Ingresa la opcion deseada
1.Inserta un valor en la tabla
2.Consultar los valores de la tabla
2
Estas en la funcion insertar
Traceback (most recent call last):
  File "insertar.py", line 56, in <module>
    menu()
  File "insertar.py", line 51, in menu
    ListaNovelas = consultar()
  File "insertar.py", line 33, in consultar
    s['nombre'] = fila['nombre']
TypeError: tuple indices must be integers, not str

your query returns list of tuplles, not dicts. That is why it fails to get key 'nombre' from a tuple — Alex
– Alex, Commented Sep 21, 2016 at 15:35

alecxe · Accepted Answer · 2016-09-21 15:42:41Z

1

db2row_factory = sqlite3.Row

This is the problematic line. Instead you meant to set the row_factory factory on the db2 connection instance:

db2.row_factory = sqlite3.Row

Then, all the fetched rows would be now sqlite3.Row instances having dictionary-like access to field values.

edited Sep 21, 2016 at 15:42

answered Sep 21, 2016 at 15:36

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hell0 Over a year ago

I saw it just after posting. f***. Thank you very much anyway!!

Collectives™ on Stack Overflow

A query in SQLite3 with python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related