def test_context_manager(self):
persist = PersistentDB(dbapi)
with persist.connection() as db:
with db.cursor() as cursor:
cursor.execute('select test')
r = cursor.fetchone()
self.assertEqual(r, 'test')
def test_threadlocal(self):
persist = PersistentDB(dbapi)
self.assertTrue(isinstance(persist.thread, local))
class threadlocal:
pass
persist = PersistentDB(dbapi, threadlocal=threadlocal)
self.assertTrue(isinstance(persist.thread, threadlocal))
def __init__(self, config: dict):
self._default_conf = {
'creator': pymysql,
'cursorclass': DictCursor,
'charset': 'utf8mb4',
'ping': 1
}
self._db_conf = {**self._default_conf, **config}
self._pool = PersistentDB(**self._db_conf)
def __init__(self):
"""Provide db pool to connect with database.
"""
self.config = {
'host': 'localhost',
'user': '******',
'password': '******',
'db': 'spider_book_data',
'charset': 'utf8mb4'}
self.db_pool = PersistentDB(pymysql, maxusage=1000, **self.config)
def test_close(self):
for closeable in (False, True):
persist = PersistentDB(dbapi, closeable=closeable)
db = persist.connection()
self.assertTrue(db._con.valid)
db.close()
self.assertTrue(closeable ^ db._con.valid)
db.close()
self.assertTrue(closeable ^ db._con.valid)
db._close()
self.assertFalse(db._con.valid)
db._close()
self.assertFalse(db._con.valid)
def test_connection(self):
persist = PersistentDB(dbapi)
db = persist.connection()
db_con = db._con
self.assertIsNone(db_con.database)
self.assertIsNone(db_con.user)
db2 = persist.connection()
self.assertEqual(db, db2)
db3 = persist.dedicated_connection()
self.assertEqual(db, db3)
db3.close()
db2.close()
db.close()
def test_failed_transaction(self):
persist = PersistentDB(dbapi)
db = persist.connection()
cursor = db.cursor()
db._con.close()
cursor.execute('select test')
db.begin()
db._con.close()
self.assertRaises(dbapi.InternalError, cursor.execute, 'select test')
cursor.execute('select test')
db.begin()
db.cancel()
db._con.close()
cursor.execute('select test')
def test_maxusage(self):
persist = PersistentDB(dbapi, 20)
db = persist.connection()
self.assertEqual(db._maxusage, 20)
for i in range(100):
cursor = db.cursor()
cursor.execute('select test%d' % i)
r = cursor.fetchone()
cursor.close()
self.assertEqual(r, 'test%d' % i)
self.assertTrue(db._con.valid)
j = i % 20 + 1
self.assertEqual(db._usage, j)
self.assertEqual(db._con.num_uses, j)
self.assertEqual(db._con.num_queries, j)
def __init__(self):
config = {
'host': self.get('mysql.host'),
'port': int(self.get('mysql.port')),
'database': self.get('mysql.db_name'),
'user': self.get('mysql.user'),
'password': self.get('mysql.password'),
'charset': 'utf8'
}
self.poolDB = PersistentDB(
# use pymysql as mysql database driver
creator=pymysql,
# max number usage of one connection,0 or None is no limits,default is 0
maxusage=int(self.get('mysql.maxusage')),
**config)
def pool(shareable, db_name):
host = "127.0.01"
port = 3306
user = "******"
password = "******"
g_pool = None
if g_pool is None:
if shareable is True:
g_pool = PooledDB(pymysql,
host="127.0.01",
port=3306,
user="******",
passwd="gene_ac252",
db=db_name,
charset='utf8',
blocking=1,
maxconnections=3)
else:
g_pool = PersistentDB(pymysql,
host=host,
port=port,
user=user,
passwd=password,
db=db_name,
charset='utf8')
conn = g_pool.connection()
cursor = conn.cursor()
return conn, cursor
def get_pool_test1(is_mult_thread):
if is_mult_thread:
poolDB = PooledDB(
# 指定数据库连接驱动
creator=mysql.connector,
# 连接池允许的最大连接数,0和None表示没有限制
maxconnections=30,
# 初始化时,连接池至少创建的空闲连接,0表示不创建
mincached=10,
# 连接池中空闲的最多连接数,0和None表示没有限制
maxcached=30,
# 连接池中最多共享的连接数量,0和None表示全部共享(其实没什么卵用)
maxshared=10,
# 连接池中如果没有可用共享连接后,是否阻塞等待,True表示等等,
# False表示不等待然后报错
blocking=True,
# 开始会话前执行的命令列表
setsession=[],
# ping Mysql服务器检查服务是否可用
ping=0,
**test1)
else:
poolDB = PersistentDB(
# 指定数据库连接驱动
creator=mysql.connector,
# 一个连接最大复用次数,0或者None表示没有限制,默认为0
maxusage=1000,
**test1)
return poolDB
class MysqlPersistentDB(object):
"""
保持常量线程数且频繁使用数据库的应用，使用PersistentDB
"""
_instance_lock = threading.Lock()
def __new__(cls, *args, **kwargs):
if not hasattr(MysqlPooledDB, "_instance"):
with MysqlPooledDB._instance_lock:
if not hasattr(MysqlPooledDB, "_instance"):
MysqlPooledDB._instance = super().__new__(cls)
return MysqlPooledDB._instance
def __init__(self, config: dict):
self._default_conf = {
'creator': pymysql,
'cursorclass': DictCursor,
'charset': 'utf8mb4',
'ping': 1
}
self._db_conf = {**self._default_conf, **config}
self._pool = PersistentDB(**self._db_conf)
def connect(self):
conn = self._pool.connection()
cursor = conn.cursor()
return conn, cursor
def _connect(host,
port,
user,
password,
db_name,
connect_charset=None,
use_unicode=None,
shareable=True):
_pool_id = "%s_%s_%s_%s" % (host, port, user, db_name)
if _pool_id not in SimpleDB._pool:
comm_kwargs = dict(host=host,
port=port,
user=user,
passwd=password,
db=db_name)
if connect_charset is not None:
comm_kwargs["charset"] = connect_charset
if use_unicode is not None:
comm_kwargs["use_unicode"] = use_unicode
if shareable is True:
pool = PooledDB(pymysql,
blocking=1,
maxconnections=SimpleDB.max_connections,
**comm_kwargs)
else:
pool = PersistentDB(pymysql, **comm_kwargs)
SimpleDB._pool[_pool_id] = pool
conn = SimpleDB._pool[_pool_id].connection()
cursor = conn.cursor()
return conn, cursor
class DBPool(object):
def __init__(self):
"""Provide db pool to connect with database.
"""
self.config = {
'host': 'localhost',
'user': '******',
'password': '******',
'db': 'spider_book_data',
'charset': 'utf8mb4'}
self.db_pool = PersistentDB(pymysql, maxusage=1000, **self.config)
def db_transaction(self, func):
@wraps(func)
def wrapper_func(*args):
conn = self.db_pool.connection()
try:
return func(*args, connection=conn)
except Exception as e:
logging.error("发生异常：" + str(e))
conn.rollback()
finally:
conn.close()
return wrapper_func
def test_setsession(self):
persist = PersistentDB(dbapi, 3, ('set datestyle',))
db = persist.connection()
self.assertEqual(db._maxusage, 3)
self.assertEqual(db._setsession_sql, ('set datestyle',))
self.assertEqual(db._con.session, ['datestyle'])
cursor = db.cursor()
cursor.execute('set test')
cursor.fetchone()
cursor.close()
for i in range(3):
self.assertEqual(db._con.session, ['datestyle', 'test'])
cursor = db.cursor()
cursor.execute('select test')
cursor.fetchone()
cursor.close()
self.assertEqual(db._con.session, ['datestyle'])
class DataBasePool(BaseConfig):
"""DataBase pool
To get connection from database pool
Example : DataBasePool.get_instance()
"""
__instance = None
def __init__(self):
config = {
'host': self.get('mysql.host'),
'port': int(self.get('mysql.port')),
'database': self.get('mysql.db_name'),
'user': self.get('mysql.user'),
'password': self.get('mysql.password'),
'charset': 'utf8'
}
self.poolDB = PersistentDB(
# use pymysql as mysql database driver
creator=pymysql,
# max number usage of one connection,0 or None is no limits,default is 0
maxusage=int(self.get('mysql.maxusage')),
**config)
def execute(self, sql):
"""execute sql by a connection from database pool
Args:
sql (str): sql
Returns:
list: results of sql
"""
logger.info("Getting a connection from database pool.")
conn = self.poolDB.connection()
logger.info("Getting a connection from database pool finished.")
cursor = conn.cursor()
# execute sql
logger.info(f"executing {sql}")
cursor.execute(sql)
result = cursor.fetchall()
conn.commit()
logger.info(f"execute finished. return {result}")
# return the connection back
conn.close()
return result
@staticmethod
def get_instance():
"""get a instance at once simultaneously"""
if DataBasePool.__instance:
return DataBasePool.__instance
try:
lock.acquire()
if not DataBasePool.__instance:
logger.info('Building DataBase Pool.')
DataBasePool.__instance = DataBasePool()
logger.info('Build DataBase Pool finished.')
finally:
lock.release()
return DataBasePool.__instance
def sqlcon(code1):
config = {
'host': '47.96.143.225', ##个人云服务器IP，可以直接运行连接数据库
'port': 3306,
'database': 'yundonghui',
'user': '******',
'password': '******',
'charset': 'utf8'
}
db_pool = PersistentDB(pymysql, **config)
# 从数据库连接池是取出一个数据库连接
conn = db_pool.connection()
cursor = conn.cursor()
cursor.execute(code1)
result = cursor.fetchall()
cursor.connection.commit()
conn.close()
return result
def sqlcon():
config = {
'host': '47.96.143.225',
'port': 3306,
'database': 'yundonghui',
'user': '******',
'password': '******',
'charset': 'utf8'
}
db_pool = PersistentDB(pymysql, **config)
# 从数据库连接池是取出一个数据库连接
conn = db_pool.connection()
cursor = conn.cursor()
# 来查下吧
cursor.execute('select * from yundongyuan')
result = cursor.fetchall()
cursor.connection.commit()
print(result)
# 关闭连接,其实不是关闭,只是把该连接返还给数据库连接池
conn.close()
def get_pool(self):
"""
功能：基于 PersistentDB 模块,管理连接池
说明：creator 指 python 所使用的 mysql 数据库模块
ping=4 指当查询时使用,检查连接
maxusage=0 指不限制一个连接使用次数
setsession 指定编码方式
cursorclass 指定为字典方式
"""
self.pool = PersistentDB(creator=MySQLdb,
host=self.host,
port=self.port,
user=self.user,
passwd=self.pwd,
db=self.dbname,
ping=4,
maxusage=0,
setsession=["set names utf8;"],
read_timeout=self.read_timeout,
write_timeout=self.write_timeout,
cursorclass=DictCursor)
return self.pool
ARD_UDP_IP_RECEIVE = "192.168.5.1"
ARD_UDP_PORT_RECEIVE = 5100
CSV_URL = '/var/www/html/csvTables/'
# connection to mysql database
db_config = {
'host': '192.168.4.2',
'user': '******',
'password': '******',
'database': 'MESSDATEN',
'connect_timeout': 5
}
# Configuration to setup a persistent database-connection
mysql_connection_pool = PersistentDB(creator=pymysql, **db_config)
# arg parser for API (running Measurement)
commentParser = reqparse.RequestParser()
commentParser.add_argument('kommentar',
required=True,
help='argument required')
commentParser.add_argument('position',
type=int,
required=True,
help='argument required')
# arg parser for API3 (creating Table)
tableCreateParser = reqparse.RequestParser()
tableCreateParser.add_argument('tableName',
type=int,
def __init__(self,
creator: object,
*,
host: str,
port: int,
username: str,
password: str,
database: str,
charset: str = 'utf8mb4',
collation: str = 'utf8mb4_general_ci',
auto_commit: bool = True,
cursor_class: type = None,
init_command_list: List[str] = None,
min_cached: int = 5,
max_cached: int = 20,
max_shared: int = 10,
max_connections: int = 40,
blocking: bool = True,
max_usage: int = 0,
reset: bool = True,
multi_thread: bool = True,
logger: Logger = None,
**kwargs) -> None:
"""
数据库操作类 MySQLDatabase 的初始化参数一览表
:param type creator:
数据库连接池支持的任何符合DB-API 2.0规范的函数或者兼容的数据库模块，例如 MySQLdb (mysqlclient) 等
:param str host:
数据库连接的主机，默认值为 127.0.0.1
:param int port:
数据库连接的端口，默认值为 3306
:param str username:
数据库连接的用户名
:param str password:
数据库连接的密码
:param str database:
数据库连接的数据库名
:param str charset:
数据库连接的字符集，默认值为 utf8mb4
:param str collation:
数据库连接的排序规则，默认值为 utf8mb4_general_ci
注：此选项仅在 mysql.connector 模块中生效
:param bool auto_commit:
数据库连接的自动提交开关，默认值为 True
:param type|None cursor_class:
数据库连接的默认游标类，例如 MySQLdb 的 Cursor SSCursor DictCursor SSDictCursor 类等
:param List[str]|None init_command_list:
数据库连接初始化时执行的命令列表，例如 ["set datestyle to ..."，"set time zone ..."] 等
:param int min_cached:
数据库连接池中空闲连接的初始数量，为 0 表示不创建，默认值为 3
注：此选项仅在 multi_thread 为 True 时生效
:param int max_cached:
数据库连接池中空闲连接的最大数量，为 0 表示不限制，默认值为 12
注：此选项仅在 multi_thread 为 True 时生效
:param int max_shared:
数据库连接池中共享连接的最大数量，为 0 表示不共享，默认值为 6
注：此选项仅在 multi_thread 为 True 时生效
:param int max_connections:
数据库连接池中连接的最大数量，为 0 表示不限制，默认值为 24
注：此选项仅在 multi_thread 为 True 时生效
:param bool blocking:
数据库连接池超过最大连接数量时的阻塞开关，阻塞则等待可用连接，不阻塞则直接抛出异常，默认值为 True
注：此选项仅在 multi_thread 为 True 时生效
:param int max_usage:
数据库连接池中单个连接的最大复用次数，为 0 表示不限制，默认值为 0
:param bool reset:
数据库连接池中连接放回池中的重置开关，为 True 时表示所有连接都执行回滚操作，默认值 True
注：此选项仅在 multi_thread 为 True 时生效
:param bool multi_thread:
是否多线程调用，多线程则用 PooledDB 模块，否则使用 PersistentDB 模块，默认值 True
:param Logger logger:
日志对象
"""
# 初始化日志对象
self._logger = logger or Logger('MySQLDatabase')
# 生成数据库配置
if creator.__name__ == 'MySQLdb':
self._config = {
'host': host,
'port': port,
'user': username,
'passwd': password,
'database': database,
'charset': charset,
'autocommit': auto_commit,
}
elif creator.__name__ == 'mysql.connector':
self._config = {
'host': host,
'port': port,
'user': username,
'password': password,
'database': database,
'charset': charset,
'collation': collation,
'autocommit': auto_commit,
}
else:
raise ValueError('暂不支持的数据库接口')
if cursor_class is not None:
self._config['cursorclass'] = cursor_class
# DBUtils 是一套 Python 数据库连接池包，并允许对非线程安全的数据库接口进行线程安全包装。
# DBUtils 提供两种外部接口：
# - PersistentDB：提供线程专用的数据库连接，并自动管理连接。
# - PooledDB：提供线程间可共享的数据库连接，并自动管理连接。
if multi_thread:
# 用于数据库连接池 PooledDB
# from DBUtils.PooledDB import PooledDB # DBUtils 1.x
from dbutils.pooled_db import PooledDB # DBUtils 2.x
self._pool = PooledDB(creator, min_cached, max_cached, max_shared,
max_connections, blocking, max_usage,
init_command_list, reset, **self._config,
**kwargs)
else:
# 用于数据库连接池 PersistentDB
# from DBUtils.PersistentDB import PersistentDB # DBUtils 1.x
from dbutils.persistent_db import PersistentDB # DBUtils 2.x
self._pool = PersistentDB(creator, max_usage, init_command_list,
**self._config, **kwargs)
# 辅助函数（s -> 字符串）（x -> 元组）（sy -> 占位符）（sp -> 间隔符）
self._wrapper = lambda s, sy='`%s`': sy % s # 'test' -> '`test`'
self._placeholder = lambda x, sy='%s', sp=', ': sp.join([sy] * len(
x)) # '(1, 2)' -> '%s, %s'
self._placeholder_plus = lambda x, sy='`%s`', sp=', ': (sp.join(
[sy] * len(x)) % x) # '(1, 2)' -> '`%s`, `%s`'
class MySQLDatabase:
def __init__(self,
creator: object,
*,
host: str,
port: int,
username: str,
password: str,
database: str,
charset: str = 'utf8mb4',
collation: str = 'utf8mb4_general_ci',
auto_commit: bool = True,
cursor_class: type = None,
init_command_list: List[str] = None,
min_cached: int = 5,
max_cached: int = 20,
max_shared: int = 10,
max_connections: int = 40,
blocking: bool = True,
max_usage: int = 0,
reset: bool = True,
multi_thread: bool = True,
logger: Logger = None,
**kwargs) -> None:
"""
数据库操作类 MySQLDatabase 的初始化参数一览表
:param type creator:
数据库连接池支持的任何符合DB-API 2.0规范的函数或者兼容的数据库模块，例如 MySQLdb (mysqlclient) 等
:param str host:
数据库连接的主机，默认值为 127.0.0.1
:param int port:
数据库连接的端口，默认值为 3306
:param str username:
数据库连接的用户名
:param str password:
数据库连接的密码
:param str database:
数据库连接的数据库名
:param str charset:
数据库连接的字符集，默认值为 utf8mb4
:param str collation:
数据库连接的排序规则，默认值为 utf8mb4_general_ci
注：此选项仅在 mysql.connector 模块中生效
:param bool auto_commit:
数据库连接的自动提交开关，默认值为 True
:param type|None cursor_class:
数据库连接的默认游标类，例如 MySQLdb 的 Cursor SSCursor DictCursor SSDictCursor 类等
:param List[str]|None init_command_list:
数据库连接初始化时执行的命令列表，例如 ["set datestyle to ..."，"set time zone ..."] 等
:param int min_cached:
数据库连接池中空闲连接的初始数量，为 0 表示不创建，默认值为 3
注：此选项仅在 multi_thread 为 True 时生效
:param int max_cached:
数据库连接池中空闲连接的最大数量，为 0 表示不限制，默认值为 12
注：此选项仅在 multi_thread 为 True 时生效
:param int max_shared:
数据库连接池中共享连接的最大数量，为 0 表示不共享，默认值为 6
注：此选项仅在 multi_thread 为 True 时生效
:param int max_connections:
数据库连接池中连接的最大数量，为 0 表示不限制，默认值为 24
注：此选项仅在 multi_thread 为 True 时生效
:param bool blocking:
数据库连接池超过最大连接数量时的阻塞开关，阻塞则等待可用连接，不阻塞则直接抛出异常，默认值为 True
注：此选项仅在 multi_thread 为 True 时生效
:param int max_usage:
数据库连接池中单个连接的最大复用次数，为 0 表示不限制，默认值为 0
:param bool reset:
数据库连接池中连接放回池中的重置开关，为 True 时表示所有连接都执行回滚操作，默认值 True
注：此选项仅在 multi_thread 为 True 时生效
:param bool multi_thread:
是否多线程调用，多线程则用 PooledDB 模块，否则使用 PersistentDB 模块，默认值 True
:param Logger logger:
日志对象
"""
# 初始化日志对象
self._logger = logger or Logger('MySQLDatabase')
# 生成数据库配置
if creator.__name__ == 'MySQLdb':
self._config = {
'host': host,
'port': port,
'user': username,
'passwd': password,
'database': database,
'charset': charset,
'autocommit': auto_commit,
}
elif creator.__name__ == 'mysql.connector':
self._config = {
'host': host,
'port': port,
'user': username,
'password': password,
'database': database,
'charset': charset,
'collation': collation,
'autocommit': auto_commit,
}
else:
raise ValueError('暂不支持的数据库接口')
if cursor_class is not None:
self._config['cursorclass'] = cursor_class
# DBUtils 是一套 Python 数据库连接池包，并允许对非线程安全的数据库接口进行线程安全包装。
# DBUtils 提供两种外部接口：
# - PersistentDB：提供线程专用的数据库连接，并自动管理连接。
# - PooledDB：提供线程间可共享的数据库连接，并自动管理连接。
if multi_thread:
# 用于数据库连接池 PooledDB
# from DBUtils.PooledDB import PooledDB # DBUtils 1.x
from dbutils.pooled_db import PooledDB # DBUtils 2.x
self._pool = PooledDB(creator, min_cached, max_cached, max_shared,
max_connections, blocking, max_usage,
init_command_list, reset, **self._config,
**kwargs)
else:
# 用于数据库连接池 PersistentDB
# from DBUtils.PersistentDB import PersistentDB # DBUtils 1.x
from dbutils.persistent_db import PersistentDB # DBUtils 2.x
self._pool = PersistentDB(creator, max_usage, init_command_list,
**self._config, **kwargs)
# 辅助函数（s -> 字符串）（x -> 元组）（sy -> 占位符）（sp -> 间隔符）
self._wrapper = lambda s, sy='`%s`': sy % s # 'test' -> '`test`'
self._placeholder = lambda x, sy='%s', sp=', ': sp.join([sy] * len(
x)) # '(1, 2)' -> '%s, %s'
self._placeholder_plus = lambda x, sy='`%s`', sp=', ': (sp.join(
[sy] * len(x)) % x) # '(1, 2)' -> '`%s`, `%s`'
@contextlib.contextmanager
def execute(self,
operation: str,
*,
params: Union[dict, tuple, list] = None,
cursor_class: type = None,
stacklevel: int = 4) -> type:
self._logger.debug('Execute operation: {}'.format(operation),
stacklevel=stacklevel)
self._logger.debug('Execute params: {}'.format(params),
stacklevel=stacklevel)
connect = self._pool.connection()
cursor = connect.cursor(cursorclass=cursor_class)
try:
cursor.execute(operation, params)
yield cursor
except Exception as e:
self._logger.exception('Execute error: {}'.format(e),
stacklevel=stacklevel)
raise e
finally:
cursor.close()
connect.close()
@contextlib.contextmanager
def executemany(self,
operation: str,
*,
seq_params: Union[dict, tuple, list],
cursor_class: type = None,
stacklevel: int = 4) -> type:
self._logger.debug('Executemany operation: {}'.format(operation),
stacklevel=stacklevel)
self._logger.debug('Executemany seq_params: {}'.format(seq_params),
stacklevel=stacklevel)
connect = self._pool.connection()
cursor = connect.cursor(cursorclass=cursor_class)
try:
cursor.executemany(operation, seq_params)
yield cursor
except Exception as e:
self._logger.exception('Executemany error: {}'.format(e),
stacklevel=stacklevel)
raise e
finally:
cursor.close()
connect.close()
def create_table(self,
table: str,
columns_info: dict,
ignore: bool = True,
database: str = None):
"""
sql = 'CREATE TABLE IF NOT EXISTS `tmp_test_script` \
'(`a1` varchar(255) NULL, `b2` varchar(255) NULL, `c3` varchar(255) NULL);'
rowcount = database.create_table(
'tmp_test_script', {'a1': 'varchar(255) NULL', 'b2': 'varchar(255) NULL', 'c3': 'varchar(255) NULL'})
"""
keys, values = zip(*columns_info.items())
operation = 'CREATE TABLE {ignore}{database}{table} ({columns});'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
ignore='IF NOT EXISTS ' if ignore else '',
table=self._wrapper(table),
columns=self._placeholder_plus(keys, sy='`%s` %%s') % values)
with self.execute(operation, stacklevel=5) as cur:
rowcount = cur.rowcount
return rowcount
def drop_table(self,
table: str,
ignore: bool = True,
database: str = None):
"""
sql = 'DROP TABLE `tmp_test_script`;'
rowcount = database.drop_table('tmp_test_script')
"""
operation = 'DROP TABLE {ignore}{database}{table};'.format(
ignore='IF EXISTS ' if ignore else '',
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table))
with self.execute(operation, stacklevel=5) as cur:
rowcount = cur.rowcount
return rowcount
def insert_one(self,
table: str,
columns: tuple,
params: tuple,
database: str = None) -> int:
"""
sql = 'INSERT INTO `tmp_test_script` (`a1`, `b2`, `c3`) VALUE (%s, %s, %s);'
rowcount = database.insert_one('tmp_test_script', ('a1', 'b2', 'c3'), ('1', '2', '3'))
"""
operation = 'INSERT INTO {database}{table} ({columns}) VALUE ({params});'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
columns=self._placeholder_plus(columns),
params=self._placeholder(columns))
with self.execute(operation, params=params, stacklevel=5) as cur:
rowcount = cur.rowcount
return rowcount
def insert_all(self,
table: str,
columns: tuple,
seq_params: List[tuple],
database: str = None) -> int:
"""
sql = 'INSERT INTO `tmp_test_script` (`a1`, `b2`, `c3`) VALUES (%s, %s, %s);'
rowcount = database.insert_all('tmp_test_script', ('a1', 'b2', 'c3'), [("4", "5", "6"), ("7", "8", "9")])
"""
operation = 'INSERT INTO {database}{table} ({columns}) VALUES ({params});'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
columns=self._placeholder_plus(columns),
params=self._placeholder(columns))
with self.executemany(operation, seq_params=seq_params,
stacklevel=5) as cur:
rowcount = cur.rowcount
return rowcount
def delete(self,
table: str,
columns: tuple,
params: tuple,
database: str = None) -> int:
"""
sql = 'DELETE FROM `tmp_test_script` WHERE `a1`=%s AND `b2`=%s AND `c3`=%s;'
rowcount = database.delete('tmp_test_script', ('a1', 'b2', 'c3'), ("4", "5", "6"))
"""
operator = 'DELETE FROM {database}{table} WHERE {columns};'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
columns=self._placeholder_plus(columns,
sy='`%s` = %%s',
sp=' AND '))
with self.execute(operator, params=params, stacklevel=5) as cur:
rowcount = cur.rowcount
return rowcount
def select_one(self,
table: str,
columns: tuple = (),
database: str = None) -> iter:
"""
sql = 'SELECT `a1`, `b2`, `c3` FROM `tmp_test_script`;'
database.select_one('tmp_test_script', ('a1', 'b2', 'c3')) <-- loop it
"""
operation = 'SELECT {columns} FROM {database}{table};'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
columns=self._placeholder_plus(columns) if columns else '*')
with self.execute(operation, stacklevel=5) as cur:
while True:
row = cur.fetchone()
if not row:
break
yield row
def select_many(self,
table: str,
columns: tuple = (),
size: int = None,
database: str = None) -> iter:
"""
sql = 'SELECT `a1`, `b2`, `c3` FROM `tmp_test_script`;'
database.select_many('tmp_test_script', ('a1', 'b2', 'c3'), size=2) <-- loop it
"""
operation = 'SELECT {columns} FROM {database}{table};'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
columns=self._placeholder_plus(columns) if columns else '*')
with self.execute(operation, stacklevel=5) as cur:
while True:
rows = cur.fetchmany(size)
if not rows:
break
yield rows
def select_all(self,
table: str,
columns: tuple = (),
database: str = None) -> list:
"""
sql = 'SELECT `a1`, `b2`, `c3` FROM `tmp_test_script`;'
rows = database.select_all('tmp_test_script', ('a1', 'b2', 'c3'))
"""
operation = 'SELECT {columns} FROM {database}{table};'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
columns=self._placeholder_plus(columns) if columns else '*')
with self.execute(operation, stacklevel=5) as cur:
rows = cur.fetchall()
return rows
def update(self,
table: str,
values: dict,
columns: tuple,
params: tuple,
database: str = None) -> int:
"""
sql = 'UPDATE `tmp_test_script` SET `a1`=%s, `b2`=%s, `c3`=%s WHERE `a1`=%s AND `b2`=%s AND `c3`=%s;'
rowcount = database.update('tmp_test_script', {'a1': '-1', 'b2': '-2', 'c3': '-3'},
('a1', 'b2', 'c3'), ("1", "2", "3"))
"""
keys, values = zip(*values.items())
operator = 'UPDATE {database}{table} SET {values} WHERE {columns};'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
values=self._placeholder_plus(keys, sy='`%s` = %%s'),
columns=self._placeholder_plus(columns,
sy='`%s` = %%s',
sp=' AND '))
with self.execute(operator, params=values + params,
stacklevel=5) as cur:
rowcount = cur.rowcount
return rowcount
def count(self,
table: str,
column: str = None,
database: str = None) -> int:
"""
sql = 'SELECT COUNT(`a1`) FROM `tmp_test_script`;'
rowcount = database.count('tmp_test_script', 'a1')
"""
operation = 'SELECT COUNT({column}) FROM {database}{table};'.format(
database='{}.'.format(self._wrapper(database)) if database else '',
table=self._wrapper(table),
column=self._wrapper(column) if column else '*')
with self.execute(operation, stacklevel=5) as cur:
row, = cur.fetchone()
return row
class Connector:
"""
功能：MySQL 连接管理器，供各python模块使用
说明：封装公共方法, 增、删、改、查，及事务处理
"""
pool = None
def __init__(self, dbinfo):
"""
init
"""
self.host = dbinfo.host
self.port = dbinfo.port
self.user = dbinfo.user
self.pwd = dbinfo.passwd
self.dbname = dbinfo.dbname
self.read_timeout = dbinfo.read_timeout
self.write_timeout = dbinfo.write_timeout
def escape(self, value):
"""
转义非法字符
"""
return MySQLdb.escape_string(value)
def get_pool(self):
"""
功能：基于 PersistentDB 模块,管理连接池
说明：creator 指 python 所使用的 mysql 数据库模块
ping=4 指当查询时使用,检查连接
maxusage=0 指不限制一个连接使用次数
setsession 指定编码方式
cursorclass 指定为字典方式
"""
self.pool = PersistentDB(creator=MySQLdb,
host=self.host,
port=self.port,
user=self.user,
passwd=self.pwd,
db=self.dbname,
ping=4,
maxusage=0,
setsession=["set names utf8;"],
read_timeout=self.read_timeout,
write_timeout=self.write_timeout,
cursorclass=DictCursor)
return self.pool
def get_db_conn(self):
"""
功能：使用 PersistentDB 管理连接池,连接均为专用连接
说明：使用方法如下,注意关闭游标,及连接
dbInfo = DBInfo(); //构造数据库连接基本信息
conn_inst = Connector(dbInfo) //构造数据库连接类实例
conn = conn_inst.get_db_conn() //调用数据库连接的接口
cursor = conn.cursor() //调用当前游标
cursor.execute(sql) //调用execute(sql),执行 sql
conn.commit() //事务性提交
cursor.close() //关闭游标
conn.close() //关闭连接
"""
try:
if self.pool is None:
self.pool = self.get_pool()
return self.pool.connection()
except MySQLdb.OperationalError as ex:
raise ex
def get_db_operate_obj(self):
"""
功能：使用 PersistentDB 管理连接池,连接均为专用连接
说明：使用此连接,可调用已封装函数,进行数据库操作,也可以自己实现,使用 mysql 事务机制
增 insert, insert_many
删 delete
改 update
查 one,all
使用方法参见备函数说明
"""
try:
if self.pool is None:
self.pool = self.get_pool()
return DBOperate(self.pool.connection())
except MySQLdb.OperationalError as ex:
raise ex
def test_threads(self):
num_threads = 3
persist = PersistentDB(dbapi, closeable=True)
try:
from queue import Queue, Empty
except ImportError: # Python 2
from Queue import Queue, Empty
query_queue, result_queue = [], []
for i in range(num_threads):
query_queue.append(Queue(1))
result_queue.append(Queue(1))
def run_queries(i):
this_db = persist.connection()
while 1:
try:
try:
q = query_queue[i].get(1, 1)
except TypeError:
q = query_queue[i].get(1)
except Empty:
q = None
if not q:
break
db = persist.connection()
if db != this_db:
r = 'error - not persistent'
else:
if q == 'ping':
r = 'ok - thread alive'
elif q == 'close':
db.close()
r = 'ok - connection closed'
else:
cursor = db.cursor()
cursor.execute(q)
r = cursor.fetchone()
cursor.close()
r = '%d(%d): %s' % (i, db._usage, r)
try:
result_queue[i].put(r, 1, 1)
except TypeError:
result_queue[i].put(r, 1)
db.close()
from threading import Thread
threads = []
for i in range(num_threads):
thread = Thread(target=run_queries, args=(i,))
threads.append(thread)
thread.start()
for i in range(num_threads):
try:
query_queue[i].put('ping', 1, 1)
except TypeError:
query_queue[i].put('ping', 1)
for i in range(num_threads):
try:
r = result_queue[i].get(1, 1)
except TypeError:
r = result_queue[i].get(1)
self.assertEqual(r, '%d(0): ok - thread alive' % i)
self.assertTrue(threads[i].is_alive())
for i in range(num_threads):
for j in range(i + 1):
try:
query_queue[i].put('select test%d' % j, 1, 1)
r = result_queue[i].get(1, 1)
except TypeError:
query_queue[i].put('select test%d' % j, 1)
r = result_queue[i].get(1)
self.assertEqual(r, '%d(%d): test%d' % (i, j + 1, j))
try:
query_queue[1].put('select test4', 1, 1)
except TypeError:
query_queue[1].put('select test4', 1)
try:
r = result_queue[1].get(1, 1)
except TypeError:
r = result_queue[1].get(1)
self.assertEqual(r, '1(3): test4')
try:
query_queue[1].put('close', 1, 1)
r = result_queue[1].get(1, 1)
except TypeError:
query_queue[1].put('close', 1)
r = result_queue[1].get(1)
self.assertEqual(r, '1(3): ok - connection closed')
for j in range(2):
try:
query_queue[1].put('select test%d' % j, 1, 1)
r = result_queue[1].get(1, 1)
except TypeError:
query_queue[1].put('select test%d' % j, 1)
r = result_queue[1].get(1)
self.assertEqual(r, '1(%d): test%d' % (j + 1, j))
for i in range(num_threads):
self.assertTrue(threads[i].is_alive())
try:
query_queue[i].put('ping', 1, 1)
except TypeError:
query_queue[i].put('ping', 1)
for i in range(num_threads):
try:
r = result_queue[i].get(1, 1)
except TypeError:
r = result_queue[i].get(1)
self.assertEqual(r, '%d(%d): ok - thread alive' % (i, i + 1))
self.assertTrue(threads[i].is_alive())
for i in range(num_threads):
try:
query_queue[i].put(None, 1, 1)
except TypeError:
query_queue[i].put(None, 1)
def test_ping_check(self):
Connection = dbapi.Connection
Connection.has_ping = True
Connection.num_pings = 0
persist = PersistentDB(dbapi, 0, None, None, 0, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 0)
db.close()
db = persist.connection()
self.assertFalse(db._con.valid)
self.assertEqual(Connection.num_pings, 0)
persist = PersistentDB(dbapi, 0, None, None, 1, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 1)
db.close()
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 2)
persist = PersistentDB(dbapi, 0, None, None, 2, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 2)
db.close()
db = persist.connection()
self.assertFalse(db._con.valid)
self.assertEqual(Connection.num_pings, 2)
cursor = db.cursor()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
cursor.execute('select test')
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
persist = PersistentDB(dbapi, 0, None, None, 4, True)
db = persist.connection()
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
db.close()
db = persist.connection()
self.assertFalse(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
cursor = db.cursor()
db._con.close()
self.assertFalse(db._con.valid)
self.assertEqual(Connection.num_pings, 3)
cursor.execute('select test')
self.assertTrue(db._con.valid)
self.assertEqual(Connection.num_pings, 4)
Connection.has_ping = False
Connection.num_pings = 0
creator=psycopg2, # 使用链接数据库的模块mincached
maxconnections=6, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=1, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=4, # 链接池中最多闲置的链接，0和None不限制
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始会话前执行的命令列表。
'''
POOL = PersistentDB(
creator=pymysql, # 使用链接数据库的模块
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始会话前执行的命令列表。
ping=0,
# ping MySQL服务端，检查是否服务可用。
closeable=False,
# 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
threadlocal=None, # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
host='172.21.86.205',
port=3306,
user='******',
password='******',
database='test',
charset='utf8')
def func():
conn = POOL.connection(shareable=False)
cursor = conn.cursor()
cursor.execute('select * from fcvane')
result = cursor.fetchall()
print(result)
