def query_table_info(*dbinfo):
sql_str = """
SELECT
IFNULL(@@hostname, @@server_id) SERVER_NAME,
%s as HOST,
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_ROWS,
t.DATA_LENGTH,
t.INDEX_LENGTH,
t.AUTO_INCREMENT,
c.COLUMN_NAME,
c.DATA_TYPE,
LOCATE('unsigned', c.COLUMN_TYPE) COL_UNSIGNED
# CONCAT(c.DATA_TYPE, IF(LOCATE('unsigned', c.COLUMN_TYPE)=0, '', '_unsigned'))
FROM
information_schema.`TABLES` t
LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
AND c.EXTRA = 'auto_increment'
WHERE
t.TABLE_SCHEMA NOT IN (
'mysql',
'information_schema',
'performance_schema',
'sys'
)
AND t.TABLE_TYPE = 'BASE TABLE'
"""
dbinfo_str = "%s:%d" % (dbinfo[0], dbinfo[1])
rs = ({},)
try:
db_conn = MySQLdb.Connect(host=dbinfo[0], port=dbinfo[1], user=dbinfo[2], passwd=dbinfo[3],
connect_timeout=5)
cur = db_conn.cursor(MySQLdb.cursors.DictCursor)
param = (dbinfo_str,)
print "\n[%s] Get schema info from db: '%s'..." % (datetime.today(), dbinfo_str)
cur.execute(sql_str, param)
rs = cur.fetchall()
except MySQLdb.Error, e:
print "Error[%d]: %s (%s)" % (e.args[0], e.args[1], dbinfo_str)
exit(-1)
if InfluxDB_INFO is not None:
print "Write '%s' schema table info to influxdb ..." % dbinfo_str
write_influxdb(*rs)
else:
print rs
评论列表
文章目录