How can I store a data from a column of a sqlite database in a variable using Python?

Question

How can I store a data from a column of a sqlite database in a variable using Python?

Navigation

#1 by (0 votes)

2

I have a problem I am trying to get a value from a database in sqlite3 using python:

import sqlite3

#Conexion a la base de datos
con = sqlite3.connect("basededatos.db")
cursor = con.cursor()

#Comprobar conexion a base de datos
cursor.execute("""CREATE TABLE IF NOT EXISTS categorias (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,categoria TEXT)""")
cursor.execute("""INSERT INTO categorias(categoria) VALUES ('Gastos generales')""")
cursor.execute("""CREATE TABLE IF NOT EXISTS datos (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,dato TEXT, categoria TEXT)""")
cursor.close()

def agregar():
 titulo()
 print ("AGREGAR")
 con = sqlite3.connect("nucleocentral.db")
 cursor = con.cursor()

 """PIDE EL TIPO DE OPERACIÓN"""
 print("Elija la operación que quiere hacer")
 print("""
 [1] Gasto
 [2] Ingreso
 """)

 operacion = input("Ingrese su operación: ")
 """-------INGRESO--------"""
 if operacion == "2":
  """Nuestra la tabla categorias"""
  cursor.execute("SELECT categoria FROM categorias")
  mostrar_categorias = cursor.fetchall()
  print(mostrar_categorias)
  """Una vez que vemos la tabla elegiremos el dato"""   
  buscar_id = int(input("Elija una categoria: "))
  cursor.execute("SELECT * FROM categorias WHERE id='%s'" % (buscar_id))
  resultado_busqueda = cursor.fetchall()
  print("ESTE ES EL REGISTRO ",resultado_busqueda)

Now is when I have the problem, I tried to take the value from the column category of the categories table and save it in a variable to later use that data to save it in the data table in its category column.

python python-3.x sqlite3

asked by Jose Angel 23.08.2018 в 12:54

source

1 answer

"Simplified" search of the same value in different fields (in the same table) How to make a progress bar with ajax?

score 0 · Accepted Answer

The methods fetchone or fetchall of SQLite return tuples. In the case of the fetchall that you are using, when you get several records, you have to iterate over those tuples in the following way.

for record in resultado_busqueda:
   print(record)  # Así conseguirías todos los registros.
   print(record[1])  # Así, consigues solo la columna que especifiques
   print(record[1] + " del ID " + record[0])  # Así, consigues varias columnas

As you can see, in our second print we only get the value of the column we want. However, we can go further and directly save the data in the order we want (third print) for better use.

To save the value of a column in a variable, simply:

la_id = record[0]  
la_categoria = record[1]

The bad thing about doing this is that you need to know the exact index of each column, and sometimes it is a bit confusing. I would suggest that you look at some ORM as peewee for python (Database Mapping). So you can do things like la_categoria = record.categoria and forget to remember the index of each column