2023년 2월 1일 수요일

PostgreSQL 설치 및 사용법

/* PostgreSQL 설치 및 사용법
 * (1) 설치(PostgreSQL과 DBeaver): Youbube를 참고하여 설치하였음
 * (2) SQL 기본 구문 구조 이해
 * (3) 파이썬 연결 관련
 * (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 코드로 어떤 테이블에 데이터 추가
 * 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;


 *  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)
 * > 아래 파이썬 명령 참고
 */
 

-- > 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;

댓글 11개:

  1. |||**Docker환경에서 PostgreSQL 실행**|||

    /*
    * |||**Docker환경에서 PostgreSQL 실행**|||하고 DBeaver에서 테스트
    * 위치: d:\2023\postgresql\billing_youtube.sql
    */
    [References]
    1. https://www.youtube.com/watch?v=V1bTHnyfUrY
    2. https://www.youtube.com/watch?v=SJMcIZzfBeY


    -- Windows powershell에서 실행
    docker run -it -d --restart=always -p 15432:5432 --name psql_db -e POSTGRES_PASSWORD=0577 -v d:/postgresql/postgres_data:/var/lib/postgresql/data postgres:latest

    -- 우분투에서 실행 (폴더 postgres_data만든 후)
    docker run -it -d --restart=always -p 15432:5432 --name psql_db -e POSTGRES_PASSWORD=0577 -v postgres_data:/var/lib/postgresql/data postgres:latest


    docker exec -it psql_db /bin/bash

    # psql -U postgres -- db를 사용할 수 있는 CLI화면으로 들어감
    postgres=# create user kdj password '0577' superuser;
    create role
    postgres=# \du -- 실행 결과(user생성), 권한 등 확인
    postgres=# select * from pg_shadow; -- 사용자에 대한 다양한 정보 확인
    postgres=# -- postgres 는 role생성, DB생성 등에 대한 모든 권한을 가지고 있음. kdj도 이러한 권한을 가지도록 변경 가능
    postgres=# alter role kdj createdb; -- 사용자 생성 권한 부여
    alter role
    postgres=# alter role kdj replication; -- 복제 가능한 권한 부여
    alter role
    postgres=# \du --권한 변경을 확인

    postgres=# create database billing owner kdj;
    create database
    postgres=# \c billing kdj -- 앞부분이 postgres에서 billing으로 바뀜
    you are now connected to database "billing" as user 'kdj'.
    billing=# create schema payment;
    create schema
    billing=# \dn -- 스키마(db내 테이블이나 개체등의 그룹)별로 소유권 확인 가능

    답글삭제
    답글
    1. -it: interactive terminal mode,
      -d: detach mode,
      -p: port forward,
      -e: environment,
      -v:volume(host-docker folder matching)

      삭제
  2. /*
    * 여기서부터는 dbeaver에서 실행
    */

    -- DBeaver에 접속 -> +버턴(컨센트모양) 누름 -> PostgreSQL선택 -> Host: localhost, port:15432,
    database: billing, user: kdj, password: 0577 -> 완료
    -- 정상적으로 연결되면, 좌측사이드에 Database밑에 billing이란 DB를 확인가능.
    -- Schema열어보면 위에서 생성한 payment확인 가능
    -- Table확인 시 아무것도 없음
    -- Payment -> 우클릭 -> SQL편집기 오픈



    -- 테이블 생성
    -- WINDOWS(dbeaver) 버전:
    create table card_history (
    id integer generated always as identity
    , card_user varchar(30) not null
    , card_time timestamp not null
    , charge integer default 0
    , constraint card_history_pk primary key (id, card_time)
    ) partition by range(card_time);
    -- partition: 큰 db의 관리편이를 위해 분리 가능하게 해둠

    -- 주석 추가
    comment on table "card_history" is '카드 히스토리 관리 테이블';

    -- 버전 확인
    select version();

    -- 파티셔닝: 위에서 partition하겠다고 했으면 아래 명령으로 파티션 해야 함. 그렇지 않으면, 아래 insert명령 오류 뜸
    create table card_history_2022_04 partition of card_history for values from ('2022-04-01 00:00:00') to ('2022-05-01 00:00:00');
    create table card_history_2022_05 partition of card_history for values from ('2022-05-01 00:00:00') to ('2022-06-01 00:00:00');
    create table card_history_2022_06 partition of card_history for values from ('2022-06-01 00:00:00') to ('2022-07-01 00:00:00');

    -- 데이터 추가
    insert into payment.card_history (card_user, card_time, charge) values ('kimjaemin', '2022-04-13 15:30:00', 1500)
    insert into payment.card_history (card_user, card_time, charge) values ('kimjaemin', '2022-05-13 15:30:00', 1500)
    insert into payment.card_history (card_user, card_time, charge) values ('kimjaemin', '2022-06-13 15:30:00', 1500)

    -- 데이터 확인
    select * from payment.card_history;
    select * from payment.card_history_2022_05;

    select * from payment.card_history;

    답글삭제
  3. -- 우분투 버전: psql에서 직접 실행 버전

    create table payment.card_history2 (
    id integer generated always as identity
    , card_user varchar(30) not null
    , card_time timestamp not null
    , charge integer default 0
    , constraint card_history2_pk primary key (id, card_time)
    );

    insert into payment.card_history2 (card_user, card_time, charge) values ('kimjaemin', '2022-04-13 15:30:00', 1500)
    insert into payment.card_history2 (card_user, card_time, charge) values ('honggildong', '2022-05-13 15:30:00', 1500)
    insert into payment.card_history2 (card_user, card_time, charge) values ('maruchi', '2022-06-13 15:30:00', 1500)
    select * from payment.card_history2;

    답글삭제
  4. /*
    * docker PostgreSQL과 python 통신에 성공한 버전
    *
    */

    docker run -it -d --restart=always -p 15432:5432 --name psql_db -v d:/postgresql/postgres_data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=0577 postgres:latest
    docker run -it -d --restart=always -p 15432:5432 --name psql_db -v d:/postgresql/postgres_data:/var/lib/postgresql/data -e POSTGRES_USER=kdj -e POSTGRES_DB=demo2 -e POSTGRES_PASSWORD=0577 postgres:latest


    -- python 명령
    from sqlalchemy import create_engine
    from sqlalchemy.orm import scoped_session, sessionmaker
    from sqlalchemy.sql import text

    conn_str="postgresql://kdj:0577@localhost:15432/billing"
    engine = create_engine(conn_str)
    db = scoped_session(sessionmaker(bind=engine))
    query_rows = db.execute(text('SELECT * FROM payment.card_history')).fetchall()

    [(1, 'kimjaemin', datetime.datetime(2022, 4, 13, 15, 30), 1500),
    (2, 'kimjaemin', datetime.datetime(2022, 5, 13, 15, 30), 1500),
    (3, 'kimjaemin', datetime.datetime(2022, 6, 13, 15, 30), 1500)]

    답글삭제
  5. *** FastAPI + PostgreSQL ***
    1. Powershell을 연다. Container를 만든다.
    docker run -it -d --restart=always -p 15432:5432 --name psql_db -v d:/postgresql/postgres_data:/var/lib/postgresql/data -e POSTGRES_USER=kdj -e POSTGRES_DB=demo2 -e POSTGRES_PASSWORD=0577 postgres:latest
    2. python 가상환경을 오픈한다. D:\2023\venv에 있는 activate_venv를 실행.
    3. D:\2023\venv\FastAPI-PostgreSQL-Docker\FastAPI\app폴더로 들어간다.
    4. uvicorn main:app --reload --host:127.0.0.1 --port 1112 실행
    5. localhist:1112/docs에 접속해서 api를 테스트 해 본다.

    답글삭제
    답글
    1. *** FASTAPI로 생성된 table 확인 ***
      1. DB로 들어감: docker exec -it psql_db /bin/bash
      2. DB명과 사용자 연결: \c billing kdj
      3. 이미 만들어진 테이블 리스트 확인: \dt
      4. FastAPI에서 테이블 속성을 바꾸면 이미 만들어진 테이블을 지우고 새로 저장해야 함
      5. 테이블 삭제: drop table 테이블명;
      6. API로 저장된 데이터 보는 명령: select * from "Event";

      삭제
    2. Ubuntu linux에서는
      uvicorn main:app --reload --host 127.0.0.1 --port 1112
      로 해야 실행됨

      삭제
  6. # *******************************************************
    # *** FastAPI + PostgreSQL + python GET/POST ***
    # *******************************************************
    import requests
    import json

    def send_api(url, method):
    headers = {'Content-Type': 'application/json', 'charset': 'UTF-8', 'Accept': '*/*'}
    body = {
    "image_path": "d:/test",
    "event_type": "Speed",
    "event_place": "PNU",
    "cctv_id": 10,
    "event_dummy1": "string",
    "event_dummy2": "string",
    "event_dummy3": "string"
    }
    try:
    if method == 'GET':
    response = requests.get(url, headers=headers)
    elif method == 'POST':
    response = requests.post(url, headers=headers, data=json.dumps(body, ensure_ascii=False, indent="\t"))
    print("response status %r" % response.status_code)
    print("response text %r" % response.text)
    except Exception as ex:
    print(ex)

    send_api("http://localhost:1112/Events/info", "POST")
    send_api("http://localhost:1112/Events/info/Sleep", "GET")

    답글삭제
    답글
    1. docker로 postgresql실행하고 uvicorn으로 fastapi실행 후, python으로 post 및 get 호출했을 때, ubuntu가 windows보다 실행 속도가 훨씬 빠름

      삭제
  7. *** Docker log check ***
    sudo docker logs --tail 100 -f aa7a8b30639a

    답글삭제