Python sql.Identifier方法代码示例

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [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 Identifier [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 Identifier [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 Identifier [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 Identifier [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 Identifier [as 别名]
def move_models_with_message_key_to_archive(msg_ids: List[int]) -> None:
    assert len(msg_ids) > 0

    for model in models_with_message_key:
        query = SQL("""
        INSERT INTO {archive_table_name} ({dst_fields})
            SELECT {src_fields}
            FROM {table_name}
            WHERE {table_name}.message_id IN {message_ids}
        ON CONFLICT (id) DO NOTHING
        """)
        move_rows(
            model['class'],
            query,
            table_name=Identifier(model['table_name']),
            archive_table_name=Identifier(model['archive_table_name']),
            message_ids=Literal(tuple(msg_ids)),
        )

# Attachments can't use the common models_with_message_key system,
# because they can be referenced by more than one Message, and we only
# want to delete the Attachment if we're deleting the last message
# referencing them. 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [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 Identifier [as 别名]
def add_index(self, cur, table_name, column_names):
        index_name = 'tp_{}_{}_idx'.format(table_name, "_".join(column_names))

        if len(index_name) > self.IDENTIFIER_FIELD_LENGTH:
            index_name_hash = hashlib.sha1(index_name.encode('utf-8')).hexdigest()[0:60]
            index_name = 'tp_{}'.format(index_name_hash)

        cur.execute(sql.SQL('''
            CREATE INDEX {index_name}
            ON {table_schema}.{table_name}
            ({column_names});
        ''').format(
            index_name=sql.Identifier(index_name),
            table_schema=sql.Identifier(self.postgres_schema),
            table_name=sql.Identifier(table_name),
            column_names=sql.SQL(', ').join(sql.Identifier(column_name) for column_name in column_names))) 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def copy_column_in_db(
    table_name: str,
    col_from: str,
    col_to: str,
):
    """Copy the values in one column to another.

    :param table_name: Table to process
    :param col_from: Source column
    :param col_to: Destination column
    :return: Nothing. The change is performed in the DB
    """
    query = sql.SQL('UPDATE {0} SET {1}={2}').format(
        sql.Identifier(table_name),
        sql.Identifier(col_to),
        sql.Identifier(col_from),
    )

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

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def is_column_unique(table_name: str, column_name: str) -> bool:
    """Return if a table column has all non-empty unique values.

    :param table_name: table
    :param column_name: column
    :return: Boolean (is unique)
    """
    query = sql.SQL('SELECT COUNT(DISTINCT {0}) = count(*) from {1}').format(
        OnTaskDBIdentifier(column_name),
        sql.Identifier(table_name),
    )

    # Get the result
    with connection.connection.cursor() as cursor:
        cursor.execute(query, [])
        return cursor.fetchone()[0] 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def db_rename_column(table: str, old_name: str, new_name: str):
    """Rename a column in the database.

    :param table: table
    :param old_name: Old name of the column
    :param new_name: New name of the column
    :return: Nothing. Change reflected in the database table
    """
    if len(new_name) > COLUMN_NAME_SIZE:
        raise Exception(
            _('Column name is longer than {0} characters').format(
                COLUMN_NAME_SIZE))

    with connection.connection.cursor() as cursor:
        cursor.execute(sql.SQL('ALTER TABLE {0} RENAME {1} TO {2}').format(
            sql.Identifier(table),
            sql.Identifier(old_name),
            sql.Identifier(new_name),
        )) 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def get_text_column_hash(table_name: str, column_name: str) -> str:
    """Calculate and return the MD5 hash of a text column.

    :param table_name: table to use
    :param column_name: column to pull the values
    :return: MD5 hash of the concatenation of the column values
    """
    query = sql.SQL('SELECT MD5(STRING_AGG({0}, {1})) FROM {2}').format(
        OnTaskDBIdentifier(column_name),
        sql.Literal(''),
        sql.Identifier(table_name),
    )

    with connection.connection.cursor() as cursor:
        cursor.execute(query)
        return cursor.fetchone()[0] 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def get_num_rows(table_name, cond_filter=None):
    """Get the number of rows in the table that satisfy the condition.

    :param table_name: Table name
    :param cond_filter: Formula
    :return: integer
    """
    query = sql.SQL('SELECT count (*) FROM {0}').format(
        sql.Identifier(table_name))

    cond_fields = []
    if cond_filter is not None:
        cond_filter, cond_fields = formula.evaluate(
            cond_filter,
            formula.EVAL_SQL,
        )
        query = sql.SQL('{0} WHERE {1}').format(query, cond_filter)

    with connection.connection.cursor() as cursor:
        cursor.execute(query, cond_fields)
        num_rows = cursor.fetchone()[0]

    return num_rows 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def delete_row(table_name: str, kv_pair: Tuple[str, Any]):
    """Delete the row with the given key, value pair.

    :param table_name: Table to manipulate
    :param kv_pair: A key=value pair to identify the row. Key is suppose to
    be unique.
    :return: Drops that row from the table in the DB
    """
    # Get the key/value subclause
    bool_clause, query_fields = get_boolean_clause(
        filter_pairs={kv_pair[0]: kv_pair[1]},
    )

    # Create the query
    query = sql.SQL('DELETE FROM {0}').format(
        sql.Identifier(table_name),
    ) + sql.SQL(' WHERE ') + bool_clause

    # Execute the query
    with connection.connection.cursor() as cursor:
        cursor.execute(query, query_fields) 

# 需要导入模块: from psycopg2 import sql [as 别名]
# 或者: from psycopg2.sql import Identifier [as 别名]
def test_psycopg2_composable_query_works(instrument, postgres_connection, elasticapm_client):
    """
    Check that we parse queries that are psycopg2.sql.Composable correctly
    """
    cursor = postgres_connection.cursor()
    query = sql.SQL("SELECT * FROM {table} WHERE {row} LIKE 't%' ORDER BY {row} DESC").format(
        table=sql.Identifier("test"), row=sql.Identifier("name")
    )
    baked_query = query.as_string(cursor.__wrapped__)
    result = None
    try:
        elasticapm_client.begin_transaction("web.django")
        cursor.execute(query)
        result = cursor.fetchall()
        elasticapm_client.end_transaction(None, "test-transaction")
    finally:
        # make sure we've cleared out the spans for the other tests.
        assert [(2, "two"), (3, "three")] == result
        transactions = elasticapm_client.events[TRANSACTION]
        spans = elasticapm_client.spans_for_transaction(transactions[0])
        span = spans[0]
        assert span["name"] == "SELECT FROM test"
        assert "db" in span["context"]
        assert span["context"]["db"]["type"] == "sql"
        assert span["context"]["db"]["statement"] == baked_query