def delete_object(info):
    """Delete object from DB"""

    # connect to racktables db
    db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)

    # check if object_id already exists, then create object if not
    object_id = ""
    for item in db.query("select * from Object where name='{0}'".format(info["hostname"])):
        object_id = item.id

    # delete object if already exists
    if object_id:
        url = """http://{0}/racktables/index.php?module=redirect&op=deleteObject&page=depot&tab=addmore&object_id={1}""".format(
            rt_server, object_id
        )
        req = requests.get(url, auth=rt_auth)
        if req.status_code != requests.codes.ok:
            print "Failed to delete the existing object: {0}".format(info["hostname"])
            return False
        else:
            print "OK - Deleted the existing object: {0}".format(info["hostname"])

    # close db
    db.close()

    return True

def _connection(config):
    conn = None
    try:
        if config.DB_BACKEND == 'mysql':
            from torndb import Connection
            conn = Connection(
                "%s:%s" % (config.DB_HOST, config.DB_PORT),
                config.DB_NAME,
                user=config.DB_USER,
                password=config.DB_PASS
            )
        elif config.DB_BACKEND == 'sqlite':
            import sqlite3
            conn = sqlite3.connect(config.DB_NAME)
            conn.row_factory = _dict_factory
        elif config.DB_BACKEND == 'postgres':
            import psycopg2
            from psycopg2.extras import DictConnection

            conn = psycopg2.connect(
                database=config.DB_NAME,
                user=config.DB_USER,
                password=config.DB_PASS,
                host=config.DB_HOST,
                port=config.DB_PORT,
                connection_factory=DictConnection,
            )
        else:
            raise ValueError("Unknown backend %r" % config.DB_BACKEND)

        yield conn
    finally:
        if conn is not None:
            conn.close()

def do_query(database=None):
    # Pick up the database credentials
    # app.logger.warning("%s requesting access to %s database" % (
    #     request.remote_addr, database))
    creds = get_db_creds(database)

    # If we couldn't find corresponding credentials, throw a 404
    if not creds:
        return {"ERROR": "Unable to find credentials matching %s." % database}
        abort(404)

    # Prepare the database connection
    app.logger.debug("Connecting to %s database (%s)" % (
        database, request.remote_addr))
    db = Connection(**creds)

    # See if we received a query
    sql = request.form.get('sql')
    if not sql:
        sql = request.args.get('sql')
        if not sql:
            return {"ERROR": "SQL query missing from request."}

    # If the query has a percent sign, we need to excape it
    if '%' in sql:
        sql = sql.replace('%', '%%')

    # Attempt to run the query
    try:
        app.logger.info("%s attempting to run \" %s \" against %s database" % (
            request.remote_addr, sql, database))
        results = db.query(sql)
        app.logger.info(results)
    except Exception, e:
        return {"ERROR": ": ".join(str(i) for i in e.args)}

    def get(self, article_id):
        # template_name = "article_details.html"
        template_name = "mobile/article_details.html"

        db = Connection(settings.DATABASE_SERVER,
                        settings.DATABASE_NAME,
                        settings.DATABASE_USER,
                        settings.DATABASE_PASSWORD,
                        )

        sql = "SELECT * FROM articles WHERE id='{0}'".format(
            article_id)
        article = db.query(sql)[0]

        # article["read_count"], article["comment_count"] = \
        #     get_article_statistics(db, article_id)
        article["url"] = urllib.quote(article["url"])

        # Update article read count
        now = datetime.datetime.now()
        sql = """INSERT INTO article_reads (`article_id`, `user_id`, `time`)
                 VALUES ('{0}', '{1}', '{2}')
              """.format(article_id, 0, now)
        db.execute(sql)

        kwargs = dict(article=article,
                      day=article["day"])

        super(ArticleDetailsHandler, self).render(
            template_name,
            **kwargs
        )

    def get(self):

        db = Connection(settings.DATABASE_SERVER,
                        settings.DATABASE_NAME,
                        settings.DATABASE_USER,
                        settings.DATABASE_PASSWORD,
                        )

        condition = """WHERE target_user_id='1' and consumed='0'
                        and article_comment.user_id=user.id
                        and article_comment.article_id=articles.id"""

        sql = """SELECT user_id, name AS user_name, email, article_id,
                        article_comment.time AS time, title as article_title,
                        article_comment.content AS content
                 FROM article_comment,user,articles {0}
                 ORDER BY article_comment.time DESC;""".format(condition)
        comments = db.query(sql)

        for comment in comments:
            comment["time"] = convert_to_time_zone(comment["time"],
                                                   "Asia/Shanghai")
            comment["time"] = comment["time"].strftime("%Y-%m-%d %H:%M:%S")

        kwargs = dict(comments=comments,
                      )

        self.render("", kwargs=kwargs)

