# monkey patch everything we need (this is how we do it in Glance)
import eventlet
eventlet.monkey_patch(time=True, socket=True)

import logging
import sqlalchemy as sa
import time

# setup logging
logging.basicConfig()
logging.getLogger().setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy.pool').setLevel(logging.DEBUG)


QUERIES_TO_COMPLETE = 16


def callback(conn, record):
    # yield the execution control to another green thread. So this is how we
    # give a chance for another green thread to execute when blocking code
    # is used - e.g. MySQLdb-Python (written in C, can not be monkey-patched)
    time.sleep(0)


eng = sa.create_engine(
    'mysql://root:devel@localhost/devel',
    pool_size=5,  # this is a default value, but let's be explicit here
    max_overflow=10  # ditto
)

# callback will be executed every time a connection returns to the pool
sa.event.listen(eng, 'checkin', callback)


def thread_func():
    # get a connection and execute the simplest query
    conn = eng.connect()
    try:
        conn.execute('select 1;')
        global QUERIES_TO_COMPLETE
        QUERIES_TO_COMPLETE -= 1
    finally:
        conn.close()  # connection goes to pool here


# execute 16 queries concurrently: each thread will get a new connection from
# the pool, so the pool is allowed to create only up to 15 connections, so the
# 16th thread must wait (up to 30s), though this shouldn't be a problem,
# because queries are really fast
for i in range(QUERIES_TO_COMPLETE):
    eventlet.spawn(thread_func)


while QUERIES_TO_COMPLETE > 0:
    # let other green threads to execute
    time.sleep(0)