[PostgreSQL]. Python ORM(sqlalchemy)으로 데이터 삽입하기

create_table.py

from sqlalchemy import MetaData, Table
from sqlalchemy import create_engine
import json
import os
from sqlalchemy import Integer, String, DateTime
from sqlalchemy import Column

meta = MetaData()

FILE = os.path.dirname(os.path.realpath(__file__))
CONFIG = json.load(open(FILE + '/pg_test_config.json'))

username = CONFIG['username']
password = CONFIG['password']
host = CONFIG['host']
port = CONFIG['port']
db_name = CONFIG['db_name']

URL = f'postgresql://{username}:{password}@{host}:{port}/{db_name}'

engine = create_engine(URL, client_encoding='utf8')

def create_korean_sns():
    students = Table(
        'korean_sns', meta,
        Column('index', Integer, primary_key=True),
        Column('session_id', Integer),
        Column('utterance', String(512)),
        Column('participantID', String(10)),
        Column('datetime', DateTime),
        Column('age', Integer),
        Column('gender', Integer),
        Column('topic', Integer),
    )
    meta.create_all(engine)


engine.dispose()

 

---

models.py

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

Base = declarative_base()

class KoreanSns(Base):
    __tablename__ = "korean_sns"
    index = Column(Integer, primary_key=True)
    session_id = Column(Integer)
    utterance = Column(String(512))
    participantID = Column(String(10))
    datetime = DateTime
    age = Column(Integer)
    gender = Column(Integer)
    topic = Column(Integer)

 

insert.py

from sqlalchemy import create_engine

from datetime import datetime
import pandas as pd
import os
import json
from tqdm import tqdm
from pathlib import Path

tqdm.pandas()

BASE_DIR = Path(__file__).resolve().parent.parent
data_path = os.path.join(BASE_DIR, 'result', 'koreanSns_sess_20220505.csv')
config_path = os.path.join(BASE_DIR, 'upload', 'pg_test_config.json')

CONFIG = json.load(open(config_path))

username = CONFIG['username']
password = CONFIG['password']
host = CONFIG['host']
port = CONFIG['port']
db_name = CONFIG['db_name']

URL = f'postgresql://{username}:{password}@{host}:{port}/{db_name}'
TABLE_NAME = 'korean_sns'

if __name__ == "__main__":
    df = pd.read_csv(data_path,index_col=0)
    df.index = df.index + 1
    engine = create_engine(URL, client_encoding='utf8')

    # print(df.head())

    df['datetime'] = df['datetime'].progress_map(lambda x : datetime.strptime(x[2:], '%y-%m-%d %H:%M:%S'))
    df.to_sql(TABLE_NAME,con=engine,if_exists='replace',chunksize=1000,method='multi')
    engine.dispose()