Viewing file:  database_functions.py (4.63 KB) -rwxrwxr-x
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
import pymysql as mdb
import json
db_host = 'localhost'
db_user = 'db_user'
db_password = '123456789'
db_database = 'pedidos'
qr_store_folder = './qrcodes'
qr_scale = 12
def get_qr_filename_from_product(product_name):
# return a qr filename for a product id
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT codigo_qr from productos WHERE nombre='%s'" % product_name)
row = con.fetchone()
if row is not None:
return row[0]
return None
def create_qr_for_new_product(product_name):
import pyqrcode
with mdb.connect(db_host, db_user, db_password, db_database) as con:
qr = pyqrcode.create('%s' % product_name)
qr_filename = '%s/%s.png' % (qr_store_folder, product_name)
qr.png(qr_filename, scale=qr_scale, module_color=[0, 0, 0, 128], background=[0xff, 0xff, 0xcc])
return qr_filename
def insert_product(name, description, price):
qr_code = create_qr_for_new_product(name)
query = "INSERT INTO productos(nombre, descripcion, precio, codigo_qr) VALUES ('%s', '%s', %f, '%s')" % (name, description, price, qr_code)
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute(query)
stock_query = "INSERT INTO stock_productos(nombre_producto, stock) VALUES ('%s', %d)" % (name, 0)
con.execute(stock_query)
def get_all_products():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT * from productos")
products = [dict(name=row[0], description=row[1], price=row[2], qr=row[3]) for row in con.fetchall()]
return products
def get_product_by_name(product_name):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "SELECT * from productos WHERE nombre='%s'" % product_name
print query
con.execute(query)
product_row = con.fetchone()
return { 'name': product_row[0], 'description': product_row[1], 'price' : float(product_row[2]) }
def insert_product_oder(order_id, json_products):
for product in json_products:
with mdb.connect(db_host, db_user, db_password, db_database) as con:
insert_query = 'INSERT INTO detalle_pedido(id_pedido, nombre_producto, cantidad) VALUES (%d, "%s", %d)' % (order_id, product['name'], int(product['count']))
con.execute(insert_query)
update_stock(product['name'], int(product['count']))
def update_stock(product_name, count):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = 'UPDATE stock_productos SET stock=stock -%d WHERE nombre_producto="%s"'% (count, product_name)
con.execute(query)
def insert_order(seller_id, client_id, products):
import datetime
date = datetime.datetime.now()
total = 0.0
#json_products = json.loads(str(products))
json_products = products
for product in json_products:
# get product info
print product
product_data = get_product_by_name(product['name'])
total += product_data['price']*int(product['count'])
order_id = 1
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "INSERT INTO pedidos(id_estado, fecha, id_cliente, total, vendedor) VALUES (%d, '%s', '%s', %f, '%s')" % (1, str(date), client_id, total, seller_id)
con.execute(query)
order_id = con.lastrowid
# Insert order detail
insert_product_oder(order_id, json_products)
####### CLIENTS
def get_all_clients():
with mdb.connect(db_host, db_user, db_password, db_database) as con:
con.execute("SELECT * from clientes")
clients = [dict(id=row[0], name=row[1], business_name=row[2], address=row[3], city=row[4], telephone=row[5], cuit=row[7], mail=row[8]) for row in con.fetchall()]
return clients
def insert_client(name, business_name, address, city, phone, cuit, mail, expresso):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
insert_query = "INSERT INTO clientes(nombre, razon_social, direccion, localidad, telefono, nombre_comercial, cuit, mail, expreso) VALUES('%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (name, business_name, address, city, phone, business_name, cuit, mail, expresso)
con.execute(insert_query)
def get_client_by_business_name(client_business_name):
with mdb.connect(db_host, db_user, db_password, db_database) as con:
query = "SELECT * from clientes WHERE razon_social='%s'" % client_business_name
con.execute(query)
row = con.fetchone()
return { 'id': row[0], 'name': row[1], 'business name': row[2], 'address' : row[3], 'city': row[4], 'phone': row[5], 'cuit': row[7]}
|