Introduction to SQLALchemy ORM – WARNING! – SQLAlchemy

View Github Repository Open presentation in a new window

jasonamyers

See all presentation from jasonamyers

Introduction to SQLALchemy ORM – WARNING! – SQLAlchemy

0 0

pygotham-intro-sqlalchemy-orm

On Github jasonamyers / pygotham-intro-sqlalchemy-orm

Introduction to SQLALchemy ORM

Created by Jason A Myers / @jasonamyers

WARNING!

SQLAlchemy

  • Core - Schema centric
  • ORM - User Model

Installing

pip install sqlalchemy

Connecting and Establishing a Session

Connecting

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:')

Establishing a Session

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)

session = Session()
The session is the way SQLAlchemy ORM interacts with the database. It wraps a database connection via an engine, and provides an identity map for objects that you load via the session or associate with the session. The identity map is a cache like data structure that contains a unique list of objects determined by the object’s table and primary key. A session also wraps a transaction, and that transaction will be open until the Session is committed or rolled back

Defining Models

Model Base

Declarative Base

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

Cookie Model

from sqlalchemy import Column, Integer, Numeric, String

class Cookie(Base):
    __tablename__ = 'cookies'

    cookie_id = Column(Integer, primary_key=True)
    cookie_name = Column(String(50), index=True)
    cookie_recipe_url = Column(String(255))
    cookie_sku = Column(String(55))
    quantity = Column(Integer())
    unit_cost = Column(Numeric(12, 2))
Inherit from the declarative_base Contain __tablename__ Contain 1+ columns Have a primary key

Persisting our Table

Base.metadata.create_all(engine)

Inserting Data

Adding a Cookie

cc_cookie = Cookie(cookie_name='chocolate chip',
                   cookie_recipe_url='http://some.aweso.me/cookie/recipe.html',
                   cookie_sku='CC01',
                   quantity=12,
                   unit_cost=0.50)

Adding to Session

session.add(cc_cookie)
session.commit()

What happened on the Database

INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO cookies (cookie_name,
cookie_recipe_url, cookie_sku, quantity, unit_cost) VALUES (?, ?, ?, ?, ?)

INFO:sqlalchemy.engine.base.Engine:('chocolate chip',
'http://some.aweso.me/cookie/recipe.html', 'CC01', 12, 0.5)

INFO:sqlalchemy.engine.base.Engine:COMMIT

Accessing Attributes

print(cc_cookie.cookie_id)

1

Bulk Inserts

c1 = Cookie(cookie_name='peanut butter',
            cookie_recipe_url='http://some.aweso.me/cookie/peanut.html',
            cookie_sku='PB01',
            quantity=24,
            unit_cost=0.25)
c2 = Cookie(cookie_name='oatmeal raisin',
            cookie_recipe_url='http://some.okay.me/cookie/raisin.html',
            cookie_sku='EWW01',
            quantity=100,
            unit_cost=1.00)
session.bulk_save_objects([c1,c2])
session.commit()

Bulk Insert Differences

c1.cookie_id
Speed with tradeoff - Relationships are not respected - Objects are not connected to the session - Primary Keys not fetched - No events triggered

Queries

All the COOKIES!

cookies = session.query(Cookie).all()
print(cookies)

[Cookie(cookie_name='chocolate chip',
        cookie_recipe_url='http://some.aweso.me/cookie/recipe.html',
        cookie_sku='CC01', quantity=12, unit_cost=0.50),
 Cookie(cookie_name='peanut butter',
        cookie_recipe_url='http://some.aweso.me/cookie/peanut.html',
        cookie_sku='PB01', quantity=24, unit_cost=0.25),
 Cookie(cookie_name='oatmeal raisin',
        cookie_recipe_url='http://some.okay.me/cookie/raisin.html',
        cookie_sku='EWW01', quantity=100, unit_cost=1.00)]

All the COOKIES! - Iterator

for cookie in session.query(Cookie):
    print(cookie)

Cookie(cookie_name='chocolate chip',
        cookie_recipe_url='http://some.aweso.me/cookie/recipe.html',
        cookie_sku='CC01', quantity=12, unit_cost=0.50)
Cookie(cookie_name='peanut butter',
        cookie_recipe_url='http://some.aweso.me/cookie/peanut.html',
        cookie_sku='PB01', quantity=24, unit_cost=0.25)
Cookie(cookie_name='oatmeal raisin',
        cookie_recipe_url='http://some.okay.me/cookie/raisin.html',
        cookie_sku='EWW01', quantity=100, unit_cost=1.00)

Particular Attributes

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

('chocolate chip', 12)

Order by

for cookie in session.query(Cookie).order_by(Cookie.quantity):
    print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))

  12 - chocolate chip
  24 - peanut butter
 100 - oatmeal raisin

Decending

from sqlalchemy import desc
for cookie in session.query(Cookie).order_by(desc(Cookie.quantity)):
      print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))

Limiting

query = session.query(Cookie).order_by(Cookie.quantity).limit(2)
print([result.cookie_name for result in query])

['chocolate chip', 'peanut butter']

Database Functions

from sqlalchemy import func

inv_count = session.query(func.sum(Cookie.quantity)).scalar()
print(inv_count)

136
Returns the first element of the first result, None if there is no result, or an error if there is more than one result.

Database Functions Count

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

(3, 0)

Labeling

rec_count = session.query(func.count(Cookie.cookie_name) \
                          .label('inventory_count')).first()
print(rec_count.keys())
print(rec_count.inventory_count)

['inventory_count']
5

