Web_Dev_Grad

SQLAlchemy

Expression Language

Expression Language is the lowest form of abstraction in SQLAlchemy and is less concerned with punctuation than other ORMs, for example you can use double or single quotations in it.

Create a new file from the terminal with touch sql-expression.py and add the imports to the top. 

from sqlalchemy import (
    create_engine, Table, Column, Float,     
    Foreignkey, Integer, String, Metadata
)

Connect to your database and assign it to a variable db. 

b = create_engine("postgresql:///database_name")
# /// means it is hosted locally in our work environment

You need to use the metadata class to make a new instance. This will contain a collection of table objects, data about those tables, and data about data in those tables. 

meta = Metadata(db)

To run some queries, first set up your tables first e.g. for an artist. If a column is an integer and not the primary key, it needs primary_key = False to be set. 

artist_table = Table(
    "Artist", meta,
    Column("ArtistID", Integer, primary_key = True),
    Column("Name", String)
)

# Syntax for foreign keys
Foreignkey("TableWhereItIsPrimaryKey. PrimaryKeyColumnName")

Connect it to the database and write queries. 

with db.connect() as connection:
    select_query = artist_table.select() OR
    select_query = 	artist_table.select().with_only_columns([artist_table.c.Name])
    .c makes it look for column title OR
    select_query = artist_table.select().where(artist_table.c.Name == "Queen") OR
    select_query = artist_table.select().where(artist_table.c.ArtistId == 51) OR
    select_query = album_table.select().where(album_table.c.ArtistId == 51) OR
    select_query = track_table.select().where(track_table.c.Composer == "Queen")
    results = connection.execute(select_query)
    for result in results:
        print(result)

Instead of connecting directly to the database each time, you'll use a session to manage transactions. 

# Create the engine:
engine = create_engine("sqlite:///music.db")  # Or use PostgreSQL, MySQL, etc.

# Set up a session:
Session = sessionmaker(bind=engine)
session = Session()

# Create tables in the database:
Base.metadata.create_all(engine)

# With your models and session ready, you can now perform queries using clean, Pythonic syntax:
# Query all artists
artists = session.query(Artist)
for artist in artists:
    print(artist.ArtistId, artist.Name, sep=" | ")

# Query only names
for artist in session.query(Artist):
    print(artist.Name)
# Filters
artist = session.query(Artist).filter_by(Name="Queen").first()
print(artist.ArtistId, artist.Name, sep=" | ")
artist = session.query(Artist).filter_by(ArtistId=51).first()
print(artist.ArtistId, artist.Name, sep=" | ")
albums = session.query(Album).filter_by(ArtistId=51)
for album in albums:
    print(album.AlbumId, album.Title, album.ArtistId, sep=" | ")
tracks = session.query(Track).filter_by(Composer="Queen")
for track in tracks:
    print(
        track.TrackId, track.Name, track.AlbumId, track.MediaTypeId,
        track.GenreId, track.Composer, track.Milliseconds,
        track.Bytes, track.UnitPrice, sep=" | "
    )

Class Based Models

Class based models is using SQLAlchemy at the highest level of abstraction. Each Class has a collection of methods that serve a common purpose and each method has its own purpose. If your methods try to do too much, split it into multiple methods.

Here, each table in your database is represented by a Python class, and each row is an instance (object) of that class. These models let you query and manipulate data using familiar object-oriented programming techniques, making the code, more modular, easier to read and maintain, and safer and less error-prone than writing raw SQL queries.

A declarative base is the "parent" class that your models will inherit from. It contains metadata used to generate and reflect tables.

Setting Up SQLAlchemy ORM files: 

# Imports
from sqlalchemy import create_engine, Column, Float, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create a Declarative base
Base = declarative_base()
# Define tables as classes
class Artist(Base):
    __tablename__ = "Artist"
    ArtistId = Column(Integer, primary_key=True)
    Name = Column(String)

class Album(Base):
    __tablename__ = "Album"
    AlbumId = Column(Integer, primary_key=True)
    Title = Column(String)
    ArtistId = Column(Integer, ForeignKey("Artist.ArtistId"))

class Track(Base):
    __tablename__ = "Track"
    TrackId = Column(Integer, primary_key=True)
    Name = Column(String)
    AlbumId = Column(Integer)
    MediaTypeId = Column(Integer)
    GenreId = Column(Integer)
    Composer = Column(String)
    Milliseconds = Column(Integer)
    Bytes = Column(Integer)
    UnitPrice = Column(Float)