Ejercicio

Ejercicio#

Vamos a construir una BBDD en SQLite que modele, de forma muy estilizada, las necesidades de un administrador de cartera.

De forma muy sencilla:

un administrador tiene clientes,
que tiene cuentas de inversión,
a esas cuentas le hacen aportes y retiros de capital
y con ese capital compran y venden activos financieros.

Creación de la Base de Datos#

import sqlite3

Si el archivo no existe, SQLite lo creará en la ruta especificada.

conn = sqlite3.connect('data/administrador.db') 

Se crea el objeto cursor.

cursor = conn.cursor()

Para habilitar las llaves foráneas.

cursor.execute("PRAGMA foreign_keys = 1")

<sqlite3.Cursor at 0x7fce14c7ca40>

La siguiente función nos permitirá ver las tablas de la base de datos.

import pandas as pd

def show_tables(conn):
    qry_tables = "SELECT name, sql FROM sqlite_master WHERE type = 'table'"
    return pd.read_sql(qry_tables, conn)

¿Qué hay ahora en la BBDD?

show_tables(conn)

	name	sql

Vemos que todavía no hay ninguna tabla.

Tablas#

La BBDD tendrá las siguientes tablas:

clientes
cuentas
tipos de movimientos (compras, ventas, aportes, retiros)
caja (cantidad de efectivo disponible en una cuenta)
tipos de inversión (supondremos para simplificar que sólo se invierte en cuotas de fondos y acciones)
inversiones (de una cuenta)
precios de mercado (de las cuotas de fondos)

Tabla `clientes`#

Notar las restricciones de tipo UNIQUE en los campos email y telefono. Estas restricciones indican que estos campos deben ser únicos para cada registro, es decir, dos o más clientes NO pueden tener el mismo email o teléfono.

qry = "CREATE TABLE IF NOT EXISTS clientes ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
nombre TEXT NOT NULL, \
apellido TEXT NOT NULL, \
email TEXT NOT NULL UNIQUE, \
telefono TEXT NOT NULL UNIQUE \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

Verifiquemos el resultado.

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)

¿Y la tabla sqlite_sequence?

pd.read_sql("SELECT * FROM sqlite_sequence", conn)

	name	seq

Con esta función vamos a ir viendo si la tabla cambia más adelante.

def sql_seq():
    return pd.read_sql("SELECT * FROM sqlite_sequence", conn)

Tabla `cuentas`#

La tabla cuentas tendrá una llave foránea hacia la tabla clientes. Este campo permite establecer de qué cliente es la cuenta.

qry = "CREATE TABLE IF NOT EXISTS cuentas ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
nombre TEXT NOT NULL, \
id_cliente INTEGER, \
FOREIGN KEY (id_cliente) REFERENCES clientes (id) \
ON DELETE NO ACTION ON UPDATE NO ACTION \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...

sql_seq()

	name	seq

Tabla `tipo_movimiento`#

Esta tabla registra los tipos de movimientos válidos de caja. Éstos serán APORTE, RETIRO, COMPRA y VENTA.

