/* 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 코드로 어떤 테이블에 데이터 추가
* [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;
* 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;
|||**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내 테이블이나 개체등의 그룹)별로 소유권 확인 가능
-it: interactive terminal mode,
삭제-d: detach mode,
-p: port forward,
-e: environment,
-v:volume(host-docker folder matching)
/*
답글삭제* 여기서부터는 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;
-- 우분투 버전: 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;
/*
답글삭제* 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)]
*** 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를 테스트 해 본다.
*** 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";
Ubuntu linux에서는
삭제uvicorn main:app --reload --host 127.0.0.1 --port 1112
로 해야 실행됨
# *******************************************************
답글삭제# *** 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")
docker로 postgresql실행하고 uvicorn으로 fastapi실행 후, python으로 post 및 get 호출했을 때, ubuntu가 windows보다 실행 속도가 훨씬 빠름
삭제*** Docker log check ***
답글삭제sudo docker logs --tail 100 -f aa7a8b30639a