Python psycopg2.extensions.AsIs() Examples

def update_schema(self):
        if self.owner != self.old_owner:
            log.info("alter schema %s owner to %s", self.old_schema, self.owner)
            with self.connection.cursor() as cursor:
                cursor.execute(
                    "ALTER SCHEMA %s OWNER TO %s",
                    [AsIs(self.old_schema), AsIs(self.owner)],
                )

        if self.schema != self.old_schema:
            log.info("alter schema %s rename to %s", self.old_schema, self.schema)
            with self.connection.cursor() as cursor:
                cursor.execute(
                    "ALTER SCHEMA %s RENAME TO %s",
                    [AsIs(self.old_schema), AsIs(self.schema)],
                ) 

def drop_db(hostname="localhost", username=None, password=None, dbname=None, port=None):
    """
    Drop database.
    :param hostname: string
    :param username: string
    :param password: string
    :param dbname: string
    """
    cn = create_cn(hostname, password, username, "postgres", port)

    # ISOLATION_LEVEL_AUTOCOMMIT = 0
    # Needed for DB drop.
    cn.set_isolation_level(0)

    with cn.cursor() as cr:
        cr.execute("drop database if exists %s", (AsIs(dbname),))

    close_cn(cn) 

def total_unread(user_id=None):
    if user_id is None:
        user_id = current_user_id()
    with db.conn() as conn:
        cur = conn.execute('''
            SELECT COUNT(*), SUM("unread_count")
            FROM %(schema_name)s.user_conversation
            WHERE
                "unread_count" > 0 AND
                "user" = %(user_id)s
            ''', {
                'schema_name': AsIs(_get_schema_name()),
                'user_id': user_id
            }
        )
        r = cur.first()
        conversation_count = r[0]
        message_count = r[1]
    return {
        'conversation': conversation_count,
        'message': message_count
    } 

def _get_new_last_message_id(conn, message):
    # TODO rewrite with database.query
    cur = conn.execute('''
            SELECT _id FROM %(schema_name)s.message
            WHERE
                deleted = false AND
                seq < %(seq)s AND
                conversation = %(conversation_id)s
            ORDER BY seq DESC LIMIT 1
        ''', {
            'schema_name': AsIs(_get_schema_name()),
            'seq': message['seq'],
            'conversation_id': message['conversation'].recordID.key
        })
    row = cur.fetchone()
    return None if row is None else row['_id'] 

def getReceiptList(self):
        """
        Returns a list of message receipt statuses.
        """
        receipts = list()
        with db.conn() as conn:
            cur = conn.execute('''
                SELECT receipt.user, read_at, delivered_at
                FROM %(schema_name)s.receipt
                WHERE
                    "message" = %(message_id)s AND
                    (read_at IS NOT NULL or delivered_at is NOT NULL)
                ''', {
                    'schema_name': AsIs(_get_schema_name()),
                    'message_id': self.id.key
                }
            )

            for row in cur:
                receipts.append({
                    'user': row['user'],
                    'read_at': to_rfc3339_or_none(row['read_at']),
                    'delivered_at': to_rfc3339_or_none(row['delivered_at'])
                })
        return receipts 

def register_numpy_types():
    # Credit: https://github.com/musically-ut/psycopg2_numpy_ext
    """Register the AsIs adapter for following types from numpy:
      - numpy.int8
      - numpy.int16
      - numpy.int32
      - numpy.int64
      - numpy.float16
      - numpy.float32
      - numpy.float64
      - numpy.float128
    """
    for typ in ['int8', 'int16', 'int32', 'int64',
                'float16', 'float32', 'float64', 'float128',
                'bool_']:
        register_adapter(np.__getattribute__(typ), AsIs) 

def _get_channels_by_user_ids(user_ids):
    # TODO: use database.query instead of raw SQL
    with db.conn() as conn:
        cur = conn.execute('''
            SELECT name
            FROM %(schema_name)s.user_channel
            WHERE _owner_id in %(user_ids)s
            LIMIT %(len)s;
            ''', {
            'schema_name': AsIs(_get_schema_name()),
            'user_ids': tuple(user_ids),
            'len': len(user_ids),
        }
        )

        results = []
        for row in cur:
            results.append(row[0])

        return results 

