You could store the rows of the products in a list or in a dictionary (with id as a key) and look for the products in it. However, if you use a database, obtain the data from it directly. It does not make much sense to create a duplicate of the table in a variable, as well as being very inefficient if the table becomes extensive.
When you enter an id simply do a query to the table productos of the bd so that you return your name and its price.
Let's see it with the code and with a fully reproducible example. First we created a database with the two necessary tables and a small list of products:
import sqlite3
with sqlite3.connect('Articulos.s3db') as con:
cursor = con.cursor()
cursor.execute('''CREATE TABLE productos (id integer PRIMARY KEY,
nombre text NOT NULL,
precio float NOT NULL)''')
lista = ((1, "Agua", 0.90),
(2, "Tostadas", 1.50),
(3, "Cafe", 1.00),
(4, "Pizza", 2.50))
for producto in lista:
cursor.execute("INSERT INTO productos (id, nombre, precio) VALUES (?,?,?)",
producto)
cursor.execute('''CREATE TABLE Comanda (Articulo text,
Precio float,
Unidades integer,
Mesa integer,
Estado integer)''')
Now that we have the database, we go with your code:
import sqlite3
def caja():
t = time.localtime()
linea_ticket = []
mesa = input("Introduzca el numero de mesa: ")
print ("asctime : ", time.asctime(t))
# Imprimimos todos los productos tabulados con ayuda de str.format
with sqlite3.connect('Articulos.s3db') as con:
cursor = con.cursor()
cursor.execute("select * from productos")
print("Listado de productos:")
print("{:>5} {:>15} {:>6}".format("id", "Nombre", "Precio"))
for producto in cursor:
print("{:>5} {:>15} {:>6.2f}".format(*producto))
estado = 1
unidades = int(input("Introduzca el numero de unidades a comandar: "))
id = int(input("Introduzca el id del producto a comandar: "))
# Buscamos el producto con esa id en la db
cursor.execute("SELECT nombre, precio FROM productos WHERE id=:id", {"id": id})
con.commit()
producto = cursor.fetchone()
if producto:
nombre, precio = producto
cursor.execute("insert into Comanda(Articulo, Precio, Unidades, Mesa, Estado) values (?, ?, ?, ?, ?)",
((nombre, precio, unidades, mesa, estado)))
linea_ticket.append((nombre, precio))
else:
print("Producto no encontrado")
The differences regarding your code are:
-
Using the with statement to use the context handler protocol. This ensures that your cursor applies the changes and that your connection is closed automatically. It is not necessary, you can close it when you end up with close() , I usually use it since I am willing to forget to close after a few lines of code: (
-
format is used to format the output in the form of a table of the list of products, it is very simple (more elaborate outputs can be created) but it looks much better.
-
The important and relevant change is that the id is queried directly with:
cursor.execute("SELECT nombre, precio FROM productos WHERE id=:id", {"id": id})
It will return a tuple with the name and the price of the product associated to that id, in case it does not exist returns None .
An example of a real execution:
>>> caja()
Introduzca el numero de mesa: 4
asctime : Sat Sep 16 15:34:09 2017
Listado de productos:
id Nombre Precio
1 Agua 0.90
2 Tostadas 1.50
3 Cafe 1.00
4 Pizza 2.50
Introduzca el numero de unidades a comandar: 2
Introduzca el id del producto a comandar: 3
We can check that the product has been added to the Comanda table:
>>> with sqlite3.connect('Articulos.s3db') as con:
cursor = con.cursor()
cursor.execute("select * from Comanda")
for row in cursor:
print(row)
('Cafe', 1.0, 2, 4, 1)
Note: the import should be done in the first lines of the script (after the first two if used for "Shebang "), not inside the function. On the other hand, if we follow the advice of PEP-8 should be each on a different line.