04/FEB/2024

뒤에 몇개의 조건문이 다 Not인 경우 다 ()로 뒤에 묶어서 Not은 괄호 앞에 하나만 써주면 다 부정된다.

여러 문자열을 하나로 합치거나 연결

CONCAT 뒤에 AS 를 붙여서 별칭을 입력 - () 뒤에 그냥 ‘’안에 입력해도 AS 뒤에 입력한 것과 같은 효과 - AS 생략가능

SELECT CONCAT(’str1’, ‘str2’,…)

칼럼이나 테이블 이름에 별칭 생성

SELECT pandas AS pd

문법1 - Column : SELECT column AS alias FROM tablename

문법2 - Table : SELECT * FROM tableneme AS alias

검색한 결과의 중복 제거

SELECT DISTINCT column1…, column2… FROM tablename;

검색결과를 정렬된 순으로 주어진 숫자만큼만 조회

SELECT column1, column2 FROM tablename, WHERE conditon LIMIT number;

AWS에서 제공하는 관계형 데이터베이스 서비스

Cloud상에서 Database 구축

https://portal.aws.amazon.com/billing/signup#/start

AWS RDS 외부접속 설정 (외부 어디에서나 접속 가능한)

VPC 보안 그룹 > 인바운드 규칙 > 인바운드 규칙 편집 > 규칙추가 > … 하기의 유형 및 소스 설정 … > 규칙저장

유형-MYSQL/Aurora

소스-AnywhereIPv4

규칙 저장하면 프로토콜-TCP 으로 되어있음.

연결&보안 - 앤드포인트 주소 : 추후 호스트 주소르 쓸 것임. / 포트 : 호스트의 포트.

% mysql -h "<엔드포인트주소>" -P <포트넘버> -u <마스터 사용자 이름> -p

데이터베이스 소스 확인 : select host, user from user;

이때에 외부 호스트는 ‘%’로 표시.

750 시간이 넉넉하지는 않으니까, 사용하지 않을 때는 중지하면 좋다.

시작하고싶은 데이터베이스 선택 후, DB 식별자 페이지에 가면 작업 목록에 ‘시작’이 있다. 그것을 누르면 시작된다.

1. 폴더 만들기

% cd Documents

% mkdir foldername

Document % cd foldername

foldername %

1. 폴더 하위에 다음을 실행. (현재 폴더를 VSCode 로 열라는 뜻)

foldername % code .

1. VSCode의 하위 폴더 foldername에서 새파일 선택
2. 생성할 파일 이름을 정하고, 파일 확장자를 sql로 입력하면 비어있는 SQL 파일이 생성된다.

SQL file 이란? : SQL 쿼리를 모아놓은 파일

1. 로그인 이후

source </path/filename.sql>

\. </path/filename.sql>

\. <filename.sql>

(source 대신 \. 사용가능)

(현재 폴더에 파일이 있으면 path 생략 가능)

1. Visual Studio Code에서 파일안에 테이블 생성
2. 폴더 위치로 이동 하여 데이터베이스 접속

% cd foldername

foldername % mysql - u root -pPW

1. SQL File 실행

source test01.sql

MYSQL에 접속하지 않고 터미널에서 실행하는 방법

데이터베이스 접속 방법 : mysql -u username -p <database> < </path/filename.sql>

1. Visual Studio Code에서 데이터베이스 파일 안에 테이블 생성
2. 터미널에서 데이터베이스에 접속하면서 SQL File 실행

% mysql -u root -p DBname < test02.sql

1. 데이터베이스에 접속하여 결과 확인

% mysql -u root -p PW

desc tablename_status;

SQL File - Database Back up

SQL File로 Database를 백업할 수 있다.

1. 특정 Database 백업

% mysqldump -u username -p dbname > backup.sql

2. 모든 Database 백업

% mysqldump -u username —all-databases > backup.sql

백업ex) % mysqldump -u root -p DBname > backup.sql

웹 백업 ex) % mysqldump --set-gtid-purged=off -h <"앤드포인트주소”> -P <포트넘버> -u <마스터 사용자 이름> -p DBname > backupfilename.sql

데이터베이스를 백업한 SQL File 을 실행하여 그 시점으로 복구하거나 이전할 수 있다.

(SQL File 을 실행하는 방법과 동일)

[예시]

1. AWS RDS (database-1) 서비스가 사용 가능한 상태에서 접속

foldername % mysql -h “<앤드포인트주소>” -P <포트넘버> -u <마스터 사용자 이름> -p

2. AWS RDS (database-1) 의 Filename Database 를 복원

filelocaion1/filelocation2/source Filename.sql

Table 단위로도 백업할 수 있다.

foldername & mysqldump -u username -p dbname tablename > backup.sql

SQL File - Table Restore

Table 을 백업한 SQL File을 실행하여, 해당 테이블을 복구하거나 이전할 수 있다.

(SQL File을 실행하는 방법과 동일함)

[예시]

