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}")