def get_env_iter():

    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')


    v_show_env_type = config.SHOW_ENV_TYPE

    if v_show_env_type==1:

        v_more_sql = ''

    elif v_show_env_type==2:  #id =3 表示生产环境

        v_more_sql = ' and id=3'

    elif v_show_env_type==3:

        v_more_sql = ' and id!=3'

    else:
        pass

    str_sql = "select id,name from resources_env where 1=1 %s order by id"  % (v_more_sql)
    

    env_list = db.iter(str_sql)

    db.close()

    return env_list

def do_update(database=None):
    """Perform databse update."""
    # Pick up the database credentials
    creds = get_db_creds(database)

    # If we couldn't find corresponding credentials, throw a 404
    if not creds:
        msg = "Unable to find credentials matching {0}."
        return {"ERROR": msg.format(database)}, 404

    # Prepare the database connection
    app.logger.debug("Connecting to %s database (%s)" % (
        database, request.remote_addr))
    db = Connection(**creds)

    # See if we received a query
    sql = request.form.get('sql')
    if not sql:
        sql = request.args.get('sql')
        if not sql:
            return {"ERROR": "SQL query missing from request."}, 400

    # If the query has a percent sign, we need to excape it
    if '%' in sql:
        sql = sql.replace('%', '%%')

    # Attempt to run the query
    try:
        app.logger.info("%s attempting to run \" %s \" against %s database" % (
            request.remote_addr, sql, database))
        results = db.update(sql)
        app.logger.info(results)
    except Exception, e:
        return {"ERROR": ": ".join(str(i) for i in e.args)}, 422

    def get_project_keys_from_mysql(self):
        from torndb import Connection
        db = Connection(
            "%s:%s" % (self.config.MYSQL_HOST, self.config.MYSQL_PORT),
            self.config.MYSQL_DB,
            user=self.config.MYSQL_USER,
            password=self.config.MYSQL_PASS
        )

        query = "select project_id, public_key, secret_key from sentry_projectkey"
        logging.info("Executing query %s in MySQL", query)

        project_keys = {}

        try:
            db_projects = db.query(query)

            if db_projects is None:
                return None

            for project in db_projects:
                logging.info("Updating information for project with id %s...", project.project_id)
                self.add_project(project_keys, project.project_id, project.public_key, project.secret_key)

        finally:
            db.close()
        return project_keys

