mysql_schema_info.py 文件源码

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