def pg_users():
    uid = str(uuid.uuid4()).replace("-", "")
    name = f'user_{uid}'
    name2 = f'user2_{uid}'
    r = Request('Create', name, name)
    with r.db_connection() as connection:
        with connection.cursor() as cursor:
            for n in [name, name2]:
                cursor.execute(
                    "CREATE ROLE %s LOGIN ENCRYPTED PASSWORD %s", [AsIs(n), n]
                )
        connection.commit()

        yield (name, name2)

        with connection.cursor() as cursor:
            #cursor.execute("DROP OWNED BY %s CASCADE", [AsIs(name)])
            for n in [name, name2]:
                cursor.execute("DROP ROLE %s", [AsIs(n)])
            connection.commit() 

def create_db(hostname='localhost', username=None, password=None, dbname=None, port=None):
    """Create test database.

    :param hostname: string
    :param username: string
    :param password: string
    :param dbname: string
    :param port: int
    :return:

    """
    cn = create_cn(hostname, password, username, 'postgres', port)

    # ISOLATION_LEVEL_AUTOCOMMIT = 0
    # Needed for DB creation.
    cn.set_isolation_level(0)

    with cn.cursor() as cr:
        cr.execute('drop database if exists %s', (AsIs(dbname),))
        cr.execute('create database %s', (AsIs(dbname),))

    cn.close()

    cn = create_cn(hostname, password, username, dbname, port)
    return cn 

def drop_db(hostname='localhost', username=None, password=None,
            dbname=None, port=None):
    """
    Drop database.
    :param hostname: string
    :param username: string
    :param password: string
    :param dbname: string
    """
    cn = create_cn(hostname, password, username, 'postgres', port)

    # ISOLATION_LEVEL_AUTOCOMMIT = 0
    # Needed for DB drop.
    cn.set_isolation_level(0)

    with cn.cursor() as cr:
        cr.execute('drop database if exists %s', (AsIs(dbname),))

    close_cn(cn) 

def _update_conversation_last_message(conn, conversation, last_message,
                                      new_last_message_id):
    last_message_key = last_message.id.key
    if last_message_key == conversation['last_message_ref'].recordID.key:
        conversation_id = last_message.conversation_id
        conn.execute('''
        UPDATE %(schema_name)s.conversation
        SET last_message = %(new_last_message_id)s
        WHERE _id = %(conversation_id)s
        ''', {
            'schema_name': AsIs(_get_schema_name()),
            'conversation_id': conversation_id,
            'new_last_message_id': new_last_message_id
        }) 

def return_as_is(adapt_X):
    """Returns the AsIs appended function of the function passed

    This is useful for adapter functions intended to be used with the
    adapt_ListOfX function, which must return strings, as it allows them
    to be standalone adapters.
    """
    if not AsIs:
        raise ImportError('There was a problem importing psycopg2.')

    def adapter_function(param):
        return AsIs("'{}'".format(adapt_X(param)))
    return adapter_function 

def test_adapt_subtype_3(self):
        from psycopg2.extensions import adapt, register_adapter, AsIs

        class A: pass
        class B(A): pass

        register_adapter(A, lambda a: AsIs("a"))
        try:
            self.assertEqual(b("a"), adapt(B()).getquoted())
        finally:
           del psycopg2.extensions.adapters[A, psycopg2.extensions.ISQLQuote] 

def create_db(
    hostname="localhost", username=None, password=None, dbname=None, port=None
):
    """Create test database.

    :param hostname: string
    :param username: string
    :param password: string
    :param dbname: string
    :param port: int
    :return:

    """
    cn = create_cn(hostname, password, username, "postgres", port)

    # ISOLATION_LEVEL_AUTOCOMMIT = 0
    # Needed for DB creation.
    cn.set_isolation_level(0)

    with cn.cursor() as cr:
        cr.execute("drop database if exists %s", (AsIs(dbname),))
        cr.execute("create database %s", (AsIs(dbname),))

    cn.close()

    cn = create_cn(hostname, password, username, dbname, port)
    return cn 

