sqlalchemy

sqlalchemy.py

https://chat.openai.com/share/3f1472a2-ff59-4b88-bec4-36c75421e3ed
"""
from sqlalchemy import (
    Column, BigInteger, Integer, String, Text, ForeignKey, Uuid, Enum, DateTime, CheckConstraint,
)

from sqlalchemy import func, select
from sqlalchemy.sql.expression import (
    case as sql_case,
    cast as sql_cast,
    select as sql_select,
    true as sql_true,
    false as sql_false,
    null as sql_null,
    and_ as sql_and,
    or_ as sql_or,
)
"""

obj = db.query(Books).all()
"""
SELECT
book.title AS book_title, book.description AS book_description, book.rating AS book_rating, book.author_id AS book_author_id, book.id AS book_id
FROM book
"""

db.query(Books.title).all()
"""
SELECT book.title AS book_title FROM book
"""

# `filter`: work with bool and complex chaining
# `filter_by`: key as field

db.query(Books).filter(Books.title=="?")
"""
SELECT book.title AS book_title, book.description AS book_description, book.rating AS book_rating, book.author_id AS book_author_id, book.id AS book_id
FROM book
WHERE book.title = ?"
"""

db.query(Books).filter_by(title="?")
"""
SELECT book.title AS book_title, book.description AS book_description, book.rating AS book_rating, book.author_id AS book_author_id, book.id AS book_id
FROM book
WHERE book.title = ?
"""

db.query(Books).filter_by(title="?").filter(Books.id=="?")
"""
SELECT book.title AS book_title, book.description AS book_description, book.rating AS book_rating, book.author_id AS book_author_id, book.id AS book_id
FROM book
WHERE book.title = ? AND book.id = ?
"""

.one_or_none()  # will return error if more than 1 row found
.first()  # LIMIT 1 OFFSET 0
.last()  # 

# .value() or .values() --DEPRICATED--> Query.with_entities() + Query.scalar()


# eagerly joined
query(User).options(joinedload(User.orders))

# eagerly load chaining
query(A).options(
    joinedload(A.bs, innerjoin=False)
     .joinedload(B.cs, innerjoin=True)
)


sess.query(Order).\
        join(Order.user).\
        options(contains_eager(Order.user))
"""
.options(contains_eager(Order.user)):

After performing the join, 
this part of the query specifies an option to eagerly load the related User entity along with each Order.
"""


`populate_existing`: allows you to populate an object with data from the db if it already exists in the session. 
This can be useful when you have objects in memory that might have been updated in the database by another session, and you want to refresh them with the latest data.
"""
sess.query(User).\
    join(User.addresses).\
    filter(Address.email_address.like('%@aol.com')).\
    options(contains_eager(User.addresses)).\
    populate_existing()
"""

`defaultload`: sets the default loading strategy for a relationship, you can still override it on a per-query basis using other loading options such as
joinedload, subqueryload, or selectinload

extend `defaultload()` can also set column-level options on a related class, namely that of
`defer()` (exclude) and `undefer()` (include)
"""
obj = db.query(Books).options(
    defaultload(Books.author).
    defer(Author.name).
    undefer(Author.id)
)"""