def update_rack(info, object_id):
    """Automate server audit for rack info into Racktables"""

    # connect to racktables db
    db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)

    # update the rackspace
    if opts["rackspace"]:
        rs_info = opts["rackspace"].split(":")
        colo = "".join(rs_info[0:1])
        row = "".join(rs_info[1:2])
        rack = "".join(rs_info[2:3])
        atom = "".join(rs_info[3:4])
        if not atom:
            print "The rackspace is not correct"
            return False

        # get rack_id
        for item in db.query(
            "select * from Rack where name = '{0}' and location_name = '{1}' and row_name = '{2}'".format(
                rack, colo, row
            )
        ):
            rack_id = item.id
        if not rack_id:
            print "Failed to get rack_id"
            return False

        atom_list = atom.split(",")
        atom_data = []
        for i in atom_list:
            if opts["rackposition"]:
                if opts["rackposition"] in ["left", "front"]:
                    atom_data.append("&atom_{0}_{1}_0=on".format(rack_id, i))
                if opts["rackposition"] in ["right", "back"]:
                    atom_data.append("&atom_{0}_{1}_2=on".format(rack_id, i))
                if opts["rackposition"] in ["interior"]:
                    atom_data.append("&atom_{0}_{1}_1=on".format(rack_id, i))
            else:
                atom_data.append("&atom_{0}_{1}_0=on&atom_{0}_{1}_1=on&atom_{0}_{1}_2=on".format(rack_id, i))
        atom_url = "".join(atom_data)

        url = """http://{0}/racktables/index.php?module=redirect&page=object&tab=rackspace&op=updateObjectAllocation""".format(
            rt_server
        )
        payload = """object_id={0}&rackmulti%5B%5D={1}&comment=&got_atoms=Save{2}""".format(
            object_id, rack_id, atom_url
        )
        req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
        if req.status_code != requests.codes.ok:
            print "Failed to update rackspace"
            return False
        print "OK - Updated rackspace"

    # close db
    db.close()

    # end
    return True

def update_blank_switch_security_pdu_offline(info):
    """Automate server autodir for PatchPanel/NetworkSwitch/NetworkSecurity/PDU into Racktables or as offline mode"""

    # connect to racktables db
    db = Connection(rt_server, rt_dbname, rt_dbuser, rt_dbpass)

    # delete object if already exists
    delete_object(info)

    # create object
    url = """http://{0}/racktables/index.php?module=redirect&page=depot&tab=addmore&op=addObjects""".format(rt_server)
    if opts["blank"]:
        payload = """0_object_type_id=9&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
            info["hostname"]
        )
    if opts["switch"]:
        payload = """0_object_type_id=8&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
            info["hostname"]
        )
    if opts["security"]:
        payload = """0_object_type_id=798&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
            info["hostname"]
        )
    if opts["pdu"]:
        payload = """0_object_type_id=2&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
            info["hostname"]
        )
    if opts["offline"]:
        payload = """0_object_type_id=4&0_object_name={0}&0_object_label=&0_object_asset_no={0}&got_fast_data=Go%21""".format(
            info["hostname"]
        )

    req = requests.post(url, data=payload, headers=rt_headers, auth=rt_auth)
    if req.status_code != requests.codes.ok:
        print "Failed to create object: {0}".format(info["hostname"])
        return False
    else:
        print "OK - Created object: {0}".format(info["hostname"])

    # get object_id
    for item in db.query("select * from Object where name='{0}'".format(info["hostname"])):
        object_id = item.id
    if not object_id:
        print "Failed to get object_id"
        return False

    # update rack info
    update_rack(info, object_id)

    # close db
    db.close()

    # end
    return True

    def get(self):
        category = self.get_argument("category", "all")
        cur_page = self.get_argument("page", "0")
        query = self.get_argument("query", "")
        num_per_page = 5

        db = Connection(settings.DATABASE_SERVER,
                        settings.DATABASE_NAME,
                        settings.DATABASE_USER,
                        settings.DATABASE_PASSWORD,
                        )

        condition = "WHERE category='{0}'".format(category)
        if category == "all":
            condition = ""
        if query:
            condition = """WHERE UPPER(title) LIKE '%%{0}%%'
                           OR UPPER(profile) LIKE '%%{0}%%'
                           OR UPPER(author) LIKE '%%{0}%%'
                           OR UPPER(content) LIKE '%%{0}%%'
                        """.format(query.upper())

        sql = "SELECT COUNT(*) FROM articles {0}".format(condition)
        count = db.query(sql)[0]["COUNT(*)"]
        max_page = int(math.ceil((count + 0.0) / num_per_page))

        sql = """SELECT articles.id AS id, title, profile, author, url, picUrl,
                        articles.time AS time,
                        (SELECT COUNT(*) FROM article_reads
                         WHERE article_reads.article_id=articles.id) AS read_count,
                        (SELECT COUNT(*) FROM article_comment
                         WHERE article_comment.article_id=articles.id) AS comment_count
                 FROM articles
                 {0}
                 ORDER BY articles.time DESC
                 LIMIT {1}, {2};
              """.format(condition, int(cur_page) * num_per_page, num_per_page)
        articles = db.query(sql)

        for art in articles:
            # art["read_count"], art["comment_count"] = \
            #     get_article_statistics(db, art["id"])
            art["time"] = art["time"].strftime("%Y-%m-%d")
            art["picUrl"] = "/image-proxy/?url={0}".format(
                urllib.quote(art["picUrl"])
            )

        kwargs = dict(articles=articles,
                      category=category,
                      cur_page=int(cur_page),
                      max_page=max_page)

        self.render("", kwargs=kwargs)

