Python sqlalchemy.dialects.postgresql.JSONB Examples

def upgrade():
    op.create_table(
        'error_logs',
        IDColumn(),
        sa.Column('created_at', sa.DateTime(timezone=True),
                  server_default=sa.func.now(), index=True),
        sa.Column('severity', sa.Enum('critical', 'error', 'warning', 'info', 'debug', name='errorlog_severity'),
                  index=True),
        sa.Column('source', sa.String),
        sa.Column('user', GUID, sa.ForeignKey('users.uuid'), nullable=True, index=True),
        sa.Column('is_read', sa.Boolean, default=False, index=True),
        sa.Column('is_cleared', sa.Boolean, default=False, index=True),
        sa.Column('message', sa.Text),
        sa.Column('context_lang', sa.String),
        sa.Column('context_env', postgresql.JSONB()),
        sa.Column('request_url', sa.String, nullable=True),
        sa.Column('request_status', sa.Integer, nullable=True),
        sa.Column('traceback', sa.Text, nullable=True),
    ) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('credit_transfer', sa.Column('transfer_metadata', postgresql.JSONB(astext_type=sa.Text()), nullable=True))

    # Create a tempoary "_status" type, convert and drop the "old" type
    tmp_type.create(op.get_bind(), checkfirst=False)
    op.execute('ALTER TABLE credit_transfer ALTER COLUMN transfer_type TYPE _transfertypeenum'
               ' USING transfer_type::text::_transfertypeenum')

    # Convert 'DISBURSEMENT' transfer_type into 'DEPOSIT'
    op.execute(tcr.update().where(tcr.c.transfer_type == u'DISBURSEMENT').values(transfer_type='DEPOSIT'))

    old_type.drop(op.get_bind(), checkfirst=False)
    # Create and convert to the "new" status type
    new_type.create(op.get_bind(), checkfirst=False)
    op.execute('ALTER TABLE credit_transfer ALTER COLUMN transfer_type TYPE transfertypeenum'
               ' USING transfer_type::text::transfertypeenum')
    tmp_type.drop(op.get_bind(), checkfirst=False)
    # ### end Alembic commands ### 

