Python database.execute函数代码示例

def export_uuids(path, numberofdays):
    """Export crash report uuids from a PostgreSQL database to a CSV file

    path - Directory where the csv file will be created.
    numberofdays - Number of days of crash reports to retrieve, before the most
                   recent crash date.

    """
    database = db.Database(config)
    connection = database.connection()
    cur = connection.cursor()

    # steps
    # 1. pull all distinct dates
    sql = """
        SELECT DISTINCT to_char(date_processed, 'YYYY-MM-DD') as day
        FROM reports
        ORDER BY day DESC
    """
    if numberofdays:
        sql = "%s LIMIT %s" % (sql, numberofdays)

    print 'Calculating dates... '
    days = db.execute(cur, sql)

    days_list = []
    for day in days:
        days_list.append(day[0])

    store_filename = 'uuids.csv'
    store_filename = os.path.normpath('%s/%s' % (path, store_filename))
    store_file = open(store_filename, 'w')
    store = csv.writer(store_file, delimiter=',', quotechar='"')
    print 'Store file created: %s' % store_filename

    for day in days_list:
        date_from = dtu.datetimeFromISOdateString(day)
        date_to = date_from + datetime.timedelta(1)

        sql = "SELECT uuid FROM reports WHERE date_processed BETWEEN %s AND %s"

        print 'Getting crash reports for day %s' % date_from.date()
        crashes_list = db.execute(cur, sql, (date_from, date_to))
        for crash in crashes_list:
            store.writerow(crash)

    store_file.close()
    connection.close()
    return store_filename

    def _get_products(self):
        """ Return a list of product names """

        sql_query = "SELECT * FROM products"

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

        default_versions = self.get_default_version()["hits"]

        try:
            connection = self.database.connection()
            cur = connection.cursor()
            results = db.execute(cur, sql_query)
        except psycopg2.Error:
            logger.error("Failed to retrieve products list from PostgreSQL", exc_info=True)
        else:
            for product in results:
                row = dict(zip(("product_name", "sort", "rapid_release_version", "release_name"), product))
                row["default_version"] = default_versions[row["product_name"]]
                json_result["hits"].append(row)
                json_result["total"] = len(json_result["hits"])

            return json_result
        finally:
            connection.close()

 def fetchAduHistory(self, parameters):
     if parameters.listOfOs_names and parameters.listOfOs_names != [""]:
         osNameListPhrase = (",".join("'%s'" % x for x in parameters.listOfOs_names)).replace("Mac", "Mac OS X")
         parameters.os_phrase = "and os_name in (%s)" % osNameListPhrase
     else:
         parameters.os_phrase = ""
     sql = (
         """
   select
       adu_date as date,
       substring(os_name, 1, 3) as product_os_platform,
       sum(adu_count)::BIGINT
   from
       product_adu pa
   join product_info pi using (product_version_id)
   where
       %%(start_date)s <= adu_date
       and adu_date <= %%(end_date)s
       and pi.product_name = %%(product)s
       and pi.version_string = %%(version)s
       %(os_phrase)s
   group by
       date,
       product_os_platform
   order by
       1"""
         % parameters
     )
     # logger.debug('%s', self.connection.cursor().mogrify(sql.encode(self.connection.encoding), parameters))
     return dict(
         (((date, os_name), count) for date, os_name, count in db.execute(self.connection.cursor(), sql, parameters))
     )

  def fetchCrashHistory (self, parameters):
    if parameters.listOfOs_names and parameters.listOfOs_names != ['']:
      localOsList = [x[0:3] for x in parameters.listOfOs_names]
      osNameListPhrase = (','.join("'%s'" % x for x in localOsList))
      parameters.os_phrase = "os_short_name in (%s)" % osNameListPhrase
    else:
      parameters.os_phrase = '1=1'

    if parameters.report_type == 'crash':
      parameters.report_type_phrase = "report_type = '%s'" % adu_codes.CRASH_BROWSER
    elif parameters.report_type == 'hang':
      parameters.report_type_phrase = "report_type IN ('%s', '%s')" % (adu_codes.HANG_BROWSER, adu_codes.HANG_PLUGIN)
    else:
      # anything other than 'crash' or 'hang' will return all crashes
      # hang normalized are avoided so as not to count some hang ids multiple times
      parameters.report_type_phrase = "report_type IN ('%s', '%s', '%s', '%s')" % (
        adu_codes.CRASH_BROWSER,
        adu_codes.HANG_PLUGIN,
        adu_codes.CONTENT,
        adu_codes.OOP_PLUGIN,
      )

    sql = """
      SELECT adu_day::DATE, os_short_name, SUM(count)
      FROM daily_crashes
      WHERE timestamp with time zone %%(start_date)s <= adu_day AND
            adu_day <= timestamp with time zone %%(end_date)s AND
            productdims_id = %%(productdims_id)s AND
             %(os_phrase)s AND
             %(report_type_phrase)s
      GROUP BY adu_day, os_short_name
      order by
          1, 2""" % parameters
    #logger.debug('%s', self.connection.cursor().mogrify(sql.encode(self.connection.encoding), parameters))
    return dict((((bucket, os_name), count) for bucket, os_name, count in db.execute(self.connection.cursor(), sql, parameters)))

    def get(self, **kwargs):
        """Return a single crash report from it's UUID. """
        filters = [
            ("uuid", None, "str"),
        ]
        params = external_common.parse_arguments(filters, kwargs)

        day = int(params.uuid[-2:])
        month = int(params.uuid[-4:-2])
        # assuming we won't use this after year 2099
        year = int("20%s" % params.uuid[-6:-4])

        crash_date = datetime.date(year=year, month=month, day=day)
        logger.debug("Looking for crash %s during day %s" % (params.uuid,
                                                             crash_date))

        sql = """/* socorro.external.postgresql.crash.Crash.get */
            SELECT reports.email, reports.url, reports.addons_checked,
            (   SELECT reports_duplicates.duplicate_of
                FROM reports_duplicates
                WHERE reports_duplicates.uuid = reports.uuid
            ) as duplicate_of
            FROM reports
            WHERE reports.uuid=%(uuid)s
            AND reports.success IS NOT NULL
            AND utc_day_is( reports.date_processed,  %(crash_date)s)
        """
        sql_params = {
            "uuid": params.uuid,
            "crash_date": crash_date
        }

        results = []

        # Creating the connection to the DB
        self.connection = self.database.connection()
        cur = self.connection.cursor()

        try:
            results = db.execute(cur, sql, sql_params)
        except psycopg2.Error:
            util.reportExceptionAndContinue(logger)

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

        for crash in results:
            row = dict(zip((
                       "email",
                       "url",
                       "addons_checked",
                       "duplicate_of"), crash))
            json_result["hits"].append(row)
        json_result["total"] = len(json_result["hits"])

        self.connection.close()

        return json_result

