SQLalchemy

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, or_
from sqlalchemy.orm import sessionmaker

### session 객체 생성
DATABASE_URI = "postgresql+psycopg2://user_id:password@localhost:5432/db_name"
engine = create_engine(DATABASE_URI)
Session = sessionmaker(bind=engine)
sess = Session()

### DB에 저장될 테이블 객체 생성 및 DB에 테이블 생성
Base = declarative_base()

class Student(Base):
    __tablename__ = 'student'

    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    age = Column(Integer)
    grade = Column(String(20))

Base.metadata.create_all(engine)

### insert data into the table
student1 = Student(nam='Choi',age='9',grade='second')
student2 = Student(nam='Kim',age='10',grade='third')
student3 = Student(nam='Lee',age='12',grade='fifth')
student4 = Student(nam='Park',age='9',grade='second')

# 1개만 추가할때는
sess.add(student1)
# 여러개 추가할때
sess.add_all([student2, student3, student4])
sess.commit()

### Read data
students = sess.query(Student)

for student in students:
    print(student.name)

students_order = sess.query(Student).order_by(Student.name)
student_third_grade = sess.query(Student).filter(Student.grade=='third').first()
student_second_grade = sess.query(Student).filter(or_( \
    Student.name=='Choi', Student.name=='Park'))

for student in student_second_grade:
    print(student)

how_many_second_grade = sess.query(Student).filter(or_( \
    Student.name=='Choi', Student.name=='Park'))

### Update data
student_update = sess.query(Student).filter(Student.grade=='third').first()
student_update.name = 'Hwang'
student_update.commit()

### Delete data
student_update = sess.query(Student).filter(Student.name=='Hwang').first()
sess.delete(student_update)
student_update.commit()