def upgrade():
### commands auto generated by Alembic - please adjust! ###
    op.create_table('expedition',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('resources_id', sa.Integer(), nullable=True),
    sa.Column('constraints', postgresql.JSONB(), nullable=True),
    sa.ForeignKeyConstraint(['resources_id'], ['resources.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('admiral_j_expedition',
    sa.Column('adm_id', sa.Integer(), nullable=True),
    sa.Column('expedition_id', sa.Integer(), nullable=True),
    sa.ForeignKeyConstraint(['adm_id'], ['admiral.id'], ),
    sa.ForeignKeyConstraint(['expedition_id'], ['expedition.id'], )
    )
    ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('fiat_ramp',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('authorising_user_id', sa.Integer(), nullable=True),
    sa.Column('created', sa.DateTime(), nullable=True),
    sa.Column('updated', sa.DateTime(), nullable=True),
    sa.Column('_payment_method', sa.String(), nullable=True),
    sa.Column('payment_amount', sa.Integer(), nullable=True),
    sa.Column('payment_reference', sa.String(), nullable=True),
    sa.Column('payment_status', sa.Enum('PENDING', 'FAILED', 'COMPLETE', name='fiatrampstatusenum'), nullable=True),
    sa.Column('credit_transfer_id', sa.Integer(), nullable=True),
    sa.Column('token_id', sa.Integer(), nullable=True),
    sa.Column('payment_metadata', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.ForeignKeyConstraint(['credit_transfer_id'], ['credit_transfer.id'], ),
    sa.ForeignKeyConstraint(['token_id'], ['token.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('key_value_store',
        sa.Column('id', sa.BigInteger(), nullable=False),
        sa.Column('key', sa.Text(), nullable=False),
        sa.Column('value', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )

    print("Creating index")
    op.create_index(op.f('ix_key_value_store_key'), 'key_value_store', ['key'], unique=True)
    print("Applying not-null constraing")
    op.alter_column('nu_release_item', 'release_date',
               existing_type=postgresql.TIMESTAMP(),
               nullable=False)
    # ### end Alembic commands ### 

def update_json(self, *args, connection=None, **kwargs):
        t = self.table
        if args:
            if len(args) > 1 and not kwargs:
                field, *path, value = args
            else:
                field, *path = args
                value = kwargs
            for p in reversed(path):
                value = {p: value}
            kwargs = {field: value}
        elif not kwargs:
            raise ValueError('Need args or kwargs')

        await connection.fetchval(
            t.update().where(
                t.c[self.primary_key] == self.pk
            ).values(
                {
                    t.c[field]: sa.func.coalesce(
                        t.c[field], sa.cast({}, JSONB)
                    ) + sa.cast(value, JSONB)
                    for field, value in kwargs.items()
                }
            ).returning(t.c[self.primary_key])) 

def upgrade():
    op.create_table('fda_dap',

        # Meta

        sa.Column('meta_id', sa.Text, unique=True),
        sa.Column('meta_source', sa.Text),
        sa.Column('meta_created', sa.DateTime(timezone=True)),
        sa.Column('meta_updated', sa.DateTime(timezone=True)),

        # General

        sa.Column('id', sa.Text, unique=True),
        sa.Column('documents', JSONB),
        sa.Column('approval_type', sa.Text),
        sa.Column('supplement_number', sa.Integer),
        sa.Column('action_date', sa.Date),
        sa.Column('fda_application_num', sa.Text),
        sa.Column('notes', sa.Text),

    ) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('domains', sa.Column('integration_id', sa.String(length=512), nullable=True))
    op.alter_column('domains', 'total_resource_slots',
                    existing_type=postgresql.JSONB(astext_type=sa.Text()),
                    nullable=True)
    op.add_column('groups', sa.Column('integration_id', sa.String(length=512), nullable=True))
    op.add_column('groups', sa.Column('total_resource_slots',
                                      postgresql.JSONB(astext_type=sa.Text()), nullable=True))
    op.add_column('users', sa.Column('integration_id', sa.String(length=512), nullable=True))
    # ### end Alembic commandk ###

    print('\nSet group\'s total_resource_slots with empty dictionary.')
    query = textwrap.dedent('''\
        UPDATE groups SET total_resource_slots = '{}'::jsonb;
    ''')
    connection = op.get_bind()
    connection.execute(query) 

def test_compile_jsonb_with_custom_json_encoder():
    jsonb_table = sa.Table(
        'meowsb', sa.MetaData(),
        sa.Column('data', postgresql.JSONB),
    )

    class JSONEncoder(json.JSONEncoder):
        def default(self, o):
            if isinstance(o, uuid.UUID):
                return str(o)
            else:
                return super().default(o)

    dialect = connection.get_dialect(
        json_serializer=partial(json.dumps, cls=JSONEncoder)
    )

    data = {
        'uuid4': uuid.uuid4(),
    }
    query = jsonb_table.insert().values(data=data)
    q, p = connection.compile_query(query, dialect=dialect)
    assert q == 'INSERT INTO meowsb (data) VALUES ($1)'
    assert p == ['{"uuid4": "%s"}' % data['uuid4']] 

def upgrade():
    op.add_column(
        'dataset',
        sa.Column(
            'meta',
            postgresql.JSONB(),
            nullable=True
        )
    )
    op.execute('''
        UPDATE "dataset" SET
            meta = jsonb_set(coalesce(meta, '{}'), '{organization_id}', to_jsonb(organization_id))
        WHERE
            organization_id IS NOT NULL
    ''')
    op.drop_column('dataset', 'organization_id') 

def test_should_postgresql_jsonb_convert():
    assert get_field(postgresql.JSONB()).type == graphene.JSONString 

def get_table_from_django(cls, model, *jsonb, **field_type):
        """Deprecated, use @derive_from_django instead"""
        from .convert import convert_model
        for i in jsonb:
            field_type[i] = JSONB
        table, _ = convert_model(model, **field_type)
        return table 

def _select_related(self, obj, relationship_key, **kwargs):
        mapper = sa.inspect(obj.__class__)
        prop = mapper.relationships[relationship_key]
        model = prop.mapper.class_

        from_obj = kwargs.pop('from_obj', None)
        if from_obj is None:
            from_obj = sa.orm.query.Query(model)

        # SQLAlchemy Query.with_parent throws warning if the primary object
        # foreign key is NULL. Thus we need this ugly magic to return empty
        # data in that scenario.
        if (
            prop.direction.name == 'MANYTOONE' and
            not prop.secondary and
            getattr(obj, prop.local_remote_pairs[0][0].key) is None
        ):
            expr = sa.cast({'data': None}, JSONB)
            if kwargs.get('as_text'):
                expr = sa.cast(expr, sa.Text)
            return sa.select([expr])

        from_obj = from_obj.with_parent(obj, prop)

        if prop.order_by:
            from_obj = from_obj.order_by(*prop.order_by)

        from_obj = from_obj.subquery()

        return SelectExpression(self, model, from_obj).build_select(
            multiple=prop.uselist,
            **kwargs
        ) 

def _write_stats(connection, date, stats):
    """Records a value with a given name and current timestamp."""

    if len(stats) != len(stats_key_map):
        raise ValueError("provided stats map is of unexpected size")
    for k, v in stats.items():
        try:
            int(v)
        except ValueError:
            raise ValueError("value %s in map isn't an integer" % v)
    query = text("""
        INSERT INTO statistics (collected, stats)
             VALUES (:collected, :stats)""").bindparams(bindparam('stats', type_=JSONB))
    connection.execute(query, {"collected": date, "stats": stats}) 

def get_activity_table(schema=None):
    return sa.Table(
        'activity',
        sa.MetaData(),
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('table_name', sa.String),
        sa.Column('verb', sa.String),
        sa.Column('old_data', JSONB),
        sa.Column('changed_data', JSONB),
        schema=schema,
    ) 

def test_jsonb(self):
        self.test_hashable_flag(
            postgresql.JSONB(),
            [
                {"a": "1", "b": "2", "c": "3"},
                {
                    "d": "4",
                    "e": {"e1": "5", "e2": "6"},
                    "f": {"f1": [9, 10, 11]},
                },
            ],
        ) 

def setup(self):
        metadata = MetaData()
        self.test_table = Table(
            "test_table",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("test_column", JSONB),
        )
        self.jsoncol = self.test_table.c.test_column 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("base_result", sa.Column("protocols", postgresql.JSONB(astext_type=sa.Text()), nullable=True))
    op.execute("UPDATE base_result SET protocols='{}'::json")
    op.alter_column("base_result", "protocols", nullable=False)
    op.add_column("result", sa.Column("wavefunction", postgresql.JSONB(astext_type=sa.Text()), nullable=True))
    # ### end Alembic commands ### 

def __init__(self):
        self._types = {
            # Django internal type => SQLAlchemy type
            'ArrayField': SA_ARRAY,
            'AutoField': sa_types.Integer,
            'BigAutoField': sa_types.BigInteger,
            'BigIntegerField': sa_types.BigInteger,
            'BooleanField': sa_types.Boolean,
            'CharField': sa_types.String,
            'DateField': sa_types.Date,
            'DateTimeField': sa_types.DateTime,
            'DecimalField': sa_types.Numeric,
            'DurationField': sa_types.Interval,
            'FileField': sa_types.String,
            'FilePathField': sa_types.String,
            'FloatField': sa_types.Float,
            'GenericIPAddressField': sa_types.String,
            'IntegerField': sa_types.Integer,
            'JSONField': SA_JSONB,
            'NullBooleanField': sa_types.Boolean,
            'PointField': Geometry,
            'PositiveIntegerField': sa_types.Integer,
            'PositiveSmallIntegerField': sa_types.SmallInteger,
            'SlugField': sa_types.String,
            'SmallIntegerField': sa_types.SmallInteger,
            'TextField': sa_types.Text,
            'TimeField': sa_types.Time,
            'UUIDField': SA_UUID,
            # TODO: Add missing GIS fields
        } 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('predictions', 'predictions')
    op.drop_column('predictions', sa.Column('predictions', postgresql.JSONB(), nullable=False))
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('predictions', 'predictions')
    op.add_column('predictions', sa.Column('predictions', postgresql.JSONB(), nullable=False))
    # ### end Alembic commands ### 

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('predictions', sa.Column('predictions', postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=False))
    op.drop_table('prediction_tiles')
    # ### end Alembic commands ### 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('prediction_tiles',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('prediction_id', sa.BigInteger(), nullable=False),
    sa.Column('quadkey', sa.String(), nullable=False),
    sa.Column('centroid', geoalchemy2.types.Geometry(geometry_type='POINT', srid=4326), nullable=True),
    sa.Column('predictions', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.ForeignKeyConstraint(['prediction_id'], ['predictions.id'], name='fk_predictions'),
    sa.PrimaryKeyConstraint('id')
    )
    op.drop_column('predictions', 'predictions')
    # ### end Alembic commands ### 

def __init__(self, api):
        """
        Parameters:
            api: A PyramidJSONAPI class instance
        Attributes:
            views (list): VIEWS named tuples associating methods with views
            column_to_schema (dict): alchemyjsonschema column to schema mapping.
                This defaults to alchemyjsonschema.default_column_to_schema,
                but can be extended or overridden.

            For example, to add a mapping of 'JSONB' to 'string'::
                from sqlalchemy.dialects.postgresql import JSONB
                self.column_to_schema[JSONB] = 'string'
        """

        self.views = [
            VIEWS(
                attr='template',
                route_name='',
                request_method='',
                renderer=''
            ),
            VIEWS(
                attr='resource_attributes_view',
                route_name='resource/{endpoint}',
                request_method='',
                renderer=''
            ),
            VIEWS(
                attr='endpoint_schema_view',
                route_name='endpoint/{endpoint}{sep:/?}{method:.*}',
                request_method='',
                renderer=''
            ),
        ]
        self.api = api
        self.column_to_schema = alchemyjsonschema.default_column_to_schema
        self.schema = {}
        self.schema_post = {}
        self.load_schema()
        self.build_definitions() 

def register_standard_filters(self):
        """Register standard supported filter operators."""
        for comparator_name in (
                '__eq__',
                '__ne__',
                'startswith',
                'endswith',
                'contains',
                '__lt__',
                '__gt__',
                '__le__',
                '__ge__'
        ):
            self.register(comparator_name)
        # Transform '%' to '*' for like and ilike
        for comparator_name in (
                'like',
                'ilike'
        ):
            self.register(
                comparator_name,
                value_transform=lambda val: re.sub(r'\*', '%', val)
            )
        # JSONB specific operators
        for comparator_name in (
                'contains',
                'contained_by',
                'has_all',
                'has_any',
                'has_key'
        ):
            self.register(
                comparator_name,
                column_type=JSONB
            ) 

def upgrade():
    op.create_table('document',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('data', postgresql.JSONB(), nullable=False),
        sa.PrimaryKeyConstraint('id')
    ) 

def upgrade():
    op.create_table('cochrane_reviews',
        sa.Column('meta_id', sa.Text),
        sa.Column('meta_created', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.Column('meta_updated', sa.DateTime(timezone=True), server_default=sa.text('now()')),
        sa.Column('meta_source', sa.Text),

        sa.Column('id', UUID, primary_key=True),
        sa.Column('study_type', sa.Text),
        sa.Column('file_name', sa.Text),
        sa.Column('robs', JSONB),
        sa.Column('study_id', sa.Text),
        sa.Column('refs', JSONB),
        sa.Column('doi_id', sa.Text),
    ) 

def upgrade():
    op.add_column('pubmed', sa.Column('mesh_headings', JSONB)) 

def restore_type(self, type):
        """Restore type from SQL
        """

        # All dialects
        mapping = {
            ARRAY: 'array',
            sa.Boolean: 'boolean',
            sa.Date: 'date',
            sa.DateTime: 'datetime',
            sa.Float: 'number',
            sa.Integer: 'integer',
            JSONB: 'object',
            JSON: 'object',
            sa.Numeric: 'number',
            sa.Text: 'string',
            sa.Time: 'time',
            sa.VARCHAR: 'string',
            UUID: 'string',
        }

        # Get field type
        field_type = None
        for key, value in mapping.items():
            if isinstance(type, key):
                field_type = value

        # Not supported
        if field_type is None:
            message = 'Type "%s" is not supported'
            raise tableschema.exceptions.StorageError(message % type)

        return field_type


# Internal 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.add_column('rule', sa.Column('conditions', postgresql.JSONB(), nullable=True))
    op.add_column('rule', sa.Column('description', sa.String(), nullable=True))
    op.drop_column('rule', 'action')
    op.drop_column('rule', 'type')
    op.drop_column('rule', 'config')
    ### end Alembic commands ###