# Python - SQLAlchemy

A technical memo for ORM libarary

## Create Table

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, String, Integer
from sqlalchemy import create_engine

# Create ORM base model
Base = declarative_base()


# Create table's corresponding class
class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), index=True)

    def __init__(self, id, name):
        self.id = id
        self.name = name


# Create connection
engine = create_engine("database url", echo=True)


# Create table in database
Base.metadata.create_all(engine)

## CRUD Operation
### Create

from create_table import User
from create_table import engine
from sqlalchemy.orm import Session

# Create one
with Session(engine) as session:
    user = User("Harry Potter")
    session.add(user)
    session.commit()


# Create multiple
with Session(engine) as session:
    session.add_all(
        [
            User("John Wick"),
            User("Tony Stark"),
            User("SpongeBob SquarePants")
        ]
    )
    session.commit()

### Read

from sqlalchemy.orm import Session
from create_table import engine, User

# select all
with Session(engine) as session:
    user_list = session.query(User).all()
    for user in user_list:
        print(user.id, user.name)


# select first
with Session(engine) as session:
    user = session.query(User).first()
    print(user.id, user.name)


# select with condition
with Session(engine) as session:
    user_list = session.query(User).filter(User.id == 4).all()
    for user in user_list:
        print(user.id, user.name)

with Session(engine) as session:
    user_list = session.query(User).filter_by(id=4).all()
    for user in user_list:
        print(user.id, user.name)

Multiple Conditions: ```and_()```, ```or_()```

from sqlalchemy import and_, or_

filtered_users = session.query(User).filter(and_(condition1, condition2)).all()

filtered_users = session.query(User).filter(or_(condition1, condition2)).all()

### Update

from sqlalchemy.orm import Session
from create_table import engine, User

# Update data
with Session(engine) as session:
    result = session.query(User).filter(User.id == 4).update({"name": "Super Mario"})
    print(result)  # 1
    session.commit()

### Delete

from sqlalchemy.orm import Session
from create_table import engine, User

# Delete
with Session(engine) as session:
    result = session.query(User).filter(User.id == 8).delete()
    print(result)  # 1
    session.commit()

## Foreign Key

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()
engine = create_engine("url", echo=True)


class Student(Base):
    __tablename__ = "students"

    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)
    school_id = Column(Integer, ForeignKey("schools.id"))

    schools = relationship("School", backref="student")


class School(Base):
    __tablename__ = "schools"

    id = Column(Integer, primary_key=True)
    name = Column(String(32), nullable=False)


Base.metadata.create_all(engine)

Use```schools = relationship("School", backref="student")``` to add students to school:

from create_table_foreign import engine, School, Student
from sqlalchemy.orm import Session

with Session(engine) as session:
    sch = School(name="Taipei")
    sch.student = [Student(name="John Wick"), Student(name="Harry Potter")]
    session.add(sch)
    session.commit()