Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
The Ultimate Python Database Toolkit: SQLAlchem...
Search
Kod.io Linz
March 01, 2014
Programming
1
270
The Ultimate Python Database Toolkit: SQLAlchemy - Muhammet Sena Aydın
Kod.io Linz
March 01, 2014
Tweet
Share
More Decks by Kod.io Linz
See All by Kod.io Linz
Kod.io Linz closing notes - Floor Drees
kodio_linz
0
180
Make yours and other people's life easier! Do support! - Mike Adolphs
kodio_linz
0
110
Objective-C for Rubyists - Mikael Konutgan
kodio_linz
0
160
How to become a better developer - Markus Prinz
kodio_linz
1
130
10 things I didn’t know about HTML, CSS, and JavaScript - Mathias Bynens
kodio_linz
5
340
Docker; The Shipping - Andreas Tiefenthaler
kodio_linz
1
140
How to Get More Women in Tech in 5 Steps - Anika Lindtner
kodio_linz
0
660
RubyMotion’s Secret Sauce - Joshua Ballenco
kodio_linz
0
280
Fly, you tools - Piotr Szotkowski
kodio_linz
1
250
Other Decks in Programming
See All in Programming
AI コーディングエージェントの時代へ:JetBrains が描く開発の未来
masaruhr
2
220
脱Riverpod?fqueryで考える、TanStack Queryライクなアーキテクチャの可能性
ostk0069
0
570
AWS Summit Japan 2024と2025の比較/はじめてのKiro、今あなたは岐路に立つ
satoshi256kbyte
1
260
副作用と戦う PHP リファクタリング ─ ドメインイベントでビジネスロジックを解きほぐす
kajitack
3
490
[DevinMeetupTokyo2025] コード書かせないDevinの使い方
takumiyoshikawa
2
220
構文解析器入門
ydah
7
1.9k
コーディングエージェント概観(2025/07)
itsuki_t88
0
450
[SRE NEXT] 複雑なシステムにおけるUser Journey SLOの導入
yakenji
0
840
CDK引数設計道場100本ノック
badmintoncryer
2
590
ご注文の差分はこちらですか? 〜 AWS CDK のいろいろな差分検出と安全なデプロイ
konokenj
4
710
PHPUnitの限界をPlaywrightで補完するテストアプローチ
yuzneri
0
350
Go製CLIツールをnpmで配布するには
syumai
0
830
Featured
See All Featured
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
Designing Experiences People Love
moore
142
24k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
47
9.6k
Building Applications with DynamoDB
mza
95
6.5k
The Art of Programming - Codeland 2020
erikaheidi
54
13k
Let's Do A Bunch of Simple Stuff to Make Websites Faster
chriscoyier
507
140k
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
8
860
We Have a Design System, Now What?
morganepeng
53
7.7k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
44
2.4k
Code Reviewing Like a Champion
maltzj
524
40k
Building a Scalable Design System with Sketch
lauravandoore
462
33k
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
161
15k
Transcript
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