92 lines
2.2 KiB
Python
92 lines
2.2 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
Acceso a SQLite desde Python (CREATE + READ)
|
|
Equivalente al ejemplo Java: acceso-a-sqlite-via-java.jar
|
|
"""
|
|
|
|
import sqlite3
|
|
import os
|
|
|
|
DB_NAME = "dam.db"
|
|
|
|
|
|
def conectar():
|
|
conn = sqlite3.connect(DB_NAME)
|
|
print(f"Conexión establecida: {os.path.abspath(DB_NAME)}")
|
|
return conn
|
|
|
|
|
|
def crear_tablas(conn):
|
|
cursor = conn.cursor()
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS profesores (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
nombre TEXT NOT NULL,
|
|
asignatura TEXT NOT NULL
|
|
)
|
|
""")
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS alumnos (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
nombre TEXT NOT NULL,
|
|
edad INTEGER NOT NULL,
|
|
id_profesor INTEGER,
|
|
FOREIGN KEY (id_profesor) REFERENCES profesores(id)
|
|
)
|
|
""")
|
|
conn.commit()
|
|
print("Tablas creadas exitosamente.")
|
|
|
|
|
|
def insertar_datos(conn):
|
|
cursor = conn.cursor()
|
|
|
|
profesores = [
|
|
("Pedro García", "Matemáticas"),
|
|
("Laura Martínez", "Física"),
|
|
("José Rodríguez", "Química"),
|
|
]
|
|
cursor.executemany("INSERT INTO profesores (nombre, asignatura) VALUES (?, ?)", profesores)
|
|
|
|
alumnos = [
|
|
("Carlos Pérez", 20, 1),
|
|
("Ana López", 22, 2),
|
|
("Luis Fernández", 21, 3),
|
|
]
|
|
cursor.executemany("INSERT INTO alumnos (nombre, edad, id_profesor) VALUES (?, ?, ?)", alumnos)
|
|
|
|
conn.commit()
|
|
print("Datos insertados.")
|
|
|
|
|
|
def consultar(conn):
|
|
cursor = conn.cursor()
|
|
cursor.execute("""
|
|
SELECT a.nombre AS alumno, p.nombre AS profesor
|
|
FROM alumnos a
|
|
JOIN profesores p ON a.id_profesor = p.id
|
|
""")
|
|
print("\nAlumnos y sus profesores:")
|
|
for alumno, profesor in cursor.fetchall():
|
|
print(f" Alumno: {alumno} | Profesor: {profesor}")
|
|
|
|
|
|
def main():
|
|
print("Hello and welcome!\n")
|
|
|
|
if os.path.exists(DB_NAME):
|
|
os.remove(DB_NAME)
|
|
|
|
conn = conectar()
|
|
try:
|
|
crear_tablas(conn) # CREATE estructura
|
|
insertar_datos(conn) # CREATE datos
|
|
consultar(conn) # READ
|
|
finally:
|
|
conn.close()
|
|
print("\nConexión cerrada.")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|