SQL 9일차 chapter13(~12)

실습환경

police_station 백업자료 만들기

PS C:\Users\user\Documents\sql_ws> mysqldump --set-gtid-purged=off -h "database-1.cnpqvifjczbt.us-east-2.rds.amazonaws.com" -P 3306 -u admin -p zerobase police_station > backup_police.sql

기존 police_station 테이블 내 데이터 삭제

delete from police_station;

주피터노트북 파일생성

python with mysql

MYSQL Driver 설치

pip install mysql-connector-python

로컬,원격 데이터베이스 연결

연결해제

create connection 예제

python with mysql execute

쿼리실행코드

테이블생성

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

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

remote.close()

sql_file 확인

테이블 삭제

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

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

remote.close()

sql_file 삭제된것을 확인

테이블 파일 실행 (테스트03 파일 따로 생성 후)

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

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

remote.close()

sql_file 다시 생김

sql file 내 쿼리가 여러개 존재하는 경우(테스트04 파일 생성 후)

remote = mysql.connector.connect(
    host = "database-1.cnpqvifjczbt.us-east-2.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()

*for문은 확인용 / 안써도 적용은 됨

*fetch all (전체나열함수 / 레코드를 배열형식으로 저장)

remote = mysql.connector.connect(
    host = "database-1.cnpqvifjczbt.us-east-2.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()

*buffered는 데이터량이 많을때 사용

python with mysql CSV

csv에 있는 데이터를 파이썬으로 INSERT

police station 데이터 읽어오기

데이터베이스에 연결

import mysql.connector

conn = mysql.connector.connect(
    host ="database-1.cnpqvifjczbt.us-east-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "",
    database = "zerobase"

커서 만들기

cursor = conn.cursor(buffered=True)

인서트문 만들기

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

데이터입력 (commit은 데이터베이스에 적용하기 위한 명령)

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

sql과 튜플이 위 %s에 하나씩 들어감

결과확인

cursor.execute("select * from police_station")
result = cursor.fetchall()
result

검색결과 pandas로 읽기

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