[SQL] Python with MySQL

Python으로 MySQL 접속하기

1. MySQL Driver설치

pip install mysql-connector-python

2. 설치 확인

import mysql.connector

3. MySQL에 접속하기 위한 코드

mydb = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때

4. 연결 종료

mydb.close()

Python으로 sql파일(쿼리) 실행하기

테이블 생성 쿼리 실행

remote = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때
)

cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
#zerobase에 테이블 생성하는 쿼리 실행
remote.close()

테이블 삭제 쿼리 실행

remote = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때
)
cur = remote.cursor()
cur.execute("DROP TABLE sql_file")
remote.close()

쿼리 실행하되, sql파일을 가져와서 실행

remote = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때
)
cur = remote.cursor()
sql = open('test03.sql').read()
cur.execute(sql)

remote.close()

sql 파일 안에 쿼리가 여러개 존재하는 경우

remote = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때
)
cur = remote.cursor()
sql = open('test04.sql').read()
for result in cur.execute(sql, multi=True):
    if result.with_rows:
        print(result.fetchall())

    else :
        print(result.statement)

remote.close()

Fetchall(전체 나열 함수) 예제

remote = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때
)

cur = remote.cursor(buffered=True) #데이터 양이 많을 경우
cur.execute('SELECT * FROM sql_file')
result = cur.fetchall()
result

->[(1, 'test01.sql'), (2, 'test02.sql'), (3, 'test03.sql'), (4, 'test04.sql')]

검색 결과를 판다스로 읽기

import pandas as pd
df = pd.DataFrame(result)
df.head()

CSV파일을 파이썬으로 데이터베이스에 옮기기

csv파일을 pandas로 읽어오기

import pandas as pd
df = pd.read_csv('police_station.csv')
df.head()

import mysql.connector
conn = mysql.connector.connect(
		host = "<hostname>" #AWS RDS에 연결할 때 엔드포인트
        #port = #AWS RDS에 연결할 때
        user = "<username>"
        password = "<password>"
        #database ="<databasename>" #특정 데이터베이스에 접속할 때
)

cursor 만들기

cursor = conn.cursor(buffered=True) #읽어올 양이 많은 경우 버퍼 설정

insert문 만들기

sql = 'insert into police_station values (%s, %s)'

데이터 입력

for i , row in df.iterrows():
    cursor.execute(sql, tuple(row))
    conn.commit() #commit은 데이터베이스에 적용하기 위한 명령

결과 확인

cursor.execute('select * from police_station')

result = cursor.fetchall()
result

조회한 결과를 pandas로 읽기

df = pd.DataFrame(result)
df.tail()