Python, MySQL POOL connection 예제

Python, MySQL POOL connection 예제 

정리. 수알치 오상문 

 

예제에 사용한 테이블은 MySQL 5.7에 기본으로 들어있는 world 테이블입니다.

 

# pip3 install mysql-connector
import mysql.connector
from mysql.connector import Error
from mysql.connector import pooling  # pool

host="localhost" # MySQL 서버 주소 
user="root" # 계정이름
password="1234" # 비밀번호
database="world"  # 데이터베이스 이름
poolname="mypool"  # pool 이름 
poolsize=32  # pool 크기

try:
    #connection = mysql.connector.connect(host=host, user=user, password=password, database=database) # 일반 연결
    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() #    
    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")

# end

 

[실행 결과]

mypool
32
(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

 

 

https://www.youtube.com/watch?v=LJ0eEedeIyY