Python源码示例：sqlalchemy.dialects.postgresql.JSONB
示例1
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),
)
示例2
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)
示例3
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 ###
示例4
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 ###
示例5
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 ###
示例6
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),
)
示例7
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 ###
示例8
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]))
示例9
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']]
示例10
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')
示例11
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('kernels', sa.Column('attached_devices',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True))
# ### end Alembic commands ###
示例12
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('users', 'integration_id')
op.drop_column('groups', 'total_resource_slots')
op.drop_column('groups', 'integration_id')
op.alter_column('domains', 'total_resource_slots',
existing_type=postgresql.JSONB(astext_type=sa.Text()),
nullable=False)
op.drop_column('domains', 'integration_id')
# ### end Alembic commands ###
示例13
def upgrade():
op.add_column('kernels', sa.Column('mount_map', pgsql.JSONB(), nullable=True, default={}))
示例14
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('kernels', sa.Column('resource_opts',
postgresql.JSONB(astext_type=sa.Text()),
nullable=True))
# ### end Alembic commands ###
示例15
def bind_expression(self, value):
return cast(value, JSONB)
示例16
def load_dialect_impl(self, dialect):
if dialect.name == 'postgresql':
return dialect.type_descriptor(JSONB())
elif dialect.name == 'mysql':
return dialect.type_descriptor(types.JSON())
elif dialect.name == 'oracle':
return dialect.type_descriptor(CLOB())
else:
return dialect.type_descriptor(String())
示例17
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('seen_netloc_tracker',
sa.Column('id', sa.BigInteger(), nullable=False),
sa.Column('netloc', citext.CIText(), nullable=False),
sa.Column('ignore', sa.Boolean(), nullable=True),
sa.Column('have', sa.Boolean(), nullable=True),
sa.Column('extra', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_seen_netloc_tracker_netloc'), 'seen_netloc_tracker', ['netloc'], unique=True)
# ### end Alembic commands ###
示例18
def store_hosts(self, hosts):
tmp = self.create_tmp_table(self.tables.scan, extracols=[
Column("scanfileid", ARRAY(LargeBinary(32))),
Column("categories", ARRAY(String(32))),
Column("source", String(32)),
# Column("cpe", postgresql.JSONB),
# Column("extraports", postgresql.JSONB),
Column("hostnames", postgresql.JSONB),
# openports
# Column("os", postgresql.JSONB),
Column("ports", postgresql.JSONB),
# Column("traceroutes", postgresql.JSONB),
])
with ScanCSVFile(hosts, self.ip2internal, tmp) as fdesc:
self.copy_from(fdesc, tmp.name)
示例19
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('user', sa.Column('_held_roles', postgresql.JSONB(astext_type=sa.Text()), nullable=True))
op.drop_column('user', '_is_view')
op.drop_column('user', '_is_supervendor')
op.drop_column('user', '_is_sempo_admin')
op.drop_column('user', '_is_subadmin')
op.drop_column('user', '_is_superadmin')
op.drop_column('user', '_is_admin')
op.drop_column('user', '_is_vendor')
# ### end Alembic commands ###
示例20
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('user', sa.Column('password_reset_tokens', postgresql.JSONB(astext_type=sa.Text()), nullable=True))
# ### end Alembic commands ###
示例21
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('user', sa.Column('failed_pin_attempts', sa.Integer(), nullable=True, default=0))
op.add_column('user', sa.Column('pin_hash', sa.String(), nullable=True))
op.add_column('user', sa.Column('pin_reset_tokens', postgresql.JSONB(astext_type=sa.Text()), nullable=True))
# ### end Alembic commands ###
示例22
def upgrade():
alembic.op.create_table('state',
sqlalchemy.Column('key', sqlalchemy.Text, primary_key=True),
sqlalchemy.Column('value', postgresql.JSONB, nullable=False),
)
示例23
def upgrade():
clips = alembic.op.create_table("clips",
sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True, autoincrement=True),
sqlalchemy.Column("slug", sqlalchemy.String(255), nullable=False),
sqlalchemy.Column("title", sqlalchemy.String(255), nullable=False),
sqlalchemy.Column("vodid", sqlalchemy.String(16), nullable=True),
sqlalchemy.Column("time", sqlalchemy.DateTime(timezone=True), nullable=False),
sqlalchemy.Column("rating", sqlalchemy.Boolean, nullable=True),
sqlalchemy.Column("data", postgresql.JSONB, nullable=False),
)
alembic.op.create_index("clips_slug", "clips", ["slug"], unique=True)
alembic.op.create_index("clips_time", "clips", ["time"])
alembic.op.create_index("clips_vodid", "clips", ["vodid", "time"])
示例24
def _is_column_json(col: MapperProperty) -> bool:
""" Is the column a PostgreSql JSON column? """
return isinstance(_get_column_type(col), (pg.JSON, pg.JSONB))
示例25
def upgrade():
op.add_column(
"project_uploads",
sa.Column("form_data", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
)
op.drop_constraint(
"project_uploads_signature_key", "project_uploads", type_="unique"
)
op.drop_column("project_uploads", "signature")
示例26
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('entity_version',
sa.Column('id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('name', sa.String(length=160), nullable=False),
sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
sa.Column('entity_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
sa.ForeignKeyConstraint(['entity_id'], ['entity.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_entity_version_entity_id'), 'entity_version', ['entity_id'], unique=False)
# ### end Alembic commands ###
示例27
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('comment', sa.Column('checklist', postgresql.JSONB(astext_type=sa.Text()), nullable=True))
op.add_column('comment', sa.Column('pinned', sa.Boolean(), nullable=True))
op.alter_column('entity', 'description',
existing_type=sa.VARCHAR(length=600),
type_=sa.String(length=1200),
existing_nullable=True)
# ### end Alembic commands ###
示例28
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_index(op.f('ix_build_job_playlist_id'), 'build_job', ['playlist_id'], unique=False)
op.drop_index('ix_login_log_created_at', table_name='login_log')
op.add_column('task', sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=True))
op.alter_column('task_type', 'for_entity',
existing_type=sa.VARCHAR(length=10),
type_=sa.String(length=30),
existing_nullable=True)
op.alter_column('task_type', 'short_name',
existing_type=sa.VARCHAR(length=10),
type_=sa.String(length=20),
existing_nullable=True)
# ### end Alembic commands ###
示例29
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('preview_file', sa.Column('annotations', postgresql.JSONB(astext_type=sa.Text()), nullable=True))
# ### end Alembic commands ###
示例30
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('api_event',
sa.Column('id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('name', sa.String(length=80), nullable=False),
sa.Column('user_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), default=uuid.uuid4, nullable=True),
sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['person.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.create_index(op.f('ix_api_event_name'), 'api_event', ['name'], unique=False)
op.create_index(op.f('ix_api_event_user_id'), 'api_event', ['user_id'], unique=False)
# ### end Alembic commands ###
