Python sql.SQL屬性代碼示例

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def _insert_org_sql(self, org_node, date):
        """Inserts the org unit information into the database."""
        select_sql = """SELECT * FROM {schema}.reporting_awsorganizationalunit
                        WHERE org_unit_name = %s and org_unit_id = %s and org_unit_path = %s and level = %s"""
        select_sql = sql.SQL(select_sql).format(schema=sql.Identifier(self.schema))
        values = [org_node["org_unit_name"], org_node["org_unit_id"], org_node["org_path"], org_node["level"]]
        self.cursor.execute(select_sql, values)
        org_exists = self.cursor.fetchone()
        if org_exists is None:
            org_insert_sql = """INSERT INTO {schema}.reporting_awsorganizationalunit
                                (org_unit_name, org_unit_id, org_unit_path, created_timestamp, level)
                                VALUES (%s, %s, %s, %s, %s);"""
            org_insert_sql = sql.SQL(org_insert_sql).format(schema=sql.Identifier(self.schema))
            values = [
                org_node["org_unit_name"],
                org_node["org_unit_id"],
                org_node["org_path"],
                date,
                org_node["level"],
            ]
            self.cursor.execute(org_insert_sql, values) 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def _set_deleted_timestamp(self, date):
        """Updates the delete timestamp for values left in the yesterday lists."""
        if self.yesterday_accounts != []:
            for account_id in self.yesterday_accounts:
                alias_query = """SELECT id FROM {schema}.reporting_awsaccountalias WHERE account_id = %s;"""
                alias_query = sql.SQL(alias_query).format(schema=sql.Identifier(self.schema))
                self.cursor.execute(alias_query, [account_id])
                alias_id = self.cursor.fetchone()[0]
                update_delete_sql = """UPDATE {schema}.reporting_awsorganizationalunit
                                       SET deleted_timestamp = %s WHERE account_alias_id = %s"""
                update_delete_sql = sql.SQL(update_delete_sql).format(schema=sql.Identifier(self.schema))
                self.cursor.execute(update_delete_sql, [date, alias_id])
        if self.yesterday_orgs != []:
            for org_unit in self.yesterday_orgs:
                update_delete_sql = """UPDATE {schema}.reporting_awsorganizationalunit
                                       SET deleted_timestamp = %s WHERE org_unit_id = %s"""
                update_delete_sql = sql.SQL(update_delete_sql).format(schema=sql.Identifier(self.schema))
                self.cursor.execute(update_delete_sql, [date, org_unit])
        self.yesterday_accounts = self.today_accounts
        self.yesterday_orgs = self.today_orgs
        self.today_accounts = []
        self.today_orgs = [] 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def copy_table(destination_table_name, columns, coercions={}):
        source_table_name = "tb_" + destination_table_name

        print(f"Transfer table contents: {source_table_name}: Querying MySQL... ", end="")

        mysql.execute(f"SELECT {','.join(columns)} FROM {source_table_name}")
        rows = mysql.fetchall()

        print("Applying coercions in-memory... ", end="")

        for column_name, coercion in coercions.items():
            row_id = columns.index(column_name)
            rows = coercion(rows, row_id)

        rows = list(rows)

        print("Inserting into PostgreSQL... ", end="")

        # psql.execute(sql.SQL("DELETE FROM {}").format(sql.Identifier(destination_table_name)))
        psql_sql = sql.SQL("INSERT INTO {} VALUES %s").format(sql.Identifier(destination_table_name))
        execute_values(psql, psql_sql, rows)

        print("done.") 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def get_col_from_populations(connect_str_or_path, use_sqlite,
                             population_id, column_name):
    if use_sqlite:
        sqlite_path = connect_str_or_path
        conn = sqlite3.connect(sqlite_path)
        command = "SELECT {} FROM populations WHERE population_id = ?"
        command = command.format(column_name)  # Warning: SQL injection
    else:
        db_connect_str = connect_str_or_path
        conn = psycopg2.connect(db_connect_str)
        command = "SELECT {} FROM populations WHERE population_id = %s"
        command = SQL(command).format(Identifier(column_name))
    cur = conn.cursor()
    cur.execute(command, [population_id])
    column = cur.fetchall()
    cur.close()
    conn.close()
    column = [value[0] for value in column]
    return column 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def get_max_of_db_column(connect_str_or_path, use_sqlite, table_name,
                         column_name):
    if use_sqlite:
        sqlite_path = connect_str_or_path
        conn = sqlite3.connect(sqlite_path)
        cur = conn.cursor()
        parameters = [column_name, table_name]
        cur.execute("SELECT MAX({}) FROM {}".format(*parameters))
    else:
        db_connect_str = connect_str_or_path
        conn = psycopg2.connect(db_connect_str)
        cur = conn.cursor()
        parameters = [Identifier(column_name), Identifier(table_name)]
        cur.execute(SQL("SELECT MAX({}) FROM {}").format(*parameters))
    max_value = cur.fetchone()[0]
    cur.close()
    conn.close()
    return max_value 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def move_messages_to_archive(message_ids: List[int], realm: Optional[Realm]=None,
                             chunk_size: int=MESSAGE_BATCH_SIZE) -> None:
    query = SQL("""
    INSERT INTO zerver_archivedmessage ({dst_fields}, archive_transaction_id)
        SELECT {src_fields}, {archive_transaction_id}
        FROM zerver_message
        WHERE zerver_message.id IN {message_ids}
        LIMIT {chunk_size}
    ON CONFLICT (id) DO UPDATE SET archive_transaction_id = {archive_transaction_id}
    RETURNING id
    """)
    count = run_archiving_in_chunks(
        query,
        type=ArchiveTransaction.MANUAL,
        message_ids=Literal(tuple(message_ids)),
        realm=realm,
        chunk_size=chunk_size,
    )

    if count == 0:
        raise Message.DoesNotExist
    # Clean up attachments:
    archived_attachments = ArchivedAttachment.objects.filter(messages__id__in=message_ids).distinct()
    Attachment.objects.filter(messages__isnull=True, id__in=archived_attachments).delete() 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def restore_messages_from_archive(archive_transaction_id: int) -> List[int]:
    query = SQL("""
        INSERT INTO zerver_message ({dst_fields})
            SELECT {src_fields}
            FROM zerver_archivedmessage
            WHERE zerver_archivedmessage.archive_transaction_id = {archive_transaction_id}
        ON CONFLICT (id) DO NOTHING
        RETURNING id
        """)
    return move_rows(
        Message,
        query,
        src_db_table='zerver_archivedmessage',
        returning_id=Literal(True),
        archive_transaction_id=Literal(archive_transaction_id),
    ) 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def restore_models_with_message_key_from_archive(archive_transaction_id: int) -> None:
    for model in models_with_message_key:
        query = SQL("""
        INSERT INTO {table_name} ({dst_fields})
            SELECT {src_fields}
            FROM {archive_table_name}
            INNER JOIN zerver_archivedmessage ON {archive_table_name}.message_id = zerver_archivedmessage.id
            WHERE zerver_archivedmessage.archive_transaction_id = {archive_transaction_id}
        ON CONFLICT (id) DO NOTHING
        """)

        move_rows(
            model['class'],
            query,
            src_db_table=model['archive_table_name'],
            table_name=Identifier(model['table_name']),
            archive_transaction_id=Literal(archive_transaction_id),
            archive_table_name=Identifier(model['archive_table_name']),
        ) 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def test_multiple_users_realms_and_bots(self) -> None:
        user1 = self.create_user()
        user2 = self.create_user()
        second_realm = Realm.objects.create(string_id='moo', name='moo')
        user3 = self.create_user(realm=second_realm)
        user4 = self.create_user(realm=second_realm, is_bot=True)
        for user in [user1, user2, user3, user4]:
            self.add_event(RealmAuditLog.USER_CREATED, 1, user=user)
        do_fill_count_stat_at_hour(self.stat, self.TIME_ZERO)
        self.assertTableState(UserCount, ['subgroup', 'user'],
                              [['false', user1], ['false', user2], ['false', user3], ['true', user4]])
        self.assertTableState(RealmCount, ['value', 'subgroup', 'realm'],
                              [[2, 'false', self.default_realm], [1, 'false', second_realm],
                               [1, 'true', second_realm]])
        self.assertTableState(InstallationCount, ['value', 'subgroup'], [[3, 'false'], [1, 'true']])
        self.assertTableState(StreamCount, [], [])

    # Not that interesting a test if you look at the SQL query at hand, but
    # almost all other CountStats have a start_date, so guarding against a
    # refactoring that adds that in.
    # Also tests the slightly more end-to-end process_count_stat rather than
    # do_fill_count_stat_at_hour. E.g. if one changes self.stat.frequency to
    # CountStat.HOUR from CountStat.DAY, this will fail, while many of the
    # tests above will not. 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def check_useractivityinterval_by_user_query(realm: Optional[Realm]) -> QueryFn:
    if realm is None:
        realm_clause = SQL("")
    else:
        realm_clause = SQL("zerver_userprofile.realm_id = {} AND").format(Literal(realm.id))
    return lambda kwargs: SQL("""
    INSERT INTO analytics_usercount
        (user_id, realm_id, value, property, subgroup, end_time)
    SELECT
        zerver_userprofile.id, zerver_userprofile.realm_id, 1, %(property)s, {subgroup}, %(time_end)s
    FROM zerver_userprofile
    JOIN zerver_useractivityinterval
    ON
        zerver_userprofile.id = zerver_useractivityinterval.user_profile_id
    WHERE
        zerver_useractivityinterval.end >= %(time_start)s AND
        {realm_clause}
        zerver_useractivityinterval.start < %(time_end)s
    GROUP BY zerver_userprofile.id {group_by_clause}
""").format(**kwargs, realm_clause=realm_clause) 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def setup_table_mapping_cache(self, cur):
        self.table_mapping_cache = {}

        cur.execute(sql.SQL('''
            SELECT c.relname, obj_description(c.oid, 'pg_class')
            FROM pg_namespace AS n
                INNER JOIN pg_class AS c ON n.oid = c.relnamespace
            WHERE n.nspname = {};
        ''').format(sql.Literal(self.postgres_schema)))

        for mapped_name, raw_json in cur.fetchall():
            table_path = None
            if raw_json:
                table_path = json.loads(raw_json).get('path', None)
            self.LOGGER.info("Mapping: {} to {}".format(mapped_name, table_path))
            if table_path:
                self.table_mapping_cache[tuple(table_path)] = mapped_name 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def __get_table_schema(self, cur, name):
        # Purely exists for migration purposes. DO NOT CALL DIRECTLY
        cur.execute(sql.SQL('''
            SELECT column_name, data_type, is_nullable FROM information_schema.columns
            WHERE table_schema = {} and table_name = {};
        ''').format(
            sql.Literal(self.postgres_schema), sql.Literal(name)))

        properties = {}
        for column in cur.fetchall():
            properties[column[0]] = self.sql_type_to_json_schema(column[1], column[2] == 'YES')

        metadata = self._get_table_metadata(cur, name)

        if metadata is None and not properties:
            return None

        if metadata is None:
            metadata = {'version': None}

        metadata['name'] = name
        metadata['type'] = 'TABLE_SCHEMA'
        metadata['schema'] = {'properties': properties}

        return metadata 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def test_loading__empty(db_cleanup):
    stream = CatStream(0)
    main(CONFIG, input_stream=stream)

    with psycopg2.connect(**TEST_DB) as conn:
        with conn.cursor() as cur:
            cur.execute(
                sql.SQL('''
            SELECT EXISTS(
              SELECT 1
              FROM information_schema.tables
              WHERE table_schema = {}
                AND table_name = {}
            );
            ''').format(
                    sql.Literal('public'),
                    sql.Literal('cats')))

            assert not cur.fetchone()[0] 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def is_empty(node, eval_type, given_variables):
    """Process the is_empty operator.

    :param node: Formula node
    :param eval_type: Type of evaluation
    :param given_variables: Dictionary of var/values
    :return: Boolean result, SQL query, or text result
    """
    if eval_type == EVAL_EXP:
        # Python evaluation
        varvalue = get_value(node, given_variables)
        return (not value_is_null(varvalue)) and varvalue == ''

    if eval_type == EVAL_SQL:
        # SQL evaluation
        query = sql.SQL('({0} = \'\') OR ({0} is null)').format(
            OnTaskDBIdentifier(node['field']),
        )

        return query, []

    # Text evaluation
    return '{0} is empty'.format(node['field']) 

# 需要導入模塊: from psycopg2 import sql [as 別名]
# 或者: from psycopg2.sql import SQL [as 別名]
def is_null(node, eval_type, given_variables):
    """Process the is_null operator.

    :param node: Formula node
    :param eval_type: Type of evaluation
    :param given_variables: Dictionary of var/values
    :return: Boolean result, SQL query, or text result
    """
    if eval_type == EVAL_EXP:
        # Python evaluation
        node_value = get_value(node, given_variables)
        return value_is_null(node_value)

    if eval_type == EVAL_SQL:
        # SQL evaluation
        query = sql.SQL('({0} is null)').format(
            OnTaskDBIdentifier(node['field']),
        )

        return query, []

    # Text evaluation
    return '{0} is null'.format(node['field'])