SQLAlchemy

Basics

Relational Database - Table
SQL
ORM - SQLAlchemy

Installing

pip install sqlalchemy

Connecting to DB

DB Engine

Connecting to PostgreSQL database
Engine requires DB info like path, user, pass etc.
Engine talks to DB

Establishing Session

Maps the objects we will define in python with rows in database table
Wraps the engine

Declarative Base

It allows creating models

Writing Models

Model Class

Has following properties-

Inherit from declarative base
Needs table name in DB
One or more Column names
One or more of those columns are primary keys

Create Table

Base.metadata.create_all(engine)

Inserting Data

Adding a Cookie

Single row insertion, Inefficient
Objects related to session

#Adding to session
session.add(cc_cookie)
session.commit()

#Accessing attributes
print(cc_cookie.cookie_id)

Bulk Insert

Multiple rows insertion, Efficient
Objects not related to session

#This won't work
c1.cookie_id

Queries

Get All Cookies

#Get all cookies together in the form of
#list of cookie objects
cookies = session.query(Cookie).all()
print(cookies)

#Get cookie objects one by one
for cookie in session.query(Cookie):
   print(cookie)

Get Particular Attributes

#Returns tuple, just returns the first in the list

print(session.query(Cookie.cookie_name, Cookie.quantity).first())

('chocolate chip', 12)

SORT

1. Ascending

2. Descending

Limiting

Complex Queries

Keep appending functions in the end to generate more complex queries!!

Sum

Count

rec_count = session.query(func.count(Cookie.cookie_name)).scalar()
print(rec_count)

Scalar() - Returns first element in the tuple returned

Database Functions

Clause Elements

Other Clause Elements -

between(cleft, cright)

in_([list])

is_(None)

Filter

Update/Delete

Update

cc_cookie is attached to session
modify any attributes and commit

Delete

one() - checks if exactly one row returned