def adapt_vanilla(param):
    """Vanilla adapter: simply returns the string representation"""
    if not AsIs:
        raise ImportError('There was a problem importing psycopg2.')
    return AsIs("'{}'".format(param)) 

def lift_constraints(cr, table, column):
    """Lift all constraints on column in table.
    Typically, you use this in a pre-migrate script where you adapt references
    for many2one fields with changed target objects.
    If everything went right, the constraints will be recreated"""
    cr.execute(
        'select relname, array_agg(conname) from '
        '(select t1.relname, c.conname '
        'from pg_constraint c '
        'join pg_attribute a '
        'on c.confrelid=a.attrelid and a.attnum=any(c.conkey) '
        'join pg_class t on t.oid=a.attrelid '
        'join pg_class t1 on t1.oid=c.conrelid '
        'where t.relname=%(table)s and attname=%(column)s '
        'union select t.relname, c.conname '
        'from pg_constraint c '
        'join pg_attribute a '
        'on c.conrelid=a.attrelid and a.attnum=any(c.conkey) '
        'join pg_class t on t.oid=a.attrelid '
        'where relname=%(table)s and attname=%(column)s) in_out '
        'group by relname',
        {
            'table': table,
            'column': column,
        })
    for table, constraints in cr.fetchall():
        cr.execute(
            'alter table %s drop constraint %s',
            (AsIs(table), AsIs(', drop constraint '.join(constraints)))
        ) 

def _change_reference_refs_sql(env, model_name, record_ids, target_record_id,
                               exclude_columns):
    cr = env.cr
    cr.execute("""
        SELECT model, name
        FROM ir_model_fields
        WHERE ttype='reference'
        """)
    rows = cr.fetchall()
    if ('ir.property', 'value_reference') not in rows:
        rows.append(('ir.property', 'value_reference'))
    for row in rows:
        try:
            model = env[row[0]]
        except KeyError:
            continue
        if not model._auto:  # Discard SQL views
            continue
        table = model._table
        if not table_exists(cr, table):
            continue
        column = row[1]
        if not column_exists(cr, table, column) or (
                (table, column) in exclude_columns):
            continue
        where = ' OR '.join(
            ["%s = '%s,%s'" % (column, model_name, x) for x in record_ids]
        )
        logged_query(
            cr, """
            UPDATE %s
            SET %s = %s
            WHERE %s
            """, (
                AsIs(table), AsIs(column),
                '%s,%s' % (model_name, target_record_id), AsIs(where)
            ), skip_no_result=True,
        ) 

def replace_account_types(env, type_spec, unlink=True):
    """ Replace old account types with their replacements. The old account
    type is allowed not to exist anymore, except when running unit tests.
    :param type_spec: list of tuples (xmlid of old account.account.type, \
xmlid of new account.account.type)
    :param unlink: attempt to unlink the old account type
    """
    logger = logging.getLogger('OpenUpgrade')
    for old_type, new_type in type_spec:
        try:
            type8 = env.ref(old_type)
        except ValueError:
            if getattr(threading.currentThread(), 'testing', False):
                raise
            continue

        type9 = env.ref(new_type)
        for table in ('account_account',
                      'account_account_template',
                      'account_move_line'):
            env.cr.execute(
                "UPDATE %s SET user_type_id = %s WHERE user_type_id = %s",
                (AsIs(table), type9.id, type8.id))
        if unlink:
            with env.cr.savepoint():
                try:
                    type8.unlink()
                except Exception as e:
                    logger.info(
                        'Could not remove account type %s: %s',
                        old_type, e) 

