개발 공부

2019년 4월 7일 일요일

[PostgreSQL] 행 순서(ROW NUMBER)에 조건 적용하기

개발프로그램

PostgreSQL 9.6

행 순서를 나타내려면 ROW_NUMBER() OVER ORDER BY ... 구분을 사용하면 되고,
중첩 SELECT문을 사용하면 해당 값에 조건을 매길 수 있다.

SELECT *
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS ROW, *
          FROM TEST_TABLE LIMIT 10) T

다음은 위 예제에서 짝수번째 행만 조회하는 쿼리이다.

SELECT *
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS ROW, *
          FROM TEST_TABLE LIMIT 10) T
  WHERE ROW%2 = 0

PostgreSQL

댓글 없음:

2019년 4월 6일 토요일

[PostgreSQL, PostGIS] 음수 Latitude값을 양수로 전환할 때

오후 11:38 홍다슬

개발프로그램

Postgresql 9.6

geometry 값에서 -180 ~ 180 범위의 Latitude 값을 0 ~ 360으로 표현하고 싶을 때

ST_SHIFT_LONGITUDE(geometry)를 사용하면 된다.

만약 원래부터 양수인 경우 알아서 그대로 나오고, 내부 연산도 하지 않는지 조회 속도에 영향을 주지도 않는다.

예제)

SELECT GEOM, ST_SHIFT_LONGITUDE(GEOM) FROM TEST_TABLE WHERE ST_X(GEOM) < 0

PostGIS / PostgreSQL

댓글 없음:

[PostgreSQL, Node.js] 소켓 통신 실시간 INSERT 이벤트 받기

오후 11:24 홍다슬

개발프로그램

Postgresql 9.6

Node.js 소켓 통신 중
PostgreSQL DB에서 특정 테이블에 INSERT가 발생한 것을 캐치하고 싶다면
우선 PostgreSQL에서 다음과 같은 FUCNTION와 TRIGGER를 생성한다.

-- create notify function
CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify('watch_realtime_table', row_to_json(NEW)::text);
RETURN new;
END;
$$ LANGUAGE plpgsql;


-- create insert trigger
CREATE TRIGGER watch_realtime_table_trigger AFTER INSERT ON realtime_table
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

각각을 설명하자면
notify_trigger은 watch_realtime_table이라는 채널에 JSON 형식의 데이터를 nofity하는 역할을 하고,
watch_realtime_table_trigger는 realtime_table이라는 테이블에 INSERT가 발생하면 해당 행들에 대해 notify_trigger()함수를 실행,
즉 결과적으로 realtime_table에 INSERT가 발생할 때마다 해당 데이터들을 watch_realtime_table라는 채널에 notify한다는 것이다.

더 간단히 말하자면 watch_realtime_table라는 채널을 구분자로 INSERT된 값들을 받아올 수 있다는 뜻이다.

이제 Node.js 부분에서 해당 채널을 LISTEN하는 코드를 추가하면 되는데, 이는 다음과 같다.

var pgp = require('pg-promise')(/*options*/);

var client = new pgp.pg.Client(dburl);
client.connect();
client.query('LISTEN "watch_realtime_table"');
client.on('notification', function(data) {
    console.log(data.payload);
});

그러면 해당 데이터들을 INSERT때마다 실시간으로 JSON 형식으로 console에서 확인할 수 있다.

또한 LISTEN 작업을 중단하려면
client.query('UNLISTEN "watch_realtime_table"'); 를 이용한다.

- 참고 사이트

https://www.pusher.com/tutorials/postgresql-realtime/

https://github.com/vitaly-t/pg-promise/issues/83

Node.js / PostgreSQL

댓글 없음:

2018년 12월 23일 일요일

[Python] 네이버 밴드 크롤링 + 글자 수 세기

오후 4:58 홍다슬

Laptop

운영체제	Windows 10 Home 64bit
개발환경	Python 3.4.4 PyCharm 2018.1.3 (Community Edition)

