Python connection.cursor方法代码示例

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def devices(request):
    data = prep_view(request)
    data['form'] = DeviceStatsForm()
    start_date = data['initial']['start_date']
    end_date = data['initial']['end_date']

    if request.method == 'POST':
        form = DeviceStatsForm(request.POST)
        if form.is_valid():
            start_date = form.cleaned_data['start_date']
            end_date = form.cleaned_data['end_date']

    cursor  = connection.cursor()
    query = '''SELECT d.description device, count(o) AS orders, count(r) AS repairs 
        FROM servo_device d, servo_order o, servo_repair r, servo_orderdevice od 
        WHERE d.id = od.device_id 
            AND o.id = od.order_id 
            AND r.order_id = o.id
            AND (o.created_at, o.created_at) OVERLAPS (%s, %s)
        GROUP BY d.description''';
    cursor.execute(query, [start_date, end_date])
    data['results'] = cursor.fetchall()
    data['title'] = _('Device statistics')

    return render(request, "stats/devices.html", data) 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def reset_db():
    """
    Reset database to a blank state by removing all the tables and recreating them.
    """
    with connection.cursor() as cursor:
        cursor.execute("select tablename from pg_tables where schemaname = 'public'")
        tables = [row[0] for row in cursor.fetchall()]

        # Can't use query parameters here as they'll add single quotes which are not
        # supported by postgres
        for table in tables:
            cursor.execute('drop table "' + table + '" cascade')

    # Call migrate so that post-migrate hooks such as generating a default Site object
    # are run
    management.call_command("migrate", "--noinput", stdout=StringIO()) 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def _run(anonymizer, objs):
    values = {}
    replacer_attr = tuple(r[0] for r in anonymizer.replacers)
    for obj in objs.iterator():
        retval = anonymizer.alter_object(obj)
        if retval is False:
            continue

        values[obj.pk] = {attname: getattr(obj, attname) for attname in replacer_attr}

    query = anonymizer.create_query(replacer_attr)
    query_args = anonymizer.create_query_args(values, replacer_attr)

    with transaction.atomic():
        with connection.cursor() as cursor:
            if connection.vendor == 'postgresql':
                cursor.execute('SET CONSTRAINTS ALL DEFERRED')
            cursor.executemany(query, query_args) 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def amount_on_field(self):
        """ Sums the total on verified field on the amount """

        query = """ SELECT SUM(field_entry.value::DOUBLE PRECISION)
                    FROM crowdataapp_documentsetfieldentry field_entry
                    INNER JOIN crowdataapp_documentsetformentry form_entry ON form_entry.id = field_entry.entry_id
                    INNER JOIN crowdataapp_document document ON document.id = form_entry.document_id
                    WHERE document.document_set_id = %d
                    AND field_entry.verified = TRUE
                    AND field_entry.field_id = %d""" % ( self.id, self.tosum_field.id)

        cursor = connection.cursor()
        cursor.execute(query)

        amount = cursor.fetchall()[0][0]

        return amount 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def get_verified_documents(self, document_set):
    """ Get all documents that have an entry with canon """

    q = """
    SELECT distinct(document.id) AS document_id, document.name as document_name
    FROM crowdataapp_documentsetfieldentry field_entry

    LEFT OUTER JOIN crowdataapp_canonicalfieldentrylabel canonical_label ON canonical_label.id = field_entry.canonical_label_id
    INNER JOIN crowdataapp_documentsetformentry form_entry ON form_entry.id = field_entry.entry_id
    INNER JOIN crowdataapp_document document ON document.id = form_entry.document_id

    WHERE document.document_set_id = %d
      AND field_entry.verified = TRUE
      AND canonical_label.id = %d
    """ % (document_set.id, self.id)

    cursor = connection.cursor()
    cursor.execute(q)
    return cursor.fetchall() 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def get_verified_documents_rankings(self, document_set, ranking_id):
    """ Get all documents that have an entry with canon """

    ranking_definition = DocumentSetRankingDefinition.objects.get(id=ranking_id)

    q = """
    SELECT distinct(document.id) AS document_id, document.name as document_name, 
           (SELECT value
                FROM crowdataapp_documentsetfieldentry
                WHERE entry_id = field_entry.entry_id 
                  AND field_id = {2})
    FROM crowdataapp_documentsetfieldentry field_entry

    LEFT OUTER JOIN crowdataapp_canonicalfieldentrylabel canonical_label ON canonical_label.id = field_entry.canonical_label_id
    INNER JOIN crowdataapp_documentsetformentry form_entry ON form_entry.id = field_entry.entry_id
    INNER JOIN crowdataapp_document document ON document.id = form_entry.document_id

    WHERE document.document_set_id = {0}
      AND field_entry.verified = TRUE
      AND canonical_label.id = {1}
    """.format(document_set.id, self.id, ranking_definition.magnitude_field_id)

    cursor = connection.cursor()
    cursor.execute(q)
    return cursor.fetchall() 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def clear_resources():
    """Removes all resource instances from your db and elasticsearch resource index"""
    se = SearchEngineFactory().create()
    match_all_query = Query(se)
    match_all_query.delete(index="terms")
    match_all_query.delete(index="resources")
    match_all_query.delete(index="resource_relations")

    print("deleting", Resource.objects.exclude(resourceinstanceid=settings.RESOURCE_INSTANCE_ID).count(), "resources")
    Resource.objects.exclude(resourceinstanceid=settings.RESOURCE_INSTANCE_ID).delete()
    print(Resource.objects.exclude(resourceinstanceid=settings.RESOURCE_INSTANCE_ID).count(), "resources remaining")

    print("deleting", models.ResourceXResource.objects.count(), "resource relationships")
    cursor = connection.cursor()
    cursor.execute("TRUNCATE public.resource_x_resource CASCADE;")
    print(models.ResourceXResource.objects.count(), "resource relationships remaining") 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def check_if_concept_in_use(self):
        """Checks  if a concept or any of its subconcepts is in use by a resource instance"""

        in_use = False
        cursor = connection.cursor()
        for value in self.values:
            sql = (
                """
                SELECT count(*) from tiles t, jsonb_each_text(t.tiledata) as json_data
                WHERE json_data.value = '%s'
            """
                % value.id
            )
            cursor.execute(sql)
            rows = cursor.fetchall()
            if rows[0][0] > 0:
                in_use = True
                break
        if in_use is not True:
            for subconcept in self.subconcepts:
                in_use = subconcept.check_if_concept_in_use()
                if in_use == True:
                    return in_use
        return in_use 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def setUpClass(cls):
        cls.factory = RequestFactory()
        cls.client = Client()
        cls.user = User.objects.create_user("test", "test@archesproject.org", "password")

        rdm_admin_group = Group.objects.get(name="RDM Administrator")
        cls.user.groups.add(rdm_admin_group)
        cls.anonymous_user = User.objects.get(username="anonymous")

        cls.token = "abc"
        cls.oauth_client_id = OAUTH_CLIENT_ID
        cls.oauth_client_secret = OAUTH_CLIENT_SECRET

        sql_str = CREATE_TOKEN_SQL.format(token=cls.token, user_id=cls.user.pk)
        cursor = connection.cursor()
        cursor.execute(sql_str) 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def tearDownClass(cls):
        sql = """
        DELETE FROM public.node_groups
        WHERE nodegroupid = '99999999-0000-0000-0000-000000000001' OR
        nodegroupid = '32999999-0000-0000-0000-000000000000' OR
        nodegroupid = '19999999-0000-0000-0000-000000000000' OR
        nodegroupid = '21111111-0000-0000-0000-000000000000';

        DELETE FROM public.resource_instances
        WHERE resourceinstanceid = '40000000-0000-0000-0000-000000000000';

        """

        cursor = connection.cursor()
        cursor.execute(sql)

        delete_terms_index()
        delete_concepts_index()
        delete_search_index() 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def set_data_location(apps, schema_editor):
    """Create DataLocation for each Data."""
    Data = apps.get_model("flow", "Data")
    DataLocation = apps.get_model("flow", "DataLocation")

    for data in Data.objects.all():
        if os.path.isdir(
            os.path.join(settings.FLOW_EXECUTOR["DATA_DIR"], str(data.id))
        ):
            with transaction.atomic():
                # Manually set DataLocation id to preserve data directory.
                data_location = DataLocation.objects.create(
                    id=data.id, subpath=str(data.id)
                )
                data_location.data.add(data)

    # Increment DataLocation id's sequence
    if DataLocation.objects.exists():
        max_id = DataLocation.objects.order_by("id").last().id
        with connection.cursor() as cursor:
            cursor.execute(
                "ALTER SEQUENCE flow_datalocation_id_seq RESTART WITH {};".format(
                    max_id + 1
                )
            ) 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def faq(request: HttpRequest):
    num_scans  = Site.objects.filter(scans__isnull=False).count()
    num_scanning_sites = Scan.objects.filter(end__isnull=True).count()

    # query = '''SELECT
    #     COUNT(jsonb_array_length("result"->'leaks'))
    #     FROM backend_scanresult
    #     WHERE backend_scanresult.scan_id IN (
    #         SELECT backend_site.last_scan_id
    #         FROM backend_site
    #         WHERE backend_site.last_scan_id IS NOT NULL)
    #     AND jsonb_array_length("result"->'leaks') > 0'''
    # 
    # with connection.cursor() as cursor:
    #     cursor.execute(query)
    #     num_sites_failing_serverleak = cursor.fetchone()[0]
        
    return render(request, 'frontend/faq.html', {
        'num_scanning_sites': num_scanning_sites,
        'num_scans':  num_scans,
        'num_sites': Site.objects.count(),
        # 'num_sites_failing_serverleak': num_sites_failing_serverleak
    }) 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def test_sql_psycopg2_string_composition(sentry_init, capture_events, query):
    sentry_init(
        integrations=[DjangoIntegration()],
        send_default_pii=True,
        _experiments={"record_sql_params": True},
    )
    from django.db import connections

    if "postgres" not in connections:
        pytest.skip("postgres tests disabled")

    import psycopg2.sql

    sql = connections["postgres"].cursor()

    events = capture_events()
    with pytest.raises(ProgrammingError):
        sql.execute(query(psycopg2.sql), {"my_param": 10})

    capture_message("HI")

    (event,) = events
    crumb = event["breadcrumbs"][-1]
    assert crumb["message"] == ('SELECT %(my_param)s FROM "foobar"')
    assert crumb["data"]["db.params"] == {"my_param": 10} 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def create_new_temp_table(self, table_name, columns):
        """Create a temporary table and return the table name."""
        temp_table_name = table_name + "_" + str(uuid.uuid4()).replace("-", "_")
        base_sql = f"CREATE TEMPORARY TABLE {temp_table_name} "
        column_types = ""
        for column in columns:
            for name, column_type in column.items():
                column_types += f"{name} {column_type}, "
        column_types = column_types.strip().rstrip(",")
        column_sql = f"({column_types})"
        table_sql = base_sql + column_sql
        with connection.cursor() as cursor:
            cursor.db.set_schema(self.schema)
            cursor.execute(table_sql)

        return temp_table_name 

# 需要导入模块: from django.db import connection [as 别名]
# 或者: from django.db.connection import cursor [as 别名]
def test_vacuum_schemas(self, mock_vacuum):
        """Test that the vacuum_schemas scheduled task runs for all schemas."""
        schema_one = "acct123"
        schema_two = "acct456"
        with connection.cursor() as cursor:
            cursor.execute(
                """
                INSERT INTO api_tenant (schema_name)
                VALUES (%s), (%s)
                """,
                [schema_one, schema_two],
            )

        tasks.vacuum_schemas()

        for schema_name in [schema_one, schema_two]:
            mock_vacuum.delay.assert_any_call(schema_name)