[Python] Python과 SQL 연결

- pymysql

파이썬에서 SQL DB에 접속할 때 사용하는 패키지 입니다.

pymysql이 설치되어 있지 않은 경우 다음과 같이 설치하면 됩니다.

# window, mac
pip install pymysql

# conda
conda install pymysql

 

- SQL에서 데이터 불러오기

import pymysql

con = pymysql.connect(
	user = 'root', # 사용자명
    passwd = '1234', # 비밀번호
    host = '127.0.0.1', # 허용 접속 IP. 일반적으로 localhost는 127.0.0.1
    db = 'shop', # 사용할 데이터베이스
    charset = 'utf8' # 인코딩 방법
)
# 로컬 DB 뿐만 아니라 aws와 같은 클라우드도 연결 가능

mycursor = con.cursor() # 데이터베이스의 커서 객체를 가져옴. 
# 커서란 화면에서 현재 사용자의 위치를 나타내며 깜빡거리는 막대기.
# 데이터베이스에서도 데이터 중에서 특정 위치, 특정 행을 가르킬 때 커서가 사용됨. 즉,여기서는 커서란 현재 작업 중인 행을 나타내는 객체.

query = """
select * from goods;
"""

mycursor.execute(query) # SQL 쿼리를 데이터베이스 서버에 보냄.
data = mycursor.fetchall() # 서버로부터 데이터를 가져옴.
con.close() # 작업을 마친 후 데이터베이스와 연결을 종료

 

- SQL에 데이터 저장하기

import pymysql

con = pymysql.connect(
	user = 'root', 
    passwd = '1234',
    host = '127.0.0.1',
    db = 'shop',
    charset = 'utf8' 
)

mycursor =  con.cursor()

query = """
	insert into goods (goods_id, goods_name, goods_classify, sell_price, buy_price, regis1ter_date)
    values ('0009', '스테이플러', '사무용품', '2000', '1500', '2020-12-30');
"""

mycursor.excute(query)

con.commit() # 삽입, 갱신, 삭제 등 DML(Data Manipulation Language)문장을 실행하는 경우 commit 메서드를 사용하여 데이터의 학정 갱신하는 작업을 실행
con.close()

 

- pandas를 이용하여 SQL 데이터 읽기 및 쓰기

위와 같은 방법을 사용했을 때는 튜플형태로 데이터를 읽어오고, 열 이름도 보이지 않기에 pandas를 이용하여 데이터프레임 형태로 SQL로 데이터를 읽고 쓸 수 있습니다.

• 데이터 읽어오기

import pandas as pd
from sqlalchemy import create_engine # pandas에서 SQL에 연결할 때는 SQLalchemy ORM을 사용합니다.
# ORM(Obejct Relational Mapping): 어플리케이션과 데이터베이스를 연결할 때, SQL 언어가 아닌 어플리케이션 개발언어로 데이터베이스를 접근할 수 있게 해주는 툴

# engine = create_engine('mysql+pymysql://[사용자명]:[비밀번호]@[호스트:포트]/[사용할 데이터베이스]')
engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/shop')

query = """select * from goods;"""
goods = pd.read_sql(query, con = engine) # read_sql(쿼리, 연결정보)를 통해 데이터프레임 형태로 데이터를 불러옴
engine.dispose() # 연결 종료

• 데이터 쓰기

import pandas as pd
from sqlalchemy import create_engine
import seaborn as sns

iris = sns.load_dataset('iris')
iris.head()

engine = create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/shop')
iris.to_sql(name = 'iris', con = engine, index = False, if_exists = 'replace') # 데이터프레임.to_sql(테이블 이름, 연결정보, index)을 통해 데이터프레임을 데이터베이스에 저장.
# index = False로 해야 인덱스를 생성하지 않습니다.
# if_exists = 'replace'를 입력하면 해당 테이블이 존재할 시 데이터를 덮어씁니다.

engine.dispose()