Python에서 대용량 데이터 적재(Insert)하기(SQL)

심준보·2023년 1월 17일

SQL

목록 보기

2/3

- SQL

sql문은 직관적이다.

아래 명령어를 작동시키면 실행이된다.

# AWS connecting
mysql -h [Database Name].[Host Address] -P [Port] -u [Username] -p

# SELECT
SELECT * FROM Table;
SELECT Column FROM Table WHERE Condition;

# UPDATE
UPDATE Table SET Column = 0 WHERE Condition;

# INSERT
INSERT INTO Table VALUES (0, 0, 0);

# DELETE
DELETE FROM Table WHERE Condition;

특히 매력적인 것은 데이터베이스를 AWS에서 이용하는 것. Remote에서 접근하는 코드를 Terminal로 입력해서 진행하는 것도 흥미로운 일인데 , 그것을 이제 VS code에서 한다.
SQL 쿼리 파일을 만들어서 , Python으로 실행해서 SQL 데이터베이스를 수정할 수도 있다. 터미널을 거치지 않고 , python에서 aws 로 바로 이용하는 것이다.

기본적으로 mysql.connector를 활용한다.

# import library : mysql.connector
import mysql.connector
conn_aws = mysql.connector.connect(
    host = "[Host Address]",
    port = [Port],
    user = "[Username]",
    password = "[Password]", 
    database = "[Database Name]"
)

# Execute project init query (Create database and table)
cur = conn_aws.cursor()
sql = open("project.sql").read()
cur.execute(sql)

# Insert data into table(weather)
# %s : value
cur = conn_aws.cursor(buffered=True)
sql = """INSERT INTO Weather VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
for i, row in weather.iterrows():
    cur.execute(sql, tuple(row))
    conn_aws.commit()
    
# Select all data from table(weather)
cur.execute("SELECT * FROM Weather")
result = cur.fetchall()
for row in result:
    print(row)

- 위 방법에서 데이터셋이 엄청 클 경우 데이터 적재 문재가 발생할 수 있다.

1. execute 대신 executemany 활용하기

# dataframe = user -> list setting
# tqdm : we can see the progress status of 'for iteration'
from tqdm import tqdm
data = []
for i, row in tqdm(user.iterrows()):
    data.append(tuple(row))

# AWS Connection
import mysql.connector
conn_aws = mysql.connector.connect(
    host = "[Host Address]",
    port = [Port],
    user = "[User Name]",
    password = "[password]", 
    database = "[Database Name]"
)

# sql writing and commit
sql = """INSERT INTO User (bicycle_index, time_start, stop_index1, stop_1, count_stop1, time_end, stop_index2, stop_2, count_stop2, cycle, distance) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

cur = conn_aws.cursor(buffered=True)
cur.executemany(sql, data)
conn_aws.commit()

executemany를 사용하는 방법 : 기존의 excute에 many가 붙은 것만 보아도 , 많은 줄의 데이터 프레임을 sql로 수행하기 위한 적절한 명령어라는 것을 느낄 수 있다.
단, 에러가 뜰 가능성이 있다.(컴퓨터의 메모리 부족이 있을 경우 )

2. sqlalchemy(create_engine) + to_sql활용하기

# library import : sqlalchemy 
from sqlalchemy import create_engine

# engine : AWS connecting
engine = create_engine("mysql+pymysql://[username]:"+"[passward]"+"[host]:[port]/[database name]?charset=utf8", encoding='utf-8')
conn = engine.connect()

# to_sql (user : Dataframe / name = tablename, con = connection)
import pandas as pd
user.to_sql(name="User", con=engine, if_exists='append', index=False)