[2019.07.21 멤버 구분 기능 추가 내용 수정]

Naver_Band_Comment_Count_Crawler.exe

친구 요청으로 간단하게 만들어본 프로그램이다.

지금 올리는 것은 작성글 보기 페이지만을 크롤링하는 특정 목적이 있는 프로그램이지만,
다른 페이지를 크롤링할 때도 태그만 수정하면 되니 동일한 절차를 거치면 될 것 같다.

Selenium 및 라이브러리를 활용하여, 로그인만 해주면 댓글 페이지의 댓글을 수집, 각각의 공백 포함 및 미포함 글자 수를 세어 리스트를 작성해 엑셀 파일로 출력한다.
또한 멤버명을 태그하여 언급하여 한 댓글에 여러명에게 얘기할 시 여러개의 댓글로 인식하여 결과를 낸다.

로그인도 자동화시키고 싶었지만 네이버 자체 보안 절차 때문에 불가능한 것 같다.

프로그램 실행 전에 두 가지 작업이 필요하다.

1. chromedriver 설치

selenium에 지원하는 버전 제한이 있는것 같아 내가 썼던 버전 링크를 적겠다.
https://chromedriver.storage.googleapis.com/index.html?path=72.0.3626.69/

여기 들어가 window용을 다운받아 압축을 풀면 chromedriver.exe라는 파일이 있다.
(아래 그림 참고)

chromedriver.exe를 위에서 첨부한 프로그램과 동일한 폴더에 위치시키면 된다.

2. URL 확인

확인할 페이지(여기서는 작성글 보기)의 링크 주소가 필요하다.
접속 방법은 아래 캡쳐한 메뉴대로 들어가면 된다.

필요한 값은 바로 위 스크린샷의 URL부분이다.

프로그램을 실행시키면 URL를 입력하는 란이 있으니 그곳에 해당 값을 입력하고,
START 버튼을 누르면 아래 그림과 같이 크롬 창이 새로 하나 실행되며 로그인 화면이 나온다.

참고할 점은 로그인할 때 이 절차가 보통 크롬으로 접속할 때보다 유독 좀 길다.
아무래도 프로그래밍적으로 접근하고 있다는 걸 인식해서 그런것 같다.

어쨌든 페이지가 완전히 나오면 OK버튼 클릭하면 알아서 스크롤을 내리면서 모두 조회한 후 엑셀 파일을 생성한다.

그리고 마지막으로 저장 창이 뜬다. 기본 파일명은 Naver_Band_Comment_Count_Result.xlsx로 설정되어 있다.

테스트로 쓴 댓글 구조는 다음과 같으며, 댓글과 멤버에 대한 매칭은 나온 순서로만 본다. 즉, [멤버1댓글1]과 [댓글1멤버1] 은 동일게 취급한다. (아래 결과 참조)

또한 연속으로 붙여적은 멤버는 모두 한 댓글에 대한 언급으로 취급한다.

그렇게 저장된 파일 형태는 다음과 같다.

전체 소스는 다음과 같다.

from selenium import webdriver
from tkinter import messagebox
import time
from openpyxl import Workbook

from tkinter import *
from tkinter.filedialog import asksaveasfilename

root = Tk()

root.title('Naver Band Comment Count Crawler')

url = StringVar()

lbl = Label(root, text="URL")
lbl.grid(sticky="W", row=0, column=0)
txt = Entry(root, textvariable=url, width=60)
txt.grid(sticky="W", row=0, column=1)

def enter(event):
    startCommentCount()

def saveFile(driver, wb):
    # 저장 파일명 설정
    file = asksaveasfilename(initialfile="Naver_Band_Comment_Count_Result.xlsx")
    if (file):
        try:
            wb.save(file)
        except:
            messagebox.showerror(title='Error', message=file + " 를 저장할 수 없습니다.\n파일이 열려있다면 종료해 주세요.")
            saveFile(driver, wb)
        else:
            messagebox.showinfo(title='Complete', message=file + " 저장 완료!")
            wb.close()
            driver.quit()
    # 취소 선택 시
    else:
        if(not messagebox.askyesno("Cancel", "파일이 저장되지 않습니다. 정말로 취소하시겠습니까?")):
            saveFile(driver, wb)
        else:
            wb.close()
            driver.quit()

