<수업 내용>
서브쿼리
- SQL 문장 안에 포함된 또 다른 SQL 문장
- 주로 SELECT, INSERT, UPDATE, DELETE 문 에서 사용
- 서브쿼리는 외부쿼리의 결과에 영향을 주거나 의존하며, 외부 쿼리 안에서 값을 도출하거나 조건을 만족하는지 확인하기 위해 사용
- 서브 쿼리의 활용
- 하나의 변수 처럼 사용
- 테이블을 리턴 받아 사용
- 하나의 칼럼처럼 사용
EX)
SELECT CustomerId
FROM invoice
WHERE Total <= (SELECT AVG(Total)
FROM invoice);
# 서브쿼리가 변수처럼 활용된 경우SELECT *
FROM (
SELECT CustomerId
FROM customer
WHERE CustomerId < 10
);
# 서브쿼리가 테이블 처럼 활용된 경우SELECT customer.LastName,
(SELECT COUNT( * )
FROM invoice
WHERE customer.CustomerId = invoice.CustomerId)
AS Invoi ceCount
FROM customer;
# 서브쿼리가 컬럼처럼 활용된 경우실습
- tracks 테이블에서 곡 길이가 평균 길이보다 긴 곡들의 정보를 선택
SELECT *
FROM tracks
WHERE Milliseconds > (SELECT AVG(Milliseconds) from tracks)
2. albums 테이블과 tracks 테이블을 사용하여 각 앨범의 제목과 해당 앨범의 곡 수를 함께 선택
SELECT count(tracks.TrackId),
(select albums.Title from albums WHERE albums.AlbumId =tracks.AlbumId)
FROM tracks 
SELECT a.title,(select count(t.TrackId) from tracks t where t.AlbumId=a.AlbumId)
from albums a 
트랜잭션
- 트랜잭션이란 데이터베이스의 상태를 변화시키는 연산의 논리적 단위
- 여러 개의 쿼리가 실행되는 경우, 이들 쿼리는 하나의 트랜잭션으로 묶인다.
- 트랜잭션은 원자성(Atomicity), 일관성(Consistency), 독립성(Isolation), 지속성(Durability)의 4가지 특성을 지니게 된다
- 원자성 (Atomicity): 트랜잭션은 "모두 수행" 또는 "전혀 수행하지 않음"의 원자적인 단위로 처리되어야 한다. 즉, 트랜잭션 내의 모든 연산은 전부 수행되거나 전혀 수행되지 않아야 한다.
- 일관성 (Consistency): 트랜잭션이 실행 전후에 데이터베이스는 일관된 상태를 유지해야 한다. 즉, 트랜잭션 실행 전에 유효한 제약 조건과 규칙을 만족하고, 트랜잭션이 성공적으로 완료된 후에도 일관된 상태여야 한다.
- 격리성 (Isolation): 트랜잭션은 동시에 여러 개가 실행될 때, 각각의 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다. 각 트랜잭션은 마치 동시에 실행되는 것처럼 보여야 하며, 한 트랜잭션이 다른 트랜잭션의 영향을 받지 않아야 한다.
- 지속성 (Durability): 트랜잭션이 성공적으로 완료되면, 그 결과는 영구적으로 저장되어야 한다. 시스템 장애 또는 전원 손실과 같은 예기치 않은 상황이 발생하더라도 트랜잭션의 결과는 영구적으로 보존되어야 한다
DB API
일반적인 데이터 불러오기[sqlite]
! pip install pysqlite3
import sqlite3
conn = sqlite3.connect("./chinook.db")
conn
c=conn.cursor()
query=''' SELECT *
FROM albums
'''
c.execute(query)
c.fetchall() #테이블 다 불러오기
c.fetchone() #테이블 한 줄씩 불러오기
c.close()
conn.close()
판다스로 데이터 불러오기[sqlite]
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()URI
- 원격으로 데이터 베이스를 연결할 때에는 URI 형식으로 연결
postgres 연결 및 데이터 불러오기
! pip install psycopg2
! pip install sqlalchemy
import psycopg2
db_params ={
"host":"localhost",
"database":"postgres",
"user":"postgres",
"password":"0000",
"port":"5432"
}
try:
conn = psycopg2.connect(**db_params)
cursor=conn.cursor()
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()
query=''' SELECT *
FROM example_table
'''
cursor.execute(query)
print(cursor.fetchall())
print("테이블 생성 완료")
except(Exception, psycopg2.Error) as error:
print(error)
finally:
if conn:
conn.close()
print("데이터베이스 연결 종료")
클라우드 데이터 베이스 연결 및 데이터 불러오기[Elephant SQL]
import psycopg2
db_params ={
"host":"snuffleupagus.db.elephantsql.com",
"database":"dxamdbqz",
"user":"dxamdbqz",
"password":"se4C9znG6e_gfsRY5JVX8Wq6RAUylSVK",
"port":"5432"
}
판다스로 데이터[github의 CSV파일] 불러오고 클라우드 DB에 데이터 입력하기
import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/jin0choi1216/dataset/main/AAPL.csv')
df.head(3)
import psycopg2
from sqlalchemy import create_engine
db_params ={
"host":"snuffleupagus.db.elephantsql.com",
"database":"dxamdbqz",
"user":"dxamdbqz",
"password":"se4C9znG6e_gfsRY5JVX8Wq6RAUylSVK",
"port":"5432"
}
try:
conn = psycopg2.connect(**db_params)
cursor=conn.cursor()
engine=create_engine('postgresql://dxamdbqz:se4C9znG6e_gfsRY5JVX8Wq6RAUylSVK@snuffleupagus.db.elephantsql.com/dxamdbqz')
with engine.connect() as con:
df.to_sql('aapl', con, if_exists='replace')
print("테이블 생성 완료")
except(Exception, psycopg2.Error) as error:
print(error)
finally:
if conn:
conn.close()
print("데이터베이스 연결 종료")
DB 입력한 데이터를 판다스로 읽어오기
import psycopg2
from sqlalchemy import create_engine
db_params ={
"host":"snuffleupagus.db.elephantsql.com",
"database":"dxamdbqz",
"user":"dxamdbqz",
"password":"se4C9znG6e_gfsRY5JVX8Wq6RAUylSVK",
"port":"5432"
}
try:
conn = psycopg2.connect(**db_params)
cursor=conn.cursor()
engine=create_engine('postgresql://dxamdbqz:se4C9znG6e_gfsRY5JVX8Wq6RAUylSVK@snuffleupagus.db.elephantsql.com/dxamdbqz')
with engine.connect() as con:
aapl=pd.read_sql('SELECT * FROM aapl', con)
print("테이블 생성 완료")
except(Exception, psycopg2.Error) as error:
print(error)
finally:
if conn:
conn.close()
print("데이터베이스 연결 종료")
aapl.head()
