Python MySQL

• Connect to MySQL
• Execute SQL Queries
• Execute SQL Files
• Fetch All
• CSV
• exercises

Connect to MySQL

Python에서 MySQL을 사용하기 위해서
먼저 MySQL Driver를 설치한다.

pip install mysql-connector-python

MySQL에 접속하기 위한 코드로는, host, user, pw가 필요하다.

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

Local DB에 연결하려면

import mysql.connector

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

AWS RDS (database-1)에 연결하려면

remote = mysql.connector.connect(
	host="엔드포인트",
    port=3306,
    user="admin",
    password="********"
)

Close:

local.close()

특정 데이터베이스를 지정하기-그냥 데베명만 추가해서 지정해주면 된다.

mydb = mysql.connector.connect(
	host="<hostname>",
    user="<username>",
    password="<password>",
    database="<databasename>"
)

---

Execute SQL Queries

.cursor()로 cursor 생성 후,
.excute 메서드로 쿼리를 담아주면 실행할 수 있다.

cur = remote.cursor()
cur.excute("쿼리내용")

zerobase db에 sql_file이라는 테이블 생성하기:

remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "zerobase",
    database = "zerobase"
)

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

remote.close()

잘 생성되었다.

테이블 삭제하기:

---

Execute SQL Files

테스트 sql 파일부터 만들어주었다 (test03.sql)

커서 생성 후, open과 read를 이용하여 불러온 후, 실행한다.

remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "zerobase",
    database = "zerobase"
)

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

remote.close()

sql_file이라는 테이블을 생성하는 쿼리가
잘 실행되었다.

• SQL file내에 query가 여러개 존재하는 경우:
  execute()에 multi=True로 옵션을 줘야 한다.

테스트 sql파일을 만들었고, (test04.sql)

이걸 실행해보자
쿼리가 실행되었다는 결과물을 보기 위해서 for문을 사용

remote = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "admin",
    password = "zerobase",
    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()코드를 입력하세요

이렇게 쿼리가 출력되었고,

테이블에도 값을 insert하는 쿼리들이 잘 반영됨을 확인하였다.

---

Fetch All

select문을 (조회)실행한 쿼리에는 데이터를 가지고 오는데 (rows를 포함)
그 데이터를 fetchall()을 사용하여 변수에 담을 수 있다
for문을 써 row마다 출력하는 방식이다.

읽어올 데이터 양이 많은 경우 buffered=True

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')]

튜플 형태로 담겨 있다.
for문으로 하나씩 꺼내서 볼 수 있고,

for result_iterator in result:
    print(result_iterator)

pandas로 변환해 출력:

import pandas as pd

df = pd.DataFrame(result)
df

---

CSV

CSV에 있는 데이터들을 파이썬 코드로 테이블에 insert해보자~

제공받은 police_station.csv를
pandas로 읽어와서 데이터를 확인하고,

import pandas as pd

df = pd.read_csv("police_station.csv")
df.tail()

mysql에 연결하고

import mysql.connector

conn = mysql.connector.connect(
    host = "엔드포인트",
    port = 3306,
    user = "zero",
    password = "zerobase",
    database = "zerobase"
)

cursor만들고

cursor = conn.cursor(buffered=True)

insert문 만들고
(값 2개를 넣을거다!-> %s, %s
%s안에 문자열 데이터들이 들어가도록!)

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

이런 식으로 튜플 형태에 데이터를 담아 가져올 수 있고...

데이터 입력:

df를 for문으로 한줄식 돌면서
튜플값을 sql문에 연결시켜준 걸 걸 execute -> 쿼리가 실행됨
커밋으로 데이터베이스에 적용하기

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

결과 확인!
select문 쿼리를 실행하고
fetchall로 print해보자

cursor.execute("select * from police_station")

result = cursor.fetchall()

result

데이터가 잘 들어왔다.

pandas로도 읽기.

df = pd.DataFrame(result)
df

CSV 한글이 깨지는 경우, encoding값을 'euc-kr'로 설정~~

---

exercises

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

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

import mysql.connector

conn = mysql.connector.connect(
    host="엔드포인트",
    port=3306,
    user="zero",
    password="zerobase",
    database="zerobase"
)

2. 2020_crime.csv 데이터 읽어오기

import pandas as pd
df = pd.read_csv("2020_crime.csv", encoding="euc-kr")
df.head(2)

3. INSERT 쿼리 작성

sql = "insert into crime_status value ('2020', %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)

4. 데이터를 crime_status 테이블에 INSERT:

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

5. crime_status 테이블 데이터 조회

cursor.execute("select * from crime_status")

result = cursor.fetchall()
for row in result:
    print(row)

...이런 형태로 잘 출력되었고,

6. 조회한 결과를 pandas로 변환해서 확인

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