def dailyUrlDump(config, sdb=sdb,
                 gzipped_csv_files=gzipped_csv_files,
                 IdCache=IdCache,
                 write_row=write_row,
                 process_crash=process_crash,
                 logger=logger):
    dbConnectionPool = sdb.DatabaseConnectionPool(config, logger)
    try:
        try:
            db_conn, db_cursor = dbConnectionPool.connectionCursorPair()

            with gzipped_csv_files(config) as csv_file_handles_tuple:
                headers_not_yet_written = True
                id_cache = IdCache(db_cursor)
                sql_parameters = setup_query_parameters(config)
                logger.debug("config.day = %s; now = %s; yesterday = %s",
                             config.day,
                             sql_parameters.now_str,
                             sql_parameters.yesterday_str)
                sql_query = sql % sql_parameters
                logger.debug("SQL is: %s", sql_query)
                for crash_row in sdb.execute(db_cursor, sql_query):
                    if headers_not_yet_written:
                        write_row(csv_file_handles_tuple,
                                  [x[0] for x in db_cursor.description])
                        headers_not_yet_written = False
                    column_value_list = process_crash(crash_row, id_cache)
                    write_row(csv_file_handles_tuple,
                              column_value_list)
                    # end for loop over each crash_row
        finally:
            dbConnectionPool.cleanup()
    except:
        util.reportExceptionAndContinue(logger)

    def get(self, **kwargs):
        """Return a single crash report from it's UUID. """
        filters = [
            ("uuid", None, "str"),
        ]
        params = external_common.parse_arguments(filters, kwargs)

        if params.uuid is None:
            raise MissingOrBadArgumentException(
                        "Mandatory parameter 'uuid' is missing or empty")

        crash_date = datetimeutil.uuid_to_date(params.uuid)
        logger.debug("Looking for crash %s during day %s" % (params.uuid,
                                                             crash_date))

        sql = """/* socorro.external.postgresql.crash.Crash.get */
            SELECT reports.email, reports.url, reports.addons_checked,
            (   SELECT reports_duplicates.duplicate_of
                FROM reports_duplicates
                WHERE reports_duplicates.uuid = reports.uuid
            ) as duplicate_of
            FROM reports
            WHERE reports.uuid=%(uuid)s
            AND reports.success IS NOT NULL
            AND utc_day_is( reports.date_processed,  %(crash_date)s)
        """
        sql_params = {
            "uuid": params.uuid,
            "crash_date": crash_date
        }

        results = []

        # Creating the connection to the DB
        self.connection = self.database.connection()
        cur = self.connection.cursor()

        try:
            results = db.execute(cur, sql, sql_params)
        except psycopg2.Error:
            util.reportExceptionAndContinue(logger)

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

        for crash in results:
            row = dict(zip((
                       "email",
                       "url",
                       "addons_checked",
                       "duplicate_of"), crash))
            json_result["hits"].append(row)
        json_result["total"] = len(json_result["hits"])

        self.connection.close()

        return json_result

