Python Connection.query方法代码示例

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
    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)

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
    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
        )

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
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

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
    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

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
    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)

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
def do_query(database=None):
    """Perform generic query on database."""
    # 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.query(sql)
        app.logger.info(results)
    except Exception, e:
        return {"ERROR": ": ".join(str(i) for i in e.args)}, 422

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
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

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
def read_db(info):
    """Get info from Racktables DB"""

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

    # check if object_id already exists
    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 "Object:{0} does not exist".format(info["hostname"])
        return False

    # get the location info
    rack_id_list = []
    unit_no_list = []
    for item in db.query(
        "select rack_id,unit_no from RackSpace where object_id=(select id from Object where name='{0}')".format(
            info["hostname"]
        )
    ):
        rack_id_list.append(int(item.rack_id))
        unit_no_list.append(int(item.unit_no))
    if not item:
        print "Object:{0} does not have location info".format(info["hostname"])
        return False
    rack_id = ",".join(str(i) for i in list(set(rack_id_list)))
    unit_no = ",".join(str(i) for i in list(set(unit_no_list)))

    location_name = ""
    row_name = ""
    rack_name = ""
    for item in db.query("select location_name,row_name,name from Rack where id='{0}'".format(rack_id)):
        location_name = item.location_name
        row_name = item.row_name
        rack_name = item.name
    if not location_name or not row_name or not rack_name:
        print "Object:{0} does not have location info".format(info["hostname"])
        return False
    print "RACKSPACE:   {0}:{1}:{2}:{3}".format(location_name, row_name, rack_name, unit_no)

    # close db
    db.close()

    return {"location_name": location_name, "row_name": row_name, "rack_name": rack_name, "unit_no": unit_no}

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
def list_object(info):
    """List the objects of the given rackspace"""

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

    # check if rackspace is correct
    rs_info = opts["hostname"].split(":")
    colo = "".join(rs_info[0:1])
    row = "".join(rs_info[1:2])
    rack = "".join(rs_info[2:3])
    if not rack:
        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

    # get object_id
    object_id_list = []
    for item in db.query("select * from RackSpace where rack_id={0}".format(rack_id)):
        object_id_list.append(item.object_id)
    if len(object_id_list) == 0:
        print "Failed to get object_id"
        return False

    # get rid of the duplicated items then sort and read one by one
    for object_id in sorted(list(set(object_id_list))):
        for item in db.query("select * from Object where id={0}".format(object_id)):
            object_name = item.name
            object_type_id = item.objtype_id
            for item in db.query("select * from Dictionary where dict_key={0}".format(object_type_id)):
                object_type_name = item.dict_value
        print "{0}: {1}".format(object_type_name, object_name)

    # close db
    db.close()

    return True

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
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

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
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

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
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

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
 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"]
         )
     )

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
    def update(self):
        config = self.application.config
        db = Connection(
            "%s:%s" % (config.MYSQL_HOST, config.MYSQL_PORT),
            config.MYSQL_DB,
            user=config.MYSQL_USER,
            password=config.MYSQL_PASS
        )

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

        projects = {}

        try:
            db_projects = db.query(query)

            if db_projects is None:
                logging.warn("Could not retrieve nformation from sentry's database because MySQL Server was unavailable")
                return

            for project in db_projects:
                logging.info("Updating information for project with id %s..." % project.project_id)

                if not project.project_id in projects.keys():
                    projects[project.project_id] = {
                        "public_key": [],
                        "secret_key": []
                    }

                projects[project.project_id]['public_key'].append(project.public_key)
                projects[project.project_id]['secret_key'].append(project.secret_key)

            self.application.project_keys = projects
        finally:
            db.close()

# 需要导入模块: from torndb import Connection [as 别名]
# 或者: from torndb.Connection import query [as 别名]
 def search_for_articles(self, from_user, to_user, timestamp, query):
     db = Connection(settings.DATABASE_SERVER,
                     settings.DATABASE_NAME,
                     settings.DATABASE_USER,
                     settings.DATABASE_PASSWORD,
                     )
     # Simple way to avoid SQL insertion attack
     if query.strip() and ";" not in query:
         condition = u"""WHERE UPPER(title) LIKE '%%{0}%%'
                        OR UPPER(profile) LIKE '%%{0}%%'
                        OR UPPER(author) LIKE '%%{0}%%'
                        OR UPPER(content) LIKE '%%{0}%%'
                     """.format(query.strip().upper())
     else:
         condition = ""
     sql = u"SELECT * FROM articles {0} ORDER BY time DESC LIMIT 10".format(
         condition)
     articles = db.query(sql)
     if len(articles) > 0:
         return self.make_multi_pic_response(
             from_user,
             to_user,
             timestamp,
             [article["title"] for article in articles],
             [article["profile"] for article in articles],
             [article["picUrl"] for article in articles],
             ["{0}/m#/article_details/{1}".format(
                 settings.SITE_HTTP_URL,
                 article["id"]
             ) for article in articles])
     else:
         return self.make_text_response(
             from_user,
             to_user,
             timestamp,
             u"还没有关于“%s”的内容哦！你有什么想法呢？告诉微君吧！" % query)