Downloaded 122 times
![SQLAlchemy
Core
Select
select([countries_table])
select([ct.c.code, ct.c.name])
select([ct.c.code.label('c')])](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-19-320.jpg)
![SQLAlchemy
Core
Select
select([ct]).where(ct.c.region ==
'Europe & Central Asia')
select([ct]).where(or_(ct.c.region.il
ike('%europe%',
ct.c.region.ilike('%asia%')))](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-20-320.jpg)
![SQLAlchemy
Core
Select A Little Bit Fancy
select([func.count(ct.c.id).label('co
unt'),
ct.c.region]).group_by(ct.c.region).o
rder_by('count DESC')
SELECT count(countries.id) AS count,
countries.region
FROM countries GROUP BY
countries.region ORDER BY count DESC](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-21-320.jpg)
![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_l
ist))
UPDATE photos SET photo_order=CASE WHEN
(photos.id = :id_1) THEN :param_1 WHEN
(photos.id = :id_2) THEN :param_2 END](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-23-320.jpg)
![SQLAlchemy
Core
Delete
ct.delete().where(ct.c.id_in([60,71,8
0,97]))](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-24-320.jpg)
![SQLAlchemy
Core
Joins
select([ct.c.name,
dt.c.data]).select_from(ct.join(dt)).
where(ct.c.code == 'TRY')](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-25-320.jpg)
![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')](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-26-320.jpg)
![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](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/85/An-introduction-to-SQLAlchemy-28-320.jpg)
![SQLAlchemy
Core
Select
select([countries_table])
select([ct.c.code, ct.c.name])
select([ct.c.code.label('c')])](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-19-2048.jpg)
![SQLAlchemy
Core
Select
select([ct]).where(ct.c.region ==
'Europe & Central Asia')
select([ct]).where(or_(ct.c.region.il
ike('%europe%',
ct.c.region.ilike('%asia%')))](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-20-2048.jpg)
![SQLAlchemy
Core
Select A Little Bit Fancy
select([func.count(ct.c.id).label('co
unt'),
ct.c.region]).group_by(ct.c.region).o
rder_by('count DESC')
SELECT count(countries.id) AS count,
countries.region
FROM countries GROUP BY
countries.region ORDER BY count DESC](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-21-2048.jpg)
![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_l
ist))
UPDATE photos SET photo_order=CASE WHEN
(photos.id = :id_1) THEN :param_1 WHEN
(photos.id = :id_2) THEN :param_2 END](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-23-2048.jpg)
![SQLAlchemy
Core
Delete
ct.delete().where(ct.c.id_in([60,71,8
0,97]))](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-24-2048.jpg)
![SQLAlchemy
Core
Joins
select([ct.c.name,
dt.c.data]).select_from(ct.join(dt)).
where(ct.c.code == 'TRY')](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-25-2048.jpg)
![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')](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-26-2048.jpg)
![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](https://image.slidesharecdn.com/sqlalchemy-140209172841-phpapp01/75/An-introduction-to-SQLAlchemy-28-2048.jpg)
More Related Content
Similar to An introduction to SQLAlchemy
An introduction to SQLAlchemy
- 1. SQLAlchemy The Python SQLToolkit and Object Relational Mapper
- 2. SQLAlchemy Muhammet S. AYDIN PythonDeveloper @ Metglobal @mengukagan
- 3. SQLAlchemy No ORM Required ●Mature ● High Performing ● Non-opinionated ● Unit of Work ● Function based query construction ● Modular ●
- 4. SQLAlchemy Seperation of mapping& classes ● Eager loading & caching related objects ● Inheritance mapping ● Raw SQL ●
- 5.
- 6.
- 7. SQLAlchemy Core Engine Starting point forSQLAlchemy app. Home base for the database and it's API.
- 8. SQLAlchemy Core Connection Provides functionality fora wrapped DB-API connection. Executes SQL statements. Not thread-safe.
- 9. SQLAlchemy Core Dialect Defines the behaviorof a specific database and DB-API combination. Query generation, execution, result handling, anything that differs from other dbs is handled in Dialect.
- 10. SQLAlchemy Core MetaData Binds to anEngine or Connection. Holds the Table and Column metadata in itself.
- 11. SQLAlchemy Core Table Represents a tablein the database. Stored in the MetaData.
- 12. SQLAlchemy Core Column Represents a columnin a database table.
- 13.
- 14. SQLAlchemy Core Creating tables Register theTable with MetaData. Define your columns. Call metadata.create_all(engine) or table.create(engine)
- 15.
- 16. SQLAlchemy Core More on Columns Columnshave some important parameters. index=bool, nullable=bool, unique=bool, primary_key=bool, default=callable/scalar, onupdate=callable/scalar, autoincrement=bool
- 17. SQLAlchemy Core Column Types Integer, BigInteger,String, Unicode, UnicodeText, Date, DateTime, Boolean, Text, Time and All of the SQL std types.
- 18.
- 19.
- 20. SQLAlchemy Core Select select([ct]).where(ct.c.region == 'Europe &Central Asia') select([ct]).where(or_(ct.c.region.il ike('%europe%', ct.c.region.ilike('%asia%')))
- 21. SQLAlchemy Core Select A LittleBit Fancy select([func.count(ct.c.id).label('co unt'), ct.c.region]).group_by(ct.c.region).o rder_by('count DESC') SELECT count(countries.id) AS count, countries.region FROM countries GROUP BY countries.region ORDER BY count DESC
- 22.
- 23. 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_l ist)) UPDATE photos SET photo_order=CASE WHEN (photos.id = :id_1) THEN :param_1 WHEN (photos.id = :id_2) THEN :param_2 END
- 24.
- 25.
- 26.
- 27. SQLAlchemy Core Func A SQL functiongenerator with attribute access. simply put: func.count() becomes COUNT().
- 28. 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
- 29. SQLAlchemy ORM - Built ontop of the core - Applied usage of the Expression Language - Class declaration - Table definition is nested in the class
- 30.
- 31. SQLAlchemy ORM Session Basically it establishesall connections to the db. All objects are kept on it through their lifespan. Entry point for Query.
- 32. SQLAlchemy ORM Master / SlaveConnection? master_session = sessionmaker(bind=engine1) slave_session = sessionmaker(bind=engine2) Session = master_session() SlaveSession = slave_session()
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
Editor's Notes
- #5 If you use ORM you just use classes But if you use the core, you can also map the tables to Python classess with mapper()
- #7 SQLAlchemy core consists of 6 classes
- #15 We define columns as arguments to Table object
- #19 Conn is retrieved from engine.connect()
- #24 Case – when statement is a way of saying When the condition is met, use this value to update the column.