def startCommentCount():

    if(url.get() == ''):
        messagebox.showinfo(title='Info', message='URL을 입력하세요.')
        return

    try:
        driver = webdriver.Chrome("chromedriver")
    except:
        messagebox.showerror(title='Error', message='chromedriver가 같은 위치에 있는지 확인하세요.')
        return

    try:
        driver.get(url.get())
    except:
        messagebox.showwarning(title='Warning', message='입력하신 URL에 연결 실패했습니다.\n다시 확인해 주세요.')
        driver.quit()
        return

    # 로그인 대기
    messagebox.showinfo(title='로그인', message='로그인 완료 후 페이지가 나타나면 OK 버튼을 눌러주세요.')

    # 더이상 refresh가 되지 않을 때까지 스크롤 내리기
    SCROLL_PAUSE_TIME = 0.5

    # Get scroll height
    last_height = driver.execute_script("return document.body.scrollHeight")

    while True:
        # Scroll down to bottom
        driver.execute_script("window.scrollTo(0, document.body.scrollHeight);")

        # Wait to load page
        time.sleep(SCROLL_PAUSE_TIME)

        # Calculate new scroll height and compare with last scroll height
        new_height = driver.execute_script("return document.body.scrollHeight")
        if new_height == last_height:
            break
        last_height = new_height

    # 댓글 리스트 크롤링
    comment_list = driver.find_elements_by_css_selector("p[class='comment']")

    # 엑셀 작성
    wb = Workbook()
    ws = wb.active

    ws.cell(row=1, column=1).value = "순번"
    ws.cell(row=1, column=2).value = "태그 멤버"
    ws.cell(row=1, column=3).value = "댓글"
    ws.cell(row=1, column=4).value = "공백 포함 글자수"
    ws.cell(row=1, column=5).value = "공백 미포함 글자수"

    i = 0
    row = 2

    for comment in comment_list:

        comment_text_list = []
        member_text_list = []

        # 언급 member 유무 확인
        member_list = comment.find_elements_by_tag_name("strong")

        if (member_list.__len__() == 0):
            member_text_list.append("")
            comment_text_list.append(comment.text)
        else:
            for member in member_list:
                member_text_list.append(member.text)

            comment_text = comment.get_attribute('innerHTML')

            # 다중 언급 댓글 분리
            m = 0
            curr = 0
            while (True):
                # strong 태그 시작을 찾아 마지막 탐색 지점과의 간격을 구함
                start = comment_text.find("<strong>", curr)
                diff = start - curr

                if (start == -1):
                    # 더이상 멤버가 없을 경우, 댓글로 끝나는 경우를 처리(추가)하고 종료
                    if (curr + 9 != len(comment_text)):
                        comment_text_list.append(comment_text[curr + 9:len(comment_text)].strip())
                    break

                if (curr == 0):
                    # 처음에 댓글 후 멤버명 언급 시 댓글 처리
                    if (comment_text[curr:start].strip() != ''):
                        comment_text_list.append(comment_text[curr:start].strip())
                else:
                    # 멤버명끼리 붙어있는 경우 (닫는 태그 길이 = 9)
                    if (diff == 9):
                        member_text_list[m] = member_text_list[m] + ", " + member_text_list[m + 1]
                        member_text_list.pop(m + 1)
                    # 댓글인 경우, 닫는 태그를 제외하고 내용 추출
                    else :
                        comment_text_list.append(comment_text[curr + 9:start].strip())
                        m += 1

                # 마지막 탐색 지점을 strong 태그 닫힌 구간으로 변경
                end = comment_text.find("</strong>", start)
                curr = end

        # 엑셀 줄 입력
        j = 1
        m = 0

        for comment in comment_text_list:

            # 다중 언급 댓글 번호 매기기
            child = ''
            if (comment_text_list.__len__() > 1):
                child = "-" + str(j)
                j += 1

            ws.cell(row=row, column=1).value = str(i + 1) + child
            ws.cell(row=row, column=2).value = member_text_list[m]
            ws.cell(row=row, column=3).value = comment
            ws.cell(row=row, column=4).value = len(comment)
            ws.cell(row=row, column=5).value = len(re.findall("[\S]", comment))

            m += 1
            row += 1

        i += 1

    saveFile(driver, wb)

