Intro to SQLAlchemy course

https://www.youtube.com/watch?v=X4-hu3vZAOg

The MetaData

MetaData is SQLAlchemy’s register over all the defined tables - it allows SQLAlchemy to understand how Tables are connected, handle foreign keys and when issuing DDL (CREATE, DROP, ALTER etc).

The metadata object should be a global object, and all Tables should use the same metadata object

meta = sa.MetaData()

Total side track:

while looking markdown snippets I found this fantastic extension:

Back to SQLAlchemy

The Table

To interact with a database, we need to represent the Table in Python code.

This is what SQLAlchemy will use to generate correct SQL when selecting data

test_table = sa.Table("test", 
                      meta, 
                      sa.Column("col1", sa.Integer), 
                      sa.Column("col2", sa.String))

The Engine

The Engine is what talks to the underlying DB-API library. To create an Engine, we need a properly formatted connection string, so the Engine knows what DB-API it needs to talk to.

Creating an engine doesn’t connect to the database, so it’s merely doing some URL validation and preparing the correct dialect. This is also where we can set various connection options

import sqlalchemy as sa

# SQLAlchemy 2.0 is still in beta, but we can opt-in to the future behaviour
engine = sa.create_engine("sqlite:///local.db", future=True)

The SQL

Now we’re ready to write some SQL - SQLAlchemy style.

sql = sa.select(test_table)
sql

# outputs: <sqlalchemy.sql.selectable.Select object at 0x0000021017926610>

print(sql)

# out: 
# SELECT test.col1, test.col2 
# FROM test

doing simple where clause:

but more magic happened

Some Behind-the-scenes

SQLAlchemy did a few different things for us here out of the box.

It generated the correct SQL for our Database (SQLite in this case).
It wrapped the result of the query in a Row object, allowing us to write .col1 or convert to a dictionary out-of-the-box.
It will convert the DB results to the types declared in the table
Behind the scenes, SQLAlchemy also creates a pool of connections, depending on the backend. Every time you run a query, you’ll check out a connection from the pool instead of creating a new one every time.

SQL Core

Peek at what happens when inserting