[python] pymysql,sqlalchemy DB연동 및 조회

Pymysql

python에서 sql을 활용할 수 있도록 도와주는 라이브러리

Pymysql 설치

pip install pymysql
import pymysql

Database 연동 및 객체 생성(Cursor)

: connect 함수를 통해 DB 접속정보를 전달하고 MySQL과 연결
DB와 연동하여 상호작용을 위한 Cursor 객체 생성

DataFrame 형태를 자주 사용하기 때문에
결과를 Dictionary 로 반환해주는 DictCursor 생성
일반 cursor의 경우, tuple 형태로 반환

Pymysql 예제

#Pymysql 예제
import pymysql
def db_connection_pymysql(DB_INFO):
	conn = pymysql.connect(**DB_INFO)
	cur = conn.cursor(pymysql.cursors.DictCursor)

   return conn, cur


if __name__ == '__main__' :

   #DB 접속 정보
   DB_INFO = {"host" : "127.0.0.1",
              "port" : 3306,
              "db" : "ORG",
              "user" : "root",
              "passwd" : "{password}"}

   
   conn, cur = db_connection_pymysql(DB_INFO)

   cur.execute("SELECT * FROM org.seouldust2022 where area='강남구' limit 2;")
   res = cur.fetchall() #type : list 안에 dictionary 여러개
   df = pd.DataFrame(res) #type : DataFrame

	conn.close()

Pymysql 출력

res = [{'date': '2022-12-31 23:00', 'area': '강남구', 'particulate_matter': 57, 'fine_particulate_matter': 44}, {'date': '2022-12-31 22:00', 'area': '강남구', 'particulate_matter': 54, 'fine_particulate_matter': 42}]

df

        date               area    particulate_matter fine_particulate_matter
0 2022-12-31 23:00   강남구    57        44
1 2022-12-31 22:00   강남구    54        42

SQLAlchemy

Python에서 사용가능한 ORM(Object-relational maping)

SQLAlchemy 특징

1. sql 쿼리를 작성하지 않아도 객체간의 관계 표현 가능
2. 개발 생산성 증가
3. 편리한 유지보수

SQLAlchemy 설치

pip install sqlalchemy

SQLAlchemy 예제

from sqlalchemy import create_engine, text 
from sqlalchemy.exc import IntegrityError #중복 에러처리

def db_connection_sqlalchemy(DB_INFO):
   
   user = DB_INFO['user']
   passwd = DB_INFO['passwd']
   host = DB_INFO['host']
   port = DB_INFO['port']
   db = DB_INFO['db']

   
   db_connections = f'mysql+pymysql://{user}:{passwd}@{host}:{port}/{db}'
   engine = create_engine(db_connections, pool_recycle=600)
   conn = engine.connect()


if __name__ == '__main__' :

   #DB 접속 정보
   DB_INFO = {"host" : "127.0.0.1",
              "port" : 3306,
              "db" : "ORG",
              "user" : "root",
              "passwd" : "{password}"}

	engine, conn = db_connection_sqlalchemy(DB_INFO)
    df = pd.read_sql_query(text("SELECT * FROM org.seouldust2022 where area='강남구' limit 2;"), conn)

	#or
	res = conn.execute(text("SELECT * FROM org.seouldust2022 where area='강남구' limit 2;"))
  	df = pd.DataFrame(res.fetchall())

	conn.close()

SQLAlchemy 출력

df

        date               area    particulate_matter fine_particulate_matter
0 2022-12-31 23:00   강남구    57        44
1 2022-12-31 22:00   강남구    54        42

주의사항

1. UPDATE, DELETE, ALTER 등의 명령어 입력시, conn.commit 필수! (안그럼 반영 안됨)
2. pd.read_sql_query 함수 사용시, text()로 쿼리 감싸주기

SQLAlchemy의 Session 생성, DB 생성 등 여러 ORM 매핑 코드에 대해 더 공부해봐야겠다.
그리고 중복처리나 DB 엔진 생성때 여러 옵션들에 대해서도 공부가 필요하다고 느낌!