root.bind('<Return>', enter)

btn = Button(root, text="START", width=15, command=startCommentCount)
btn.grid(row=3, column=1)

root.mainloop()

Python / Selenium / Tkinter

댓글 1개:

2018년 9월 17일 월요일

[jQuery] 채팅 스크롤 구현 (최하단 자동 포커싱 + 이동 버튼 및 안 읽은 수 표시)

오전 1:32 홍다슬

Laptop

운영체제	Windows 10 Home 64bit
브라우저	Chrome 버전 68.0.3440.106

채팅창처럼 수시로 행이 늘어나는 table의 경우 몇몇 편의상의 스크롤 제어가 필요하다.
일단 기능 자체는 메신저 텔레그램에서 영감을 많이 얻었다.

수행하는 기능은
1. 새로운 행 추가 시 자동으로 부드럽게 최하단 스크롤
2. 이전 기록으로 (위로) 스크롤 시 다시 보던 위치로 이동할 수 있는 버튼 플로팅
3. 버튼이 떠있는 동안은 자동 스크롤이 되지 않음
4. 버튼이 떠있는 동안 새로운 라인이 추가될 시 안 읽은 수가 표시

html구조는 스크롤을 제어할 div안에 동적 table이 있고, 버튼은 div 두개를 써서 하나는 실제 보이는 버튼, 하나는 버튼이 숨김 상태일 때 가리기 위한 용도이다. 즉, 보임/숨김은 단순히 top 조정으로 제어된다.

결과물은 다음과 같다.
css는 기본적인 것 외에는 거의 적용하지 않았고, 버튼 디자인은 다른 소스를 참고한 것이니 세부적인 건 하단 참고 사이트 링크에서 확인하면 된다.

- 참고 사이트
동적 테이블 추가: https://qkrrudtjr954.github.io/jquery/2018/01/29/jquery-dynamic-table.html

스크롤 애니메이션: http://jamesdreaming.tistory.com/215

슬라이드 버튼: http://brilliantcoding.tistory.com/entry/JQuery-%EC%8A%A4%ED%81%AC%EB%A1%A4%EC%97%90-%EB%94%B0%EB%9D%BC-%EC%8A%AC%EB%9D%BC%EC%9D%B4%EB%93%9C-%EB%90%98%EB%8A%94-%ED%97%A4%EB%8D%94-%EB%A7%8C%EB%93%A4%EA%B8%B0

HTML / JavaScript / jQuery

댓글 1개:

2018년 9월 8일 토요일

[PostgreSQL] COPY FROM CSV 사용 시 Date, Time Format 지정하기

오전 12:25 홍다슬

개발프로그램

Postgresql 9.6

csv파일의 값 형태가 컬럼형에 맞지 않아 직접 넣을 수 없을 때,

formatting을 해야하는 경우가 있다.
예를 들면 timestamp형 데이터로 09:30:10을 넣어야 하는데
csv에 093010라고 적혀있어서 오류가 뜨는 경우이다.

결론만 말하자면 to_timestamp()를 이용하는 간편한 방법은 없다.

총 3가지가 있는데,
2, 3번의 경우 임시 데이터를 생성하므로 사용할 데이터가 대용량일 경우

작업 PC의 하드 용량에 어느 정도 여유분이 필요하다는 주의사항이 있다.

