pymysql(MySQL)과 psycopg2(PostgreSQL) 사용하기

게시일 : 2019년 04월 08일    
# pymysql # psycopg2 # Python

Python에서 SQL client 라이브러리인 pymysql(MySQL)과 psycopg2(PostgreSQL) 사용법을 알아본다.

pymysql - mysql client for python

데이터 읽기

mysql과 연결된 cursor라는 객체를 통해 SQL을 처리한다.

connection 설정

cursorclass를 DictCursor로 설정 => return값을 dict 형태로 표현한다는 의미

import pymysql
import pandas as pd
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='mysql',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

cursor = connection.cursor() # 객체를 가져오는 함수

cursor에서 SQL문을 실행시키고 실행 결과값을 fetch 함수를 통해 가져온다.

sql = "SELECT Host, User FROM user;"
cursor.execute(sql) # SQL문 실행
# 결과값의 개수가 출력
4

fetch로 return한 값은 cursor 객체에서 값이 사라진다. ( 중복으로 결과를 가져오지 못한다. )

fetchone : 한 개 return fechmany : 지정 개수 return fetchall : 모두 return
res = cursor.fetchone()
print(res)
res2 = cursor.fetchone()
print(res2)
{'Host': '%', 'User': 'root'}
{'Host': 'localhost', 'User': 'mysql.session'}
res3 = cursor.fetchmany(size=4)
[{'Host': '%', 'User': 'root'},
 {'Host': 'localhost', 'User': 'mysql.session'},
 {'Host': 'localhost', 'User': 'mysql.sys'},
 {'Host': 'localhost', 'User': 'root'}]
res = cursor.fetchall()
[{'Host': '%', 'User': 'root'},
 {'Host': 'localhost', 'User': 'mysql.session'},
 {'Host': 'localhost', 'User': 'mysql.sys'},
 {'Host': 'localhost', 'User': 'root'}]
pd.DataFrame(res)
Host User
0 % root
1 localhost mysql.session
2 localhost mysql.sys
3 localhost root

일반적인 cursor의 경우, tuple 형태로 값만 출력된다. ( column명에 대한 추가 작업이 필요 )

# cursorclass를 명시하지 않은 경우
connection2 = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='mysql',
                             charset='utf8mb4')
cursor2 = connection2.cursor()
cursor2.execute("SELECT Host, User FROM user;")
res2 = cursor2.fetchall()
res2
(('%', 'root'),
 ('localhost', 'mysql.session'),
 ('localhost', 'mysql.sys'),
 ('localhost', 'root'))
# LIST로 타입 변환이 필요
pd.DataFrame.from_records(list(res2), columns=["Host", "User"])
Host User
0 % root
1 localhost mysql.session
2 localhost mysql.sys
3 localhost root

TABLE 생성

# pymysql sample code
query = "CREATE TABLE users ( \
    id int(11) NOT NULL AUTO_INCREMENT, \
    email varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, \
    password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, \
    PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci \
    AUTO_INCREMENT=1 ;"

cursor.execute(query)
0
cursor.execute("DESCRIBE users;")
pd.DataFrame(cursor.fetchall())
Default Extra Field Key Null Type
0 None auto_increment id PRI NO int(11)
1 None email NO varchar(255)
2 None password NO varchar(255)

데이터 INSERT

INSERT 후에 cursor객체를 생성한 connection에 commit을 실행시켜야 서버에 저장된다.

# %s를 활용하여 value값만 변경하여 간단히 query를 날릴 수 있다.
insert_query = "INSERT INTO users (email, password) values (%s, %s);"
cursor.execute(insert_query, ("aaa@gmail.com","12345"))
1

한 번에 여러 데이터 INSERT

executemany는 multiple row를 insert할 때 성능이 좋다. ( 단순 query에 사용할 경우 반복문과 같은 효과 )

# data는 LIST(TUPLE) 형태로
data = (('bbb@gmail.com','asdf'),('ccc@gmail.com','qwer'),('ddd@gmail.com','asd123'))
cursor.executemany(insert_query, data)
3
cursor.execute("SELECT * FROM users;")
pd.DataFrame(cursor.fetchall())
# 객체에만 저장되었을 뿐 DB서버에는 아직 저장되지 않은 상태
email id password
0 aaa@gmail.com 1 12345
1 bbb@gmail.com 2 asdf
2 ccc@gmail.com 3 qwer
3 ddd@gmail.com 4 asd123
connection.commit() # 서버에 INSERT한 결과를 저장 ( 반드시 connection 변수를 활용 )
connection.close() # connection 종료

psycopg2 - postgresql client for python

설치 : pip install psycopg2-binary

기본적인 사용법은 pymysql과 동일하다.

import psycopg2
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect(database="expert", 
                        host="localhost", 
                        user="yahwang", 
                        password="", 
                        ) # return 값을 dict로
psql_cursor=conn.cursor(cursor_factory=RealDictCursor)

참고 : psycopg2 cursor class