PostgreSQL 설치 및 사용법

/* PostgreSQL 설치 및 사용법

 * (1) 설치(PostgreSQL과 DBeaver): Youbube를 참고하여 설치하였음

 *      https://www.youtube.com/watch?v=SJMcIZzfBeY

 * (2) SQL 기본 구문 구조 이해

 *      https://www.youtube.com/watch?v=EbWewyxezi0

 * (3) 파이썬 연결 관련

 *      https://www.bearpooh.com/147

 *      https://www.youtube.com/watch?v=yzHa1S3OO2s

 * (4) SQL shell 명령(psql)

 * (5) 이미지 파일의 저장(BYTEA)

 * (6) 저장된 파일 경로

 *      d:\2023\postgresql\test_script.sql

 *      data도 있음

 */

/* (1) PostgreSQL과 DBeaver 설치

 *   Win10기준으로 Youtube를 참조하여 설치

 */

--자세한 내용은 Youtube를 참조함

/*

 * (2) SQL 기본 구문 구조 이해

 *   테스트 data는 (2)의 Youtube에 있음

 *   DBeaver에서 테스트(test_scripts.sql)

 */

-- 실행할 구문 선택 후 ^+Enter로 실행

select * from prod_cat_info;

select * from transactions;

select * from city;

select * from customer;

/* 테스트(필터링) 구문 */

select          count(distinct a.transaction_id) as cnt_orders

    ,       sum(a.total_amt) as sum_amt

    ,       max(a.tran_date) as latest_tran_date

from        transactions a

--where         total_amt > 0

--group by  customer_id

--having        count(discount transaction_id) >= 2

--order by  sum_amt desc

--limit         12;

-- c는 alias(별칭), distinct는 구별되는 유니크한 것만 추출

select      count(c.customer_id)

    ,       count(distinct c.transaction_id)

    ,       sum(c.total_amt)

from        transactions c;

select      a.customer_id       as  고객번호

    ,       a.tran_date         as  거래일자

    ,       a.rate              as  판매가

    ,       a.qty               as  주문수량

    ,       a.total_amt         as  주문금액

from        transactions a;

select      count(distinct  b.transaction_id)

        ,   count(distinct  b.customer_id)

        ,   sum(b.total_amt)

from        transactions b;

-- 중복포함 모두 출력

select      store_type

from        transactions;

-- 중복된 것은 제거하고 서로 다른 것만 출력

select      distinct store_type

from        transactions ;

-- 문자 형식의 리터럴은 ''으로 감싸서 입력

-- 날짜 리터럴은 date키워드와 함께 날짜형식으로 입력

-- 날짜와 nation이 일치하는 데이터만 추출

select      a.city_code

    ,       a.city_name

    ,       'KOREA'                 as  nation

    ,       date '2021-09-26'       as  insert_dt

    ,       4923                    as  number_literal

from        city a;

/* 새로운 테이블을 하나 만든다

 * 기 생성된 DB Retail아래에 mytable이란 테이블 생김

 * public -> Tables -> mytable이 생김

 */

create table mytable (

    name        varchar(80),

    pet         varchar(80)

);

insert into mytable values ('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt');

-- 테이블 만드는 또 다른 예제

CREATE TABLE CUSTOMER

(   CUSTOMER_ID VARCHAR(200)    PRIMARY KEY,

    DOB         DATE            NOT NULL,

    GENDER      VARCHAR(200),

    CITY_CODE   VARCHAR(200)

);

/* (3) Python 연결

 * Python 코드로 어떤 테이블에 데이터 추가

 * [Ref] https://www.bearpooh.com/147

 * import psycopg2

 * conn = psycopg2.connect("host='localhost' dbname='Retail' user='postgres' password='0577' port=5432")

 * cur = conn.cursor()

 * cur.execute("INSERT INTO mytable (name, pet) values ('Kdj', 'tan');")

 * conn.commit()  # 데이터 추가됨

 */

select * from mytable;

/* https://docs.streamlit.io/knowledge-base/tutorials/databases/postgresql

 *  cur.execute("SELECT * from mytable;")

 *  rows = cur.fetchall()

 *  rows # 출력

 *  [('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt'), ('Kdj', 'tan')]

 */

-- 데이터 갱신

-- > cur.execute("update mytable set name='kdj' where pet='tan'")

-- > cur.execute("select * from mytable;")

-- > conn.commit()

-- > rows:

--   [('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt'), ('lee', 'tan')]

-- 데이터 삭제

-- > cur.execute("delete from mytable where name='lee'")

-- > cur.execute("select * from mytable;")

-- > rows = cur.fetchall()

-- > rows: [('Mary', 'dog'), ('John', 'cat'), ('Robert', 'bitt')]

/* (4) SQL shell

 * >>> PostgreSQL13 -> SQL shell(psql) 실행 <<<

 * Table의 상위인 DATABASE의 조회, 생성, 삭제 가능

 * command #로 들어가서

 * \l or \l+  # 조회, 또는 상세 조회

 * CREATE DATABASE hello;  # DB생성

 * DROP DATABASE hello;  # DB제거

 * 이미 만들어진 Retail 확인 가능

 */

/* (5) 이미지 파일의 저장(BYTEA)

 * [Ref] https://www.geeksforgeeks.org/save-a-image-file-on-a-postgres-database-python/amp/

 * > 아래 파이썬 명령 참고

 */

 

-- > cur.execute("CREATE TABLE IF NOT EXISTS cartoon(cartoonID INTEGER, name TEXT, cartoonImg BYTEA)")

-- > conn.commit()

-- > cartoonID = 1

-- > name = "Casper"

-- > file_path = 'test.jpg'

-- > drawing = open(file_path, 'rb').read()

-- >

-- > cur.execute("INSERT INTO cartoon (cartoonID,name,cartoonImg) " + "VALUES(%s,%s,%s)", (cartoonID, name, psycopg2.Binary(drawing)))

-- > conn.commit()

-- image가 저장 되었는지 확인 가능

select *

from cartoon;