Introduction to SQLAlchemy :: Part - 2

Overview

In Part - 1 of this series, we focused on basic capabilities of the Core layer for interacting with PostgreSQL database.

In this article, we will explore the Object Relational Mapping (ORM) layer for interacting with the SQL database.

The ORM layer allows one to associate a user defined Python class (domain model) with a database table. Object instances of the class correspond to the rows in the database table. Any changes made to state in the object instances, transparently synchronizes with the corresponding rows in the database table.

In addition, the ORM layer allows one to express database queries on tables as well as relationships between tables (such as one-to-one, on-to-many, many-to-one, many-to-many) in terms of the user defined classes.

Hands-on with SQLAlchemy ORM

In Part - 1, we already created a simple database table called customer. In this demonstration, we will create two user defined classes - one to represent the existing database table customer and the other to represent a new database table account (with a one-to-many relationship between the customer and the account tables. The following diagram illustrates this relationship:

The classes Customer and Account defined in the following Python program (ex_sa_05.py) correspond to the database tables customer and account respectively:

from datetime import datetime
from sqlalchemy import Column, DateTime, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Customer(Base):
    __tablename__ = "customer"

    id = Column(Integer, autoincrement=True, primary_key=True)
    first_name = Column(String(25), nullable=False)
    last_name = Column(String(25), nullable=False, index=True)
    email = Column(String(50))
    mobile = Column(String(10))

    def __repr__(self):
        return "[Customer: id=%d, first_name=%s, last_name=%s, email=%s]" % \
               (self.id, self.first_name, self.last_name, self.email)


class Account(Base):
    __tablename__ = "account"

    acct_no = Column(Integer, primary_key=True)
    acct_name = Column(String(50), nullable=False)
    acct_open_dt = Column(DateTime(), default=datetime.now)
    acct_update_dt = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
    cust_id = Column(Integer, ForeignKey('customer.id'))

    customer = relationship("Customer", backref='accounts')

    def __repr__(self):
        return "[Account: acct_no=%d, acct_name=%s, acct_open_dt=%s, acct_update_dt=%s, customer=%s]" % \
               (self.acct_no, self.acct_name, self.acct_open_dt, self.acct_update_dt, self.customer.last_name)

The following are brief descriptions for some of the Python classes and methods:

• declarative_base() :: Method that creates the ORM object model base class, which needs be extended by the user defined class to create the domain model object

• __tablename__ :: Internal attribute that specifies the database table name this user defined class represents

• relationship() :: Method that allows one to establish the ORM domain model object relationship

• backref :: Keyword that automatically adds the specified name as the attribute in the related domain model object

The user defined domain model class will contain one or more Column attributes that represent the database columns. One *DOES NOT* have to supply the column name as the first argument to the Column constructor. Instead, the attribute name will be used as the column name.

Also, one *MUST* ensure there is at least one attribute marked as a primary key, so as to uniquely identify and associate an instance of the class with a specific row in the database table.

On the one-to-many relationship between Customer and Account domain objects, one *MUST* have a foreign key attribute Column in the Account class. In addition, one *SHOULD* specify the backref parameter when indicating the related domain model object. In this example, the Account object can access the related Customer object via the customer attribute. Since we specified the backref parameter with the name accounts, it will be implicitly accessible as an attribute via the related Customer object.

In following Python program (ex_sa_06.py), the method create_account_table creates the account database table and the method insert_account_recs inserts *3* sample accounts:

import logging

from sqlalchemy.engine import Engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker
from SQLAlchemy.ex_sa_00 import create_db_engine
from SQLAlchemy.ex_sa_05 import Base, Account

logging.basicConfig(format='%(asctime)s - %(message)s', level=logging.INFO)


def create_account_table(engine: Engine) -> bool:
    status = False

    if not engine.dialect.has_table(engine, 'account'):
        Base.metadata.create_all(db_engine)

        logging.info("Created the account table !!!")

        status = True
    else:
        logging.info("The account table already exists !!!")

    return status


def insert_account_recs(engine: Engine):
    if engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        try:
            ad_acct = Account(acct_no=1001, acct_name='Alice Trade Account', cust_id=1)
            session.add(ad_acct)
            session.commit()

            logging.info("Inserted account for Alice")
        except SQLAlchemyError as e:
            logging.error(e)

        try:
            bb_acct = Account(acct_no=1002, acct_name='Bob Credit Account', cust_id=2)
            session.add(bb_acct)
            session.commit()

            logging.info("Inserted account for Bob")
        except SQLAlchemyError as e:
            logging.error(e)

        try:
            cd_acct = Account(acct_no=1003, acct_name='Charlie Trade Account', cust_id=3)
            session.add(cd_acct)
            session.commit()

            logging.info("Inserted account for Charlie")
        except SQLAlchemyError as e:
            logging.error(e)

        session.close()
    else:
        logging.info("The account table *DOES NOT* exists !!!")


if __name__ == "__main__":
    db_engine = create_db_engine()
    if create_account_table(db_engine):
        insert_account_recs(db_engine)

The following are brief descriptions for some of the Python classes and methods:

• Base.metadata.create_all() :: Method that creates database table(s) for all the domain object class(es) in the metadata if not already present

• Session :: An object that manages the database persistence operations of the ORM domain model object(s)

• sessionmaker() :: Factory method that creates an instance of Session object

• Session.add() :: Method to add the specified domain model object into the Session object for persistence

• Session.commit() :: Method to flush and commit all the changes to the Session object

To run the Python program ex_sa_06.py, execute the following command:

$ python ex_sa_06.py

The following would be a typical output:

Output.1

2020-04-17 21:30:53,386 - Postgres database url: postgres://polarsparc:polarsparc$123@localhost:5432/my_test_db
2020-04-17 21:30:53,447 - Connected to the Postgres database !!!
2020-04-17 21:30:53,473 - Created the account table !!!
2020-04-17 21:30:53,491 - Inserted account for Alice
2020-04-17 21:30:53,494 - Inserted account for Bob
2020-04-17 21:30:53,496 - Inserted account for Charlie

The following Python program (ex_sa_07.py) demonstrates the CRUD (Create, Read, Update, Delete) operations on the account database table.

The method create_dummy_account first creates a dummy customer record and then creates a dummy account record associated with the dummy customer record, the method query_dummy_account reads the dummy account record, the method update_dummy_account updates the dummy account record, and finally the method delete_dummy_account deletes the dummy account and customer records.

import logging

from sqlalchemy.engine import Engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError
from SQLAlchemy.ex_sa_00 import create_db_engine
from SQLAlchemy.ex_sa_05 import Customer, Account

logging.basicConfig(format='%(asctime)s - %(message)s', level=logging.INFO)


def create_dummy_account(engine: Engine):
    if engine.dialect.has_table(engine, 'customer') and engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        dummy_cust = Customer(first_name='Dummy', last_name='Joker', email='djoker@losers.io')

        try:
            session.add(dummy_cust)
            session.commit()

            logging.info("Inserted record for Dummy customer: %s" % dummy_cust)
        except SQLAlchemyError as e:
            logging.error(e)

        try:
            dummy_acct = Account(acct_no=9999, acct_name='Dummy Coin Account', cust_id=dummy_cust.id)
            session.add(dummy_acct)

            session.commit()

            logging.info("Inserted record for Dummy account: %s" % dummy_acct)
        except SQLAlchemyError as e:
            logging.error(e)

        session.close()
    else:
        logging.info("The customer and/or account table(s) *DOES NOT* exist !!!")


def query_dummy_account(engine: Engine):
    if engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        recs = session.query(Account).filter(Account.acct_no == 9999)
        if recs.count() == 1:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record for Dummy account *DOES NOT* exist !!!")

        session.close()
    else:
        logging.info("The account table *DOES NOT* exist !!!")


def update_dummy_account(engine: Engine):
    if engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        rec = session.query(Account).filter(Account.acct_no == 9999).first()
        if rec:
            rec.acct_name = 'Dummy Crypto Account'
        else:
            logging.info("Record for Dummy account *DOES NOT* exist !!!")

        session.commit()

        logging.info("Updated record for Dummy account")

        session.close()
    else:
        logging.info("The account table *DOES NOT* exist !!!")


def delete_dummy_account(engine: Engine):
    if engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        session.query(Account).filter(Account.acct_no == 9999).delete()

        session.commit()

        logging.info("Deleted record for Dummy account")

        session.query(Customer).filter(Customer.last_name == 'Joker').delete()

        session.commit()

        logging.info("Deleted record for Dummy customer")

        session.close()
    else:
        logging.info("The account table *DOES NOT* exist !!!")


if __name__ == "__main__":
    db_engine = create_db_engine()
    create_dummy_account(db_engine)
    query_dummy_account(db_engine)
    update_dummy_account(db_engine)
    query_dummy_account(db_engine)
    delete_dummy_account(db_engine)
    query_dummy_account(db_engine)

The following are brief descriptions for some of the Python classes and methods:

• query() :: Method that represents a database query operation and translates into a SELECT operation. It returns an object of type Query

• Query.filter() :: Method that applies the specified search criteria and translates into a WHERE clause. It returns an object of type Query

• Query.first() :: Method that returns the first result from the Query object

• Query.delete() :: Method that deletes row(s) represented by the Session object for persistence

• Session.commit() :: Method to flush and commit all the changes to the Query object

To run the Python program ex_sa_07.py, execute the following command:

$ python ex_sa_07.py

The following would be a typical output:

Output.2

2020-04-17 21:33:18,385 - Postgres database url: postgres://polarsparc:polarsparc$123@localhost:5432/my_test_db
2020-04-17 21:33:18,441 - Connected to the Postgres database !!!
2020-04-17 21:33:18,454 - Inserted record for Dummy customer: [Customer: id=49, first_name=Dummy, last_name=Joker, email=djoker@losers.io]
2020-04-17 21:33:18,460 - Inserted record for Dummy account: [Account: acct_no=9999, acct_name=Dummy Coin Account, acct_open_dt=2020-04-17 21:33:18.455890, acct_update_dt=2020-04-17 21:33:18.455898, customer=Joker]
2020-04-17 21:33:18,465 - [Account: acct_no=9999, acct_name=Dummy Coin Account, acct_open_dt=2020-04-17 21:33:18.455890, acct_update_dt=2020-04-17 21:33:18.455898, customer=Joker]
2020-04-17 21:33:18,471 - Updated record for Dummy account
2020-04-17 21:33:18,475 - [Account: acct_no=9999, acct_name=Dummy Crypto Account, acct_open_dt=2020-04-17 21:33:18.455890, acct_update_dt=2020-04-17 21:33:18.469849, customer=Joker]
2020-04-17 21:33:18,478 - Deleted record for Dummy account
2020-04-17 21:33:18,481 - Deleted record for Dummy customer
2020-04-17 21:33:18,484 - Record for Dummy account *DOES NOT* exist !!!

The following Python program (ex_sa_08.py) demonstrates various query operations on the account database table.

The method create_cust_accts first creates 3 test customer records and then creates 5 test account records associated with the 3 test customer records. The method query_cust_accts demonstrates the various query operations on the customer and account database tables. Finally, method delete_cust_accts removes all the test account and customer records.

import logging

from sqlalchemy import and_, func
from sqlalchemy.engine import Engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError
from SQLAlchemy.ex_sa_00 import create_db_engine
from SQLAlchemy.ex_sa_05 import Customer, Account

logging.basicConfig(format='%(asctime)s - %(message)s', level=logging.INFO)


def create_cust_accts(engine: Engine):
    if engine.dialect.has_table(engine, 'customer') and engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        c1 = Customer(first_name='David', last_name='Plumber', email='dplum@home.co', mobile='4445556666')
        c2 = Customer(first_name='Emily', last_name='Player', email='emilyp@cool.net')
        c3 = Customer(first_name='Frank', last_name='Doctor', email='fdoctor@pain.cc', mobile='5556667777')

        try:
            session.add_all([c1, c2, c3])
            session.commit()

            logging.info("Inserted record(s) for 3 customers: %s, %s, %s" % \
                         (c1.first_name, c2.first_name, c3.first_name))
        except SQLAlchemyError as e:
            logging.error(e)

        try:
            d1 = Account(acct_no=2001, acct_name='David Trade Account', cust_id=c1.id)
            d2 = Account(acct_no=2002, acct_name='David Cash Account', cust_id=c1.id)
            d3 = Account(acct_no=2003, acct_name='Emily Crypto Account', cust_id=c2.id)
            d4 = Account(acct_no=2004, acct_name='Frank Cash Account', cust_id=c3.id)
            d5 = Account(acct_no=2005, acct_name='Frank Credit Account', cust_id=c3.id)
            session.add_all([d1, d2, d3, d4, d5])

            session.commit()

            logging.info("Inserted record(s) for 5 accounts for: %s, %s, %s" % \
                         (c1.first_name, c2.first_name, c3.first_name))
        except SQLAlchemyError as e:
            logging.error(e)

        session.close()
    else:
        logging.info("The customer and/or account table(s) *DOES NOT* exist !!!")


def query_cust_accts(engine: Engine):
    if engine.dialect.has_table(engine, 'customer') and engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        logging.info("SQL => %s" % session.query(Customer.last_name, Customer.email))
        recs = session.query(Customer.last_name, Customer.email).all()
        if len(recs) > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in customer *DO NOT* exist !!!")
        logging.info("-------------------------")

        logging.info("SQL => %s" % session.query(Customer.last_name, Customer.email).\
                     filter(Customer.last_name.like('pl%')))
        recs = session.query(Customer.last_name, Customer.email).filter(Customer.last_name.like('pl%'))
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in customer for last_name like 'pl%' *DO NOT* exist !!!")
        logging.info("-------------------------")

        recs = session.query(Customer.last_name, Customer.email).filter(Customer.last_name.ilike('pl%'))
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in customer for last_name like (insensitive) 'pl%' *DO NOT* exist !!!")
        logging.info("-------------------------")

        logging.info("SQL => %s" % session.query(Customer.first_name, Customer.last_name, Customer.email).\
                     filter(Customer.last_name.in_(['Driver', 'Plumber'])))
        recs = session.query(Customer.first_name, Customer.last_name, Customer.email). \
            filter(Customer.last_name.in_(['Driver', 'Plumber']))
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in customer for last_name in ['Driver', 'Plumber'] *DO NOT* exist !!!")
        logging.info("-------------------------")

        logging.info("SQL => %s" % session.query(Customer).order_by(Customer.last_name))
        recs = session.query(Customer).order_by(Customer.last_name)
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in customer *DO NOT* exist !!!")
        logging.info("-------------------------")

        recs = session.query(Customer.first_name, Customer.last_name, Customer.mobile).filter(Customer.mobile != None)
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in customer with mobile *DO NOT* exist !!!")
        logging.info("-------------------------")

        logging.info("SQL => %s" % session.query(Account).limit(2))
        recs = session.query(Account).limit(2)
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in account *DO NOT* exist !!!")
        logging.info("-------------------------")

        logging.info("SQL => %s" % session.query(Customer.last_name, Account.acct_name).join(Account))
        recs = session.query(Customer.last_name, Account.acct_name).join(Account)
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in account.join(customer) *DO NOT* exist !!!")
        logging.info("-------------------------")

        logging.info("SQL => %s" % session.query(Customer.last_name, func.count(Account.cust_id).label('count')).\
                     join(Account).group_by(Customer.id).order_by('count'))
        recs = session.query(Customer.last_name, func.count(Account.cust_id).label('count')).\
                     join(Account).group_by(Customer.id).order_by('count')
        if recs.count() > 0:
            for r in recs:
                logging.info(r)
        else:
            logging.info("Record(s) in account.join(customer) group_by *DO NOT* exist !!!")
        logging.info("-------------------------")

        session.close()
    else:
        logging.info("The account/customer table(s) *DOES NOT* exist !!!")


def delete_cust_accts(engine: Engine):
    if engine.dialect.has_table(engine, 'customer') and engine.dialect.has_table(engine, 'account'):
        Session = sessionmaker(bind=engine)

        session = Session()

        session.query(Account).filter(and_(Account.acct_no >= 2001, Account.acct_no <= 2005)).delete()

        session.commit()

        logging.info("Deleted record(s) for account numbers: [2001 thru 2005]")

        session.query(Customer).filter(Customer.first_name == 'Frank').delete()
        session.query(Customer).filter(Customer.last_name.like('%Pl%')).delete(synchronize_session=False)

        session.commit()

        logging.info("Deleted record(s) for customers: [David, Emily, Frank]")

        session.close()
    else:
        logging.info("The account table *DOES NOT* exist !!!")


if __name__ == "__main__":
    db_engine = create_db_engine()
    create_cust_accts(db_engine)
    query_cust_accts(db_engine)
    delete_cust_accts(db_engine)

Notice the use of the argument synchronize_session=False in the delete() method.

!!! ATTENTION !!!

Ensure the flag synchronize_session=False is specified as an argument to the delete() method when records are filtered using some expression criteria. Else will encounter the following error:

sqlalchemy.exc.InvalidRequestError: Could not evaluate current criteria in Python: "Cannot evaluate BinaryExpression with operator <function like_op at 0x...>". Specify 'fetch' or False for the synchronize_session parameter

The following are brief descriptions for some of the Python methods:

• query(D.c1, D.c2) :: Method that represents a database query operation selecting the columns c1 and c2 on the database table represented by the domain object D. This translates into SELECT c1, c2 ON d operation. It returns an object of type Query

• Query.all() :: Method that returns all the results from the Query object

• Query.limit(N) :: Method that limits the number of returned results only only N entries from the Query object

• Query.filter(D.c2.like('P')) :: Method that filters the column c2 values of the database table represented by the domain object D using the case-sensitive LIKE match using the pattern P. It returns an object of type Query

• Query.filter(D.c2.ilike('P')) :: Method that filters the column c2 values of the database table represented by the domain object D using the case-insensitive LIKE match using the pattern P. It returns an object of type Query

• Query.filter(D.c2.in_([v1, v2])) :: Method that filters the column c2 values of the database table represented by the domain object D using the IN clause for the desired values v1 and v2 specified as a list. It returns an object of type Query

• query(D1).join(D2) :: Method that returns all the columns and all the rows from the JOIN operation on the database tables represented by the domain objects D1 and D2. It returns an object of type Query

To run the Python program ex_sa_08.py, execute the following command:

$ python ex_sa_08.py

The following would be a typical output:

Output.3

2020-04-17 21:35:38,345 - Postgres database url: postgres://polarsparc:polarsparc$123@localhost:5432/my_test_db
2020-04-17 21:35:38,403 - Connected to the Postgres database !!!
2020-04-17 21:35:38,420 - Inserted record(s) for 3 customers: David, Emily, Frank
2020-04-17 21:35:38,427 - Inserted record(s) for 5 accounts for: David, Emily, Frank
2020-04-17 21:35:38,430 - SQL => SELECT customer.last_name AS customer_last_name, customer.email AS customer_email 
FROM customer
2020-04-17 21:35:38,431 - ('Doctor', 'alice.d@timbuk2.do')
2020-04-17 21:35:38,431 - ('Builder', 'bbuilder@nomansland.bu')
2020-04-17 21:35:38,431 - ('Driver', 'charlie.driver@vehicles.ve')
2020-04-17 21:35:38,431 - ('Plumber', 'dplum@home.co')
2020-04-17 21:35:38,431 - ('Player', 'emilyp@cool.net')
2020-04-17 21:35:38,431 - ('Doctor', 'fdoctor@pain.cc')
2020-04-17 21:35:38,431 - -------------------------
2020-04-17 21:35:38,432 - SQL => SELECT customer.last_name AS customer_last_name, customer.email AS customer_email 
FROM customer 
WHERE customer.last_name LIKE %(last_name_1)s
2020-04-17 21:35:38,434 - Record(s) in customer for last_name like 'pl%' *DO NOT* exist !!!
2020-04-17 21:35:38,434 - -------------------------
2020-04-17 21:35:38,437 - ('Plumber', 'dplum@home.co')
2020-04-17 21:35:38,437 - ('Player', 'emilyp@cool.net')
2020-04-17 21:35:38,437 - -------------------------
2020-04-17 21:35:38,438 - SQL => SELECT customer.first_name AS customer_first_name, customer.last_name AS customer_last_name, customer.email AS customer_email 
FROM customer 
WHERE customer.last_name IN (%(last_name_1)s, %(last_name_2)s)
2020-04-17 21:35:38,442 - ('Charlie', 'Driver', 'charlie.driver@vehicles.ve')
2020-04-17 21:35:38,442 - ('David', 'Plumber', 'dplum@home.co')
2020-04-17 21:35:38,442 - -------------------------
2020-04-17 21:35:38,442 - SQL => SELECT customer.id AS customer_id, customer.first_name AS customer_first_name, customer.last_name AS customer_last_name, customer.email AS customer_email, customer.mobile AS customer_mobile 
FROM customer ORDER BY customer.last_name
2020-04-17 21:35:38,445 - [Customer: id=2, first_name=Bob, last_name=Builder, email=bbuilder@nomansland.bu]
2020-04-17 21:35:38,445 - [Customer: id=1, first_name=Alice, last_name=Doctor, email=alice.d@timbuk2.do]
2020-04-17 21:35:38,445 - [Customer: id=52, first_name=Frank, last_name=Doctor, email=fdoctor@pain.cc]
2020-04-17 21:35:38,445 - [Customer: id=3, first_name=Charlie, last_name=Driver, email=charlie.driver@vehicles.ve]
2020-04-17 21:35:38,445 - [Customer: id=51, first_name=Emily, last_name=Player, email=emilyp@cool.net]
2020-04-17 21:35:38,445 - [Customer: id=50, first_name=David, last_name=Plumber, email=dplum@home.co]
2020-04-17 21:35:38,445 - -------------------------
2020-04-17 21:35:38,448 - ('Alice', 'Doctor', '1112223333')
2020-04-17 21:35:38,448 - ('Charlie', 'Driver', '2223334444')
2020-04-17 21:35:38,448 - ('David', 'Plumber', '4445556666')
2020-04-17 21:35:38,448 - ('Frank', 'Doctor', '5556667777')
2020-04-17 21:35:38,448 - -------------------------
2020-04-17 21:35:38,449 - SQL => SELECT account.acct_no AS account_acct_no, account.acct_name AS account_acct_name, account.acct_open_dt AS account_acct_open_dt, account.acct_update_dt AS account_acct_update_dt, account.cust_id AS account_cust_id 
FROM account 
 LIMIT %(param_1)s
2020-04-17 21:35:38,452 - [Account: acct_no=1001, acct_name=Alice Trade Account, acct_open_dt=2020-04-17 21:30:53.483108, acct_update_dt=2020-04-17 21:30:53.483124, customer=Doctor]
2020-04-17 21:35:38,453 - [Account: acct_no=1002, acct_name=Bob Credit Account, acct_open_dt=2020-04-17 21:30:53.492095, acct_update_dt=2020-04-17 21:30:53.492107, customer=Builder]
2020-04-17 21:35:38,454 - -------------------------
2020-04-17 21:35:38,455 - SQL => SELECT customer.last_name AS customer_last_name, account.acct_name AS account_acct_name 
FROM customer JOIN account ON customer.id = account.cust_id
2020-04-17 21:35:38,457 - ('Doctor', 'Alice Trade Account')
2020-04-17 21:35:38,457 - ('Builder', 'Bob Credit Account')
2020-04-17 21:35:38,457 - ('Driver', 'Charlie Trade Account')
2020-04-17 21:35:38,457 - ('Plumber', 'David Trade Account')
2020-04-17 21:35:38,457 - ('Plumber', 'David Cash Account')
2020-04-17 21:35:38,457 - ('Player', 'Emily Crypto Account')
2020-04-17 21:35:38,457 - ('Doctor', 'Frank Cash Account')
2020-04-17 21:35:38,457 - ('Doctor', 'Frank Credit Account')
2020-04-17 21:35:38,457 - -------------------------
2020-04-17 21:35:38,458 - SQL => SELECT customer.last_name AS customer_last_name, count(account.cust_id) AS count 
FROM customer JOIN account ON customer.id = account.cust_id GROUP BY customer.id ORDER BY count
2020-04-17 21:35:38,462 - ('Builder', 1)
2020-04-17 21:35:38,462 - ('Player', 1)
2020-04-17 21:35:38,462 - ('Doctor', 1)
2020-04-17 21:35:38,462 - ('Driver', 1)
2020-04-17 21:35:38,462 - ('Plumber', 2)
2020-04-17 21:35:38,462 - ('Doctor', 2)
2020-04-17 21:35:38,462 - -------------------------
2020-04-17 21:35:38,467 - Deleted record(s) for account numbers: [2001 thru 2005]
2020-04-17 21:35:38,470 - Deleted record(s) for customers: [David, Emily, Frank]

References

Introduction to SQLAlchemy :: Part - 1

SQLAlchemy Object Relational Tutorial

SQLAlchemy

PySheet SQLAlchemy

© PolarSPARC