def create_example_data():
session = db_session.factory()
manning = create_publisher("Manning Publications")
session.add(manning)
# one-to-many
bookA = create_book("The Mikado Method", "9781617291210", 245, manning)
session.add(bookA)
bookB = create_book("Specification by Example", "9781617290084", 249, manning)
session.add(bookB)
# many-to-many
authorA = create_author("Ola", "Ellnestam")
session.add(authorA)
authorB = create_author("Daniel", "Brolund")
session.add(authorB)
authorC = create_author("Gojko", "Adzic")
session.add(authorC)
bookA.authors.append(authorA)
bookA.authors.append(authorB)
bookB.authors.append(authorC)
# one-to-one
detail = create_book_details("477597.jpg", bookA)
session.add(detail)
session.commit()
return manning.id, bookA.id, bookB.id
def create_publisher_with_3_books():
session = db_session.factory()
manning = create_publisher("Manning Publications")
session.add(manning)
bookA = create_book("The Mikado Method", "9781617291210", 245, manning)
session.add(bookA)
bookB = create_book("Specification by Example", "9781617290084", 249, manning)
session.add(bookB)
bookC = create_book("Python Workout", "9781617295508", 248, manning)
session.add(bookC)
authorA = create_author("Ola", "Ellnestam")
session.add(authorA)
authorB = create_author("Daniel", "Brolund")
session.add(authorB)
authorC = create_author("Gojko", "Adzic")
session.add(authorC)
authorD = create_author("Reuven M.", "Lerner")
session.add(authorD)
bookA.authors.append(authorA)
bookA.authors.append(authorB)
bookB.authors.append(authorC)
bookC.authors.append(authorD)
session.commit()
return manning.id
def query_all():
session = db_session.factory()
for employe in session.query(Employee):
print(employe)
session.close()
def query_and_():
session = db_session.factory()
for employe in session.query(Employee).\
filter(and_(Employee.id == 7, Employee.last_name == "King")):
print(employe)
session.close()
def query_or_():
session = db_session.factory()
for employe in session.query(Employee).\
filter(or_(Employee.id == 2, Employee.id == 3)):
print(employe)
session.close()
def query_not_equals():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id != 1):
print(employe)
session.close()
def query_greather_than():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id > 5):
print(employe)
session.close()
def query_multiple_filter_in_one():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id == 7, Employee.last_name == "King"):
print(employe)
session.close()
def query_not_like():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.last_name.not_like("Pe%k")):
print(employe)
session.close()
def query_contains():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.last_name.contains("u")):
print(employe)
session.close()
def query_not_in():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id.not_in([1,2,3])):
print(employe)
session.close()
def query_in_negated():
session = db_session.factory()
for employe in session.query(Employee).\
filter(~Employee.id.in_([1,2,3])):
print(employe)
session.close()
def query_endswith():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.last_name.endswith("n")):
print(employe)
session.close()
def query_less_than():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id < 3):
print(employe)
session.close()
def query_with_python_or_():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id == 2 | Employee.id == 3):
print(employe)
session.close()
def query_with_python_and_fails():
session = db_session.factory()
for employe in session.query(Employee).\
filter(Employee.id == 7 & Employee.last_name == "King"):
print(employe)
# throws exception
session.close()
def load_employee(id):
session = db_session.factory()
employee = session.query(Employee) \
.filter(Employee.id == id) \
.first()
print(employee)
session.close()
def delete_employee(id):
session = db_session.factory()
employee = session.query(Employee) \
.filter(Employee.id == id) \
.first()
print(employee)
session.delete(employee)
session.commit()
def show_book_and_publisher(publisher_id):
session = db_session.factory()
publisher = session.query(Publisher).\
options(joinedload("books")).\
filter(Publisher.id == publisher_id).first()
for b in publisher.books:
print(f"{b.title} published by {b.publisher}")
session.close()
def show_book_details(bookA_id):
session = db_session.factory()
books = session.query(Book).\
options(joinedload("details")).\
filter(Book.id == bookA_id).all()
for b in books:
print(b)
print(b.details)
session.close()
def lazy_loading_throws_error(publisher_id):
session = db_session.factory()
publisher = session.query(Publisher).\
filter(Publisher.id == publisher_id).\
first()
session.close()
print(f"{publisher}:")
for book in publisher.books:
print(f"\t {book}:")
for author in book.authors:
print(f"\t\t {author}")
def update_employee(id):
session = db_session.factory()
employee = session.query(Employee) \
.filter(Employee.id == id) \
.first()
print(employee)
employee.last_name = "Lord"
session.commit()
print(employee)
def show_authors_of_book(bookA_id, bookB_id):
session = db_session.factory()
books = session.query(Book).\
options(joinedload("authors")).\
filter(Book.id.in_([bookA_id, bookB_id])).all()
for b in books:
print(b)
for a in b.authors:
print(f"\t{a}")
session.close()
def add_employee():
employee = Employee()
employee.last_name = "King"
employee.first_name = "Robert"
employee.birth_date = '1990-05-29'
print(employee)
session = db_session.factory()
session.add(employee)
session.commit()
print(employee)
return employee.id
def eager_loading_selectinload(publisher_id):
session = db_session.factory()
publisher = session.query(Publisher).\
options(selectinload("books").\
selectinload("authors")).\
filter(Publisher.id == publisher_id).\
first()
session.close()
print(f"{publisher}:")
for book in publisher.books:
print(f"\t {book}:")
for author in book.authors:
print(f"\t\t {author}")
