| PolarSPARC |
Introduction to SQLAlchemy :: Part - 2
| Bhaskar S | 04/17/2020 |
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:
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:
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.
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:
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