파이썬 각종 db 연결(sqlalchemy, pymysql)

[각종 db 연결(sqlalchemy, pymysql)]

회사에서 각종 데이터들을 실시간으로 db에서 불러와서 가공하고 분석하는 업무를 봤었는데요.
그때 필수적이었던 파이썬 db 연동을 먼저 다뤄보겠습니다.
 
특정 쿼리를 이미 짰다는 전제하에
예를 들어, query = select * from specific_table이라고 해보겠습니다.
이때 "select * from specific_table"이라는 쿼리를 돌려서 데이터 프레임을 만들어주는 코드입니다.

import pymysql
import pandas as pd

# 데이터베이스 연결
conn = pymysql.connect(host="호스트 이름",
                           user="유저 이름", password="비밀번호", db="db 이름", charset="utf8")

# db에서 쿼리를 돌린 결과값을 토대로 데이터프레임 생성
result_df = pd.read_sql_query(query, conn)

# 데이터베이스 연결 종료
conn.close()

만약에 데이터프레임으로 만들지 않고 그냥 데이터를 확인하고 싶다면 아래와 같이 사용해도 됩니다.

import pymysql

# 데이터베이스 연결
conn = pymysql.connect(host="호스트 이름",
                           user="유저 이름", password="비밀번호", db="db 이름", charset="utf8")
# 
curs = conn.cursor()

# 연결된 데이터베이스에서 쿼리 실행하기
curs.execute(query)

#실행된 쿼리 결과값을 가져오기
rows = curs.fetchall()

# 반복문으로 한줄씩 읽기
for row in rows:
    print(row)

# 데이터베이스 연결 종료
conn.close()

cursor()는 Python에서 데이터베이스에 연결한 후 데이터베이스 내의 데이터를 읽거나 수정하는 작업을 수행할 때 사용하는 메서드입니다.

데이터베이스는 일반적으로 표 형태로 데이터를 저장하며, cursor() 메서드는 이러한 표의 행(row)을 하나씩 가져와 Python에서 사용할 수 있는 형태로 변환해 주는 역할을 합니다.

cursor() 메서드는 Python에서 데이터베이스에 접근하고, SQL 쿼리를 실행하며, 결과를 가져오는 것과 관련된 많은 작업을 처리합니다. cursor() 메서드를 사용하여 데이터베이스 내의 테이블을 선택하고, 테이블의 행(row)을 가져오는 등의 작업을 수행할 수 있습니다.

저는 맥북에서 파이썬을 돌렸었는데 pymysql로 돌리니까
UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy라는 워닝이 떠서.. 그냥 참고 오래 쓰긴 했었는데 너무 계속 보이니까 신경 쓰여서.. SQL Alchemy로 나중에는 변경을 했습니다.

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine(
    "mysql+pymysql://유저 이름:비밀번호@호스트 이름/db 이름",
        encoding="utf8",
    )
conn = engine.connect()
result_df = pd.read_sql_query(query, conn)
conn.close()

create_engine() 함수는 SQLAlchemy의 엔진 객체를 생성을 의미합니다. 이 함수의 인자로는 연결할 데이터베이스의 정보를 입력합니다. 여기서는 "mysql+pymysql"을 사용하여 MySQL 데이터베이스에 연결합니다. 그리고, 연결할 MySQL 데이터베이스의 정보(유저 이름, 비밀번호, 호스트 이름, db 이름)를 입력합니다.

engine.connect() 함수는 데이터베이스를 연결을 의미합니다.

pd.read_sql_query() 함수는 SQL 쿼리를 실행하여 그 결과를 Pandas DataFrame으로 반환합니다. 이 함수의 첫 번째 인자인 query는 실행할 SQL 쿼리를 의미합니다. 두 번째 인자인 conn은 SQLAlchemy의 연결 객체를 의미합니다.
 
conn.close() 함수는 데이터베이스와의 연결을 종료를 의미합니다.

 

[alchemy로 쿼리를 돌릴 때 주의해야 할 점]

만약 쿼리 안에 like '%조건%'과 같은 표현이 들어가게 되면 %를 제대로 읽지 못해서 텍스트로 하기 위해선 like '%%조건%%'과 같이 적어주셔야 합니다.

예를 들어, 어떤 테이블에서 이름의 가운데 글자가 승이 들어가는 모든 사람을 조회하려 한다면,
select * from specific_table where name like ''%승%"라고 적을 텐데,
위에 적은 sqlalchemy 코드로 돌리려고 하면
select * from specific_table where name like ''%%승%%"라고 적어주셔야 합니다.