def __init__(self, pool_options=None, **kwargs):
self._configurations = kwargs
pool_options = pool_options or {}
self._pool = ConnectionPool(self._connect, **{
**self._defaults,
**pool_options
})
def test_pool(self):
cursors = []
ts = []
threads_count = 100
for i in range(threads_count):
t = threading.Thread(target = create_cursor, args = (cursors, ts, self))
ts.append(t)
t.setDaemon(True)
t.start()
ts.append(t)
for t in ts:
t.join()
self.assertEqual(len(cursors), threads_count)
self.assertEqual(len(ConnectionPool._connections), ConnectionPool._hold_count)
self.assertEqual(ConnectionPool._count, 0)
ConnectionPool.close()
def create_pool(resource_name, max_size, max_usage, idle, ttl):
def create_resource_client():
resource = ResourceEnum[resource_name].value
return resource
return ConnectionPool(create=create_resource_client,
max_size=max_size,
max_usage=max_usage,
idle=idle,
ttl=ttl)
def create_cursor(cursors, ts, tester):
c = ConnectionPool.cursor()
cursors.append(c)
t = threading.Thread(target = close_cursor, args = (c, ))
t.setDaemon(True)
t.start()
ts.append(t)
# print ConnectionPool._count, ConnectionPool.MAX_COUNT
tester.assertTrue(ConnectionPool._count <= ConnectionPool.MAX_COUNT + 1)
tester.assertTrue(len(ConnectionPool._connections) <= ConnectionPool.MAX_COUNT)
def create_pool(username, pw, host, database, max_size, max_usage, idle,
ttl):
def create_storage_client():
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(
username, pw, host, database))
if not database_exists(engine.url):
create_database(engine.url)
Base.metadata.create_all(engine)
return engine
return ConnectionPool(create=create_storage_client,
max_size=max_size,
max_usage=max_usage,
idle=idle,
ttl=ttl)
class Autocomplete:
# create connection pool object
connection_pool = ConnectionPool()
def get_similar_titles(self, query):
print("similar titles called")
db_connection = self.connection_pool.get_pool_connection()
cursor = db_connection.cursor(buffered=True)
sql = """ SELECT title FROM full_movies_data WHERE title LIKE %s"""
search_query = f"%{query}%"
cursor.execute(sql, (search_query, ))
result = cursor.fetchall()
titles_list = []
for title in result:
titles_list.append(title[0])
# return result list to request
return titles_list
class ConnectionPooled(object):
'''数据库连接池类
创建连接池：
pooled = ConnectionPooled(host='192.0.0.1', database='foo',
pool_options=dict(max_size=5))
获取一个未使用连接池的连接，并执行 SQL：
pooled.execute(sql)
pooled.connection.execute(sql)
使用连接池执行 SQL：
with pooled.pool() as connection:
connection.execute(sql)
'''
# 这个连接，是不进入连接池的
_connection = None
_defaults = dict(max_size=POOL_SIZE,
max_usage=POOL_USAGES,
ttl=POOL_TTL,
idle=POOL_IDLE)
def __init__(self, pool_options=None, **kwargs):
self._configurations = kwargs
pool_options = pool_options or {}
self._pool = ConnectionPool(self._connect, **{
**self._defaults,
**pool_options
})
def _connect(self):
return Connection(**self._configurations)
@property
def connection(self):
if not self._connection:
self._connection = self._connect()
return self._connection
def pool(self):
return self._pool.item()
def __getattr__(self, method):
return getattr(self.connection, method)
def create_pool(max_size, max_usage, idle, ttl):
def create_k8s_client():
try:
# Load configuration inside the Pod
config.load_incluster_config()
except ConfigException:
# Load configuration for testing
config.load_kube_config()
# Create the Apis
v1_core = client.CoreV1Api()
return v1_core
return ConnectionPool(create=create_k8s_client,
max_size=max_size,
max_usage=max_usage,
idle=idle,
ttl=ttl)
def setConnectionPool(self, create_connection, max_size, max_usage, idle, ttl):
try:
if not self.connection_pool.exists(self.connector_type_key):
print(
f'🟡 _Connector.setConnectionPool - New connection pool [{self.connector_type_key}]')
self.connection_pool.update(
connector_type=self.connector_type_key,
connection_pool=ConnectionPool(
create=create_connection,
max_size=max_size,
max_usage=max_usage,
idle=idle,
ttl=ttl
)
)
except:
print('_Connector.setConnectionPool - Failed to set connection pool')
traceback.print_exc()
def connection_pool(self):
"""set up a pool of re-usable active connections"""
# merge this object's values with those of its parent provider
args_to_use, kwargs_to_use = self.get_args_to_use()
# hashing connection pool on tuple of relevant arguments. There
# may be a better way to do this
conn_pool_label_tuple = (
type(self),
*args_to_use,
*[kwargs_to_use.get(k, None) for k in self.conn_keywords],
)
if conn_pool_label_tuple not in self.connection_pools:
create_callback = partial(
self.create_connection, *args_to_use, **kwargs_to_use
)
self.connection_pools[conn_pool_label_tuple] = ConnectionPool(
create_callback, close=self.close_connection, max_size=self.pool_size
)
return self.connection_pools[conn_pool_label_tuple]
from flask import request
from flask_cors import CORS
from flask_caching import Cache
from connection_pool import ConnectionPool
from autocomplete import Autocomplete
import engine
app = Flask(__name__)
CORS(app)
# tell Flask to use the above defined config
app.config['CACHE_TYPE'] = 'simple'
app.cache = Cache(app)
# creat connection pool object
connection_pool = ConnectionPool()
title_autocomplete = Autocomplete()
@app.cache.memoize(timeout=100)
def get_similar_titles(query):
return title_autocomplete.get_similar_titles(query)
def get_full_recommendations_data(recs):
result = []
# buffered=True allows to read the result as buffered
db_connection = connection_pool.get_pool_connection()
cursor = db_connection.cursor(buffered=True)
for movie in recs:
sql = """ SELECT * FROM full_movies_data WHERE title = %s """
class TransactionManager(object):
"""
handles all transaction and db query
"""
def __init__(self):
self.conn = ConnectionPool().pool.connection()
self.cursor = self.conn.cursor()
def startTransaction(self):
"""
For databases that support transactions,
the Python interface silently starts a transaction when the cursor is created.
so we do nothing here.
"""
pass
def commitTransaction(self):
self.cursor.close()
self.conn.commit()
def endTransaction(self):
"""
结束事务
"""
pass
def rollbackTransaction(self):
"""
回滚事务
"""
self.cursor.close()
self.conn.rollback()
def queryInsert(self, sqlid, inputObject):
"""
查询插入
"""
#=======================================================================
# resultclasstype参数在没有返回值的时候用不到
#=======================================================================
sql, resultclasstype = processSql(sqlid, inputObject)
try:
var = self.cursor.execute(sql)
return var
except IntegrityError:
return -1
def queryUpdate(self, sqlid, inputObject):
"""
查询更新
"""
self.queryInsert(sqlid, inputObject)
def queryDelete(self, sqlid, inputObject):
"""
查询删除
"""
self.queryInsert(sqlid, inputObject)
def queryForObject(self, sqlid, inputObject):
"""
查询并返回一个对象
"""
sql, resultclasstype = processSql(sqlid, inputObject)
self.cursor.execute(sql)
objList = Data2Object.data2object(self.cursor, resultclasstype)
if len(objList) == 0:
return None
elif len(objList) == 1:
return objList[0]
else:
raise Exception('query for one object, but get many.');
def queryForList(self, sqlid, inputObject):
"""
查询并返回一个列表
"""
sql , resultclasstype = self.processSql(sqlid, inputObject)
self.cursor.execute(sql)
objList = self.data2object(self.cursor, resultclasstype)
return objList
def processSql(self, sqlid, inputObject):
# TODO (zj)
return '', ''
def data2object(cursor, resultclasstype):
# TODO (zj)
return []
def execute_sql(self, sql, pre_sql=None):
r = []
try:
if pre_sql:
self.cursor.execute(pre_sql)
self.cursor.execute(sql)
r = self.cursor.fetchall()
self.conn.commit()
except Exception, e:
print "Exception when execute_sql:%s, rollback" % e
self.conn.rollback()
finally:
def __init__(self):
self.conn = ConnectionPool().pool.connection()
self.cursor = self.conn.cursor()
from connection_pool import ConnectionPool
class Connection(object):
def __init__(self, **kwargs):
self.args = kwargs
self.state = 'Connected'
def close(self):
self.state = 'Closed'
def connect():
return Connection()
def close(connection):
connection.close()
pool = ConnectionPool(create=connect, close=close,
max_size=10, max_usage=10000, idle=60, ttl=120)
with pool.item() as connection:
assert connection.state == 'Connected'
