Python database.singleValueSql函数代码示例

 def testSingleValueEmpty(self):
   try:
     cur = TestEmptyCursor()
     db.singleValueSql(cur,"")
     assert False, "must raise SQLDidNotReturnSingleValue"
   except db.SQLDidNotReturnSingleValue,e:
     pass

def latestEntryBeforeOrEqualTo(connection, aDate, product, version):
    """
    Retrieve the closest report date containing the provided product and
    version that does not exceed the provided date.

    We append a day to the max(report_date) to ensure that we
    capture reports to the end of the day, not the beginning.
    """
    sql = """
                SELECT
                    max(report_date) + 1
                FROM
                    tcbs JOIN product_versions USING (product_version_id)
                WHERE
                    tcbs.report_date <= %s
                    AND product_name = %s
                    AND version_string = %s
                """
    cursor = connection.cursor()
    try:
        result = db.singleValueSql(cursor, sql, (aDate, product, version))
        connection.commit()
    except:
        result = None
        connection.rollback()
    return result or aDate

def latestEntryBeforeOrEqualTo(aCursor, aDate, productdims_id):
  sql = """
    select
        max(window_end)
    from
        top_crashes_by_signature tcbs
    where
        tcbs.window_end <= %s
        and tcbs.productdims_id = %s
    """
  try:
    result = db.singleValueSql(aCursor, sql, (aDate, productdims_id))
    if result: return result
    return aDate
  except:
    return aDate

def whichTCBS(aCursor, dbParams, product, version):
  '''
  Answers a boolean indicating if the old top crashes by signature should
  be used.
  '''
  sql = """
        /* socorro.services.topCrashBySignatures useTCBSClassic */
        SELECT which_table
        FROM product_selector
        WHERE product_name = '%s' AND
              version_string = '%s'""" % (product, version)
  try:
    return db.singleValueSql(aCursor, sql, dbParams)
  except db.SQLDidNotReturnSingleValue:
    logger.info("No record in product_selector for %s %s."
      % (product, version))
    raise ValueError, "No record of %s %s" % (product, version)

def latestEntryBeforeOrEqualTo(aCursor, aDate, product, version):
  """
  Retrieve the closest report date containing the provided product and
  version that does not exceed the provided date. 
  """
  sql = """
        SELECT
          max(report_date)
        FROM
          tcbs JOIN product_versions USING (product_version_id)
        WHERE
          tcbs.report_date <= %s
          AND product_name = %s
          AND version_string = %s
        """ 
  try:
    result = db.singleValueSql(aCursor, sql, (aDate, product, version))
  except:
    result = None
  return result or aDate 

 def getId(self, product, version):
   try:
     return self.cache[(product, version)]
   except KeyError:
     connection = self.database.connection()
     cursor = connection.cursor()
     sql = """
         select
             product_version_id as id
         from
             product_info
         where
           product_name = %s
           and version_string = %s
         """
     try:
       self.cache[(product, version)] = id = db.singleValueSql(cursor, sql, (product, version))
       return id
     except Exception:
       util.reportExceptionAndContinue(self.config['logger'])
       raise KeyError((product, version))

def totalNumberOfCrashesForPeriod (aCursor, databaseParameters):
  """
  """
  where = ""
  if databaseParameters["crash_type"] == 'browser':
    where = "AND tcbs.plugin_count = 0 AND tcbs.hang_count = 0"
  if databaseParameters["crash_type"] == 'plugin':
    where = "AND tcbs.plugin_count > 0 OR tcbs.hang_count > 0"

  sql = """
    select
        sum(tcbs.count)
    from
        top_crashes_by_signature tcbs
    where
        '%s' < tcbs.window_end
        and tcbs.window_end <= '%s'
        and tcbs.productdims_id = %d
        %s
    """ % (databaseParameters["startDate"], databaseParameters["to_date"], \
            databaseParameters["productdims_id"], where)
  #logger.debug(aCursor.mogrify(sql, databaseParameters))
  return db.singleValueSql(aCursor, sql, databaseParameters)

 def testSingleValueMulti(self):
   try:
     cur = TestMultiCursor(numRows=5)
     assert "Row 0, Column 0" == db.singleValueSql(cur,"")
   except Exception, e:
     assert False, "must not raise an exception for this "+e

 def testSingleValueSingle(self):
   try:
     cur = TestSingleCursor()
     assert "Row 0, Column 0" == db.singleValueSql(cur,"")
   except Exception, e:
     assert False, "must not raise an exception for this %s" %e