qry = "CREATE TABLE IF NOT EXISTS tipo_movimiento ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
codigo TEXT NOT NULL UNIQUE \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...
3	tipo_movimiento	CREATE TABLE tipo_movimiento ( id INTEGER PRIM...

sql_seq()

	name	seq

Tabla `caja`#

En esta tabla se registrarán los movimientos de caja de las cuentas.

qry = "CREATE TABLE IF NOT EXISTS caja ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
id_cuenta INTEGER, \
codigo_movimiento TEXT NOT NULL, \
monto NUMERIC NOT NULL, \
FOREIGN KEY (id_cuenta) REFERENCES cuentas (id) \
ON DELETE NO ACTION ON UPDATE NO ACTION, \
FOREIGN KEY (codigo_movimiento) REFERENCES tipo_movimiento (codigo) \
ON DELETE NO ACTION ON UPDATE NO ACTION \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...
3	tipo_movimiento	CREATE TABLE tipo_movimiento ( id INTEGER PRIM...
4	caja	CREATE TABLE caja ( id INTEGER PRIMARY KEY AUT...

sql_seq()

	name	seq

Tabla `tipos_instrumentos`#

En esta tabla se registran los tipos de instrumentos en los que se puede invertir. Para este ejemplo, sólo utilizaremos activos P x Q, es decir ACCIONES, ETF, CUOTAS_FONDOS.

qry = "CREATE TABLE IF NOT EXISTS tipos_instrumentos ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
codigo TEXT UNIQUE \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...
3	tipo_movimiento	CREATE TABLE tipo_movimiento ( id INTEGER PRIM...
4	caja	CREATE TABLE caja ( id INTEGER PRIMARY KEY AUT...
5	tipos_instrumentos	CREATE TABLE tipos_instrumentos ( id INTEGER P...

sql_seq()

	name	seq

Tabla `instrumentos`#

Esta tabla requiere una llave foránea hacia la tabla tipos_instrumentos.

qry = "CREATE TABLE IF NOT EXISTS instrumentos ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
codigo TEXT UNIQUE, \
codigo_instrumento TEXT NOT NULL, \
FOREIGN KEY (codigo_instrumento) REFERENCES tipos_instrumentos (codigo) \
ON DELETE NO ACTION ON UPDATE NO ACTION \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...
3	tipo_movimiento	CREATE TABLE tipo_movimiento ( id INTEGER PRIM...
4	caja	CREATE TABLE caja ( id INTEGER PRIMARY KEY AUT...
5	tipos_instrumentos	CREATE TABLE tipos_instrumentos ( id INTEGER P...
6	instrumentos	CREATE TABLE instrumentos ( id INTEGER PRIMARY...

sql_seq()

	name	seq

Tabla `inversiones`#

Aquí se registra el código, monto y precio de una inversión que pertenece a una cuenta. El campo id_cuenta es entonces una llave foránea a la tabla cuentas.

qry = "CREATE TABLE IF NOT EXISTS inversiones ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
id_cuenta INTEGER NOT NULL, \
codigo_instrumento TEXT NOT NULL, \
monto NUMERIC NOT NULL, \
precio NUMERIC NOT NULL, \
FOREIGN KEY (id_cuenta) REFERENCES cuentas (id) \
ON DELETE NO ACTION ON UPDATE NO ACTION, \
FOREIGN KEY (codigo_instrumento) REFERENCES instrumentos (codigo) \
ON DELETE NO ACTION ON UPDATE NO ACTION \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...
3	tipo_movimiento	CREATE TABLE tipo_movimiento ( id INTEGER PRIM...
4	caja	CREATE TABLE caja ( id INTEGER PRIMARY KEY AUT...
5	tipos_instrumentos	CREATE TABLE tipos_instrumentos ( id INTEGER P...
6	instrumentos	CREATE TABLE instrumentos ( id INTEGER PRIMARY...
7	inversiones	CREATE TABLE inversiones ( id INTEGER PRIMARY ...

sql_seq()

	name	seq

Tabla `precios_mercado`#

Esta tabla registrará los precios de mercado de los instrumentos. En este caso definiremos una restricción de tipo UNIQUE a la combinación de campos fecha y id_instrumento. Con esta restricción sólo podemos registrar un precio diario por cada instrumento. Esta lógica es adecuada para precios de fin de día que son los que se utilizan para la valorización de fin de día de todas las inversiones.

qry = "CREATE TABLE IF NOT EXISTS precios_mercado ( \
id INTEGER PRIMARY KEY AUTOINCREMENT, \
fecha TEXT NOT NULL, \
id_instrumento INTEGER NOT NULL, \
precio NUMERIC NOT NULL, \
FOREIGN KEY (id_instrumento) REFERENCES instrumentos (id) \
ON DELETE NO ACTION ON UPDATE NO ACTION, \
UNIQUE(fecha, id_instrumento) \
)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

show_tables(conn)

	name	sql
0	clientes	CREATE TABLE clientes ( id INTEGER PRIMARY KEY...
1	sqlite_sequence	CREATE TABLE sqlite_sequence(name,seq)
2	cuentas	CREATE TABLE cuentas ( id INTEGER PRIMARY KEY ...
3	tipo_movimiento	CREATE TABLE tipo_movimiento ( id INTEGER PRIM...
4	caja	CREATE TABLE caja ( id INTEGER PRIMARY KEY AUT...
5	tipos_instrumentos	CREATE TABLE tipos_instrumentos ( id INTEGER P...
6	instrumentos	CREATE TABLE instrumentos ( id INTEGER PRIMARY...
7	inversiones	CREATE TABLE inversiones ( id INTEGER PRIMARY ...
8	precios_mercado	CREATE TABLE precios_mercado ( id INTEGER PRIM...

sql_seq()

	name	seq

Insertar Data#

Vamos a insertar data en las distintas tablas que hemos definido.

Tabla `clientes`#

Recordemos los campos de la tabla.

pd.read_sql("SELECT * FROM clientes", conn)

	id	nombre	apellido	email	telefono

Para insertar data en una BBDD relacional se utiliza la instrucción INSERT. Notar que, dado que el campo id es AUTOINCREMENT no es necesario incluirlo.

qry = "INSERT INTO clientes (nombre, apellido, email, telefono) \
VALUES ('Alvaro', 'Diaz', 'alvaro@efaa.cl', 56955556667)"

cursor.execute(qry)

<sqlite3.Cursor at 0x7fce14c7ca40>

pd.read_sql("SELECT * FROM clientes", conn)

	id	nombre	apellido	email	telefono
0	1	Alvaro	Diaz	alvaro@efaa.cl	56955556667

Veamos lo siguiente:

abrimos una nueva conexión a la BBDD
ejecutamos SELECT * FROm CLIENTES usando esa conexión y …

conn2 = sqlite3.connect('data/administrador.db') 

pd.read_sql("SELECT * FROM clientes", conn2)

	id	nombre	apellido	email	telefono

Note

¡NO hay nada en la tabla!

Esto ocurre porque la inserción aún no ha sido COMITEADA …

conn.commit()  # Se utiliza la misma conexión que para el INSERT

De ese modo …

pd.read_sql("SELECT * FROM clientes", conn2)

	id	nombre	apellido	email	telefono
0	1	Alvaro	Diaz	alvaro@efaa.cl	56955556667

Para liberar la memoria, cerramos conn2.

conn2.close()

Podemos también ingresar más de un registro a la vez.

qry = "INSERT INTO clientes (nombre, apellido, email, telefono) \
VALUES ('Juan', 'Perez', 'juan.perez@efaa.cl', 56955551234), \
('Jose', 'Gonzalez', 'jose.gonzalez@efaa.cl', 56955554321)"

cursor.execute(qry)
conn.commit()

pd.read_sql("SELECT * FROM clientes", conn)

	id	nombre	apellido	email	telefono
0	1	Alvaro	Diaz	alvaro@efaa.cl	56955556667
1	2	Juan	Perez	juan.perez@efaa.cl	56955551234
2	3	Jose	Gonzalez	jose.gonzalez@efaa.cl	56955554321

Recordemos que el campo email tiene una restricción UNIQUE, veamos qué ocurre si, por error, ingresamos un email repetido.

qry = "INSERT INTO clientes (nombre, apellido, email, telefono) \
VALUES ('Alvaro', 'Valenzuela', 'alvaro@efaa.cl', 56955553141)"

try:
    cursor.execute(qry)
except Exception as e:
    print("Hubo un error ...")
    print(e)

Hubo un error ...
UNIQUE constraint failed: clientes.email

pd.read_sql("SELECT * FROM clientes", conn)

	id	nombre	apellido	email	telefono
0	1	Alvaro	Diaz	alvaro@efaa.cl	56955556667
1	2	Juan	Perez	juan.perez@efaa.cl	56955551234
2	3	Jose	Gonzalez	jose.gonzalez@efaa.cl	56955554321

Finalmente, podemos observar qué es lo que registra la tabla sqlite_sequence.

sql_seq()

	name	seq
0	clientes	3

Dejo como ejercicio adicional la interpretación de los registros de esta tabla.

Tabla `cuentas`#

Recordemos los campos de esta tabla.

pd.read_sql("SELECT * FROM cuentas", conn)

	id	nombre	id_cliente

Vamos a insertar algunas cuentas.

cuentas = [
    ('CUENTA1', 1),
    ('CUENTA2', 1),
    ('CUENTA1', 2),
    ('CUENTA1', 3)
]

Con este comando podemos insertar todas las cuentas con una sola instrucción.

cursor.executemany("INSERT INTO cuentas (nombre, id_cliente) VALUES (?, ?)", cuentas)
conn.commit()

pd.read_sql("SELECT * FROM cuentas", conn)

	id	nombre	id_cliente
0	1	CUENTA1	1
1	2	CUENTA2	1
2	3	CUENTA1	2
3	4	CUENTA1	3

¿Qué pasa si tratamos de insertar una cuenta para un cliente que no existe?

try:
    cursor.execute("INSERT INTO cuentas (nombre, id_cliente) VALUES ('CUENTA1', 4)")
except Exception as e:
    print("Hubo un error ...")
    print(e)

Hubo un error ...
FOREIGN KEY constraint failed

sql_seq()

	name	seq
0	clientes	3
1	cuentas	4

Tabla `tipo_movimiento`#

Esta es una tabla con data estática. Vamos a insertar los 4 tipos de movimientos que indicamos al inicio.

pd.read_sql("SELECT * FROM tipo_movimiento", conn)

	id	codigo

movimientos = [
    ('APORTE',), ('RETIRO',), ('COMPRA',), ('VENTA',)
]

cursor.executemany("INSERT INTO tipo_movimiento (codigo) VALUES (?)", movimientos)
conn.commit()

Tabla `caja`#

Vamos a hacerle un aporte de 10 MM CLP a la CUENTA1.

qry = "INSERT INTO caja (id_cuenta, codigo_movimiento, monto) VALUES (1, 'APORTE', 10000000)"

cursor.execute(qry)
conn.commit()

pd.read_sql("SELECT * FROM caja", conn).style.format({'monto': '{:,.0f}'})

	id	id_cuenta	codigo_movimiento	monto
0	1	1	APORTE	10,000,000

Tabla `tipos_instrumentos`#

Ahora la CUENTA1 tiene saldo a favor en caja, pero no podemos registrar inversiones si no damos de alta los tipos de instrumentos y los instrumentos específicos con los que vamos a operar. Estas son tablas con data estática.

tipos_instrumentos = [
    ('ACCIONES',), ('CUOTA_FONDOS',), ('ETF',)
]

cursor.executemany("INSERT INTO tipos_instrumentos (codigo) VALUES (?)", tipos_instrumentos)
conn.commit()

pd.read_sql("SELECT * FROM tipos_instrumentos", conn)

	id	codigo
0	1	ACCIONES
1	2	CUOTA_FONDOS
2	3	ETF

Tabla `instrumentos`#

instrumentos = [
    ('COPEC', 'ACCIONES'),
    ('MMKT_LV', 'CUOTA_FONDOS'),
    ('SP500', 'ETF'),
]

cursor.executemany("INSERT INTO instrumentos (codigo, codigo_instrumento) VALUES (?, ?)", instrumentos)
conn.commit()

pd.read_sql("SELECT * FROM instrumentos", conn)

	id	codigo	codigo_instrumento
0	1	COPEC	ACCIONES
1	2	MMKT_LV	CUOTA_FONDOS
2	3	SP500	ETF

Compra de un Instrumento#

Aquí vamos a ver un ejemplo de la utilidad de usar commit. Al ingresar una compra de un instrumento, se deben realizar dos inserciones de datos:

Un nuevo registro en la tabla inversiones
Un nuevo movimiento en la tabla caja que refleje que se ha utilizado el saldo.

Ambas operaciones están envueltas de forma explícita en una TRANSACTION, de modo que si cualquier parte de ésta falla, no quedará incompleta y todo se puede reversar.

id_cuenta = 1
instrumento = 'COPEC'
codigo = 'COMPRA'
numero = 1000
precio = 7150

qry_caja = "INSERT INTO caja (id_cuenta, codigo_movimiento, monto) VALUES (?, ?, ?)"

qry_inversion = "INSERT INTO inversiones (id_cuenta, codigo_instrumento, monto, precio) \
VALUES (?, ?, ?, ?)"

try:
    cursor.execute("BEGIN TRANSACTION")
    # Cuando compro registro egreso, cuando vendo un ingreso
    cursor.execute(qry_caja, [id_cuenta, codigo, numero * precio if codigo == 'VENTA' else -numero * precio])
    # En la tabla de inversiones es al revés
    cursor.execute(qry_inversion, [id_cuenta, instrumento, numero if codigo == 'COMPRA' else -numero, precio])
    conn.commit()
except Exception as e:
    print(str(e))
    cursor.execute("ROLLBACK")

Vemos como quedan las tablas caja e inversiones.

pd.read_sql("SELECT * FROM caja", conn).style.format({'monto': '{:,.0f}'})

	id	id_cuenta	codigo_movimiento	monto
0	1	1	APORTE	10,000,000
1	2	1	COMPRA	-7,150,000

pd.read_sql("SELECT * FROM inversiones", conn).style.format({'monto': '{:,.0f}', precio: '{:,.0f}'})

	id	id_cuenta	codigo_instrumento	monto	precio
0	1	1	COPEC	1,000	7150

Cerrar Conexión#

En esta sección llegamos hasta acá. Cerramos la conexión a la based e datos.

conn.close()

Ejercicio

Contents

Ejercicio#

Creación de la Base de Datos#

Tablas#

Tabla clientes#

Tabla cuentas#

Tabla tipo_movimiento#

Tabla caja#

Tabla tipos_instrumentos#

Tabla instrumentos#

Tabla inversiones#

Tabla precios_mercado#

Insertar Data#

Tabla clientes#

Tabla cuentas#

Tabla tipo_movimiento#

Tabla caja#

Tabla tipos_instrumentos#

Tabla instrumentos#

Compra de un Instrumento#

Cerrar Conexión#

Tabla `clientes`#

Tabla `cuentas`#

Tabla `tipo_movimiento`#

Tabla `caja`#

Tabla `tipos_instrumentos`#

Tabla `instrumentos`#

Tabla `inversiones`#

Tabla `precios_mercado`#

Tabla `clientes`#

Tabla `cuentas`#

Tabla `tipo_movimiento`#

Tabla `caja`#

Tabla `tipos_instrumentos`#

Tabla `instrumentos`#