기록's

PostgreSQL 문에서 Select으로 리스트를 뽑을 때 Json 형식으로 Row를 Export 하는 방식이다.

row_to_json으로 사용하는 것으로 생각하면 된다.

특정 알파뱃 제외 구문이나 인코딩 방식 등 포함 되어 있으니 보고하실거면 주의 필요함

#-*- coding: euc-kr -*-
import json
import psycopg2
import requests
import sys
import chardet

sys.stdout = open('/usr/share/openldap-servers/userList.json', 'w')

try:
    conn_string = "host='dbsvr.com' dbname='gooddb' user='test' password='test12!@' port='5432'"
    conn = psycopg2.connect(conn_string)
    cur = conn.cursor()
    qry_origin = "select userid, status, empno, username, mailaddr, dutycode, positionname, deptcode, deptname, dutyname, updated_at from view_user where updated_at >= current_timestamp + '-120 minute' and deptcode not like 'E%' and dutyname is not null order by updated_at desc"
    #qry_origin = "select userid, status, empno, username, mailaddr, dutycode, positionname, deptcode, deptname, dutyname, updated_at from view_user where status='ONLINE'
    qry = u"select row_to_json(tmp) from ("+ qry_origin +") tmp;"
    cur.execute(qry)
    rownum = int(cur.rowcount)
    i=0
    while i < rownum:
        result = cur.fetchone()[0]
        print(json.dumps(result, ensure_ascii=False).encode('utf8'))
        i = i + 1
    cur.close()
    conn.close()
except psycopg2.DatabaseError as db_err:
    print('!!! not connected !!!', db_err)

sys.stdout.close()

sys.stdout = open('/usr/share/openldap-servers/userStopList.json', 'w')

try:
    conn_string = "host='dbsvr.com' dbname='gooddb' user='test' password='test12!@' port='5432'"
    conn = psycopg2.connect(conn_string)
    cur = conn.cursor()
    qry_origin = "select userid, status, empno, username, mailaddr, dutycode, positionname, deptcode, deptname, dutyname from view_user where status='STOP'"
    qry = u"select row_to_json(tmp) from ("+ qry_origin +") tmp;"
    cur.execute(qry)
    rownum = int(cur.rowcount)
    i=0
    while i < rownum:
        result = cur.fetchone()[0]
        print(json.dumps(result, ensure_ascii=False).encode('utf8'))
        i = i + 1
    cur.close()
    conn.close()
except psycopg2.DatabaseError as db_err:
    print('!!! not connected !!!', db_err)

sys.stdout.close()