async def register(self):
dbs = str(await self.agent_dbs())
server_dbpool = await self.__server_pool
async with server_dbpool.acquire() as conn:
async with conn.cursor() as cur:
try:
check_register_sql = "SELECT hostname FROM Agent WHERE hostname='%s';" % hostname
await cur.execute(check_register_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % check_register_sql))
os._exit(0)
else:
try:
(host, ) = await cur.fetchall()
except Exception as e:
try:
register_sql = 'INSERT INTO Agent(hostname, status, dbs) VALUES("%s", "running", "%s");' % (hostname, dbs)
await cur.execute(register_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % register_sql), exc_info=True)
os._exit(0)
else:
logger.info('(Regist agent successful: %s)' % hostname)
await conn.commit()
await cur.close()
conn.close()
server_dbpool.close()
await server_dbpool.wait_closed()
else:
if not host:
try:
register_sql = 'INSERT INTO Agent(hostname, status) VALUES("%s", "running", "%s");' % (hostname, dbs)
await cur.execute(register_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % register_sql))
os._exit(0)
else:
logger.info('(Regist agent successful: %s)' % hostname)
await conn.commit()
await cur.close()
conn.close()
server_dbpool.close()
await server_dbpool.wait_closed()
else:
logger.info('(%s is registed)' % hostname)
try:
interverl_sql = "SELECT intervel FROM Agent WHERE hostname='%s';" % hostname
await cur.execute(interverl_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % interverl_sql))
os._exit(0)
else:
(intervel, ) = await cur.fetchone()
print('时间啊', intervel)
await cur.close()
conn.close()
server_dbpool.close()
await server_dbpool.wait_closed()
return intervel
def scroll(self, value, mode='relative'):
"""Scroll the cursor in the result set to a new position according
to mode.
If mode is relative (default), value is taken as offset to the
current position in the result set, if set to absolute, value
states an absolute target position. An IndexError should be raised in
case a scroll operation would leave the result set. In this case,
the cursor position is left undefined (ideal would be to
not move the cursor at all).
"""
self._check_executed()
if mode == 'relative':
r = self._rownumber + value
elif mode == 'absolute':
r = value
else:
raise ProgrammingError("unknown scroll mode %s" % mode)
if not (0 <= r < len(self._rows)):
raise IndexError("out of range")
self._rownumber = r
fut = asyncio.Future(loop=self._loop)
fut.set_result(None)
return fut
async def scroll(self, value, mode='relative'):
"""Scroll the cursor in the result set to a new position
according to mode . Same as :meth:`Cursor.scroll`, but move cursor
on server side one by one row. If you want to move 20 rows forward
scroll will make 20 queries to move cursor. Currently only forward
scrolling is supported.
:param int value: move cursor to next position according to mode.
:param str mode: scroll mode, possible modes: `relative` and `absolute`
"""
self._check_executed()
if mode == 'relative':
if value < 0:
raise NotSupportedError("Backwards scrolling not supported "
"by this cursor")
for _ in range(value):
await self._read_next()
self._rownumber += value
elif mode == 'absolute':
if value < self._rownumber:
raise NotSupportedError(
"Backwards scrolling not supported by this cursor")
end = value - self._rownumber
for _ in range(end):
await self._read_next()
self._rownumber = value
else:
raise ProgrammingError("unknown scroll mode %s" % mode)
async def agent_dbs(self):
pool = await self.__agent_pool
async with pool.acquire() as conn:
async with conn.cursor() as cur:
try:
dbs_sql = "SHOW DATABASES;"
await cur.execute(dbs_sql)
except Exception as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % dbs_sql), exc_info=True)
os._exit(0)
else:
dbs = await cur.fetchall()
await cur.close()
pool.close()
await pool.wait_closed()
return [list(db)[0] for db in dbs]
def find_by_id( # pylint: disable=arguments-differ
self,
column_set: List[str] = None,
entity_id: str = None) -> Optional[CrudEntity]:
"""TODO"""
if self.is_connected():
if entity_id:
stm = self._sql_factory \
.select(column_set=column_set, filters=SqlFilter({"UUID": '{}'.format(entity_id)})) \
.replace(':tableName', self.table_name())
log.debug('Executing SQL statement: {}'.format(stm))
self.execute(stm, True)
result = self._cursor.fetchall()
if len(result) > 1:
raise ProgrammingError(f'Multiple results found {len(result)}')
return self.row_to_entity(result[0]) if len(result) > 0 else None
return None
raise NotConnectedError('Not connected to database.')
def scroll(self, value, mode='relative'):
self._check_executed()
if mode == 'relative':
if value < 0:
raise NotSupportedError("Backwards scrolling not supported "
"by this cursor")
for _ in range(value):
yield from self._read_next()
self._rownumber += value
elif mode == 'absolute':
if value < self._rownumber:
raise NotSupportedError(
"Backwards scrolling not supported by this cursor")
end = value - self._rownumber
for _ in range(end):
yield from self._read_next()
self._rownumber = value
else:
raise ProgrammingError("unknown scroll mode %s" % mode)
def _check_executed(self):
if not self._executed:
raise ProgrammingError("execute() first")
def _get_db(self):
if not self._connection:
raise ProgrammingError("Cursor closed")
return self._connection
async def dbinfo(self):
pool = await self.__agent_pool
now = arrow.now()
create_time = now.format('YYYY-MM-DD HH:mm:ss')
async with pool.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute(QUESTIONS)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % QUESTIONS))
os._exit(0)
else:
(_, questions) = await cur.fetchone()
try:
await cur.execute(COM_SELECT)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % COM_SELECT))
else:
(_, com_select) = await cur.fetchone()
try:
await cur.execute(COM_INSERT)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % COM_INSERT))
else:
(_, com_insert) = await cur.fetchone()
try:
await cur.execute(COM_UPDATE)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % COM_UPDATE))
else:
(_, com_update) = await cur.fetchone()
try:
await cur.execute(COM_DELETE)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % COM_DELETE))
else:
(_, com_delete) = await cur.fetchone()
writes = (int(com_insert) + int(com_update) + int(com_delete))
await cur.close()
conn.close()
async with pool.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute(PERFORMANCE)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % PERFORMANCE))
os._exit(0)
else:
performance = []
for db, count, avgmicrosec, err_count, create_time in await cur.fetchall():
performance.append((db, count, avgmicrosec, err_count))
await cur.close()
conn.close()
async with pool.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute(MAX_CONN)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % MAX_CONN))
os._exit(0)
else:
(_, max_conn) = await cur.fetchone()
try:
await cur.execute(THREADS_CONNCTED)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % THREADS_CONNCTED))
os._exit(0)
else:
(_, threads_connected) = await cur.fetchone()
try:
await cur.execute(THREADS_RUNNING)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % THREADS_RUNNING))
os._exit(0)
else:
(_, threads_running) = await cur.fetchone()
try:
await cur.execute(ABORTED_CONNECTS)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % ABORTED_CONNECTS))
os._exit(0)
else:
(_, aborted_connects) = await cur.fetchone()
try:
await cur.execute(CONNECTION_ERRORS_INTERNAL)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % CONNECTION_ERRORS_INTERNAL))
os._exit(0)
else:
(_, connection_errors_internal) = await cur.fetchone()
try:
await cur.execute(CONNECTION_ERRORS_MAX_CONNECTIONS)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % CONNECTION_ERRORS_MAX_CONNECTIONS))
os._exit(0)
else:
(_, connection_errors_max_connections) = await cur.fetchone()
await cur.close()
conn.close()
async with pool.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute(INNODB_BUFFER_POOL_SIZE)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % INNODB_BUFFER_POOL_SIZE))
os._exit(0)
else:
(_, innodb_buffer_pool_size) = await cur.fetchone()
try:
await cur.execute(INNODB_BUFFER_POOL_PAGES_TOTAL)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % INNODB_BUFFER_POOL_PAGES_TOTAL))
os._exit(0)
else:
(_, innodb_buffer_pool_pages_total) = await cur.fetchone()
try:
await cur.execute(INNODB_BUFFER_POOL_PAGES_FREE)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % INNODB_BUFFER_POOL_PAGES_FREE))
os._exit(0)
else:
(_, innodb_buffer_pool_pages_free) = await cur.fetchone()
try:
await cur.execute(INNODB_BUFFER_POOL_READ_REQUESTS)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % INNODB_BUFFER_POOL_READ_REQUESTS))
os._exit(0)
else:
(_, innodb_buffer_pool_read_requests) = await cur.fetchone()
try:
await cur.execute(INNODB_BUFFER_POOL_READS)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % INNODB_BUFFER_POOL_READS))
os._exit(0)
else:
(_, innodb_buffer_pool_reads) = await cur.fetchone()
try:
await cur.execute(INNODB_PAGE_SIZE)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % INNODB_PAGE_SIZE))
os._exit(0)
else:
(_, innodb_page_size) = await cur.fetchone()
await cur.close()
conn.close()
pool.close()
await pool.wait_closed()
return {'create_time': create_time,
'Throughput': (questions, com_select, writes),
'Performance': performance,
'Connections': (max_conn, threads_connected, threads_running, aborted_connects,
connection_errors_internal, connection_errors_max_connections),
'Innodb': (innodb_buffer_pool_size, innodb_buffer_pool_pages_total,
innodb_buffer_pool_pages_free, innodb_buffer_pool_read_requests,
innodb_buffer_pool_reads, innodb_page_size)
}
async def save_sql(self):
try:
data = await self.dbinfo()
except Exception as e:
logger.error("Get agent data error", exc_info=True)
os._exit(0)
else:
server_dbpool = await self.__server_pool
create_time = data['create_time']
performance = data['Performance']
throughput = data['Throughput']
connections = data['Connections']
innodb = data['Innodb']
async with server_dbpool.acquire() as conn:
async with conn.cursor() as cur:
try:
questions, com_select, writes = throughput
save_throughput_sql = 'INSERT INTO Throughput(hostname, questions, com_select, writes, create_time) VALUES("%s", %d, %d, %d, "%s");'% (hostname, int(questions), int(com_select), int(writes), create_time)
await cur.execute(save_throughput_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % save_throughput_sql))
os._exit(0)
else:
logger.info('(Success: %s)' % save_throughput_sql)
for raw in performance:
try:
db, count, avgmicrosec, err_count = raw
save_queryperformance_sql = 'INSERT INTO Performance(hostname, db_name, avgmicrosec, err_count, create_time) VALUES("%s", "%s", %d, %d, "%s");' % (hostname, str(db), int(avgmicrosec), int(err_count), create_time)
await cur.execute(save_queryperformance_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % save_queryperformance_sql))
os._exit(0)
else:
logger.info('(Success: %s)' % save_queryperformance_sql)
try:
max_conn, threads_connected, threads_running, aborted_connects, connection_errors_internal, connection_errors_max_connections = connections
save_connections_sql = 'INSERT INTO Connections(hostname, max_connections, threads_connected, threads_running, aborted_connects, connection_errors_internal, connection_errors_max_connections, create_time) VALUES("%s", %d, %d, %d, %d, %d, %d,"%s");' % (hostname, int(max_conn),int(threads_connected),int(threads_running),int(aborted_connects),int(connection_errors_internal),int(connection_errors_max_connections), create_time)
await cur.execute(save_connections_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % save_connections_sql))
os._exit(0)
else:
logger.info('(Success: %s)' % save_connections_sql)
try:
innodb_buffer_pool_size, innodb_buffer_pool_pages_total, innodb_buffer_pool_pages_free, innodb_buffer_pool_read_requests, innodb_buffer_pool_reads, innodb_page_size = innodb
save_innodbbufferpool_sql = 'INSERT INTO Innodb(hostname, innodb_buffer_pool_pages_total, innodb_buffer_pool_read_requests, innodb_buffer_pool_reads, innodb_buffer_pool_rate, innodb_page_size, create_time) VALUES("%s", %d, %d,%d,%d, %d, "%s");' % (hostname, int(innodb_buffer_pool_pages_total),int(innodb_buffer_pool_read_requests),int(innodb_buffer_pool_reads), ((int(innodb_buffer_pool_pages_total) - int(innodb_buffer_pool_pages_free)) / int(innodb_buffer_pool_pages_total)), int(innodb_page_size), create_time)
await cur.execute(save_innodbbufferpool_sql)
except ProgrammingError as e:
logger.error("(%s)" % ProgrammingError('SQL syntax error: %s' % save_connections_sql))
os._exit(0)
else:
logger.info('(Success: %s)' % save_connections_sql)
await conn.commit()
await cur.close()
server_dbpool.close()
await server_dbpool.wait_closed()