SQL - Python with MySQL

MySQL Driver 설치

pip install mysql-connector-python
import mysql.connector   #설치확인

• MySQL 접속 코드

mydb = mysql.connector.connect(
    host = "hostname",
    user = "username",
    password = "password"
)

• Local Database 연결

local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********"
)

• AWS RDS 연결

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********"
)

• 사용 후엔 close() 꼭 해주기.

local.close()
remote.close()

✏ CREATE CONNECTION 예제 1

: Local MySQL의 zerobase 연결

local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
    database = "zerobase"
)
local.close()

✏ CREATE CONNECTION 예제 2

: AWS RDS (database-1)의 zerobase에 연결

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)
remote.close()

⏹ SQL쿼리 실행

쿼리 실행하기 위한 코드

• 테이블 생성

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

remote.close()

• 결과 확인

mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| filename | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

• 테이블 삭제

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

cur = remote.cursor()
cur.execute("DROP TABLE sql_file")

remote.close()

• 결과 확인

mysql> desc sql_file;
ERROR 1146 (42S02): Table 'zerobase.sql_file' doesn't exist

SQL file 실행하기 위한 코드

• test03.sql파일 생성
  
• test03.sql 실행

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()

• 결과 확인

mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| filename | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

SQL 파일 내 쿼리가 여러개 존재하는 경우

result = mycursor.execute(sql,multi=True)

• test04.sql 생성
  

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

cur = remote.cursor()
sql = open("test04.sql").read()
for result_iterator in cur.execute(sql,multi=True):
    if result_iterator.with_rows:
        print(result_iterator.fetchall())
    else:
        print(result_iterator.statement)
remote.commit()
remote.close()

• 결과 확인

mysql> select * from sql_file;
+------+------------+
| id   | filename   |
+------+------------+
|    1 | test01.sql |
|    2 | test02.sql |
|    3 | test03.sql |
|    4 | test04.sql |
+------+------------+

⏹ Fetch All

레코드를 배열 형식으로 저장

- sql_file 테이블 조회 (읽어올 데이터 많은 경우 `buffered=True`)
remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "*********",
    database = "zerobase"
)

cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")

result = cur.fetchall()
for result_iterator in result:
    print(result_iterator)

remote.close()

• Pandas 로 결과 조회

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

	0	1
0	1	test01.sql
1	2	test02.sql
2	3	test03.sql
3	4	test04.sql