def log_dba_jobs_progress(v_job_id,v_cur_prog_desc,v_cur_cum_prog_desc,v_cur_prog_com_rate):

    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')

    v_add_job_progress_sql='''insert into dba_job_progress(job_id,cur_prog_desc,cur_cum_prog_desc,cur_prog_com_rate) values(%d,'%s','%s',%d)''' % (
                v_job_id,v_cur_prog_desc,v_cur_cum_prog_desc,v_cur_prog_com_rate)

    db.execute(v_add_job_progress_sql.replace('%','%%'))

    db.close()

def initial_dba_job(v_op_user,v_op_comment):

    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')

    v_add_job_sql = '''insert into dba_jobs(op_user,job_desc) values('%s','%s')''' % (
            v_op_user,v_op_comment)

    v_job_id=db.execute(v_add_job_sql.replace('%','%%'))

    db.close()

    return v_job_id

def final_dba_job(v_job_id):

    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')

    # 更新job队列状态为完成
    v_update_job_sql = '''update dba_jobs set status=1 where job_id=%d''' % (
        v_job_id)


    db.execute(v_update_job_sql.replace('%','%%'))

    db.close()

def upload_processlist():

    # 连接配置中心库
    # db = Connection('/home/apps/inception/inc.socket',
    #                 '',
    #                 '',
    #                 '',
    #                 time_zone='+8:00')

    db = Connection("127.0.0.1:6669", "", "", "", time_zone="+8:00")

    print "aa"

    v_sql = r"""/*--user=mysqladmin;--password=mysql;--host=172.26.137.125;
    --enable-check;--port=3306;*/  
    inception_magic_start;  
    use test;  
    CREATE TABLE adaptive_office23(id int);  
    inception_magic_commit;"""

    # print v_sql

    upload_server_list = db.iter(v_sql)

    if upload_server_list:  # 对实例表进行循环

        i = 0

        print upload_server_list

        for upload_server in upload_server_list:

            stage = upload_server["stage"]

            print stage

            stagestatus = upload_server["stagestatus"]

            print stagestatus

        #     mysql_port = upload_server['port']

        #     v_host =host_ip + ':' + str(mysql_port)

        #     i=i+1

    db.close()

def get_job_status(v_job_id):

    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')

    v_get_sql = '''SELECT cur_cum_prog_desc,cur_prog_com_rate,cur_prog_shell_cmd from dba_job_progress where id=(select max(id) from dba_job_progress where job_id=%d)''' % (
        v_job_id)

    job_list = db.query(v_get_sql)
            

    db.close()

    return job_list