def _convert_field_bootstrap_3to4_sql(cr, table, field, ids=None):
    """Convert a field from Bootstrap 3 to 4, using raw SQL queries.

    TODO Support multilang fields.

    :param odoo.sql_db.Cursor cr:
        Database cursor.

    :param str table:
        Table name.

    :param str field:
        Field name, which should contain HTML content.

    :param list ids:
        List of IDs, to restrict operation to them.
    """
    sql = "SELECT id, %s FROM %s " % (field, table)
    params = ()
    if ids:
        sql += "WHERE id IN %s"
        params = (ids,)
    cr.execute(sql, params)
    for id_, old_content in cr.fetchall():
        new_content = convert_string_bootstrap_3to4(old_content)
        if old_content != new_content:
            cr.execute(
                "UPDATE %s SET %s = %s WHERE id = %s",
                AsIs(table), AsIs(field), new_content, id_,
            ) 

def adapt_numpy_int32(numpy_int32):
    return AsIs(numpy_int32) 

def _update_user_conversation_unread_count(conn, deleted_message):
    conn.execute('''
        WITH read_receipt AS (
            SELECT r.user AS read_user
            FROM %(schema_name)s.receipt r
            WHERE r.message = %(message_id)s
            AND read_at IS NOT NULL
        )
        UPDATE %(schema_name)s.user_conversation
        SET unread_count =
        CASE WHEN
            unread_count <= 0
        THEN
            0
        ELSE
            unread_count - 1
        END
        WHERE
            "conversation" = %(conversation_id)s
            AND "user" != %(user_id)s
            AND "user" NOT IN (SELECT read_user FROM read_receipt)
    ''', {
        'schema_name': AsIs(_get_schema_name()),
        'conversation_id': deleted_message.conversation_id,
        'message_id': deleted_message.id.key,
        'user_id': deleted_message.owner_id
    }) 

def adapt_list(param):
    """Adapts a list into an array"""
    if not AsIs:
        raise ImportError('There was a problem importing psycopg2.')
    final_string = ""
    if param:
        for item in param:
            final_string = final_string + str(item) + ","
        final_string = "{" + final_string + "}"
    return AsIs("'{}'".format(final_string)) 

def updateMessageStatus(self, conn) -> bool:
        """
        Update the message status field by querying the database for
        all receipt statuses.
        """
        cur = conn.execute('''
            WITH
              read_count AS (
                SELECT DISTINCT COUNT(receipt.user) as count
                FROM %(schema_name)s.receipt
                WHERE message = %(message_id)s
                    AND read_at IS NOT NULL
              ),
              participant_count AS (
                SELECT count(1) as count
                FROM %(schema_name)s.user_conversation
                WHERE conversation = %(conversation_id)s
              )
            UPDATE %(schema_name)s.message
            SET _updated_at = NOW(),
                message_status =
                  CASE
                    WHEN read_count.count = 0 THEN 'delivered'
                    WHEN read_count.count < participant_count.count
                        THEN 'some_read'
                    ELSE 'all_read'
                  END
            FROM read_count, participant_count
            WHERE _id = %(message_id)s
            RETURNING _updated_at, message_status
            ''', {
                'schema_name': AsIs(_get_schema_name()),
                'message_id': self.id.key,
                'conversation_id': self.conversation_id
            }
        )

        row = cur.fetchone()
        if row is not None:
            self['_updated_at'] = row[0]
            self['message_status'] = row[1] 

def adapt_numpy_int64(numpy_int64):
    return AsIs(numpy_int64) 

def adapt_numpy_uint16(numpy_uint16):
    return AsIs(numpy_uint16) 

def adapt_numpy_uint32(numpy_uint32):
    return AsIs(numpy_uint32) 

def adapt_numpy_uint64(numpy_uint64):
    return AsIs(numpy_uint64) 

def adapt_numpy_float32(numpy_float32):
    return AsIs(numpy_float32) 

def adapt_numpy_ndarray(numpy_ndarray):
    return AsIs(numpy_ndarray.tolist()) 

def adapt_numpy_float64(numpy_float64):
    return AsIs(numpy_float64)