def getListOfTopCrashersBySignature(aCursor, databaseParameters, totalNumberOfCrashesForPeriodFunc=totalNumberOfCrashesForPeriod):
  """
  """
  databaseParameters["totalNumberOfCrashes"] = totalNumberOfCrashesForPeriodFunc(aCursor, databaseParameters)

  if databaseParameters["totalNumberOfCrashes"] == None:
    return []

  assertPairs = {
   'totalNumberOfCrashes': long,
   'startDate': datetime.datetime,
   'to_date': datetime.datetime,
   'productdims_id': int,
   'limit': int
  }
  for param in assertPairs.keys():
    assert type(databaseParameters[param]) is assertPairs[param], \
    "Expected %s for %s, actual type is %s" % (assertPairs[param], param, type(databaseParameters[param]))

  where = []
  if databaseParameters["crash_type"] == 'browser':
    where.append("tcbs.plugin_count = 0 AND tcbs.hang_count = 0")
  if databaseParameters["crash_type"] == 'plugin':
    where.append("tcbs.plugin_count > 0 OR tcbs.hang_count > 0")
  if databaseParameters['os']:
    where.append("os.os_name ILIKE '%s%%'" % databaseParameters['os'])

  if where:
    where = "where %s" % " AND ".join(where)
  else:
    where = ""

  sql = """
  select
      tcbs.signature,
      sum(tcbs.count) as count,
      cast(sum(tcbs.count) as float) / %d as percentOfTotal,
      sum(case when os.os_name LIKE 'Windows%%' then tcbs.count else 0 end) as win_count,
      sum(case when os.os_name = 'Mac OS X' then tcbs.count else 0 end) as mac_count,
      sum(case when os.os_name = 'Linux' then tcbs.count else 0 end) as linux_count,
      sum(tcbs.hang_count) as hang_count,
      sum(tcbs.plugin_count) as plugin_count
  from
      top_crashes_by_signature tcbs
          join osdims os on tcbs.osdims_id = os.id
                            and '%s' < tcbs.window_end
                            and tcbs.window_end <= '%s'
                            and tcbs.productdims_id = %d
  %s
  group by
      tcbs.signature
  order by
    2 desc
  limit %d""" % (databaseParameters["totalNumberOfCrashes"], databaseParameters["startDate"], \
                    databaseParameters["to_date"], databaseParameters["productdims_id"], where, \
                    databaseParameters["limit"])

  #logger.debug(aCursor.mogrify(sql, databaseParameters))
  return db.execute(aCursor, sql)

 def testExecute(self):
   aCursor = TestMultiCursor(numCols=1,numRows=3)
   f = db.execute(aCursor,"")
   vals = [x for x in f]
   assert 3 == len(vals)
   assert 'Row 0, Column 0' == vals[0][0]
   assert 'Row 2, Column 0' == vals[-1][0]
   aCursor = TestMultiCursor(numCols=1,numRows=1)

    def get(self, **kwargs):
        """Return a job in the job queue. """
        filters = [
            ("uuid", None, "str"),
        ]
        params = external_common.parse_arguments(filters, kwargs)

        if not params.uuid:
            raise MissingOrBadArgumentError(
                        "Mandatory parameter 'uuid' is missing or empty")

        fields = [
            "id",
            "pathname",
            "uuid",
            "owner",
            "priority",
            "queueddatetime",
            "starteddatetime",
            "completeddatetime",
            "success",
            "message"
        ]
        sql = """
            /* socorro.external.postgresql.job.Job.get */
            SELECT %s FROM jobs WHERE uuid=%%(uuid)s
        """ % ", ".join(fields)

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

        connection = None
        try:
            # Creating the connection to the DB
            connection = self.database.connection()
            cur = connection.cursor()
            results = db.execute(cur, sql, params)
        except psycopg2.Error:
            logger.error("Failed retrieving jobs data from PostgreSQL",
                         exc_info=True)
        else:
            for job in results:
                row = dict(zip(fields, job))

                # Make sure all dates are turned into strings
                for i in row:
                    if isinstance(row[i], datetime.datetime):
                        row[i] = datetimeutil.date_to_string(row[i])

                json_result["hits"].append(row)
            json_result["total"] = len(json_result["hits"])
        finally:
            if connection:
                connection.close()

        return json_result

    def get(self, **kwargs):
        filters = [
            ("start_date", None, "datetime"),
            ("end_date", None, "datetime"),
            ("product", None, "str"),
            ("version", None, "str"),
            ]

        params = external_common.parse_arguments(filters, kwargs)
        results = []  # So we have something to return.

        query_string = """SELECT product_name,
                    version_string,
                    product_version_id,
                    report_date,
                    nightly_builds.build_date,
                    days_out,
                    sum(report_count) as report_count
                FROM nightly_builds
                JOIN product_versions USING ( product_version_id )
                WHERE report_date <= %(end_date)s
                AND report_date >= %(start_date)s
                AND product_name = %(product)s
                AND version_string = %(version)s
                GROUP BY product_name,
                         version_string,
                         product_version_id,
                         report_date,
                         nightly_builds.build_date,
                         days_out"""

        try:
            connection = self.database.connection()
            cursor = connection.cursor()
            sql_results = db.execute(cursor, query_string, params)
        except psycopg2.Error:
            logger.error("Failed retrieving crashtrends data from PostgreSQL",
                         exc_info=True)
        else:
            for trend in sql_results:
                row = dict(zip((
                              "product_name",
                              "version_string",
                              "product_version_id",
                              "report_date",
                              "build_date",
                              "days_out",
                              "report_count"), trend))
                row['report_date'] = datetimeutil.date_to_string(row['report_date'])
                row['build_date'] = datetimeutil.date_to_string(row['build_date'])
                results.append(row)
        finally:
            connection.close()
        results = {'crashtrends' : results}
        return results

    def get(self, **kwargs):
        """Return a list of signature - bug id associations. """
        filters = [
            ("signatures", None, ["list", "str"]),
        ]
        params = external_common.parse_arguments(filters, kwargs)

        if not params.signatures:
            raise MissingOrBadArgumentError(
                        "Mandatory parameter 'signatures' is missing or empty")

        # Preparing variables for the SQL query
        signatures = []
        sql_params = {}
        for i, elem in enumerate(params.signatures):
            signatures.append("%%(signature%s)s" % i)
            sql_params["signature%s" % i] = elem

        sql = """/* socorro.external.postgresql.bugs.Bugs.get */
            SELECT ba.signature, bugs.id
            FROM bugs
                JOIN bug_associations AS ba ON bugs.id = ba.bug_id
            WHERE EXISTS(
                SELECT 1 FROM bug_associations
                WHERE bug_associations.bug_id = bugs.id
                AND signature IN (%s)
            )
        """ % ", ".join(signatures)
        sql = str(" ".join(sql.split()))  # better formatting of the sql string

        connection = None

        try:
            connection = self.database.connection()
            cur = connection.cursor()
            #~ logger.debug(cur.mogrify(sql, sql_params))
            results = db.execute(cur, sql, sql_params)
        except psycopg2.Error:
            logger.error("Failed retrieving extensions data from PostgreSQL",
                         exc_info=True)
        else:
            result = {
                "total": 0,
                "hits": []
            }

            for crash in results:
                row = dict(zip(("signature", "id"), crash))
                result["hits"].append(row)
            result["total"] = len(result["hits"])

            return result
        finally:
            if connection:
                connection.close()

 def _get_builds_for_version(self, version):
     cursor = self.connection.cursor(
         cursor_factory=psycopg2.extras.RealDictCursor)
     result = db.execute(cursor, """
         SELECT product_name as product,
                version,
                build_id,
                build_type,
                platform,
                repository
         FROM releases_raw
         WHERE version = %(version)s
     """, {"version": version})
     return list(result)

  def fetchCrashHistoryDetails (self, parameters):
    if parameters.listOfOs_names and parameters.listOfOs_names != ['']:
      localOsList = [x[0:3] for x in parameters.listOfOs_names]
      osNameListPhrase = (','.join("'%s'" % x for x in localOsList)) 
      parameters.os_phrase = "os_short_name in (%s)" % osNameListPhrase
    else:
      parameters.os_phrase = '1=1'

    if parameters.listOfReport_types and parameters.listOfReport_types != ['']:
      lookup = {'crash':        adu_codes.CRASH_BROWSER,
                'oopp':         adu_codes.OOP_PLUGIN,
                'hang_unique':  adu_codes.HANGS_NORMALIZED,
                'hang_browser': adu_codes.HANG_BROWSER,
                'hang_plugin':  adu_codes.HANG_PLUGIN,
                }
      reportTypeListPhrase = (','.join("'%s'" % lookup[x] for x in parameters.listOfReport_types)) 
      parameters.report_types_phrase = "report_type in (%s)" % reportTypeListPhrase
    else:
      parameters.report_types_phrase = '1=1'

    columnSql = "SUM(CASE WHEN report_type = '%s' THEN count ELSE 0 END) as %s"
    parameters.selectListPhrase = (', '.join((columnSql % (lookup[x], x)) for x in parameters.listOfReport_types))
    logger.debug("created phrase %s" % parameters.selectListPhrase)
    sql = """
      SELECT adu_day, os_short_name, %(selectListPhrase)s
      FROM daily_crashes
      WHERE timestamp without time zone %%(start_date)s < adu_day AND
            adu_day <= timestamp without time zone %%(end_date)s AND
            productdims_id = %%(productdims_id)s AND
             %(os_phrase)s AND
             %(report_types_phrase)s
      GROUP BY adu_day, os_short_name
      order by
          1, 2""" % parameters
    logger.debug('%s', self.connection.cursor().mogrify(sql.encode(self.connection.encoding), parameters))
    db_results = db.execute(self.connection.cursor(), sql, parameters)
    # idea... we could do {'crash': crash, 'hang_plugin': hang_plugin}... etc building this dict up above
    #return dict((((bucket, os_name), crash) for bucket, os_name, crash, hang_plugin in db_results))
    column_names = ('date', 'os') + tuple(parameters.listOfReport_types)
    # grab bucket value and os_name value, rest go into (('hang_plugin': 3), ...)

    structure = [dict(zip(column_names, x)) for x in db_results]
    structures = dict([((x['date'], x['os']), x) for x in structure])
    # [[('adu_by_day', date), ('os_short_name', 'Win'),...],
    #  [...]]
    # ultimately we want to produce [(date, os): {'date':date, 'os': "Lin", 'users': 234, 'crashes': 234]
    # so we can do an quick lookup by (date, os)
    logger.info("Wow, structure=")
    logger.info(structures)
    return structures

    def get(self, **kwargs):
        """Return a list of extensions associated with a crash's UUID."""
        filters = [
            ("uuid", None, "str"),
            ("date", None, "datetime"),
        ]
        params = external_common.parse_arguments(filters, kwargs)

        sql = """/* socorro.external.postgresql.extensions.Extensions.get */
            SELECT extensions.*
            FROM extensions
            INNER JOIN reports ON extensions.report_id = reports.id
            WHERE reports.uuid = %(uuid)s
            AND reports.date_processed = %(crash_date)s
            AND extensions.date_processed = %(crash_date)s
        """
        sql_params = {
            "uuid": params.uuid,
            "crash_date": params.date
        }

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

        try:
            connection = self.database.connection()
            cur = connection.cursor()
            results = db.execute(cur, sql, sql_params)
        except psycopg2.Error:
            logger.error("Failed retrieving extensions data from PostgreSQL",
                         exc_info=True)
        else:
            for crash in results:
                row = dict(zip((
                           "report_id",
                           "date_processed",
                           "extension_key",
                           "extension_id",
                           "extension_version"), crash))
                result["hits"].append(row)
                row["date_processed"] = datetimeutil.date_to_string(row["date_processed"])
            result["total"] = len(result["hits"])
        finally:
            connection.close()

        return result