[TIL] Python with MYSQL

💻 실습

install mysql driver

# mysql driver 설치
pip install mysql-connector-python

# 설치확인

import.mysql.connector

Python with MYSQL

create connection

MYSQL에 접속하기 위한 코드

<connection name> = mysql.connector.connect(
		host = "<hostname>",
    	user = "<username>",
    	password = "<password>",
    	database = "<databasename>" #특정 데이터베이스 설정
)

close connection

<connection name>.close()

execute SQL

Query 를 실행하기 위한 코드

<cursorname> = <connection name>.cursor()
<cursorname>.execute(<query>)

execute SQL File

SQL File 을 실행하기 위한 코드

<cursorname> = <connection name>.cursor()

sql = open("<filename>.sql").read()
<cursorname>.execute(sql) # SQL File 내에 Query 가 여러개 존재하는 경우 multi=True 추가

fetchall()

<cursorname> = <connection name>.cursor() # 읽어올 데이터 양이 많은 경우 buffered=True
mycursor.execute(<query>)

result = <cursorname>.fetchall()
for data in result:
	print(data)

➕ 검색결과 pandas 로 읽기

import pandas as pd

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

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

• mysql 에 접속한 상태

1️⃣ read csv

import pandas as pd

df = pd.read_csv("<csvfilename>") # csv 한글이 깨지는 경우, encoding 값을 'euc-kr' 로 설정
df.head()

2️⃣ cursor 만들기

<cursorname> = <connection name>.cursor(buffered=True)

3️⃣ inser문 만들기

sql = "insert into <tablename> values (%s, %s,...)"

4️⃣ 데이터 입력

for i, row in df.iterrows():
	<cursorname>.execute(sql, tuple(row))
    print(tuple(row))
    <connection name>.commit() # commit() 은 database 에 적용하기 위한 명령

5️⃣ 결과확인

<cursorname>.execute("select * from <tablename>")

result = <cursorname>.fetchall()
for row in result:
	print(row)

➕ pandas 로 결과 읽기

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

---

🧸 review

• 공공데이터 csv 파일로 연습하기
  기상청 기상자료개방포털 https://buly.kr/A42pJWU
  

❌ encoding='utf-8' 로 하면 한글이 깨지고,
encoding='cp949' & 'euc-kr'은 인식을 못하는 문제가 발생함 😭

[-해결방안 정리해서 작성하기-]

-- 이번파일은 데이터가 크지 않아서 csv 파일을 수정해서 사용함.

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

conn = mysql.connector.connect(
    host = "database-1.ct6emoc66tc9.ap-southeast-2.rds.amazonaws.com",
     port = "3306",
    user = "admin",
    password = "***********",
    database = "review"
)

• Temperature Table 생성하기

sql = "create table temperature (날짜 date, 지역 varchar(8), 평균기온 float, 최저기온 float, 최고기온 float)"
cursor = conn.cursor(buffered=True)

cursor.execute(sql)

• 데이터를 pandas로 읽어오기

df = pd.read_csv("temperature.csv", encoding='utf-8')
df.head(2)

• 데이터를 temperature 테이블에 insert 하기

sql = "insert into temperature values(%s,%s,%s,%s,%s)"
cursor = conn.cursor(buffered=True)

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

• temperature 테이블의 데이터 조회하기

cursor.execute("select*from temperature")

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

• 조회된 데이터를 Pandas 로 변환하여 출력하기

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

실습확인

---