Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Sign up for free
Menu
Search
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Features
All features
Private URLs
Password Protection
Custom URLS
Scheduled publishing
Remove Branding
Restrict embedding
Deck Collections
Notes
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Explore
Featured decks
Featured speakers
Programming
Technology
Storyboards
Pricing
Search
Sign in
Sign up for free
The Ultimate Python Database Toolkit: SQLAlchem...
Search
Kod.io Linz
March 01, 2014
Programming
330
1
Share
Embed
Copy iframe code
Copy JS code
Copy link
Start on current slide
The Ultimate Python Database Toolkit: SQLAlchemy - Muhammet Sena Aydın
Kod.io Linz
March 01, 2014
Transcript
41 slides
SQLAlchemy SQLAlchemy The Python SQL Toolkit and Object Relational Mapper
SQLAlchemy SQLAlchemy Muhammet S. AYDIN Python Developer @ Metglobal @mengukagan
SQLAlchemy SQLAlchemy • No ORM Required • Mature • High
Performing • Non-opinionated • Unit of Work • Function based query construction • Modular
SQLAlchemy SQLAlchemy • Seperation of mapping & classes • Eager
loading & caching related objects • Inheritance mapping • Raw SQL
SQLAlchemy SQLAlchemy Drivers: PostgreSQL MySQL MSSQL SQLite Sybase Drizzle Firebird
Oracle
SQLAlchemy SQLAlchemy Core Engine Connection Dialect MetaData Table Column
SQLAlchemy SQLAlchemy Core Engine Starting point for SQLAlchemy app. Home
base for the database and it's API.
SQLAlchemy SQLAlchemy Core Connection Provides functionality for a wrapped DB-API
connection. Executes SQL statements. Not thread-safe.
SQLAlchemy SQLAlchemy Core Dialect Defnes the behavior of a specifc
database and DB-API combination. Query generation, execution, result handling, anything that differs from other dbs is handled in Dialect.
SQLAlchemy SQLAlchemy Core MetaData Binds to an Engine or Connection.
Holds the Table and Column metadata in itself.
SQLAlchemy SQLAlchemy Core Table Represents a table in the database.
Stored in the MetaData.
SQLAlchemy SQLAlchemy Core Column Represents a column in a database
table.
SQLAlchemy SQLAlchemy Core Creating an engine:
SQLAlchemy SQLAlchemy Core Creating tables Register the Table with MetaData.
Defne your columns. Call metadata.create_all(engine) or table.create(engine)
SQLAlchemy SQLAlchemy Core Creating tables
SQLAlchemy SQLAlchemy Core More on Columns Columns have some important
parameters. index=bool, nullable=bool, unique=bool, primary_key=bool, default=callable/scalar, onupdate=callable/scalar, autoincrement=bool
SQLAlchemy SQLAlchemy Core Column Types Integer, BigInteger, String, Unicode, UnicodeText,
Date, DateTime, Boolean, Text, Time and All of the SQL std types.
SQLAlchemy SQLAlchemy Core Insert insert = countries_table.insert().values( code='TR', name='Turkey') conn.execute(insert)
SQLAlchemy SQLAlchemy Core Select select([countries_table]) select([ct.c.code, ct.c.name]) select([ct.c.code.label('c')])
SQLAlchemy SQLAlchemy Core Select select([ct]).where(ct.c.region == 'Europe & Central Asia')
select([ct]).where(or_(ct.c.region.ilike('%euro pe%', ct.c.region.ilike('%asia%')))
SQLAlchemy SQLAlchemy Core Select A Little Bit Fancy select([func.count(ct.c.id).label('count'), ct.c.region]).group_by(ct.c.region).order_by('
count DESC') SELECT count(countries.id) AS count, countries.region FROM countries GROUP BY countries.region ORDER BY count DESC
SQLAlchemy SQLAlchemy Core Update ct.update().where(ct.c.id == 1).values(name='Turkey', code='TUR')
SQLAlchemy SQLAlchemy Core Cooler Update case_list = [(pt.c.id == photo_id,
index+1) for index, photo_id in enumerate(order_list)] pt.update().values(photo_order=case(case_list)) UPDATE photos SET photo_order=CASE WHEN (photos.id = :id_1) THEN :param_1 WHEN (photos.id = :id_2) THEN :param_2 END
SQLAlchemy SQLAlchemy Core Delete ct.delete().where(ct.c.id_in([60,71,80,97]))
SQLAlchemy SQLAlchemy Core Joins select([ct.c.name, dt.c.data]).select_from(ct.join(dt)).where(ct.c .code == 'TRY')
SQLAlchemy SQLAlchemy Core Joins select([ct.c.name, dt.c.data]).select_from(join(ct, dt, ct.c.id == dt.c.country_id)).where(ct.c.code
== 'TRY')
SQLAlchemy SQLAlchemy Core Func A SQL function generator with attribute
access. simply put: func.count() becomes COUNT().
SQLAlchemy SQLAlchemy Core Func select([func.concat_ws(“ -> “, ct.c.name, ct.c.code)]) SELECT
concat_ws(%(concat_ws_2)s, countries.name, countries.code) AS concat_ws_1 FROM countries
SQLAlchemy SQLAlchemy ORM - Built on top of the core
- Applied usage of the Expression Language - Class declaration - Table defnition is nested in the class
SQLAlchemy SQLAlchemy ORM Defnition
SQLAlchemy SQLAlchemy ORM Session Basically it establishes all connections to
the db. All objects are kept on it through their lifespan. Entry point for Query.
SQLAlchemy SQLAlchemy ORM Master / Slave Connection? master_session = sessionmaker(bind=engine1)
slave_session = sessionmaker(bind=engine2) Session = master_session() SlaveSession = slave_session()
SQLAlchemy SQLAlchemy ORM Querying Session.query(Country).flter(Country.name.s tartswith('Tur')).all() Session.query(func.count(Country.id)).one() Session.query(Country.name, Data.data).join(Data).all()
SQLAlchemy SQLAlchemy ORM Querying Session.query(Country).flter_by(id=1).updat e({“name”: “USA”}) Session.query(Country).flter(~Country.regio n.in_('Europe &
Central Asia')).delete()
SQLAlchemy SQLAlchemy ORM Relationships: One To Many
SQLAlchemy SQLAlchemy ORM Relationships: One To One
SQLAlchemy SQLAlchemy ORM Relationships: Many To Many
SQLAlchemy SQLAlchemy ORM Relationships: Many To Many
SQLAlchemy SQLAlchemy ORM Relationship Loading
SQLAlchemy SQLAlchemy ORM Relationship Loading
SQLAlchemy SQLAlchemy ORM More? http://sqlalchemy.org http://github.com/zzzeek/sqlalchemy irc.freenode.net #sqlalchemy
More Decks by Kod.io Linz
See All by Kod.io Linz
Kod.io Linz closing notes - Floor Drees
kodio_linz
0
200
Make yours and other people's life easier! Do support! - Mike Adolphs
kodio_linz
0
150
Objective-C for Rubyists - Mikael Konutgan
kodio_linz
0
180
How to become a better developer - Markus Prinz
kodio_linz
1
150
10 things I didn’t know about HTML, CSS, and JavaScript - Mathias Bynens
kodio_linz
5
370
Docker; The Shipping - Andreas Tiefenthaler
kodio_linz
1
170
How to Get More Women in Tech in 5 Steps - Anika Lindtner
kodio_linz
0
720
RubyMotion’s Secret Sauce - Joshua Ballenco
kodio_linz
0
330
Fly, you tools - Piotr Szotkowski
kodio_linz
1
300
Other Decks in Programming
See All in Programming
TS 7.0 で baseUrl が使えなくなった今、パスエイリアスをどうするか
bicstone
2
710
Distance field of Julia set
kinakomoti321
0
330
AI-Powered Domain-Driven Design
ziobrando
0
460
Engineering Biometric UX Across Android Versions | DroidKnights 2026
ryunen344
1
210
Apple Intelligenceとオンデバイス時代の到来
yahir0
0
250
조합이폭발하는디자인시스템이걸눈으로도테스트하라고요
giljongyu
0
770
Crash Auto Pipelines
veronikapj
1
430
Growing PicoRuby's Ecosystem on ESP32
yuuu
2
140
Augmenting AI with the Power of Jakarta EE
ivargrimstad
0
360
AIがコードを書いてくれる時代、我々は何を確かめるのか
yosuke_furukawa
PRO
13
6.1k
React Native経験者が Flutterを30⽇間触って驚いたこと
3saito
0
200
複数ドメインを一つのアプリに統合するFlutterの設計と運用_FlutterKaigi2026登壇資料(須永高弘)
takahirosunaga
0
200
Featured
See All Featured
WENDY [Excerpt]
tessaabrams
15
41k
Fantastic passwords and where to find them - at NoRuKo
philnash
53
4k
Hiding What from Whom? A Critical Review of the History of Programming languages for Music
tomoyanonymous
3
1.4k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
52
6.2k
16th Malabo Montpellier Forum Presentation
akademiya2063
PRO
0
470
Google's AI Overviews - The New Search
badams
0
1.7k
Mind Mapping
helmedeiros
1
440
Producing Creativity
orderedlist
PRO
348
41k
RailsConf 2023
tenderlove
30
1.6k
How Fast Is Fast Enough? [PerfNow 2025]
tammyeverts
3
980
The State of eCommerce SEO: How to Win in Today's Products SERPs - #SEOweek
aleyda
2
12k
Information Architects: The Missing Link in Design Systems
soysaucechin
1
1.3k