#.........这里部分代码省略.........
                r.signature,
                r.url,
                r.os_name,
                r.os_version,
                r.cpu_name,
                r.cpu_info,
                r.address,
                r.reason,
                r.last_crash,
                r.install_age,
                r.hangid,
                r.process_type,
                (r.client_crash_date - (r.install_age * INTERVAL '1 second'))
                    AS install_time,
                rd.duplicate_of
        """

        sql_from = self.build_reports_sql_from(params)
        sql_from = """%s
            LEFT OUTER JOIN reports_duplicates rd ON r.uuid = rd.uuid
        """ % sql_from

        (sql_where, sql_params) = self.build_reports_sql_where(params,
                                                               sql_params,
                                                               self.context)

        sql_order = """
            ORDER BY r.date_processed DESC
        """

        (sql_limit, sql_params) = self.build_reports_sql_limit(params,
                                                               sql_params)

        # Assembling the query
        sql_query = " ".join((
                "/* socorro.external.postgresql.report.Report.list */",
                sql_select, sql_from, sql_where, sql_order, sql_limit))

        # Query for counting the results
        sql_count_query = " ".join((
                "/* socorro.external.postgresql.report.Report.list */",
                "SELECT count(*)", sql_from, sql_where))

        # Debug
        logger.debug(sql_count_query)
        logger.debug(cur.mogrify(sql_count_query, sql_params))

        # Querying the DB
        try:
            total = db.singleValueSql(cur, sql_count_query, sql_params)
        except db.SQLDidNotReturnSingleValue:
            total = 0
            util.reportExceptionAndContinue(logger)

        results = []

        # No need to call Postgres if we know there will be no results
        if total != 0:
            try:
                results = db.execute(cur, sql_query, sql_params)
            except psycopg2.Error:
                util.reportExceptionAndContinue(logger)

        json_result = {
            "total": total,
            "hits": []
        }

        # Transforming the results into what we want
        for crash in results:
            row = dict(zip((
                       "date_processed",
                       "uptime",
                       "user_comments",
                       "uuid",
                       "product",
                       "version",
                       "build",
                       "signature",
                       "url",
                       "os_name",
                       "os_version",
                       "cpu_name",
                       "cpu_info",
                       "address",
                       "reason",
                       "last_crash",
                       "install_age",
                       "hangid",
                       "process_type",
                       "install_time",
                       "duplicate_of"), crash))
            for i in row:
                if isinstance(row[i], datetime.datetime):
                    row[i] = str(row[i])
            json_result["hits"].append(row)

        self.connection.close()

        return json_result

    def get_comments(self, **kwargs):
        """Return a list of comments on crash reports, filtered by
        signatures and other fields.

        See socorro.lib.search_common.get_parameters() for all filters.
        """
        # Creating the connection to the DB
        self.connection = self.database.connection()
        cur = self.connection.cursor()

        params = self.prepare_search_params(**kwargs)

        # Creating the parameters for the sql query
        sql_params = {}

        # Preparing the different parts of the sql query

        # WARNING: sensitive data is returned here (email). When there is
        # an authentication mecanism, a verification should be done here.
        sql_select = """
            SELECT
                r.date_processed,
                r.user_comments,
                r.uuid,
                CASE
                    WHEN r.email = '' THEN null
                    WHEN r.email IS NULL THEN null
                    ELSE r.email
                END
        """

        sql_from = self.build_reports_sql_from(params)

        (sql_where, sql_params) = self.build_reports_sql_where(params,
                                                               sql_params,
                                                               self.context)
        sql_where = "%s AND r.user_comments IS NOT NULL" % sql_where

        sql_order = "ORDER BY email ASC, r.date_processed ASC"

        # Assembling the query
        sql_query = " ".join((
                "/* external.postgresql.crashes.Crashes.get_comments */",
                sql_select, sql_from, sql_where, sql_order))

        # Query for counting the results
        sql_count_query = " ".join((
                "/* external.postgresql.crashes.Crashes.get_comments */",
                "SELECT count(*)", sql_from, sql_where))

        # Querying the DB
        try:
            total = db.singleValueSql(cur, sql_count_query, sql_params)
        except db.SQLDidNotReturnSingleValue:
            total = 0
            util.reportExceptionAndContinue(logger)

        results = []

        # No need to call Postgres if we know there will be no results
        if total != 0:
            try:
                results = db.execute(cur, sql_query, sql_params)
            except psycopg2.Error:
                util.reportExceptionAndContinue(logger)

        result = {
            "total": total,
            "hits": []
        }

        # Transforming the results into what we want
        for crash in results:
            row = dict(zip((
                       "date_processed",
                       "user_comments",
                       "uuid",
                       "email"), crash))
            for i in row:
                if isinstance(row[i], datetime.datetime):
                    row[i] = str(row[i])
            result["hits"].append(row)

        self.connection.close()

        return result

#.........这里部分代码省略.........
            params["plugin_terms"] = Search.prepare_terms(
                                                params["plugin_terms"],
                                                params["plugin_search_mode"])

        # Get information about the versions
        util_service = Util(config=self.context)
        params["versions_info"] = util_service.versions_info(**params)

        # Parsing the versions
        params["versions_string"] = params["versions"]
        (params["versions"], params["products"]) = Search.parse_versions(
                                                            params["versions"],
                                                            params["products"])
        if hasattr(self.context, 'webapi'):
            context = self.context.webapi
        else:
            # old middleware
            context = self.context
        # Changing the OS ids to OS names
        for i, elem in enumerate(params["os"]):
            for platform in context.platforms:
                if platform["id"] == elem:
                    params["os"][i] = platform["name"]

        # Creating the parameters for the sql query
        sql_params = {
        }

        # Preparing the different parts of the sql query
        sql_select = self.generate_sql_select(params)

        # Adding count for each OS
        for i in context.platforms:
            sql_params["os_%s" % i["id"]] = i["name"]

        sql_from = self.build_reports_sql_from(params)

        (sql_where, sql_params) = self.build_reports_sql_where(params,
                                                               sql_params,
                                                               self.context)

        sql_group = self.generate_sql_group(params)

        sql_order = """
            ORDER BY total DESC, signature
        """

        (sql_limit, sql_params) = self.build_reports_sql_limit(params,
                                                               sql_params)

        # Assembling the query
        sql_query = " ".join(("/* socorro.search.Search search */",
                              sql_select, sql_from, sql_where, sql_group,
                              sql_order, sql_limit))

        # Query for counting the results
        sql_count_query = " ".join((
                "/* socorro.external.postgresql.search.Search search.count */",
                "SELECT count(DISTINCT r.signature)", sql_from, sql_where))

        # Debug
        logger.debug(cur.mogrify(sql_query, sql_params))

        # Querying the DB
        try:
            total = db.singleValueSql(cur, sql_count_query, sql_params)
        except db.SQLDidNotReturnSingleValue:
            total = 0
            util.reportExceptionAndContinue(logger)

        results = []

        # No need to call Postgres if we know there will be no results
        if total != 0:
            try:
                results = db.execute(cur, sql_query, sql_params)
            except psycopg2.Error:
                util.reportExceptionAndContinue(logger)

        json_result = {
            "total": total,
            "hits": []
        }

        # Transforming the results into what we want
        for crash in results:
            if params["report_process"] == "plugin":
                row = dict(zip(("signature", "count", "is_windows", "is_mac",
                                "is_linux", "numhang", "numplugin",
                                "numcontent", "pluginname", "pluginversion",
                                "pluginfilename"), crash))
            else:
                row = dict(zip(("signature", "count", "is_windows", "is_mac",
                                "is_linux", "numhang", "numplugin",
                                "numcontent"), crash))
            json_result["hits"].append(row)

        self.connection.close()

        return json_result

#.........这里部分代码省略.........
                comp = "="

                if params["plugin_search_mode"] in ("contains", "starts_with"):
                    comp = " LIKE "

                sql_where_plugin_in = []
                for f in params["plugin_in"]:
                    if f == "name":
                        field = "plugins.name"
                    elif f == "filename":
                        field = "plugins.filename"

                    sql_where_plugin_in.append(comp.join((field,
                                                          "%(plugin_term)s")))

                sql_where.append("(%s)" % " OR ".join(sql_where_plugin_in))

        elif params["report_process"] == "browser":
            sql_where.append("r.process_type IS NULL")

        elif params["report_process"] == "content":
            sql_where.append("r.process_type = 'content'")

        sql_where = " AND ".join(sql_where)

        #---------------------------------------------------------------
        # GROUP BY
        #---------------------------------------------------------------

        sql_group = self.generate_sql_group(params)

        #---------------------------------------------------------------
        # ORDER BY
        #---------------------------------------------------------------

        sql_order = """
            ORDER BY total DESC
        """

        #---------------------------------------------------------------
        # LIMIT OFFSET
        #---------------------------------------------------------------

        sql_limit = """
            LIMIT %(limit)s
            OFFSET %(offset)s
        """

        # Assembling the query
        sql_from = " JOIN ".join(sql_from)
        sql_query = " ".join(("/* socorro.search.Search search */",
                              sql_select, sql_from, sql_where, sql_group,
                              sql_order, sql_limit))

        # Query for counting the results
        sql_count_query = " ".join((
                "/* socorro.external.postgresql.search.Search search.count */",
                "SELECT count(DISTINCT r.signature)", sql_from, sql_where))

        # Debug
        logger.debug(cur.mogrify(sql_query, sql_params))

        # Querying the DB
        try:
            total = db.singleValueSql(cur, sql_count_query, sql_params)
        except Exception:
            total = 0
            util.reportExceptionAndContinue(logger)

        # No need to call Postgres if we know there will be no results
        if total != 0:
            try:
                results = db.execute(cur, sql_query, sql_params)
            except Exception:
                results = []
                util.reportExceptionAndContinue(logger)
        else:
            results = []

        json_result = {
            "total": total,
            "hits": []
        }

        # Transforming the results into what we want
        for crash in results:
            if params["report_process"] == "plugin":
                row = dict(zip(("signature", "count", "is_windows", "is_mac",
                                "is_linux", "numhang", "numplugin",
                                "numcontent", "pluginname", "pluginversion",
                                "pluginfilename"), crash))
            else:
                row = dict(zip(("signature", "count", "is_windows", "is_mac",
                                "is_linux", "numhang", "numplugin",
                                "numcontent"), crash))
            json_result["hits"].append(row)

        self.connection.close()

        return json_result

#.........这里部分代码省略.........
        ## Adding reason to where clause
        if reason:
            if type(reason) is list:
                sql_where.append( "".join( ( "(", PostgresAPI.array_to_string(xrange(len(reason)), " OR ", "r.reason=%(reason", ")s"), ")" ) ) )
            else:
                sql_where.append("r.reason=%(reason)s")

        if report_type == "crash":
            sql_where.append("r.hangid IS NULL")
        elif report_type == "hang":
            sql_where.append("r.hangid IS NOT NULL")

        ## Searching through plugins
        if report_process == "plugin":
            sql_where.append("r.process_type = 'plugin'")
            sql_where.append("plugins_reports.date_processed BETWEEN %(from_date)s AND %(to_date)s")

            if plugin_term:
                comp = "="

                if plugin_search_mode == "contains" or plugin_search_mode == "starts_with":
                    comp = " LIKE "

                field = "plugins.name"
                if plugin_in == "filename":
                    field = "plugins.filename"

                if type(plugin_term) is list:
                    sql_where.append( "".join( ( "(", PostgresAPI.array_to_string(xrange(len(plugin_term)), " OR ", field + comp +"%(plugin_term", ")s"), ")" ) ) )
                else:
                    sql_where.append( "".join( ( field, comp, "%(plugin_term)s" ) ) )

        elif report_process == "browser":
            sql_where.append("r.process_type IS NULL")

        sql_where = " AND ".join(sql_where)

        #---------------------------------------------------------------
        # GROUP BY
        #---------------------------------------------------------------

        sql_group = self.generate_sql_group(report_process)

        #---------------------------------------------------------------
        # ORDER BY
        #---------------------------------------------------------------

        sql_order = """
            ORDER BY total DESC
        """

        #---------------------------------------------------------------
        # LIMIT OFFSET
        #---------------------------------------------------------------

        sql_limit = """
            LIMIT %(limit)s
            OFFSET %(offset)s
        """

        # Assembling the query
        sql_from = " JOIN ".join(sql_from)
        sql_query = " ".join( ( "/* socorro.search.postgresAPI search */", sql_select, sql_from, sql_where, sql_group, sql_order, sql_limit ) )

        # Query for counting the results
        sql_count_query = " ".join( ( "/* socorro.search.postgresAPI search.count */ SELECT count(DISTINCT r.signature) ", sql_from, sql_where ) )

        # Querying the DB
        try:
            total = db.singleValueSql(cur, sql_count_query, params)
        except Exception:
            total = 0
            util.reportExceptionAndContinue(logger)

        # No need to call Postgres if we know there will be no results
        if total != 0:
            try:
                results = db.execute(cur, sql_query, params)
            except Exception:
                results = []
                util.reportExceptionAndContinue(logger)
        else:
            results = []

        json_result = {
            "total" : total,
            "hits" : []
        }

        # Transforming the results into what we want
        for crash in results:
            if report_process == "plugin":
                row = dict( zip( ("signature", "count", "is_windows", "is_mac", "is_linux", "is_solaris", "numhang", "numplugin", "pluginname", "pluginversion", "pluginfilename"), crash ) )
            else:
                row = dict( zip( ("signature", "count", "is_windows", "is_mac", "is_linux", "is_solaris", "numhang", "numplugin"), crash ) )
            json_result["hits"].append(row)

        self.connection.close()

        return json_result