[2023.11.30] SQL_Python with MySQL

하은·2023년 12월 1일
0

[데이터] 제로베이스 데이터 취업스쿨

목록 보기
51/74

  • VSCode 통해 실행, ds_study 환경에서 작업

  • 모두 sql_ws폴더에서 시작

  • AWS RDS로 생성한 database_1을 모두 사용

  • AWS RDS zerobase의 police_station 테이블의 데이터를 모두 삭제
    delete from police_station;

  • 백업
    _텍스트_zerobase로 이동
    police_station 삭제

- install MySQL Driver

- 설치

pip install mysql-connecter-python

- 설치확인

import mysql.connecter

- create connection

- mysql 에 접속하기 위한 코드

mydb = mysql.connecter.connect(
	host = <"hostname">,
	user = <"username">,
	password = <"password">,
	#port = <"port">, #원격
	#database = <"database"> #db지정할 때
)

사용 끝날 땐 종료해줘야 함.
mydb.close()

- AWS RDS 에 연결

remote = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com" ,
	user = "admin",
	password = <"password">,
	port = 3306,
	#database = "********"
)

사용 끝날 땐 종료해줘야 함.
remote.close()

- 특정 database에 연결

예) local MySQL의 zerobase에 연결

import mysql.connector

mydb = mysql.connecter.connect(
	host = "localhost",
	user = "root",
	password = "********",
	database = "zerobase"
);

mydb.close()

- AWS RDS의 zerobase에 연결

예) local MySQL의 zerobase에 연결

remote = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	database = "zerobase"
);

remote.close()

- Execute SQL

- 쿼리를 실행하기 위한 코드

mydb = mysql.connecter.connect(
	host = <"hostname">,
	user = <"username">,
	password = <"password">,
	database = <"database"> 
)
#커넥션 맺고
#커서 생성
mycursor = mydb.cursor()
#쿼리 실행
mycursor.execute(<query>);

예) 테이블 생성

remote = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	database = "zerobase"
)

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

remote.close()

---
결과확인
desc sql_file

-h -P -u -p
-h endpoint -P port -u 마스터 이름 -p 비밀번호

예) 테이블 삭제

remote = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	database = "zerobase"
)

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

remote.close()

---
결과확인
desc sql_file

- Execute SQL File 1

- SQL File 을 실행하기 위한 코드

#커넥션 맺고
mydb = mysql.connecter.connect(
	host = <"hostname">,
	user = <"username">,
	password = <"password">,
	database = <"databaseㅜ믇"> 
)
#커서 생성
mycursor = mydb.cursor()
#쿼리 생성하는데, open함수 써서 sql파일 읽어오게 함
sql = open("<filename>.sql").read()
#커서 통해 sql실행하면 파일이 실행됨
mycursor.execute(sql);

예)

  • test03.sql 생성 = 쿼리;sql파일 table 생성
test03.sql

CREATE TABLE sql_file
(
	id int,
    filename varchar(16)
);
  • test 03.sql 실행
remote = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	database = "zerobase"
)

cur = remote.cursor()

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

remote.close()

---
결과확인
desc sql_file

- Execute SQL File 2

- SQL File 내에 Query가 여러개 존재하는 경우

#커넥션 맺고
mydb = mysql.connecter.connect(
	host = <"hostname">,
	user = <"username">,
	password = <"password">,
	database = <"databasename"> 
)
#커서 생성
mycursor = mydb.cursor()
#쿼리 생성하는데, open함수 써서 sql파일 읽어오게 함
sql = open("<filename>.sql").read()
#커서 통해 sql실행하면 파일이 실행되고, 조건 추가
result = mycursor.execute(sql, multi=True);

예)

  • test04.sql 생성
test04.sql

