Python - mysql.connector.Connect | 설치, 연결

---

🧷 라이브러리 설치
pip install mysql-connector-python
import mysql.connector
🧷 MySQL 접속 코드

• Local Database 연결

local = mysql.connector.Connect(
    host = 'hostname',
    user = 'username',
    password = 'password'
    # 특정 Database 에 접속하기 위한 코드
    , database = 'dbname'
)
local.close()

• AWS RDS (database-1) 연결

remote = mysql.connector.Connect(
    host = 'hostname',
    port = <port> ,
    user = 'username',
    password = 'password'
    # 특정 Database 에 접속하기 위한 코드
    , database = 'dbname'
)
remote.close()

📌 연결 시 특정 database에 접속하고자 한다면,
database = 옵션을 사용하면 된다.
📌 사용 후에는 꼭 close()로 닫아주도록 한다.

• Local Database 연결

local = mysql.connector.Connect(
    host = 'localhost',
    user = 'root',
    password = 'pw'
    # 특정 Database 에 접속하기 위한 코드
    , database = 'zerobase'
)
local.close()

• AWS RDS (database-1) 연결

remote = mysql.connector.Connect(
    host = 'database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = 'pw'
    # 특정 Database 에 접속하기 위한 코드
    , database = 'zerobase'
)
remote.close()

execute | Query 실행

---

🧷

• 
• 접속코드 변수에 cursor()를 연결해주고,
  execute()의 괄호안에 쿼리문 삽입

• zerobase db에 sql_file 생성

remote = mysql.connector.Connect(
    host = 'database-1.cpc8kqyoiq1s.ap-southeast-2.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> use zerobase
Database changed
mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.15 sec)

#생성후
mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| sql_file           |
| test1              |
| test2              |
+--------------------+
7 rows in set (0.15 sec)

mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| filename | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.15 sec)

• sql_file 테이블 삭제

remote = mysql.connector.Connect(
    host = 'database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com',
    port = 3306,
    user = 'admin',
    password = '비번입력',
    database = 'zerobase'
)

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

remote.close()

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.15 sec)

execute | SQL File

---

🧷

• 
• 
• execute()안에 읽어온 sql파일변수 넣기

1. 단일쿼리 예제

• TEST03.sql에 테이블생성 쿼리 작성 후 저장
  

• 실행코드작성

remote = mysql.connector.connect(
    host = "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)

cur = remote.cursor()
sql = open('TEST03.sql').read()
cur.execute(sql)

remote.close()

• 테이블생성 확인

mysql> show tables;
+--------------------+
| Tables_in_zerobase |
+--------------------+
| celeb              |
| crime_status       |
| police_station     |
| snl_show           |
| sql_file           |
| test1              |
| test2              |
+--------------------+
7 rows in set (0.15 sec)

mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| filename | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.16 sec)

2. 다중쿼리예제

• TEST04.sql에 insert문 작성 후 저장
  

• 실행코드작성

remote = mysql.connector.connect(
    host = "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)

cur = remote.cursor()
sql = open('TEST04.sql').read()
for i in cur.execute(sql,multi=True):
    if i.with_rows:
        print(i.fetchall())
    else :
        print(i.statement)

remote.commit()
remote.close()

⛔ error | Commands out of sync; you can't run this command now

remote = mysql.connector.connect(
    host = "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)
cur = remote.cursor()
sql = open('TEST04.sql').read()
cur.execute(sql, multi=True)
remote.commit()
remote.close()

for문은 데이터를 콘솔창에 찍기위해 코딩한줄알고
위와같이 코드를 짜서 실행해보았는데, error가 떴다.
이 오류는 이전 쿼리의 결과 집합을 모두 소모하지 않은 상태에서 다음 쿼리를 실행하려고 할 때 발생한다고 한다.
결과적으로 for문까지 모두 실행을 해야만 정상적으로 다중쿼리를 실행할 수 있다.

• 데이터 확인

mysql> select * from sql_file;
+------+------------+
| id   | filename   |
+------+------------+
|    1 | test01.sql |
|    2 | test02.sql |
|    3 | test03.sql |
|    4 | test04.sql |
+------+------------+
4 rows in set (0.15 sec)

fetch all

---

🧷 select 쿼리문처럼 결과 값이 row를 포함하고 있으면 Fetch All 해서 출력
(읽어올 데이터 양이 많은 경우 buffered=True)

• sql_file 읽어오기

remote = mysql.connector.connect(
    host = "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)

cur = remote.cursor(buffered=True)
cur.execute("select * from sql_file")

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

remote.close()

• pandas로 불러오기
  
  

import pandas as pd

df = pd.DataFrame(result)
df

CSV 에 있는 데이터를 Python 으로 INSERT

---

• csv 파일 불러온 후 데이터 확인
  

• AWS mysql 연결

import mysql.connector

conn = mysql.connector.connect(
    host = "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)

• cursor 생성

cursor = conn.cursor(buffered=True)

• insert문 작성

sql = "INSERT INTO police_station VALUE (%s,%s)"

• 데이터 입력

for i, row in df.iterrows() :
    cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()

🧷 tuple ()
위 df value값에 해당하는 열 데이터가 튜플로 반환
🧷 (%s,%s)
cursor.execute(sql, tuple(row)) 문에서 %s,%s 에 tuple(row)값이 들어감

• 데이터 확인
  

• 데이터프레임으로 확인
  

연습

---

crime_status 테이블에 2020_crime.csv 데이터를 입력하는 코드를 작성해보자

• 2020_crime.csv 데이터 불러오기
  

• AWS RDS(database-1) zerobase 에 접속

import mysql.connector

conn = mysql.connector.connect(
    host =  "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)

• cursor연결

cursor = conn.cursor(buffered=True)

• Insert 문 작성

sql = "INSERT INTO crime_status VALUES ('2020',%s,%s,%s,%s)"

• 데이터입력
  

• 입력된 데이터 읽기
  

• 데이터프레임화
  

실습

---

1. AWS RDS(database-1) zerobase 에 접속합니다

import mysql.connector

conn = mysql.connector.connect(
    host =  "database-1.cpc8kqyoiq1s.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = 'admin',
    password = 'pw',
    database = 'zerobase'
)

2. cctv Table 을 생성합니다.

sql = "CREATE TABLE cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)"
cursor = conn.cursor(buffered=True)
cursor.execute(sql)

3. cctv CSV 데이터를 Pandas 로 읽어옵니다
   

4. 데이터를 cctv 테이블에 INSERT 합니다.
   

5. cctv 테이블의 데이터 조회하여 확인합니다

6. 조회된 데이터를 Pandas 로 변환하여 출력합니다
   

---

Daily Study Note