1. AWS RDS (database-1)의 DBname 에서 Tablename을 삭제

foldername % mysql -h “<앤드포인트주소>” -P <포트넘버> -u <마스터 사용자 이름> -p DBname

…

drop table tablename;

2. AWS RDS (database-1)의 DBname 에서 Tablename 복구

source filename.sql

…

3. 결과 확인

select * from Tablename;

SQL File - Table Schema Backup

데이터를 제외하고 테이블 생성 쿼리만 백업할 수 있다.

1. 특정 Table Schema Backup

% mysqldump -d -u username -p dbname tablename > backup.sql

2. 모든 Table Schema Backup

% mysqldump -d -u username -p dbname > backup.sql

Python with MYSQL - 실행위치

Jupyter notebook 과 mysql은 모두 sql_ws(예제폴더)폴더에서 시작한다.

• Jupyter 파일 형식 : filename.ipynb
• conda create -n ds_study python=3.9

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

pip install mysql-connector-python

import mysql.connector

MySQL 에 접속하기 위한 코드

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

1. Local Database 연결

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

2. AWS RDS (database-1) 연결

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

)

local.close()

or

DBname.close()

or

remote.close()

import mysql. connector mydb = mysql. connector. connect ( host = "<hostname>" user = "<username>" password = "‹password>" database = "«databasename>" ) mycursor = mydb. cursor ( ) mycursor.execute (<query>);

테이블 생성 ex

remote = mysql.connector. connect ( host = "앤드포인트주소", port = 3306, user = "admin" password = "**********" database = "DBname" cur = remote. cursor ( ) cur.execute ("CREATE TABLE sql_file (id int, filename varchar (16))") remote. close ()

결과확인 ex

desc tablename;

테이블 생성 ex

remote = mysql. connector. connect ( host = "앤드포인트주소, port = 3306, user = "admin", password = "**********" database = "DBname" cur = remote.cursor ) cur. execute ("DROP TABLE sql_file") remote. close ()

mydb = mysql. connector. connect ( host = "<hostname>" user = "<username>" password = "‹password>" database = "«databasename>" ) mycursor = mydb. cursor () sql = open ("<filename>.sql"). read () mycursor.execute (sql)

예제

1. filename.sql 생성

CREATE TABLE sql_file ( id int, filename

)

2. filename.sql 실행

remote = mysql. connector. connect ( host = "앤드포인트주소", port = 3306, user = "admin" password = "**********" database = "DBname" cur = remote.cursor! ( sql = open ("filename.sql").read() cur.execute (sal)

) remote.close ( )

mydb = mysql. connector. connect ( host = "<hostname>" user = "<username>" password = "‹password>" database = "‹databasename>" ) mycursor = mydb. cursor () sql = open ("<filename>.sql").read() result = mycursor. execute (sql, multi=True)

예제

1. filename.sql 생성

INSERT INTO sql_file VALUES (1,"filename1.sql "); INSERT INTO sql_file VALUES (2, "filename.2sql "); INSERT INTO sql_file VALUES (3, "filename.3sql "); INSERT INTO sql_file VALUES (4, "filename4.sql ");

2. filename.sql 실행

remote = mysql. connector. connect ( host = "앤드포인트주소" port = 3306, user = "admin" password = "**********" database = "DBname") cur = remote. cursor () sql = open ("filename. 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 ()

mycursor.execute (<query>) result = mycursor.fetchall () for data in result: print (data)

예제

1. 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)

remote = mysql. connector. connect ( host = "앤드포인트주소" port = 3306, user = "admin" password = "**********" database = "DBname”

) cur = remote. cursor(buffered=True) cur.execute ("SELECT * FROM filename") result = cur. fetchall () for result iterator in result: print(result iterator) remote. close ()

2. 검색결과를 Pandas 로 읽기

import pandas as pd df = pd. DataFrame (result) df. head ()

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

1. 제공받은 police_station.csv 를 Pandas 로 읽어와서 데이터를 확인한다.

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

1-2) 데이터 끝을 확인하고 싶은 경우

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

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

cursor = conn.cursor(buffered=True)

3. Insert문 만들기

sql = "INSERT INTO tablename VALUES (%s, %s)”

4-1) 데이터입력 을 위한 commit()

for i, row in df.iterrows():
    print(row)

4-2) 데이터입력 을 위한 commit() - tuple 형태

for i, row in df.iterrows():
    print(tuple(row))

4-2) 데이터입력 을 위한 commit() - tuple 형태 - sql 에 담기

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

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

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

5. 결과 확인

cursor.execute ("SELECT * FROM tablename") result = cursor. fetchall () for row in result: print (row)

6. 검색결과를 pandas 로 읽기

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

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

import pandas as pd df = pd. read _csv ('filename.csv', encoding='euc-kr') df. head ()

아무래도 파이썬 부분은 복습을 빡세게 해야할듯

Studied from 제로베이스 데이터 분석 스쿨