사용할 table의 컬럼은 대충
test_table (
id INT4,
date DATE,
time TIMESTAMP)
라는 전제로 하겠다. (time 외 다른 컬럼에 별 의미는 없다)
csv 파일명은 test.csv, 테이블과 동일한 컬럼 형태에 ','로 구분됐다는 전제를 두겠다.

1. 초반에 VARCHAR형으로 선언한 후 ALTER문으로 TYPE 변경하기
일단 VARCHAR형으로 선언하면 문자열이기 때문에 어떤 형식이든 입력은 성공한다.
그리고 to_timestamp()를 써서 컬럼형을 변경하는 방법이다.

장점은 임시적인 컬럼이나 파일이 필요없다는 것,
단점은 동일한 형식의 csv파일을 추가적으로 COPY할 수 없기 때문에
필요할 때는 결국 2 또는 3번 방법을 이용해야 한다는 것이다.

(HEADER는 헤더를 없애고 읽는다는 뜻, ENCODING은 말 그대로 csv 파일의 인코딩이다.)

COPY test_table(id, date, time) FROM 'test.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

ALTER TABLE test_table ALTER COLUMN time TYPE TIME USING (to_timestamp(time_char, 'HH24MISS'));

2. VARCHAR형 임시 컬럼 생성 후 본래 컬럼에 다시 UPDATE하기

예를 들어 time_char라는 임시 컬럼을 만들고 거기에 csv의 자료형을 넣고,
본래 써야 할 time 컬럼은 null로 둔 뒤
UPDATE문을 써서 변경하는 방법이다.

time_char은 이후에 컬럼을 제거해도 되지만
이후에 또 동일한 형식의 csv파일을 추가적으로 COPY할 수도 있을 경우에는
일단 냅두고 UPDATE문에 null 조건을 달아 동일한 작업을 하면 되지 않을까 싶다.

COPY test_table(id, date, time_char) FROM 'test.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

UPDATE dtg_data SET time = to_timestamp(time_char, 'HH24MISS');

3. csv 파일 자체를 해당 포맷에 맞게 변환하기
데이터의 용량이 클 경우 무조건 이 방법을 택하기를 권한다.
실제로 나는 csv파일만 약 30GB인 파일들로 작업을 했는데,
하나당 COPY에도 약 3일이 걸리고 UPDATE에도 마찬가지로 3일정도가 걸려서
실제로 많은 시간을 소모했으나
이 방법을 마지막에서야 깨닫고 시도해보니 훨씬 시간이 단축되었다.
(하지만 COPY할때 걸리는 3일은 어쩔 수가 없었던...)

언어는 무얼 쓰든 자유이나, 나는 이전에 Python으로 csv 관련 작업을 한 적이 있어 익숙한 편이라 이것을 택했다.

지금 예제의 프로그램을 써보자면 (csv파일의 위치는 C:/ 바로 아래라고 하자)

import csv
with open('C:\\test.csv', 'r') as csvfile:
    reader = csv.DictReader(csvfile)
    with open('C:\\test2.csv', 'w', newline='') as csvfile2:
        writer = csv.writer(csvfile2, delimiter = ',')
        writer.writerow(reader.fieldnames)
        for row in reader:
            split_time = row['time']
            time = split_time[0] + split_time[1] + ':' + split_time[2] + split_time[3] + ':' + split_time[4] + split_time[5]
            row['time'] = time
            writer.writerow(row.values())

test2의 newline='' 속성은 개행을 없앤다는 뜻이다.
쓰지 않을 경우 각 행들이 모두 한 줄씩의 공백(즉 엔터를 두번 친 모양)을 가진 채 생긴다.

단순히 time값을 한글자씩 쪼개어 사이사이에 ':'를 입력하고 time행을 수정,
그 후 행 단위로 csv를 입력해나가는 간단한 프로그램이다.

