149 lines
4.8 KiB
Python
149 lines
4.8 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
Crear relaciones entre modelos
|
|
|
|
"""
|
|
# Ej. se muestra una relación uno a muchos (un autor muchos libros):
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
from sqlalchemy import Column, Integer, String, Sequence, ForeignKey
|
|
from sqlalchemy.orm import sessionmaker, relationship
|
|
|
|
Base = declarative_base()
|
|
engine = create_engine('sqlite:///:memory:')
|
|
|
|
class Author(Base):
|
|
|
|
__tablename__ = 'author'
|
|
id = Column(Integer, Sequence('author_id_seq'), primary_key=True)
|
|
firstname = Column(String)
|
|
lastname = Column(String)
|
|
"""
|
|
Se crea una relación con el modelo Book, ordenado por book.id y
|
|
con referencia hacia atrás "author".
|
|
"""
|
|
# books = relationship("Book", order_by="Book.id", back_populates="author")
|
|
# CASCADE
|
|
books = relationship("Book",
|
|
order_by="Book.id",
|
|
back_populates="author",
|
|
cascade="all, delete, delete-orphan")
|
|
|
|
def __repr__(self):
|
|
return "{} {}".format(self.firstname, self.lastname)
|
|
|
|
|
|
class Book(Base):
|
|
|
|
__tablename__ = 'book'
|
|
id = Column(Integer, Sequence('book_id_seq'), primary_key=True)
|
|
isbn = Column(String)
|
|
title = Column(String)
|
|
description = Column(String)
|
|
"""
|
|
Se crea la columna de tipo entero, ForeignKey de author.id
|
|
"""
|
|
author_id = Column(Integer, ForeignKey('author.id'))
|
|
"""
|
|
Se crea la relación con el modelo Author, con una
|
|
referencia hacia atrás a "books".
|
|
"""
|
|
author = relationship("Author", back_populates="books")
|
|
|
|
def __repr__(self):
|
|
return "{}".format(self.title)
|
|
|
|
|
|
Base.metadata.create_all(engine)
|
|
Session = sessionmaker(bind=engine)
|
|
session = Session()
|
|
|
|
j_rowling = Author(firstname='Joanne', lastname='Rowling')
|
|
print(j_rowling.books)
|
|
|
|
j_rowling.books = [Book(isbn='9788498387087',
|
|
title='Harry Potter y la Piedra Filosofal',
|
|
description='La vida de Harry Potter cambia para siempre el ...'),
|
|
Book(isbn='9788498382679',
|
|
title='Harry Potter y la camara secreta',
|
|
description='Tras derrotar una vez mas a lord Voldemort, ...')]
|
|
|
|
print(j_rowling.books[1])
|
|
print(j_rowling.books[1].title)
|
|
|
|
session.add(j_rowling)
|
|
session.commit()
|
|
|
|
j_rowling = session.query(Author).filter_by(firstname='Joanne').one()
|
|
print(j_rowling.books)
|
|
|
|
"""
|
|
Consultas QUERYs
|
|
|
|
"""
|
|
print("Query #1:",
|
|
"Devuelve Autor y Libro para el isbn especuficado")
|
|
for an_author, a_book in session.query(Author, Book).\
|
|
filter(Author.id == Book.author_id).\
|
|
filter(Book.isbn == '9788498387087').\
|
|
all():
|
|
print(an_author, '\n', a_book)
|
|
|
|
print("Query #2:",
|
|
"Devuelve el Autor del libro con isbn 9788498387087\n",
|
|
session.query(Author).join(Book).filter(Book.isbn == '9788498387087').all())
|
|
|
|
print("Query #3:",
|
|
"Devuelve los autores de los libros CONDICIóN EXLPíCITA\n",
|
|
session.query(Author).join(Book, Author.id == Book.author_id).all())
|
|
|
|
print("Query #4:",
|
|
"Devuelve los autores y los libros, especificando la relación de izq. a derecha\n",
|
|
session.query(Author).join(Author.books).all())
|
|
|
|
print("Query #5:",
|
|
"Devuelve los Autores de los libros para una relación específica\n",
|
|
session.query(Author).join(Book, Author.books).all())
|
|
|
|
print("Query #6:",
|
|
"Busqueda de todos los Autores, usando un string\n",
|
|
session.query(Author).join('books').all())
|
|
|
|
# print("Query #7:",
|
|
# "Filtrado por autores con nombre 'Joél Ez'")
|
|
# stmt = exists().where(Book.author_id == Author.id)
|
|
# for name, in session.query(Author.firstname).filter(stmt):
|
|
# print(name)
|
|
"""
|
|
stmt = exists().where(Book.author_id == Author.id)
|
|
NameError: name 'exists' is not defined
|
|
"""
|
|
|
|
print("Query #8:",
|
|
"Devuelve el nombre del autor del libro usando un filtro 'any'")
|
|
for name, in session.query(Author.firstname).filter(Author.books.any()):
|
|
print(name)
|
|
|
|
print("Query #9:"
|
|
"Devuelve el nombre del autor, usando un filtro 'like', se le puede"
|
|
"definir una condición, ej."
|
|
"que el apellido contenga la cadena 'Row'")
|
|
for name, in session.query(Author.firstname).\
|
|
filter(Author.books.any(Author.lastname.like('%Row%'))):
|
|
print(name)
|
|
|
|
print("Query #10",
|
|
"Devuelve los libros donde el autor NO se llame 'Joanne'\n",
|
|
session.query(Book).filter(~Book.author.has(Author.firstname == 'Joanne')).all(),
|
|
'\n')
|
|
|
|
"""
|
|
Borrar objetos de la BD
|
|
|
|
"""
|
|
session.delete(j_rowling)
|
|
print("Autores con nombre Joanne :",
|
|
session.query(Author).filter_by(firstname='Joanne').count())
|
|
print("Libros según isbn 9788498387087 y 9788498382679 :",
|
|
session.query(Book).filter(Book.isbn.in_(['9788498387087', '9788498382679'])).count())
|