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 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
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 _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
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 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
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_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_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_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 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'])
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
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)
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)
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`'