SQLALCHEMY를 이용한 PostgreSQL 사용법

SQL 알케미랑 SQL 서버와 연결하는 부분

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


URL = "postgresql://아이디:비밀번호@서버주소/DB이름"
engine = create_engine(URL,echo=False)
SessionLocal = sessionmaker(autocommit=False, autoflush=True, bind=engine)

 
PostgreSql과 SQLAlchemy를 연결하는 부분입니다. 하지만 이렇게 하면 PostgreSQL 서버에 DB가 자동으로 생기지 않더군요 그래서 할수 없이 그냥 수동으로 DB를 생성한뒤 테이블을 만들었습니다. 
좋은 방법이 있으신분을 가르쳐 주시면 감사하겠습니다. 
이 부분 이후 부터는 SQLite, MySQL 다 똑같을 겁니다. 

테이블의 컬럼을 만드는 부분

 
SQLAlchemy로 테이블을 만드는 부분입니다. 
time = Column(DateTime(timezone=True),server_default=sqlalchemy.sql.func.now())
자동으로 시간이 입력되는 부분입니다. 이부분도 더 좋은 방법을 알고 계신분은 알려주시면 감사하겠습니다. 

#model.py

from sqlalchemy import Column, String, Integer,Boolean,DateTime
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime


Base = declarative_base()

class PhotoDB(Base):
    __tablename__ = "photo_dbs"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(String,index=True)
    file_name = Column(String,index=True)
    url = Column(String,unique=True)
    is_down_images = Column(Boolean, default=False)

class PostDB(Base):
    __tablename__ = "Post_dbs"

    id = Column(Integer, primary_key=True, index=True)
    post_url = Column(String,unique=True)
    subject = Column(String)
    is_get_images = Column(Boolean, default=False)

class PhotoPath(Base):
    __tablename__ = "photo_path"
    id = Column(Integer, primary_key=True, index=True)
    photo_path = Column(String,unique=True)
    user_id = Column(String,index=True)
    file_name = Column(String,index=True)


class LogDB(Base):
    __tablename__ = "log_db"
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(String)
    status = Column(String)
    time = Column(DateTime(timezone=True),server_default=sqlalchemy.sql.func.now())

테이블이 실제 생성되는 부분입니다. 

model.Base.metadata.create_all(bind=engine)

 

기본적인 데이터를 입력하는 방법 INSERT INTO 문

테이블에 데이터를 입력하는 부분입니다. 
SQLite와는 다르게 Postgres는 db.close()를 안하면 오류가 나는것 같습니다.
 
dataToDB = LogDB(user_id=self.user_id,status=status) 이런식으로 인스턴스를 생성하고 
db.add(dataToDB)를 하고 저는 그냥 git으로 따지면 스테이지에 올리는거라고 생각하고 그냥 넘어갔는데
                        이부분도 혹시 정확한 의미를 아시는분이 보시면 알려주시면 감사하겠습니다. 
db.commit() 커밋을 해야 진짜로 DB에 입력이 됩니다. 

from database import SessionLocal

db = SessionLocal()


dataToDB = LogDB(user_id=self.user_id,status=status)
db.add(dataToDB)
db.commit()

db.refresh(dataToDB)
db.close()

데이터 불러오기 SELECT 문

# 세션에 관련된 데코레이션 함수
def add_session(func):
    def wrapper_func(*agr, **kwd):
        try: 
            db = SessionLocal()
            result = func(db,*agr, **kwd)
            return result
        except:
            pass
        finally:
            db.close()
    return wrapper_func


@add_session 
def get_post_by_url(db,url):
    return db.query(PostDB).filter(PostDB.post_url==url).first()

@add_session                               
def get_all_data(db):
    return db.query(PhotoDB)

@add_session
def get_all_id(db):    
    return db.query(PhotoDB.user_id).group_by(PhotoDB.user_id)\
           .order_by(PhotoDB.user_id).all()

@add_session
def get_data_by_id(db:Session,user_id):   
    return db.query(PhotoDB).filter(PhotoDB.user_id==user_id)\
    .distinct(PhotoDB.file_name).order_by(PhotoDB.file_name, PhotoDB.id.desc())

@add_session
def get_photo_id(db:Session,user_id):
    return db.query(PhotoPath).filter(PhotoPath.user_id==user_id)\
    .order_by(PhotoPath.id.desc()).all()

SQLite와 다르게 Postgresql은 db.query(PhotoDB.user_id).group_by(PhotoDB.user_id) 이렇게 해야 오류가 안나더군요 즉, query에 들어가는 것과 group_by에 들어가는 것이 같아야 오류가 안나네요
 
query(PhotoDB)로하면 DB에 있는 모든 컬럼이 나오고 query(PhotoDB.user_id)을 하면 user_id 컬럼만 셀렉트 됩니다.
 
query(PhotoDB)은 SELECT * FROM "Post_dbs"
query(PhotoDB.user_id) 은 SELECT user_id FROM "Post_dbs" 와 같습니다. 
 
order_by와 distinct를 함께 쓰려면 distinct에 들어 간것이랑 order_by가 첫번째랑 같아야 오류가 안남니다.
.distinct(PhotoDB.file_name).order_by(PhotoDB.file_name, PhotoDB.id.desc())
 
이상 SQLALCHEMY를 이용한 PostgreSQL 사용법 이었습니다. 

2024년 최신 sql알케미 사용법
https://jh-industry.tistory.com/171