[핀테커스] 230908 postgresql 실습

sql 실습

-- 230908 SQL 실습
-- 1(과제). invoices 테이블에서 가장 높은 총 금액을 가진 청구서의 정보를 선택
SELECT * 
FROM invoices i3 
WHERE Total = (
	SELECT MAX(Total) 
	from invoices i4);


-- 2. tracks 테이블에서 곡 길이가 평균 길이보다 긴 곡들의 정보를 선택
SELECT * 
FROM tracks t 
WHERE Milliseconds > (
	SELECT AVG(Milliseconds) 
	from tracks t2) ;


-- 3(과제). genres 테이블에서 곡 수가 가장 많은 장르의 정보를 선택
SELECT * 
FROM genres g 
WHERE GenreId = (
	SELECT GenreId 
	from tracks t 
	group by GenreId 
	HAVING COUNT(*) 
	ORDER BY COUNT(*) DESC 
	LIMIT 1);


-- 4. albums 테이블과 tracks 테이블을 사용하여 각 앨범의 제목과 해당 앨범의 곡 수를 함께 선택
SELECT a.Title , 
	(SELECT COUNT(*)  
	from tracks t 
	WHERE a.AlbumId = t.AlbumId 
	group by t.AlbumId  )
FROM albums a ;


-- 5(과제). artists 테이블에서 각 아티스트의 이름과 해당 아티스트가 발매한 첫 번째 앨범의 제목을 함께 선택
SELECT a2.Title , (
	SELECT a3.Name  
	from artists a3 
	WHERE a3.ArtistId = a2.ArtistId 
	LIMIT 1) 
FROM albums a2 ;

 

 

Python 을 사용하여 Postgresql 제어

pip install sqlite pandas¶

In [ ]:

!pip install pandas pysqlite3
#!pip install sqlite3 

일반적인 방법[sqlite]¶

In [ ]:

import sqlite3
conn = sqlite3.Connection('../0907_chinook.db')
#conn = sqlite3.connet('../0907_chinook.db')
c = conn.cursor()
query = '''
SELECT *
FROM albums
'''
c.execute(query)

Out[ ]:

<sqlite3.Cursor at 0x1066922d0>

In [ ]:

c.fetchone() # 전체 가져오기
print(c.fetchone()) # 한줄씩 가져오기

(1, 'For Those About To Rock We Salute You', 1)

In [ ]:

c.close()
conn.close()

판다스로 가져오기¶

In [ ]:

import sqlite3
import pandas as pd

conn = sqlite3.Connection('../0907_chinook.db')
c = conn.cursor()
query = '''
SELECT *
FROM albums
'''

df = pd.read_sql(query, conn)
df.head()

Out[ ]:

	AlbumId	Title	ArtistId
0	1	For Those About To Rock We Salute You	1
1	2	Balls to the Wall	2
2	3	Restless and Wild	2
3	4	Let There Be Rock	1
4	5	Big Ones	3

Postgresql¶

패키지 설치¶

In [ ]:

!pip install psycopg2-binary sqlalchemy

PostgreSql 연결 테스트¶

In [ ]:

import psycopg2

db_params = {
    'host' : '< 로컬호스트 혹은 서버 주소 >',
    'database' : 'postgres',
    'user' : 'postgres',
    'password' : 'postgres',
    'port' : '5432'
}

# alter user postgres with password 'postgres'; # postgresql 콘솔에서 패스워드 설정 함. 기본값은 0000, 1234

In [ ]:

# 종료까지 완성된 코드를 실행해야 하므로 예외처리 문을 사용한다.
try:
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor() # cursor 란 쿼리문에 의해서 반환되는 결과값들을 저장하는 메모리 공간
    print('db 연결 성공')

    create_table_query = 'CREATE TABLE IF NOT EXISTS exmple_table (id SERIAL PRIMARY KEY, DATA VARCHAR)'
    insert_data_query = "INSERT INTO exmple_table (data) VALUES ('HI')"
    cursor.execute(create_table_query)
    cursor.execute(insert_data_query)
    conn.commit()

    cursor.execute('select * from exmple_table;')
    print(cursor.fetchall())
    print('테이블 생성 완료!')

except (Exception, psycopg2.Error) as e:
    print(f'db 연결 실패 : {e}')
finally:
    if conn:
        conn.close()
        print('db 연결 종료')

db 연결 성공
[(1, 'HI')]
db 연결 종료

Postgresql 실습¶

In [ ]:

# 데이터 예시
import pandas as pd
df = pd.read_csv('https://raw.githubusercontent.com/jin0choi1216/dataset/main/AAPL.csv')
df.head(3)

Out[ ]:

	Date	Open	High	Low	Close	Adj Close	Volume
0	2012-01-03	14.621429	14.732143	14.607143	14.686786	12.466090	302220800
1	2012-01-04	14.642857	14.810000	14.617143	14.765714	12.533089	260022000
2	2012-01-05	14.819643	14.948214	14.738214	14.929643	12.672229	271269600

In [ ]:

import psycopg2
from sqlalchemy import create_engine

db_params = {
    'host' : '< 로컬호스트 혹은 서버 주소 >',
    'database' : 'sba',
    'user' : 'postgres',
    'password' : 'postgres',
    'port' : '5432'
}

In [ ]:

# 데이터 삽입 df.to_sql()
try:
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor()
    engine = create_engine(f'postgresql://<user>:<password>@<host>/<db_name>')
    print('db 연결 성공')

    with engine.connect() as con:
        df.to_sql('aapl', con, if_exists='replace')

    # 'aapl' 테이블 조회
    cursor.execute('select * from aapl;')
    print(cursor.fetchall())

except (Exception, psycopg2.Error) as e:
    print(f'오류 발생 : {e}')
finally:
    if conn:
        conn.close()
        print('db 연결 종료')

db 연결 성공
db 연결 종료

In [ ]:

# 데이터 불러오기 df.read_sql()
try:
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor()
    engine = create_engine(f'postgresql://<user>:<password>@<host>/<db_name>')
    print('db 연결 성공')

    with engine.connect() as con:
        aapl = pd.read_sql('SELECT * FROM aapl', con)
        

except (Exception, psycopg2.Error) as e:
    print(f'오류 발생 : {e}')
finally:
    if conn:
        conn.close()
        print('db 연결 종료')

db 연결 성공
db 연결 종료

In [ ]:

aapl.head()

Out[ ]:

	index	Date	Open	High	Low	Close	Adj Close	Volume
0	0	2012-01-03	14.621429	14.732143	14.607143	14.686786	12.466090	302220800
1	1	2012-01-04	14.642857	14.810000	14.617143	14.765714	12.533089	260022000
2	2	2012-01-05	14.819643	14.948214	14.738214	14.929643	12.672229	271269600
3	3	2012-01-06	14.991786	15.098214	14.972143	15.085714	12.804703	318292800
4	4	2012-01-09	15.196429	15.276786	15.048214	15.061786	12.784389	394024400

 

 

 

---