34번째 스터디노트 / SQL 심화 - SQL file, Database 백업, Python과 SQL

※제로베이스 데이터 취업스쿨 11기 수강 중

📗 34번째 공부 내용 요약

 

SQL 심화 내용을 학습하였다.

1. AWS RDS : AWS 계정을 생성하고,  로컬에서 RDS에 접속해 데이터 베이스를 생성 및 조작

2. SQL FILE : sql파일을 실행하는 방법과, 데이터베이스/테이블/테이블스키마를 sql파일로 백업하는 방법 학습

3. Python with MySQL : 파이썬으로 데이터베이스에 접속하고, sql 쿼리와 sql 파일을 실행하는 방법 학습

4. Python wiht CSV : 파이썬에서 CSV파일을 불러와, 데이터베이스의 테이블로 입력하는 방법 학습

---

📖  34번째 공부 내용 자세히

2. SQL FILE

1) SQL 파일

• SQL 쿼리를 모아놓은 파일
• SQL 확장자를 가진 파일
• SQL 파일에 실행할 SQL 쿼리를 모아서 작성 후, SQL 파일을 실행하면 작성된 쿼리가 한번에 실행됨

# SQL 파일 실행하기 => source 명령어

mysql -u root -p <password>
use <database_name>
source <파일명>.spl

 

2) Database Backup

• SQL 파일로 데이터베이스를 백업할 수 있다

#특정 database backup
mysqldump -u <username> -p <dbname> > <backup>.sql 

# 모든 database backup
mysqlcump -u <username> -p --all-databases > <backup>.sql

# AWS RDS database backup
mysqldump --set-gtid-purged=OFF -h "<host>" -P <portnumber> -u <username> -p <dbname> > <backup>.sql

 

3) Database Restore

• 데이터베이스를 백업한 SQL을 실행하면 그 시점으로 복구하거나 이전할 수 있다
• SQL파일을 실행하는 방법과 동일하다 → source <파일명>.sql

 

4) Table Backup

• 테이블 단위로도 백업할 수 있다

mysqldump -u <username> -p <dbname> <tablename> > <backup>.sql

 

5)  Table Restore

• Table을 백업한 SQL 파일을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다
• SQL파일을 실행하는 방법과 동일하다 → source <파일명>.sql

 

4) Table Schema Backup

• 테이블을 제외하고 테이블 생성 쿼리만 백업할 수 있다

# 특정 table backup
mysqldump -d -u <username> -p <dbname> <tablename> > <backup>.sql

# 모든 table backup
mysqldump -d -u <username> -p <dbname> > <backup>.sql

 

3. Python with MySQL

 

1) MySQL Driver 설치

#터미널에서 실행 (ds_study 환경어세)
pip install mysql-connector-python

#설치확인(vs코드)
import mysql.connector

 

2) Create Connection / Close DataBase

• mysql.connector을 import하여 파이썬에서 local Database와 aws database에 연결할 수 있다

import mysql.connector

#Local Database에 연결
local = mysql.connector.connect(
    host = '<hostname>',
    user = '<username>',
    password = '<password>'
)

#AWS RDS에 연결
remote = mysql.connector.connect(
    host = '<hostname>',
	port = <portnumber>,
    user = '<username>',
    password = '<password>',
		database = '<dbname>' #특정 Database에 접속
)

#close database
remote.close()

 

3) Execute SQL

• excute( ) 괄호 안데 쿼리를 작성하면 실행 가능하다

import mysql.connector

mydb = mysql.connector.connect(
	 host = '<hostname>',
		port = <portnumber>,
    user = '<username>',
    password = '<password>',
		database = '<dbname>' 
)

mycursor = mydb.cursor() #읽어올 데이터 양이 많을 경우 buffered=True
mycursor.execute( <query> );

 

4) Execute SQL File

• 변수 = open("<filename>.sql").read() 를 작성하고, excute() 괄호에 변수를 넣어주면 sql 파일을 실행할 수 있다

import mysql.connector

mydb = mysql.connector.connect(
	host = '<hostname>',
	port = <portnumber>,
    user = '<username>',
    password = '<password>',
	database = '<dbname>' 
)

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql)
#mycursor.execute(sql, multi=True) #sql 파일 내에 쿼리가 여러개일 경우
mydb.commit()

 

5) Fetch All

• fetchall()을 활용해 excute의 실행 결과를 알 수 있다

mycursor.excute(<query>)

result = mycursor.fetchall()
for data in result:
	print(data)

 

4.Python with CSV

파이썬에서 CSV 파일을 읽어와 데이터프레임으로 만든뒤, database의  테이블로 넣을 수 있다

# 1. csv 파일 pandas로 읽어오기
import pandas as pd
df = pd.read_csv("<filename>.csv")
df.tail()


# 2. database 연결
import mysql.connector

conn = mysql.connector.connect(
	host = '<hostname>',
    port = <portnum>,
    user = '<username>',
    password = '<password>',
    database = '<dbname>'
)


# 3. cursor 만들기
cursor = conn.cursor(buffered=True)


# 4. insert문 만들기
sql = 'INSERT INTO <tablename> VALUES (%s, %s, ..., %s)'


# 5. 데이터 입력
for i, row in df.iterrows():
	cursor.execute(sql, tuple(row))
    conn.commit()
    
    
# 6. 결과 확인
cursor.execute('SELECT * FROM <tablename>')

result = cursor.fetchall()

for row in result:
	print(row)
    
    
# 7. 검색 결과를 pandas로 읽기
df = pd.DataFrame(result)

---

➰ 34번째 후기

어쩌다보니 밀려서 저번주에 학습한 내용을 지금 스터디노트로 쓰고 있는데, 복습이 되어 오히려 좋아..^^

파이썬에서도 SQL 쿼리들을 실행해보는 것이 색달랐고, 계속 터미널로 하느라 불편했는데 vs code로 하니 편하고 좋았다는 점,,,ㅎㅎㅎ

저번에 했을 때는 몇개의 명령어가 실행이 안되어서 곤욕이었는데, 복습할 때 하니 제대로 되어 쵸콤 당황했다,, 뭘까!

얼른 스터디노트도 현재 진도에 맞춰지게 빠릿빠릿 작성해야겠다.

---

※본 내용은 제로베이스 데이터 취업 스쿨에서 제공하는 학습 내용에 기반합니다.