<jupyter_start><jupyter_text>PostgreSQL    Python을 이용한 RDB 프로그래밍 기본<jupyter_code>import psycopg2
import pandas as pd
import numpy as np

from psycopg2 import pool
import mybatis_mapper2sql
from multiprocessing import Process
class PGManager(object):
    def __new__(self):
        if not hasattr(self, 'instance'):
            self.instance = super(PGManager, self).__new__(self)
            self.__connection_pool = None
        return self.instance
    
    def createPool(self, user, passwd, db, host, minCon=5, maxCon=20, port=5432):
        self.close()
        self.__connection_pool = pool.SimpleConnectionPool(
            minCon, maxCon, user = user, password=<PASSWORD>,
            host=host, port=port, database=db)
    
    def close(self):
        if (self.__connection_pool):
            self.__connection_pool.closeall()
    
    def setMapper(self, xmlMapperFile):
        self._mapper, _ = mybatis_mapper2sql.create_mapper(xml=xmlMapperFile)
    
    def query(self, mapId, params=None):
        connection = self.__connection_pool.getconn()
        cursor = connection.cursor()
        statement = mybatis_mapper2sql.get_child_statement(self._mapper, mapId)
        cursor.execute(statement, params)
        records = cursor.fetchall()
        cursor.close()
        self.__connection_pool.putconn(connection)
        
        return records
    
    def execute(self, mapId, params=None):
        connection = self.__connection_pool.getconn()
        cursor = connection.cursor()
        statement = mybatis_mapper2sql.get_child_statement(self._mapper, mapId)
        cursor.execute(statement, params)
        cursor.close()
        connection.commit()
        self.__connection_pool.putconn(connection)
    
    def executeMany(self, mapId, paramsList):
        connection = self.__connection_pool.getconn()
        cursor = connection.cursor()
        statement = mybatis_mapper2sql.get_child_statement(self._mapper, mapId)
        cursor.executemany(statement, paramsList)
        cursor.close()
        connection.commit()
        self.__connection_pool.putconn(connection)
    
    def asyncExecuteMany(self, mapper, mapId, paramsList):
        p = Process(target=self.executeMany, args=(mapId, paramsList))
        p.start()
        p.join()
db = PGManager()
db.createPool("postgres", "postgres", "postgres", "192.168.0.200")
db.setMapper("mapper.xml")
db.execute("initDB")<jupyter_output><empty_output><jupyter_text>xls를 DB에 저장.<jupyter_code>df = pd.read_excel("유가증권.xlsx", encoding="utf-8", dtype={"종목코드":str})
df.fillna("", inplace = True) 
df.head()
params = []
for item in df.to_numpy():
    params.append((str(item[1]), item[0], item[2], item[3], str(item[4]), item[5][:-1], item[6], item[7], item[8], False))
db.executeMany("inCorporation", params)<jupyter_output><empty_output><jupyter_text>각종 쿼리<jupyter_code>a = "20180530"
b = "20180601"
d = datetime.datetime.strptime('2018-05-19', '%Y-%m-%d')
a = {"1":"a"}
a
a.clear()
a<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12717536813922356, 'fraction_numerical': 0.020080321285140562, 'mean_word_length': 3.487987987987988, 'pattern_counts': {'":': 2, '<': 14, '<?xml version=': 0, '>': 14, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 4}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '203809', 'license': 'Apache-2.0', 'n_tokens_mistral': 1014, 'n_tokens_neox': 1010, 'n_words': 178, 'path': 'lecture_source/collect_analysis/postgres01_basic.ipynb', 'repo_name': 'nockchun/rsnet', 'size': '2984'}