INSERT INTO sql_file VALEUS (1, "test01.sql"
INSERT INTO sql_file VALEUS (2, "test02.sql"
INSERT INTO sql_file VALEUS (3, "test03.sql"
INSERT INTO sql_file VALEUS (4, "test04.sql"
  • Multi = True 이후 실행
remote = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	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)
        # 아니면 statement로 찍음
remote.commit()
remote.close()

- Fetch ALL

- SELECT실행했을때는 데이터를 변수에 담음

remote = mysql.connector.connect(
    host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
    user = "admin",
    port = 3306,
    password = "prorege4344",
    database='zerobase'
)
cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")
result = cur.fetchall()
for result_iterater in result:
    print(result_iterater)

remote.close()

예) 참고, 검색결과를 Pandas로 읽기

import pandas as pd

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

- Python with CSV

큰 데이터들이 엑셀이나 csv에 들어있는 경우가 있음. mysql에서는 워크벤치나 명령어로 바로 넣을 수 있음. 코딩이 맞지 않을 땐 한꺼번에 안 들어감. 우리나라 사이트에서 다운받은 건 uft-8이 아니라서 더 fail이 자주남. 근데 파이썬으로 하면 금방 해결됨

-> csv에 있는 데이터를 파이썬으로 테이블에 INSERT

- READ CSV

1) 제공받은 police_station.csv를 pandas로 읽어와서 데이터 확인

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

2) zerobase에 연결

import mysql.connecter

conn = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	database = "zerobase"
)

3) cursor만들기

읽어올 양이 많은 경우 cursor 생성시 buffer설정을 해준다

cursor = conn.cursor(buffered= True)

4) INSERT문 만들기 ; 쿼리 만들기

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

한번 insert할 쿼리면 됨. for문 돌면서 반복시킬 것. 값에 대한 부분은 str으로 받을 수 있게 설정

5) 데이터 입력

commit()은 database 에 적용하기 위한 명령

# 판다스로 읽어온 데이터를 df에 담아뒀는데 한줄씩 가져옴
for i, row in df.iterrows():
	#튜플 써서 sql에 값대입 - > 쿼리에 데이터가 들어가서 완성된 상태로 커서를 실행할 것
    cursor.execute(sql, tuple(row))
    # 값찍어보면 나옴
    print(tuple(row))
    # commit하는 순간 database에 적용됨.
    # for문 돌다가 fail이 여섯번째때 나도 다섯번재까지는 db에 들어가 있음.
    # 더 좋은 작업을 할 수 있게 도와줌
    conn.commmit()

6) 결과 확인

cursor.execute("SELECT * FROM police_station")

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

7) 검색결과를 pandas로 읽기

df = pd.DataFrame(result)
df

* tip) csv 한글이 깨지는 경우, encoding값을 'euc-kr'로 설정(특히 우리나라 사이트에서 제공받은 csv파일들)

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

예시) crime_status 테이블에 2020_crime.csv 데이터 입력 코드 작성

1) zerobase에 연결

import mysql.connecter

conn = mysql.connecter.connect(
	host = "database-1.ceyxjjsqhwcr.us-east-1.rds.amazonaws.com",
	user = "admin",
	password = "********",
	port = 3306,
	database = "zerobase"
)

2) 데이터 읽어오기

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

3) INSERT 쿼리 만들기

sql = """INSERT INTO crime_status VALUES ("2020", %s, %s, %s, %s)"""
cursor = conn.cursir(buffered=True)

4) 데이터를 테이블에 insert

for i, row in df.iterrows():
	#튜플 써서 sql에 값대입 - > 쿼리에 데이터가 들어가서 완성된 상태로 커서를 실행할 것
    cursor.execute(sql, tuple(row))
    # 값찍어보면 나옴
    print(tuple(row))
    # commit하는 순간 database에 적용됨.
    # for문 돌다가 fail이 여섯번째때 나도 다섯번재까지는 db에 들어가 있음.
    # 더 좋은 작업을 할 수 있게 도와줌
    conn.commmit()

5) 테이블의 데이터 조회

cursor.execute("SELECT * FROM crime_status")

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

6) 검색결과를 pandas로 읽기

df = pd.DataFrame(result)
df
profile
하은
이전 포스트

[2023.11.29] SQL_SQL File

다음 포스트

[2023.12.01] SQL_PRIMARY KEY, FOREIGN KEY

0개의 댓글