Preparing for the workshop Getting & installing the code git - PowerPoint PPT Presentation

Preparing for the workshop Getting & installing the code git clone https://github.com/bustawin/sqlalchemy-tutorial cd sqlalchemy-tutorial Read the README.rst of the project for more instructions. psst. I have a pendrive with Postgres and Git installers (Mac, Windows). Any problems? Come talk to me :-) (Yes, you can create a virtualenv / use docker’s psql) 1

SQLAlchemy, from 0 to proficient Xavier Bustamante Talavera bustawin.com — xavier@bustawin.com 2

Objectives • Learn how to use SQLA — in the real WWW world. • Understand and have a reference when dealing with more complicated SQLA constructs. 3

G UERRILLA TUTORIAL 4

Xavier Bustamante • bustawin.com — xavier@bustawin.com • Software engineer / ex-CPO startup. bustawin.com/about/ • Looking for new challenges 😊 5

SQLAlchemy SQL the pythonic way. Special thanks to Michael Bayer, the creator of SQLA & The rest of the community of SQLA & Konsta Vesterinen, the creator of SQLA-utils 6

What I like the most of SQLA • DRY: • Common functionality —not reinvent the wheel. • Relationships (i.e. foreign keys) auto fetched and updated by assigning, adding… to the model. • Declarative pattern; you tell what you want, let SQLA figure the rest. • Right amount of configuration vs coding. Elegantly extend / circumvent SQLA when you just need that thing custom made. • Not magic, but defaults that you can modify / extend. • Don’t over automate / optimize everything. • Abstract from different SQL dialects.* 7

Accessing the documentation • Probably a user of SQLA had your problem before. • Documentation: • Seriously, it is very good: https://docs.sqlalchemy.org/en/13/ • Just use something like Dash. • For help, Stackoverflow (#sqlalchemy) / official Google Groups. • Understand the why / how with the official video tutorial. • Another nice tutorial. 8

Mini introduction Table, columns… Connect DB Write SQL as python http://aosabook.org/en/sqlalchemy.html 9

A0: Intro to SQLA Core 10

A1: Intro to SQLA ORM + Flask What… what is an ORM? 11

https://www.youtube.com/watch?v=woKYyhLCcnU 12

The session • In Flask, the session lives only in the request.* • Any changes you do inside in a request, is by default in the same transaction. • Identity map: unique object per PK. • Querying twice Computer ID 1 in the same session will give you the same python object: pc1 = Computer.query.first() pc1.foo = ‘bar’ pc2 = Computer.query.first() pc1 is pc2 pc2.foo == ‘bar’ • You can safely modify any copy of the same row in your request! http://docs.sqlalchemy.org/en/13/orm/session_basics.html 13

Some more expressions • and_, or_, is_, in_… • or_(Computer.id == 1, Computer.id == 2, Computer.id == 9) • or_(*[Computer.id == id for id in [1,2,9]]) • Computer.id.in_([1,2,9]) • Computer.model + ‘ 2019’ => computer.model || “ 2019” https://docs.sqlalchemy.org/en/13/core/sqlelement.html 14

Break! 15

Execute “sh init-db.sh” between the following examples. 16

B0: Basic relationship http://docs.sqlalchemy.org/en/13/orm/extensions/declarative/relationships.html 17

How would you add the user TO the computer? 18

B1: Many — Many relationship http://docs.sqlalchemy.org/en/13/orm/extensions/declarative/relationships.html#declarative-many-to-many 19

Query wonders • Chain: query methods usually return a new query object. query = Computer.query.filter(…) grouped_query = query.group_by(…) • SQL statements: query.insert / query.delete / query.join … • Configure Lazy Loading / Eager Loading to optimize the number of SQL queries SQLA when loading relationships: query.options(selectinload(User.addresses)) for pc in Computer.query: pc.author # This does SELECT author for each computer • Alias: “ FROM computer c1, computer c2 ” • Filter by: Computer .filter_by(id = 1) == Computer.filter(Computer.id == 1) • Flask’s session: .paginate() .one_or_404() https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying 20

C: Inheritance (Joined Table Inheritance) http://docs.sqlalchemy.org/en/13/orm/extensions/declarative/inheritance.html 21

D: Types Doc: http://docs.sqlalchemy.org/en/13/core/type_basics.html SQLA-utils: https://sqlalchemy-utils.readthedocs.io/en/latest/data_types.html 22

E: Validations http://docs.sqlalchemy.org/en/13/orm/mapped_attributes.html#simple-validators 23

F: Events http://docs.sqlalchemy.org/en/13/core/event.html 24

How to remove the responsible when is removed as a traveler? 25

H: Natural Search http://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-match 26

G: Views 27

What have I not explained? • Why things works / architectural and pattern decisions. • Joins in detail. • Properties and hybrid attributes. If I have in a table start_time and end_time , how can I auto-compute an elapsed field? • Reflection: generate the schema (Table()) from the database. • Alembic: database migrations. • GeoAlchemy. 28

Thank you! xavier@bustawin.com bustawin.com 29