def test_get_connection(self):
dbconfig = tests.get_mysql_config()
cnxpool = pooling.MySQLConnectionPool(pool_size=2, pool_name='test')
self.assertRaises(errors.PoolError, cnxpool.get_connection)
cnxpool = pooling.MySQLConnectionPool(pool_size=1, **dbconfig)
# Get connection from pool
pcnx = cnxpool.get_connection()
self.assertTrue(isinstance(pcnx, pooling.PooledMySQLConnection))
self.assertRaises(errors.PoolError, cnxpool.get_connection)
self.assertEqual(pcnx._cnx._pool_config_version,
cnxpool._config_version)
prev_config_version = pcnx._pool_config_version
prev_thread_id = pcnx.connection_id
pcnx.close()
# Change configuration
config_version = cnxpool._config_version
cnxpool.set_config(autocommit=True)
self.assertNotEqual(config_version, cnxpool._config_version)
pcnx = cnxpool.get_connection()
self.assertNotEqual(
pcnx._cnx._pool_config_version, prev_config_version)
self.assertNotEqual(prev_thread_id, pcnx.connection_id)
self.assertEqual(1, pcnx.autocommit)
pcnx.close()
def test_get_connection(self):
dbconfig = tests.get_mysql_config()
if tests.MYSQL_VERSION < (5, 7):
dbconfig["client_flags"] = [-ClientFlag.CONNECT_ARGS]
cnxpool = pooling.MySQLConnectionPool(pool_size=2, pool_name='test')
self.assertRaises(errors.PoolError, cnxpool.get_connection)
cnxpool = pooling.MySQLConnectionPool(pool_size=1, **dbconfig)
# Get connection from pool
pcnx = cnxpool.get_connection()
self.assertTrue(isinstance(pcnx, pooling.PooledMySQLConnection))
self.assertRaises(errors.PoolError, cnxpool.get_connection)
self.assertEqual(pcnx._cnx._pool_config_version,
cnxpool._config_version)
prev_config_version = pcnx._pool_config_version
prev_thread_id = pcnx.connection_id
pcnx.close()
# Change configuration
config_version = cnxpool._config_version
cnxpool.set_config(autocommit=True)
self.assertNotEqual(config_version, cnxpool._config_version)
pcnx = cnxpool.get_connection()
self.assertNotEqual(
pcnx._cnx._pool_config_version, prev_config_version)
self.assertNotEqual(prev_thread_id, pcnx.connection_id)
self.assertEqual(1, pcnx.autocommit)
pcnx.close()
# Get connection from pool using a context manager
with cnxpool.get_connection() as pcnx:
self.assertTrue(isinstance(pcnx, pooling.PooledMySQLConnection))
def test_add_connection(self):
cnxpool = pooling.MySQLConnectionPool(pool_name='test')
self.assertRaises(errors.PoolError, cnxpool.add_connection)
dbconfig = tests.get_mysql_config()
cnxpool = pooling.MySQLConnectionPool(pool_size=2, pool_name='test')
cnxpool.set_config(**dbconfig)
cnxpool.add_connection()
pcnx = pooling.PooledMySQLConnection(
cnxpool,
cnxpool._cnx_queue.get(block=False))
self.assertTrue(isinstance(pcnx._cnx, MySQLConnection))
self.assertEqual(cnxpool, pcnx._cnx_pool)
self.assertEqual(cnxpool._config_version,
pcnx._cnx._pool_config_version)
cnx = pcnx._cnx
pcnx.close()
# We should get the same connectoin back
self.assertEqual(cnx, cnxpool._cnx_queue.get(block=False))
cnxpool.add_connection(cnx)
# reach max connections
cnxpool.add_connection()
self.assertRaises(errors.PoolError, cnxpool.add_connection)
# fail connecting
cnxpool._remove_connections()
cnxpool._cnx_config['port'] = 9999999
cnxpool._cnx_config['unix_socket'] = '/ham/spam/foobar.socket'
self.assertRaises(errors.InterfaceError, cnxpool.add_connection)
self.assertRaises(errors.PoolError, cnxpool.add_connection, cnx=str)
def test___init__(self):
dbconfig = tests.get_mysql_config()
self.assertRaises(errors.PoolError, pooling.MySQLConnectionPool)
self.assertRaises(AttributeError, pooling.MySQLConnectionPool,
pool_name='test',
pool_size=-1)
self.assertRaises(AttributeError, pooling.MySQLConnectionPool,
pool_name='test',
pool_size=0)
self.assertRaises(AttributeError, pooling.MySQLConnectionPool,
pool_name='test',
pool_size=(pooling.CNX_POOL_MAXSIZE + 1))
cnxpool = pooling.MySQLConnectionPool(pool_name='test')
self.assertEqual(5, cnxpool._pool_size)
self.assertEqual('test', cnxpool._pool_name)
self.assertEqual({}, cnxpool._cnx_config)
self.assertTrue(isinstance(cnxpool._cnx_queue, Queue))
self.assertTrue(isinstance(cnxpool._config_version, uuid.UUID))
cnxpool = pooling.MySQLConnectionPool(pool_size=10, pool_name='test')
self.assertEqual(10, cnxpool._pool_size)
cnxpool = pooling.MySQLConnectionPool(pool_size=10, **dbconfig)
self.assertEqual(dbconfig, cnxpool._cnx_config,
"Connection configuration not saved correctly")
self.assertEqual(10, cnxpool._cnx_queue.qsize())
self.assertTrue(isinstance(cnxpool._config_version, uuid.UUID))
def test_reset_session(self):
"""Test MySQLConnectionPool.reset_session property"""
cnxpool = pooling.MySQLConnectionPool(pool_name='test',
pool_reset_session=False)
self.assertFalse(cnxpool.reset_session)
cnxpool._reset_session = True
self.assertTrue(cnxpool.reset_session)
def selectAll(userId):
userAllOrder = []
try:
connection_pool = pooling.MySQLConnectionPool(
host=os.getenv("SERVER_HOST"),
# port = os.getenv("SERVER_PORT"),
user=os.getenv("SERVER_USER"),
password=os.getenv("SERVER_PASSWORD"),
database="taipei",
charset="utf8",
auth_plugin='mysql_native_password')
except Exception as e:
print(e)
try:
sql_cmd = f"""
SELECT *
FROM orders o
LEFT JOIN user u
ON o.userId = u.id
WHERE o.userId= { userId }
"""
connection_object = connection_pool.get_connection()
if connection_object.is_connected():
taipeiCursor = connection_object.cursor()
taipeiCursor.execute(sql_cmd)
taipeiResults = taipeiCursor.fetchall()
connection_object.close()
if taipeiResults:
for taipeiResult in taipeiResults:
orderData = dict(zip(taipeiCursor.column_names, taipeiResult))
userAllOrder.append(orderData)
return userAllOrder
except:
print(e)
def deleteBookingData(**kwargs):
try:
connection_pool = pooling.MySQLConnectionPool(
host=os.getenv("SERVER_HOST"),
# port = os.getenv("SERVER_PORT"),
user=os.getenv("SERVER_USER"),
password=os.getenv("SERVER_PASSWORD"),
database="taipei",
charset="utf8",
auth_plugin='mysql_native_password')
except Exception as e:
print(e)
try:
deleteId = kwargs["userId"]
sql_cmd = f"""
DELETE FROM booking
WHERE userId = { deleteId }
"""
connection_object = connection_pool.get_connection()
if connection_object.is_connected():
taipeiCursor = connection_object.cursor()
taipeiCursor.execute(sql_cmd)
connection_object.commit()
except Exception as e:
print(e)
finally:
closePool(connection_object, taipeiCursor)
def __init__(self, env='desktop', name="comment_sense"):
self.name = name
self.env = env
if self.env == 'docker':
self.pw = get_docker_secret('db-password', default='password')
# Setup a temporary connection to build the table
cnx = mysql.connector.connect(
host="localhost" if self.env == 'desktop' else 'db',
user="******",
password='******' if self.env == 'desktop' else self.pw,
)
cursor = cnx.cursor(dictionary=True)
cursor.execute(f"CREATE DATABASE IF NOT EXISTS {self.name}")
cursor.execute(f"USE {self.name}")
cursor.close()
cnx.close()
# Setup Pooling
# ref: https://pynative.com/python-database-connection-pooling-with-mysql/
self.connection_pool = pooling.MySQLConnectionPool(
pool_name="cs_pool",
pool_size=8,
pool_reset_session=True,
host="localhost" if self.env == 'desktop' else 'db',
database=name,
user="******",
password='******' if self.env == 'desktop' else self.pw)
self.build_database()
def __init__(self, pool_size: int, config: Dict[str, str]) -> None:
self.pool = pooling.MySQLConnectionPool(pool_name='Aika',
pool_size=pool_size,
pool_reset_session=True,
autocommit=True,
**config)
return
def get_connection(config=None):
global _connection_pool
if config is None:
config = {
env_database_host: os.getenv(env_database_host),
env_database_username: os.getenv(env_database_username),
env_database_password: os.getenv(env_database_password)
}
if _connection_pool is None:
try:
_connection_pool = pooling.MySQLConnectionPool(
host=config[env_database_host],
user=config[env_database_username],
password=config[env_database_password],
database="capstone",
pool_name="capstone_pool")
except Error as e:
logging.exception(e)
try:
connection = _connection_pool.get_connection()
return connection
except Error as e:
logging.exception(e)
return None
def __init__(self, pool_size=3):
'''
初始化数据库：不存在则建立，存在则建立tables
'''
try:
con = mysql.connector.connect(user=self.dbconfig["user"],
password=self.dbconfig["password"])
except mysql.connector.Error as err: #要是连接server出错就没有办法了
print(err)
return
cur = con.cursor()
try:
con.database = self.dbconfig["database"]
except mysql.connector.Error as err: #连接特定的数据库出错，很可能是数据库不存在，启动except里面的纠错机制
if err.errno == errorcode.ER_BAD_DB_ERROR:
self.create_database(cur) #建立特定数据库
con.database = self.dbconfig["database"]
finally:
self.create_tables(cur)
#self.create_danmaku(cur)
cur.close()
con.close()
#建立数据库基本信息是单次连接，随后释放掉了，接下来我们要建立一个内部的连接池供其他函数使用
self.con_pool = pooling.MySQLConnectionPool(pool_name="smth_pool",
**self.dbconfig)
def init_pool(self):
try:
self.pool = pooling.MySQLConnectionPool(
pool_name=self.db_config['database'],
pool_size=10,
**self.db_config)
print('pool initialized: {}'.format(self.db_config['database']))
# setelah connect database, jalanin query USE {nama database}
cnx = self.pool.get_connection()
cursor = cnx.cursor()
cursor.execute('USE {}'.format(self.db_config['database']))
cursor.close()
cnx.close()
# self.table_check()
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
quit()
def test_set_config(self):
dbconfig = tests.get_mysql_config()
if tests.MYSQL_VERSION < (5, 7):
dbconfig["client_flags"] = [-ClientFlag.CONNECT_ARGS]
cnxpool = pooling.MySQLConnectionPool(pool_name='test')
# No configuration changes
config_version = cnxpool._config_version
cnxpool.set_config()
self.assertEqual(config_version, cnxpool._config_version)
self.assertEqual({}, cnxpool._cnx_config)
# Valid configuration changes
config_version = cnxpool._config_version
cnxpool.set_config(**dbconfig)
self.assertEqual(dbconfig, cnxpool._cnx_config)
self.assertNotEqual(config_version, cnxpool._config_version)
# Invalid configuration changes
config_version = cnxpool._config_version
wrong_dbconfig = dbconfig.copy()
wrong_dbconfig['spam'] = 'ham'
self.assertRaises(errors.PoolError, cnxpool.set_config,
**wrong_dbconfig)
self.assertEqual(dbconfig, cnxpool._cnx_config)
self.assertEqual(config_version, cnxpool._config_version)
def __init__(self, config):
self.pool = pooling.MySQLConnectionPool(pool_name=config["pool_name"],
pool_size=config["pool_size"],
host=config["host"],
port=config["port"],
database=config["database"],
user=config["user"],
password=config["password"],
charset="utf8",
pool_reset_session=True)
self.config = config
# 用户信息
self.add_user_sql = "insert into user_info values (\"%s\",\"%s\",%d)"
self.rm_user_sql = "delete from user_info where username = \"%s\""
self.get_password_sql = "select password from user_info where username=\"%s\""
self.add_job_sql = "insert into job_info values('%s','%s','%s','%s',%d)"
self.rm_job_sql = "delete from job_info where username=\"%s\" and jobname=\"%s\""
self.get_job_sql = "select jobname,jobdetail,username,jobstatus,create_time from job_info where username=\"%s\" order by create_time"
self.update_job_status_sql = "update job_info set jobstatus=\"%s\" where jobname=\"%s\""
self.get_all_job_sql = "select jobname,jobdetail,username,jobstatus,create_time from job_info where jobstatus!=\"finished\" and jobstatus!=\"failed\" order by create_time"
#图像增删查
self.get_imgs_sql = "select img_path,img_name,feature_path,create_time from img_info where username=\"%s\""
self.add_imgs_sql = "insert into img_info(img_path,img_name,username,create_time) values (%s,%s,%s,%s)"
self.rm_imgs_sql = "delete from img_info where username=%s and img_path =%s"
#视频增删查
self.get_videos_sql = "select video_path,video_name,feature_path,create_time from video_info where username=\"%s\""
self.add_videos_sql = "insert into video_info(video_path,video_name,username,create_time) values (%s,%s,%s,%s)"
self.rm_videos_sql = "delete from video_info where username=%s and video_path =%s"
''' 暂时不通过Mysql查询，直接利用文件系统索引查询
def test__set_pool_name(self):
cnxpool = pooling.MySQLConnectionPool(pool_name='test')
self.assertRaises(AttributeError, cnxpool._set_pool_name, 'pool name')
self.assertRaises(AttributeError, cnxpool._set_pool_name, 'pool%%name')
self.assertRaises(AttributeError, cnxpool._set_pool_name,
'long_pool_name' * pooling.CNX_POOL_MAXNAMESIZE)
def __connect(self, db_config, pool_size):
self.logger.info(
"start connect mysql database [ user=%s, host=%s, port=%s ]",
db_config["user"], db_config["host"], db_config["port"])
self.pool = pooling.MySQLConnectionPool(pool_size=pool_size,
**db_config)
self.logger.info("connect database success")
def getPoolConnection(self):
return pooling.MySQLConnectionPool(
pool_size=1,
pool_name="mysqlpool",
host=self.__host,
database=self.database_name,
user=self.__user,
password=self.__password).get_connection()
def beginDataBaseConnection():
global connection_pool
connection_pool = pooling.MySQLConnectionPool(pool_size=32,
pool_reset_session=True,
host="XXXXX",
user="******",
passwd="XXXXX")
print("Started database connection")
def __init__(self, host, name, user, password):
self.pool = pooling.MySQLConnectionPool(
pool_name="smash_pool",
pool_size=pooling.CNX_POOL_MAXSIZE,
database=name,
host=host,
user=user,
passwd=password)
def _init_pool(self, pool_token):
try:
connection_pool = pooling.MySQLConnectionPool(**pool_token)
return connection_pool
except Exception as e:
logger.error("Fail to initialize MySQL database pool.")
exit(1)
def _generatePool(self):
conf = {
"database": self._database,
"pool_size": self._poolSize,
"pool_name": self._poolName,
}
return connectorPooling.MySQLConnectionPool(**{**conf, **_g_208MysqlConf})
def main():
# TODO: Use MySQLCursorRaw cursor for performance
# User used on mariadb should have at least `SELECT` and `UPDATE`
# Privileges on the database. Please follow the Principle of least privilege
# and don't ever give more privileges than a user needs.
connection_pool = pooling.MySQLConnectionPool(
pool_name="pynative_pool",
pool_size=32,
pool_reset_session=True,
user=os.getenv('DBUser'),
password=os.getenv('DBPassword'),
host=os.getenv('DBHost'),
database=os.getenv('DATABASE'),
port=os.getenv('DBPort'))
connection = connection_pool.get_connection()
futures_list = []
objectsqueue = queue.Queue()
resultqueue = queue.Queue()
# spawn threads to process
workers_number = 15
logger.info('spawning {} to workers to delele and update objects'.format(
workers_number))
for _ in range(0, workers_number):
worker = ProcessThread(objectsqueue, resultqueue, s3, connection_pool)
worker.setDaemon(True)
worker.start()
with concurrent.futures.ThreadPoolExecutor(max_workers=32) as executor:
for row in iter_row(connection):
futures = executor.submit(move_all_objs, row)
futures_list.append(futures)
for future in futures_list:
try:
result = future.result(timeout=60)
update_record(connection_pool, result)
# delete_all_objects(result)
# results.append(result)
for k in result:
objectsqueue.put(k)
except Exception as e:
print(e)
# results.append(None)
# spawn threads to print
worker = PrintThread(resultqueue)
worker.setDaemon(True)
worker.start()
# wait for queue to get empty
objectsqueue.join()
resultqueue.join()
connection.close()
def test__set_pool_size(self):
cnxpool = pooling.MySQLConnectionPool(pool_name='test')
self.assertRaises(AttributeError, cnxpool._set_pool_size, -1)
self.assertRaises(AttributeError, cnxpool._set_pool_size, 0)
self.assertRaises(AttributeError, cnxpool._set_pool_size,
pooling.CNX_POOL_MAXSIZE + 1)
cnxpool._set_pool_size(pooling.CNX_POOL_MAXSIZE - 1)
self.assertEqual(pooling.CNX_POOL_MAXSIZE - 1, cnxpool._pool_size)
def __init__(self, **kwargs):
self._init_kwargs(**kwargs)
self._pool = pooling.MySQLConnectionPool(pool_size=31,
**self.__db_config)
if MySQLHelper.__instance is not None:
raise Exception('Not none singleton instance')
else:
MySQLHelper.__instance = self
def __init__(self, pool_size=5):
self.auth = {
"host": databases["edges"]["host"],
"user": databases["edges"]["user"],
"passwd": databases["edges"]["passwd"],
"db": databases["edges"]["db"],
"port": databases["edges"]["port"],
}
self.pool = pooling.MySQLConnectionPool(pool_size=pool_size, pool_reset_session=True, **self.auth)
def create_pool(**dbconfig: Dict) -> pooling.MySQLConnectionPool:
pool = ""
try:
pool = pooling.MySQLConnectionPool(**dbconfig)
logger.debug(
f"Connection pool created with name {dbconfig['pool_name']} "
f"and initial size {dbconfig['pool_size']}")
except Error as e:
print(f"Error while creating mysql connection pool: {e}")
return pool
def create_conn_pool(conf: Dict, name: str):
_pool = pooling.MySQLConnectionPool(pool_name=name,
pool_size=5,
buffered=False,
autocommit=True,
# raise_on_warnings=True,
**conf)
print(current_process().pid, 'create pool', name)
# traceback.print_stack()
return _pool
def beginDatabaseConnection():
global connection_pool
connection_pool = pooling.MySQLConnectionPool(
pool_size=32,
pool_reset_session=True,
host=settings.databasehost,
user=settings.databaseuser,
passwd=settings.databasepassword,
)
print("Started database connection")
def __init__(self, host, username, password, db='jobstats'):
self.cnx_pool = mysql.MySQLConnectionPool(pool_name='jobstats-site',
pool_size=32,
host=host,
user=username,
password=password,
database=db)
self.yesterday = datetime.date.today() - datetime.timedelta(1)
self.last_week = datetime.date.today() - datetime.timedelta(7)
def beginDataBaseConnection():
global connection_pool
connection_pool = pooling.MySQLConnectionPool(
pool_size=32,
pool_reset_session=True,
host=settings.database_host,
user=settings.database_user,
passwd=settings.database_password,
auth_plugin='mysql_native_password')
print("Started database connection")
