#%%
import threading
import time
import dataset

engine_kwargs={"echo":False, "pool_size":3, "max_overflow":2, "pool_recycle":180,"pool_pre_ping":True,"pool_use_lifo":True}
config_str='postgresql://postgres:123456@127.0.0.1:5432/test'
db = dataset.connect(config_str,engine_kwargs=engine_kwargs)

print(len(db.connections))
def insert_data():
    with db as tx:
        print(len(db.connections),"insert",threading.current_thread().getName())
        tx['user'].insert(dict(name='John Doe', age=46, country='China'))
        print(len(db.connections),"end",threading.current_thread().getName())
        
threads = []
for _ in range(5):
    thread = threading.Thread(target=insert_data)
    threads.append(thread)
    thread.start()
 
for thread in threads:
    thread.join()
 
time.sleep(0.5) 
print(len(db.connections))
print("start block")

# QueuePool limit of size 3 overflow 2 reached, connection timed out, timeout 30.00 
print(db['user'].count())
# %%