2주차(6) - [SQL] 서브쿼리, 트랜젝션, DB API

1. 서브쿼리

• SQL 문장 안에 포함된 또 다른 SQL문장
• SELECT / INSERT / UPDATE / DELETE문에서 사용
• GROUP BY / JOIN의 기능을 효율적으로하기 위함
• 서브쿼리 용도 - 변수 / 테이블 / 칼럼으로 이용하기 위해

서브쿼리 - 변수 용도

SELECT CustomerId
FROM invoice
WHERE Total <= (SELECT AVG(Total) FROM invoice);

서브쿼리 - 테이블 용도

SELECT *
FROM(
	SELECT CustomerId
	FROM customer
	WHERE CustomerId < 10)

 

상하의 구문이 똑같다는 의미.

실습

예제 #1 실습

예제 #2 실습

SELECT TrackId,Name,Milliseconds
FROM tracks
WHERE Milliseconds >= (
	SELECT AVG(Milliseconds)
	FROM tracks
	)

예제 #3 실습

예제 #4 실습

SELECT a.Title, (
	SELECT COUNT(*) 
	FROM tracks t
	WHERE t.AlbumId = a.AlbumId)
as trackCount
FROM albums a

예제 #5 실습

(5번) ORDER BY 2 DESC LIMIT 5 → LIMIT 함수 사용

2. 트랜젝션

트랜젝션

• 데이터베이스 상태를 변화시키는 연산의 논리적 단위
• 여러개 쿼리 실행 시, 쿼리는 하나의 트랙잭션으로 묶인다
  • 도중에 끊기면 안되기
  1. 원자성: 모두 수행 / 수행되지 않음 두 상태만 있어야
  2. 일관성: 일관된 상태 유지
  3. 독립성: 각 트랜잭션은 독립적
  4. 지속성: 반영이 됐다면, 영구적

→ 트랜잭션의 안정성 보장을 위한 성질

NULL

• 데이터베이스에 아무 값이 존재하지 않음

NULL을 포함한 데이터 연산은 결과가 NULL

• 곱하거나 더해도 NULL

3. DB API

• DB - 파이썬 연결
• 파이썬을 활용해 데이터 베이스에서 데이터 가져와서 분석 / 모델링을 진행
• 데이터를 요청하고, 불러오고, 수정하는 과정을 거친다
• DB 마다 Python DB API가 다르다

실습예제

일반적 방법[sqlite]

• 폴더 생성
• chinook.db를 연결해주고 connect 함수 실행

쿼리 동작(일반적 방법)

   import sqlite3
   
   conn = sqlite3.connect('chinook.db')
   
   c = conn.cursor()
   
   query = '''
   SELECT *
   FROM albums
   '''
   
   c.execute(query)

---

쿼리 불러오기

    c.fetchall() #쿼리 전부 불러오기
    c.fetchone #한 줄만 불러오기

• sql활용을 통해 데이터 베이스 연결
• python으로 여는 코드

---

쿼리 끝내기

 c.close() -> 커서 끝내기
 conn.close() -> 커넥션 끝내기

• 데이터베이스 입장에서 끝내는 것이 바람직
• 판다스로 가져오기

판다스로 가져오기

 import sqlite3
 import pandas as pd
 
 conn = sqlite3.connect('chinook.db')
 
 c = conn.cursor()
 
 query = '''
 SELECT *
 FROM albums
 '''
 
 df = pd.read_sql(query, conn)
 df.head()

---

psycopg2 / sqlalchemy 설치

 !pip install psycopg2-binary
 !pip install sqlalchemy

---

데이터 제공

 import psycopg2
 
 db_params = {
     "host": '',
     'database': '',
     'user': '',
     'password': '',
     'port': ''
 }

    try:
        conn = psycopg2.connect(**db_params)
        cursor = conn.cursor()
        print('데이터베이스 연결 성공')
    
        #테이블 만들기
        create_table_query='CREATE TABLE IF NOT EXISTS example_table(id SERIAL PRIMARY KEY, data VARCHAR)'
        insert_data_query="INSERT INTO example_table (data) VALUES ('HI')"
        #테이블 만든 후 실행
        cursor.execute(create_table_query)
        cursor.execute(insert_data_query)
    
        #테이블 내보내기(커밋)
        conn.commit()
    		
    		#데이터 example_table 만들기
        read_data_query = 'SELECT * FROM example_table'
        cursor.execute(read_data_query)
        print(cursor.fetchall())
        print('테이블 생성 완료')
    
    except(Exception, psycopg2.Error) as error:
        print(error)
    
    finally:
        if conn:
            conn.close()
            print('데이터베이스 연결 종료')

ElephantSQL

https://customer.elephantsql.com/instance/

postgresql://:@:/<database_name>

• <username> : 데이터베이스 사용자 이름
• <password> : 데이터베이스 사용자 비밀번호
• <host> : 데이터베이스 서버 호스트 이름 또는 IP 주소
• <port> : 데이터베이스 서버 포트 번호 (기본값: 5432)
• <database_name> : 연결할 데이터베이스 이름

postgres://vgkkoxqw:NcGCrHZaUkcooNsc7dS9NzKEUL-O7tSl@floppy.db.elephantsql.com/vgkkoxqw

URI 이해하기

제공해야 하는 데이터

• 서비스
• 유저이름
• 유저 비밀번호
• 호스트
• 포트번호
• 경로(or 데이터베이스 이름):

→ scheme:[//[user[:password]@]host[:port]][/path][?query][#fragment]

ElephantSQL 연동 시키기

import psycopg2
from sqlalchemy import create_engine
db_elephant = {
    "host": 'floppy.db.elephantsql.com',
    'database': 'vgkkoxqw',
    'user': 'vgkkoxqw',
    'password': 'NcGCrHZaUkcooNsc7dS9NzKEUL-O7tSl',
    'port': '5432'
}

try:
    conn = psycopg2.connect(**db_elephant)
    cursor = conn.cursor()
    engine = create_engine('postgresql://vgkkoxqw:NcGCrHZaUkcooNsc7dS9NzKEUL-O7tSl@floppy.db.elephantsql.com/vgkkoxqw')
    print('데이터베이스 연결 성공')
    with engine.connect() as con:
        df.to_sql('aapl', con, if_exists='replace')
        
except(Exception, psycopg2.Error) as error:
    print(error)

finally:
    if conn:
        conn.close()
        print('데이터베이스 연결 종료')

• 구조짜기

import psycopg2
from sqlalchemy import create_engine
db_elephant = {
    "host": 'floppy.db.elephantsql.com',
    'database': 'vgkkoxqw',
    'user': 'vgkkoxqw',
    'password': 'NcGCrHZaUkcooNsc7dS9NzKEUL-O7tSl',
    'port': '5432'
}

try:
    conn = psycopg2.connect(**db_elephant)
    cursor = conn.cursor()
    engine = create_engine('postgresql://vgkkoxqw:NcGCrHZaUkcooNsc7dS9NzKEUL-O7tSl@floppy.db.elephantsql.com/vgkkoxqw')
    print('데이터베이스 연결 성공')
    with engine.connect() as con:
        #sql 가져오기
        aapl = pd.read_sql('SELECT * FROM aapl', con)
        
except(Exception, psycopg2.Error) as error:
    print(error)

finally:
    if conn:
        conn.close()
        print('데이터베이스 연결 종료')

aapl.head()