SQLAlchemy
python에서 사용가능한 ORM
install
pip install sqlalchemy
Python
복사
connect with FastAPI
import databases
import sqlalchemy
from fastapi import FastAPI
from pydantic import BaseModel
DATABASE_URL = 'mysql://~~~`'
database = databases.Database(DATABASE_URL)
metadata = sqlalchemy.MetaData()
# Create Table notes
notes = sqlalchemy.Table(
"notes",
metadata,
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column("text", sqlalchemy.String),
sqlalchemy.Column("completed", sqlalchemy.Boolean),
)
# DB 정의
# echo=True - 진행 log 찍어줌
engine = sqlalchemy.create_engine(
DATABASE_URL, connect_args={"check_same_thread": False}, echo=True
)
metadata.create_all(engine)
Python
복사
생성한 DB에 데이터 처리하기 위해서는 sessionmaker 이용해야함.
세션 생성하고 생성해둔 engine 연결
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
Python
복사
데이터 추가 후 commit까지 진행해야 DB에 반영됨
session.add(movie_list)
session.commit()
Python
복사
Table
class User(Base):
__tablename__ = 'users'
id=Colume
Python
복사
Type
•
Integer() - INT
•
String() - ASCII strings - VARCHAR
•
Unicode() - Unicode string - VARCHAR or NVARCHAR depending on database
•
Boolean() - BOOLEAN, INT, TINYINT depending on db support for boolean type
•
DateTime() - DATETIME or TIMESTAMP returns Python datetime() objects.
•
Float() - floating point values
•
Numeric() - precision numbers using Python Decimal()
Transaction
from sqlalchemy.orm import Session
session = Session(engine)
session.add(some_object())
session.add(some_other_object())
session.commit()
result = session.execute()
session.add_all()
session.commit()
session.add()
session.rollback()
Python
복사
Query
Bash
복사
Reference
transaction