python, MySQL Pool database.py, main.py 분리 예제 1

python, MySQL Pool database.py, main.py 분리 예제  1

 

[파일] main.py

from database import connectionpool
import atexit

try:
    connection = connectionpool.get_connection()
except Exception as e:
    print("DB Connection Error:", e)
    exit(1)

with connection.cursor() as cursor:
    try:
        cursor.execute("select * from city limit 5")
        rows = cursor.fetchall()
        for r in rows:
            print(r)
        print("Query executed")
    except Error as e:
        print("Error:", e)
    finally:
        pass
        #if connection.is_connected():
        #    cursor.close()
        #    print("DB Cursor is closed")

if connection:
    connection.close()
    print("DB connection is closed")


# 프로그램 종료시 DB 풀 종료 처리 
def exit_process():
    try:
        if connection.is_connected():
            cursor.close()
            print("DB Cursor is closed") 
    except:
        pass
    
    try:
        if connection:
            connection.close()
            print("DB connection is closed")     
    except:
        pass
    
atexit.register(exit_process)

 

 

[파일] database.py 

# 설치 순서
#pip3 uninstall mysql-connector-python
#pip3 uninstall mysql-connector
#pip3 install mysql-connector-python

import mysql.connector
from mysql.connector import Error
from mysql.connector import pooling  # pool

host = "localhost"
user = "root"
password = "1234"
database = "world"
poolname = "mypool" # pool
poolsize = 32 # pool 1~32

connectionpool = None

try:   
    connectionpool = mysql.connector.pooling.MySQLConnectionPool(pool_name=poolname,
                                                                 pool_size=poolsize,
                                                                 pool_reset_session=True,
                                                                 host=host, user=user,
                                                                 password=password,
                                                                 database=database)
    connection = connectionpool.get_connection() #    
except Error as e:
    print("Error:", e)


# Test
if __name__ == "__main__":
    try: 
        print(connectionpool.pool_name)  
        print(connectionpool.pool_size)  
        cursor = connection.cursor()

        cursor.execute("select * from city limit 5")
        rows = cursor.fetchall()
        for r in rows:
            print(r)
        print("Query executed")
    except Error as e:
        print("Error:", e)
    finally:
        if (connection.is_connected()):
            cursor.close()
            connection.close()
            print("DB connection is closed")

[실행 결과]

(1, 'Kabul', 'AFG', 'Kabol', 1780000)
(2, 'Qandahar', 'AFG', 'Qandahar', 237500)
(3, 'Herat', 'AFG', 'Herat', 186800)
(4, 'Mazar-e-Sharif', 'AFG', 'Balkh', 127800)
(5, 'Amsterdam', 'NLD', 'Noord-Holland', 731200)
Query executed
DB connection is closed