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_idQueries
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
Credits
SQLAlchemy
By Rajat Jain