이렇게 하면 포맷이 설정된 새로운 test2.csv라는 파일이 형성된다.
(정확히는 측정하지 못했으나 30GB 처리 소요시간이 2~3시간쯤으로 추정된다.)

그리고 앞의 예제들과 마찬가지로 COPY를 쓰면 된다.

COPY test_table(id, date, time) FROM 'test2.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

데이터 추가시에도 이와 같이 새로운 csv 파일을 생성하고, COPY하면 된다.

참고 사이트: https://www.postgresql.org/message-id/55BA474E.7080605%40aklaver.com

PostgreSQL / Python

댓글 없음:

2018년 9월 7일 금요일

[JSP] _jspService(HttpServletRequest, HttpServletResponse) is exceeding the 65535 bytes limit error 해결법

오후 12:59 홍다슬

Laptop

운영체제	Windows 10 Enterprise 64bit
개발프로그램	Eclipse Photon (4.8.0)

말 그대로 서블릿 java 파일이 제한인 65535 bytes (약 64KB)를 초과하여 생기는 현상이다.

해결법은 실행 중인 서버의 web.xml 파일에서

<servlet-name>jsp</servlet-name>
<servlet-class>org.apache.jasper.servlet.JspServlet</servlet-class>

를 찾아 아래 소스를 추가해준다.

<init-param>
            <param-name>mappedfile</param-name>
            <param-value>false</param-value>
</init-param>

만약 Eclipse + Tomcat 이라면 다음과 같이 바로 찾을 수 있다.

servlet-name값이 default인 것도 있으니 헷갈리지 않게 주의...(그걸로 몇 시간 헤맸다)

원리를 대충 설명하자면

jsp 내에 html코드가 들어갈 경우 (모든 html인지, jsp와 혼합된 html만인지는 모르겠다)

out.write("<!DOCTYPE html>\r\n");
out.write("<html>\r\n");
out.write("<head>\r\n");

이런 식으로 out.write로 변환해 버리는 구조로 되어있는데, 이것을 out.write("\r\n<!DOCTYPE html>\r\n<html>\r\n<head>\r\n"); 처럼 한줄로 바꾸는 별거 아닌 설정이다.

덤으로 마찬가지로 용량을 줄이기 위한 다른 설정들은 다음과 같다.

<init-param>
 <param-name>genStringAsCharArray</param-name>
 <param-value>true</param-value>
</init-param>
<init-param>
 <param-name>trimSpaces</param-name>
 <param-value>true</param-value>
</init-param>

하지만 이것들도 String을 char배열로 바꾸거나 공백을 없애는 등 소소한 설정들이고

사실 가장 적절한 해결법은 jsp 소스에서 최대한 참조로 뺄 수 있는 걸 빼는 거지만 규모가 크다면 매우 번거로운 작업이므로...

참고 사이트:
http://s4-ba.hatenablog.jp/entry/2016/06/19/095937 https://docs.oracle.com/cd/E19146-01/821-1489/ggkhi/index.html

Eclipse / Java / JSP

댓글 1개:

개발 공부

프로필

Translate

2019년 4월 7일 일요일

[PostgreSQL] 행 순서(ROW NUMBER)에 조건 적용하기

2019년 4월 6일 토요일

[PostgreSQL, PostGIS] 음수 Latitude값을 양수로 전환할 때

[PostgreSQL, Node.js] 소켓 통신 실시간 INSERT 이벤트 받기

2018년 12월 23일 일요일

[Python] 네이버 밴드 크롤링 + 글자 수 세기

2018년 9월 17일 월요일

[jQuery] 채팅 스크롤 구현 (최하단 자동 포커싱 + 이동 버튼 및 안 읽은 수 표시)

2018년 9월 8일 토요일

[PostgreSQL] COPY FROM CSV 사용 시 Date, Time Format 지정하기

2018년 9월 7일 금요일

[JSP] _jspService(HttpServletRequest, HttpServletResponse) is exceeding the 65535 bytes limit error 해결법

Popular Posts

Tag

Category

Blog Archive