122 lines
4.6 KiB
Python
122 lines
4.6 KiB
Python
import sqlite3
|
|
from time import sleep
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
cursor.execute("DROP TABLE IF EXISTS empleados;")
|
|
query = """CREATE TABLE IF NOT EXISTS empleados(
|
|
id integer PRIMARY KEY,
|
|
nombre text NOT NULL,
|
|
depto text NOT NULL,
|
|
salario integer);"""
|
|
cursor.execute(query)
|
|
print('1)\n'+query+'\n')
|
|
query = """INSERT INTO empleados(id,nombre,depto,salario)
|
|
VALUES("1","Bob","Ventas","25000")"""
|
|
print('2)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
db.commit()
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = "INSERT INTO empleados(id,nombre,depto,salario) VALUES(?,?,?,?)"
|
|
print('3)\n'+query+'\n')
|
|
new_id = input("Ingresa el ID: ")
|
|
new_name = input("Ingresa el nombre: ")
|
|
new_dept = input("Ingresa el departamento: ")
|
|
new_salary = input("Enter salary: ")
|
|
cursor.execute(query, (new_id,new_name,new_dept,new_salary))
|
|
db.commit()
|
|
query = "SELECT * FROM empleados"
|
|
cursor.execute(query)
|
|
print('4)\n'+query+'\n')
|
|
print(cursor.fetchall())
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = "SELECT * FROM empleados"
|
|
print('5)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
for x in cursor.fetchall():
|
|
print(x)
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = "SELECT * FROM empleados ORDER BY nombre"
|
|
print('6)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
|
|
for x in cursor.fetchall():
|
|
print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = "SELECT * FROM empleados WHERE salario>20000"
|
|
print('7)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
|
|
for x in cursor.fetchall():
|
|
print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))
|
|
query = "SELECT * FROM empleados WHERE depto='Ventas'"
|
|
print('8)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
|
|
for x in cursor.fetchall():
|
|
print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
cursor.execute("DROP TABLE IF EXISTS deptos;")
|
|
query = """CREATE TABLE IF NOT EXISTS deptos(
|
|
id integer PRIMARY KEY,
|
|
admin text NOT NULL,
|
|
depto text NOT NULL);"""
|
|
cursor.execute(query)
|
|
sleep(0.1)
|
|
query = """INSERT INTO deptos(id,admin,depto)
|
|
VALUES("1","Rob","Ventas"),("2", "Zerio","TI")"""
|
|
cursor.execute(query)
|
|
query = """SELECT empleados.id, empleados.nombre, deptos.admin
|
|
FROM empleados, deptos WHERE empleados.depto=deptos.depto
|
|
AND empleados.salario >20000"""
|
|
print('9)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
print("ID".rjust(4), "NOMBRE".ljust(10), "ADMIN".ljust(8))
|
|
for x in cursor.fetchall():
|
|
print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8))
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = "SELECT * FROM empleados WHERE depto=?"
|
|
print('10)\n'+query+'\n')
|
|
que_depto = input("Ingresa un departmento (Ventas o TI): ")
|
|
cursor.execute(query,[que_depto])
|
|
print("ID".rjust(4), "NOMBRE".ljust(10), "DEPTO".ljust(8), "SUELDO".rjust(6))
|
|
for x in cursor.fetchall():
|
|
print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8), str(x[3]).rjust(6))
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = """SELECT empleados.id, empleados.nombre, deptos.admin
|
|
FROM empleados, deptos WHERE empleados.depto=deptos.depto;"""
|
|
print('11)\n'+query+'\n')
|
|
print("ID".rjust(4), "NOMBRE".ljust(10), "ADMIN".ljust(8))
|
|
for x in cursor.fetchall():
|
|
print(str(x[0]).rjust(4), x[1].ljust(10), x[2].ljust(8))
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
query = "UPDATE empleados SET nombre='Tony' WHERE id=1"
|
|
print('11)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
db.commit()
|
|
|
|
with sqlite3.connect("./db/compania.db") as db:
|
|
cursor = db.cursor()
|
|
cursor.execute("SELECT * FROM empleados WHERE id=1")
|
|
print(cursor.fetchone())
|
|
query = "DELETE FROM empleados WHERE id=1"
|
|
print('12)\n'+query+'\n')
|
|
cursor.execute(query)
|
|
db.commit()
|