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

▶
preparing for the workshop
SMART_READER_LITE
LIVE PREVIEW

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

Aug 27, 2022 120 likes •426 views

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

slide-1
SLIDE 1

Preparing for the workshop

git clone https://github.com/bustawin/sqlalchemy-tutorial cd sqlalchemy-tutorial

Getting & installing the code

(Yes, you can create a virtualenv / use docker’s psql)

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 :-)

1

slide-2
SLIDE 2

SQLAlchemy, from 0 to proficient

Xavier Bustamante Talavera bustawin.com — xavier@bustawin.com

2

slide-3
SLIDE 3

Objectives

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

SQLA constructs.

3

slide-4
SLIDE 4

GUERRILLA TUTORIAL

4

slide-5
SLIDE 5

Xavier Bustamante

  • bustawin.com — xavier@bustawin.com
  • Software engineer / ex-CPO startup.

bustawin.com/about/

  • Looking for new challenges 😊

5

slide-6
SLIDE 6

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

SQLAlchemy

6

slide-7
SLIDE 7

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

slide-8
SLIDE 8

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

slide-9
SLIDE 9

Mini introduction

Connect DB Write SQL as python Table, columns…

http://aosabook.org/en/sqlalchemy.html

9

slide-10
SLIDE 10

A0: Intro to SQLA Core

10

slide-11
SLIDE 11

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

11

slide-12
SLIDE 12

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

12

slide-13
SLIDE 13

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
  • bject:

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!

13

http://docs.sqlalchemy.org/en/13/orm/session_basics.html

slide-14
SLIDE 14

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”

14

https://docs.sqlalchemy.org/en/13/core/sqlelement.html

slide-15
SLIDE 15

Break!

15

slide-16
SLIDE 16

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

16

slide-17
SLIDE 17

B0: Basic relationship

17

http://docs.sqlalchemy.org/en/13/orm/extensions/declarative/relationships.html

slide-18
SLIDE 18

How would you add the user TO the computer?

18

slide-19
SLIDE 19

B1: Many — Many relationship

19

http://docs.sqlalchemy.org/en/13/orm/extensions/declarative/relationships.html#declarative-many-to-many

slide-20
SLIDE 20

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

slide-21
SLIDE 21

C: Inheritance (Joined Table Inheritance)

21

http://docs.sqlalchemy.org/en/13/orm/extensions/declarative/inheritance.html

slide-22
SLIDE 22

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

slide-23
SLIDE 23

E: Validations

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

23

slide-24
SLIDE 24

F: Events

24

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

slide-25
SLIDE 25

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

25

slide-26
SLIDE 26

H: Natural Search

26

http://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-match

slide-27
SLIDE 27

G: Views

27

slide-28
SLIDE 28

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

slide-29
SLIDE 29

Thank you! xavier@bustawin.com bustawin.com

29