filter_by

record = session.query(Cookie). \
             filter_by(cookie_name='chocolate chip').first()
print(record)

Cookie(cookie_name='chocolate chip',
       cookie_recipe_url='http://some.aweso.me/cookie/recipe.html',
       cookie_sku='CC01', quantity=12, unit_cost=0.50)

filter

record = session.query(Cookie). \
             filter(Cookie.cookie_name == 'chocolate chip').first()
print(record)

ClauseElements

query = session.query(Cookie).filter(
            Cookie.cookie_name.like('%chocolate%'))
for record in query:
    print(record.cookie_name)

chocolate chip
ClauseElements are just an entity we use in a clause, and they are typically columns in a table; however, unlike columns, ClauseElements come with many additional capabil‐ ities.

ClauseElement Methods

  • between(cleft, cright) - Find where the column is between cleft and cright
  • distinct() - Find only unique values for column
  • in_([list]) - Find where the column is in the list
  • is_(None) - Find where the column is None (commonly used for Null checks with None)
  • contains('string') - Find where the column has 'string' in it (Case-sensitive)
  • endswith('string') - Find where the column ends with 'string' (Case-sensitive)
  • startswith('string') - Find where the column begins with 'string' (Case-sensitive)
  • ilike('string') - Find where the column is like 'string' (NOT Case-sensitive)

Operators

from sqlalchemy import cast
query = session.query(Cookie.cookie_name,
                      cast((Cookie.quantity * Cookie.unit_cost),
                           Numeric(12,2)).label('inv_cost'))
for result in query:
    print('{} - {}'.format(result.cookie_name, result.inv_cost))

chocolate chip - 6.00
peanut butter - 6.00
oatmeal raisin - 100.00

Conjunctions

from sqlalchemy import and_, or_, not_
query = session.query(Cookie).filter(
    or_(
        Cookie.quantity.between(10, 50),
        Cookie.cookie_name.contains('chip')
    )
)
for result in query:
    print(result.cookie_name)

chocolate chip
peanut butter

Updating Cookies

Updating Cookies

query = session.query(Cookie)
cc_cookie = query.filter(Cookie.cookie_name == "chocolate chip").first()

cc_cookie.quantity = cc_cookie.quantity + 120

session.commit()
print(cc_cookie.quantity)

132

Deleting Cookies

Deleting Cookies

query = session.query(Cookie)
query = query.filter(Cookie.cookie_name == "peanut butter")

dcc_cookie = query.one()
session.delete(dcc_cookie)
session.commit()

dcc_cookie = query.first()
print(dcc_cookie)

None
queries all the rows, and raises an exception if anything other than a single result is returned

Okay time for a breather

Relationships

Imports

from datetime import datetime
from sqlalchemy import DateTime, ForeignKey, Boolean
from sqlalchemy.orm import relationship, backref

User Model

class User(Base):
    __tablename__ = 'users'

    user_id = Column(Integer(), primary_key=True)
    username = Column(String(15), nullable=False, unique=True)
    email_address = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)
    password = Column(String(25), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

Order Model

class Order(Base):
    __tablename__ = 'orders'

    order_id = Column(Integer(), primary_key=True)
    user_id = Column(Integer(), ForeignKey('users.user_id'))
    shipped = Column(Boolean(), default=False)

    user =  relationship("User", backref=backref('orders', order_by=order_id))

LineItem Model

class LineItem(Base):
    __tablename__ = 'line_items'

    line_item_id = Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey('orders.order_id'))
    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))
    quantity = Column(Integer())
    extended_cost = Column(Numeric(12, 2))

    order = relationship("Order", backref=backref('line_items', order_by=line_item_id))
    cookie = relationship("Cookie", uselist=False)

Persist them

Base.metadata.create_all(engine)

Defining a user

cookiemon = User(username='cookiemon',
                 email_address='mon@cookie.com',
                 phone='111-111-1111',
                 password='password')

session.add(cookiemon)

session.commit()

Setting up an Order

o1 = Order()
o1.user = cookiemon
session.add(o1)

Preparing Line Items

cc = session.query(Cookie).filter(Cookie.cookie_name ==
                                  "chocolate chip").one()
line1 = LineItem(cookie=cc, quantity=2, extended_cost=1.00)

pb = session.query(Cookie).filter(Cookie.cookie_name ==
                                  "oatmeal raisin").one()
line2 = LineItem(quantity=12, extended_cost=3.00)
line2.cookie = pb

Associate Order and Line Items

o1.line_items.append(line1)
o1.line_items.append(line2)

session.commit()

Using Relationships in Queries

query = session.query(Order.order_id, User.username, User.phone,
                      Cookie.cookie_name, LineItem.quantity,
                      LineItem.extended_cost)
query = query.join(User).join(LineItem).join(Cookie)
results = query.filter(User.username == 'cookiemon').all()
print(results)

[(1, 'cookiemon', '111-111-1111', 'chocolate chip', 2, Decimal('1.00')),
 (1, 'cookiemon', '111-111-1111', 'oatmeal raisin', 12, Decimal('3.00'))]

Another Example

query = session.query(User.username, func.count(Order.order_id))
query = query.outerjoin(Order).group_by(User.username)
for row in query:
    print(row)

('cookiemon', 1)

What other things are out there?

  • Automap
  • Geospatial Queries

Questions

Jason Myers / @jasonamyers / Essential SQLAlchemy http://bit.ly/jam-sqlalchemy-orm

Introduction to SQLALchemy ORM Created by Jason A Myers / @jasonamyers