def get_domain_list_from_env(v_role_id,belong_env):

    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')


    str_sql = '''select b.id,b.name from resources_role_app a,resources_app b where 
    a.app_id = b.id and a.role_id = %d and b.app_type= %d ''' % (v_role_id,belong_env)
        
    

    app_list = db.query(str_sql)

    db.close()

    return app_list

    def post(self):
        title = self.get_argument("title", "")
        author = self.get_argument("author", "")
        category = self.get_argument("category", "")
        date = self.get_argument("date", "")
        profile = self.get_argument("profile", "")
        picUrl = self.get_argument("picUrl", "")
        url = self.get_argument("url", "")

        time = datetime.datetime.strptime(date, "%m/%d/%Y")
        day = {
            0: "Mon",
            1: "Tue",
            2: "Wed",
            3: "Thu",
            4: "Fri",
            5: "Sat",
            6: "Sun",
        }[time.weekday()]

        sql = """INSERT INTO articles (`title`, `author`, `day`, `time`, `url`, `profile`, `picUrl`, `category`)
                 VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');
              """.format(
              title,
              author,
              day,
              time,
              url,
              profile,
              picUrl,
              category
        )
        db = Connection(settings.DATABASE_SERVER,
                        settings.DATABASE_NAME,
                        settings.DATABASE_USER,
                        settings.DATABASE_PASSWORD,
                        )
        lastrowid = db.execute(sql)
        if lastrowid:
            self.redirect("/articles/details/{0}/".format(lastrowid))
        else:
            template_name = "/upload_error.html"
            self.render(template_name)

 def get_latest_article(self, from_user, to_user, timestamp):
     db = Connection(settings.DATABASE_SERVER,
                     settings.DATABASE_NAME,
                     settings.DATABASE_USER,
                     settings.DATABASE_PASSWORD,
                     )
     sql = "SELECT * FROM articles ORDER BY time DESC LIMIT 1"
     article = db.query(sql)[0]
     return self.make_single_pic_response(
         from_user,
         to_user,
         timestamp,
         article["title"],
         article["profile"],
         article["picUrl"],
         "{0}/m#/article_details/{1}".format(
             settings.SITE_HTTP_URL,
             article["id"]
         )
     )

def remote_start_mysql_server(v_host,v_os_user,v_os_password,
    v_db_port): 

    # v_mysql_version 1: mysql5 4:mariadb5 5:mariadb10
    # 
    db = Connection('/tmp/mysql3306.sock',
                    config.DB_NAME,
                    config.DB_USER,
                    config.DB_PASSWD,
                    time_zone='+8:00')

    # 获得Mysql server 版本: mysql5,mariadb5,mariadb10
    v_get_sql = r'''SELECT case mysql_version when 1 then 'mysql5' when 4 then 'mariadb5' when 5 then 'mariadb10' when 6 then 'percona5.6' when 7 then 'pxc5.6' end mysql_version from tag where ip='%s' and port=%d ''' % (v_host,int(v_db_port))

    v_list = db.get(v_get_sql)

    v_mysql_version = v_list['mysql_version']
            

    db.close()

            
    #v_db_socket='--socket=/tmp/mysql'+str(v_db_port)+'.sock'
    #nohup 防止paramiko 进程退出后，中断执行
    # 必须要加 1>/dev/null 2>&1,否则命令不执行。可能是paramiko 模式下，不加的话，执行命令时日志无法输出
    
    v_exe_cmd = r'''nohup /apps/svr/%s/bin/mysqld_safe --defaults-file=%s/%s_%d.cnf 1>/dev/null 2>&1 &''' % (
                v_mysql_version,config.mysql_conf_path,v_mysql_version,v_db_port)

    print v_exe_cmd

    # 远程paramiko调用 在本机执行sql  

    result = remote_shell_cmd_no_result(v_host,v_os_user,v_os_password,v_exe_cmd)

    time.sleep(20)   # 等待20秒 mysql 完全启动，更好的方法是，做个循环判断mysql 完全起来了，再退出



    
    return result  #返回空串表示成功