Python sqlalchemy.LargeBinary() Examples

def upgrade():
    conn = op.get_bind()

    temp_log_table = op.create_table(
        'temp_log',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('result_id', sa.Integer(), nullable=True),
        sa.Column('data', sa.LargeBinary(length=2048), nullable=True),
        sa.ForeignKeyConstraint(['result_id'], ['result.id'], ),
        sa.PrimaryKeyConstraint('id'))
    res = conn.execute('SELECT id, result_id, data FROM log')
    results = res.fetchall()
    if len(results) > 0:
        modified_logs = [{
            'id': r[0],
            'result_id': r[1],
            'data': msgpack.packb(json.loads(r[2]), use_bin_type=True)}
            for r in results]
        op.bulk_insert(temp_log_table, modified_logs)
    op.drop_table('log')
    op.rename_table('temp_log', 'log') 

def test_large_type_deprecation(self):
        d1 = mssql.dialect(deprecate_large_types=True)
        d2 = mssql.dialect(deprecate_large_types=False)
        d3 = mssql.dialect()
        d3.server_version_info = (11, 0)
        d3._setup_version_attributes()
        d4 = mssql.dialect()
        d4.server_version_info = (10, 0)
        d4._setup_version_attributes()

        for dialect in (d1, d3):
            eq_(str(Text().compile(dialect=dialect)), "VARCHAR(max)")
            eq_(str(UnicodeText().compile(dialect=dialect)), "NVARCHAR(max)")
            eq_(str(LargeBinary().compile(dialect=dialect)), "VARBINARY(max)")

        for dialect in (d2, d4):
            eq_(str(Text().compile(dialect=dialect)), "TEXT")
            eq_(str(UnicodeText().compile(dialect=dialect)), "NTEXT")
            eq_(str(LargeBinary().compile(dialect=dialect)), "IMAGE") 

def upgrade():
    if not has_table('runs'):
        return

    if not has_table('snapshots'):
        op.create_table(
            'snapshots',
            sa.Column('id', sa.Integer, primary_key=True, autoincrement=True, nullable=False),
            sa.Column('snapshot_id', sa.String(255), unique=True, nullable=False),
            sa.Column('snapshot_body', sa.LargeBinary, nullable=False),
            sa.Column('snapshot_type', sa.String(63), nullable=False),
        )

    if not has_column('runs', 'snapshot_id'):
        op.add_column(
            'runs',
            sa.Column('snapshot_id', sa.String(255), sa.ForeignKey('snapshots.snapshot_id')),
        ) 

def upgrade():
    if not has_table('runs'):
        return

    if not has_table('snapshots'):
        op.create_table(
            'snapshots',
            sa.Column('id', sa.Integer, primary_key=True, autoincrement=True, nullable=False),
            sa.Column('snapshot_id', sa.String(255), unique=True, nullable=False),
            sa.Column('snapshot_body', sa.LargeBinary, nullable=False),
            sa.Column('snapshot_type', sa.String(63), nullable=False),
        )

    if not has_column('runs', 'snapshot_id'):
        op.add_column(
            'runs',
            sa.Column('snapshot_id', sa.String(255), sa.ForeignKey('snapshots.snapshot_id')),
        ) 

def upgrade():
    if not has_table('runs'):
        return

    if not has_table('snapshots'):
        op.create_table(
            'snapshots',
            sa.Column('id', sa.Integer, primary_key=True, autoincrement=True, nullable=False),
            sa.Column('snapshot_id', sa.String(255), unique=True, nullable=False),
            sa.Column('snapshot_body', sa.LargeBinary, nullable=False),
            sa.Column('snapshot_type', sa.String(63), nullable=False),
        )

    if not has_column('runs', 'snapshot_id'):
        op.add_column(
            'runs',
            sa.Column('snapshot_id', sa.String(255), sa.ForeignKey('snapshots.snapshot_id')),
        ) 

def upgrade():
    op.create_table('scep_payload',
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('url', sa.String(), nullable=False),
                    sa.Column('name', sa.String(), nullable=True),
                    sa.Column('subject', commandment.dbtypes.JSONEncodedDict(), nullable=False),
                    sa.Column('challenge', sa.String(), nullable=True),
                    sa.Column('key_size', sa.Integer(), nullable=False),
                    sa.Column('ca_fingerprint', sa.LargeBinary(), nullable=True),
                    sa.Column('key_type', sa.String(), nullable=False),
                    sa.Column('key_usage', sa.Enum('Signing', 'Encryption', 'All', name='keyusage'), nullable=True),
                    sa.Column('subject_alt_name', sa.String(), nullable=True),
                    sa.Column('retries', sa.Integer(), nullable=False),
                    sa.Column('retry_delay', sa.Integer(), nullable=False),
                    sa.Column('certificate_renewal_time_interval', sa.Integer(), nullable=False),
                    sa.ForeignKeyConstraint(['id'], ['payloads.id'], ),
                    sa.PrimaryKeyConstraint('id')
                    ) 

def upgrade():
    op.create_table('profiles',
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('data', sa.LargeBinary(), nullable=True),
                    sa.Column('payload_type', sa.String(), nullable=True),
                    sa.Column('description', sa.Text(), nullable=True),
                    sa.Column('display_name', sa.String(), nullable=True),
                    sa.Column('expiration_date', sa.DateTime(), nullable=True),
                    sa.Column('identifier', sa.String(), nullable=False),
                    sa.Column('organization', sa.String(), nullable=True),
                    sa.Column('uuid', commandment.dbtypes.GUID(), nullable=True),
                    sa.Column('removal_disallowed', sa.Boolean(), nullable=True),
                    sa.Column('version', sa.Integer(), nullable=True),
                    sa.Column('scope', sa.Enum('User', 'System', name='payloadscope'), nullable=True),
                    sa.Column('removal_date', sa.DateTime(), nullable=True),
                    sa.Column('duration_until_removal', sa.BigInteger(), nullable=True),
                    sa.Column('consent_en', sa.Text(), nullable=True),
                    sa.Column('is_encrypted', sa.Boolean(), nullable=True),
                    sa.PrimaryKeyConstraint('id')
                    )
    op.create_index(op.f('ix_profiles_uuid'), 'profiles', ['uuid'], unique=True) 

def test_comparison(self, connection):
        """test that type coercion occurs on comparison for binary"""

        expr = binary_table.c.data == "foo"
        assert isinstance(expr.right.type, LargeBinary)

        data = os.urandom(32)
        connection.execute(binary_table.insert(), data=data)
        eq_(
            connection.scalar(
                select([func.count("*")])
                .select_from(binary_table)
                .where(binary_table.c.data == data)
            ),
            1,
        ) 

def init_database():

    Base = declarative_base()

    class Model(Base):
        __tablename__ = 'models'
        TS_name = Column(String(250), nullable=False,primary_key=True)
        TS_winner_name = Column(String(250), nullable=False)
        TS_model = Column(LargeBinary())
        TS_model_params = Column(String(250))
        TS_metric = Column(Numeric)
        TS_update = Column('TS_update', DATETIME, index=False, nullable=False,primary_key=True,default=datetime.datetime.utcnow)

    class TS(Base):
        __tablename__ = 'timeseries'
        TS_name = Column(String(250), nullable=False,primary_key=True)
        TS_data = Column(Text())
        TS_update = Column('TS_update', DATETIME, index=False, nullable=False,primary_key=True,default=datetime.datetime.utcnow)


    DB_NAME = 'sqlite:///Timecop_modelsv1.db'
    engine = create_engine(DB_NAME)
    #self.__db.echo = True
    Base.metadata.create_all(engine) 

def upgrade():
    op.create_table(
        'asset',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('result_id', sa.Integer(), nullable=False),
        sa.Column('summary', sa.String(length=1024), nullable=True),
        sa.Column('file_modified_at', sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(['result_id'], ['result.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_table(
        'bindata',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('asset_id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=1024), nullable=False),
        sa.Column('tag', sa.String(length=1024), nullable=True),
        sa.Column('note', sa.String(length=1024), nullable=True),
        # < 10MB
        sa.Column('content', sa.LargeBinary(length=1e7), nullable=False),
        sa.ForeignKeyConstraint(['asset_id'], ['asset.id'], ),
        sa.PrimaryKeyConstraint('id')
    ) 

def _cast(t, expr):
    arg, typ = expr.op().args

    sa_arg = t.translate(arg)
    sa_type = t.get_sqla_type(typ)

    # specialize going from an integer type to a timestamp
    if isinstance(arg.type(), dt.Integer) and isinstance(sa_type, sa.DateTime):
        return sa.func.timezone('UTC', sa.func.to_timestamp(sa_arg))

    if arg.type().equals(dt.binary) and typ.equals(dt.string):
        return sa.func.encode(sa_arg, 'escape')

    if typ.equals(dt.binary):
        #  decode yields a column of memoryview which is annoying to deal with
        # in pandas. CAST(expr AS BYTEA) is correct and returns byte strings.
        return sa.cast(sa_arg, sa.LargeBinary())

    return sa.cast(sa_arg, sa_type) 

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('passentry',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('connectiondate', sa.String(length=20), nullable=True),
    sa.Column('password', sa.LargeBinary(length=500), nullable=True),
    sa.Column('salt', sa.LargeBinary(length=500), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_passentry_connectiondate'), 'passentry', ['connectiondate'], unique=False)
    op.create_index(op.f('ix_passentry_password'), 'passentry', ['password'], unique=False)
    op.create_index(op.f('ix_passentry_salt'), 'passentry', ['salt'], unique=False)
    op.create_table('target_pass',
    sa.Column('target_id', sa.Integer(), nullable=False),
    sa.Column('logentry_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['logentry_id'], ['passentry.id'], ),
    sa.ForeignKeyConstraint(['target_id'], ['target.id'], ),
    sa.PrimaryKeyConstraint('target_id', 'logentry_id')
    )
    # ### end Alembic commands ### 

def test_cache_noleak_on_statement_values(self):
        # This is a non regression test for an object reference leak caused
        # by the compiled_cache.

        metadata = self.metadata
        photo = Table(
            "photo",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("photo_blob", LargeBinary()),
        )
        metadata.create_all()

        conn = testing.db.connect()
        cache = {}
        cached_conn = conn.execution_options(compiled_cache=cache)

        class PhotoBlob(bytearray):
            pass

        blob = PhotoBlob(100)
        ref_blob = weakref.ref(blob)

        ins = photo.insert()
        with patch.object(
            ins, "_compiler", Mock(side_effect=ins._compiler)
        ) as compile_mock:
            cached_conn.execute(ins, {"photo_blob": blob})
        eq_(compile_mock.call_count, 1)
        eq_(len(cache), 1)
        eq_(conn.exec_driver_sql("select count(*) from photo").scalar(), 1)

        del blob

        gc_collect()

        # The compiled statement cache should not hold any reference to the
        # the statement values (only the keys).
        eq_(ref_blob(), None) 

def define_tables(cls, metadata):
        Table(
            "z_test",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", Text),
            Column("bindata", LargeBinary),
        )

        Table(
            "binary_table",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("data", LargeBinary),
        ) 

def init(self, auto_create=True):

        # TODO handle if user does not pass in table sqlite://path.db
        uri_splt = self.uri.split(":")
        engine_uri = ":".join(uri_splt[:-1])
        table_name = uri_splt[-1]

        metadata = MetaData()
        postref_table = Table(table_name, metadata,
                              Column('id', Integer, primary_key=True),
                              Column('created_at', DateTime, default=func.now()),
                              Column('updated_at', DateTime, default=func.now(), onupdate=func.current_timestamp()),
                              Column('uuid', String(512)),
                              Column('path', String(512)),
                              Column('revision', Integer, default=0),
                              Column('status', Integer, default=self.PostStatus.DRAFT.value),
                              Column('ref', String(512)),
                              Column('data', LargeBinary))
        self.engine = create_engine(engine_uri, pool_recycle=3600)
        self.session = scoped_session(sessionmaker(bind=self.engine))
        if auto_create:
            postref_table.create(self.engine, checkfirst=True)

        class PostRef(object):
            pass
        mapper(PostRef, postref_table)
        self.PostRef = PostRef

    # ------------- Repository actions / state ------------------------------------ 

def test_large_binary(self):
        stream1 = self._load_stream("binary_data_one.dat")
        self._test_round_trip(sqltypes.LargeBinary, stream1) 

def test_large_legacy_types(self):
        stream1 = self._load_stream("binary_data_one.dat")
        self._test_round_trip(
            sqltypes.LargeBinary, stream1, deprecate_large_types=False
        ) 

def upgrade():
    Session = op.create_table('telethon_sessions',
                              sa.Column('session_id', sa.String, nullable=False),
                              sa.Column('dc_id', sa.Integer, nullable=False),
                              sa.Column('server_address', sa.String, nullable=True),
                              sa.Column('port', sa.Integer, nullable=True),
                              sa.Column('auth_key', sa.LargeBinary, nullable=True),
                              sa.PrimaryKeyConstraint('session_id', 'dc_id'))
    SentFile = op.create_table('telethon_sent_files',
                               sa.Column('session_id', sa.String, nullable=False),
                               sa.Column('md5_digest', sa.LargeBinary, nullable=False),
                               sa.Column('file_size', sa.Integer, nullable=False),
                               sa.Column('type', sa.Integer, nullable=False),
                               sa.Column('id', sa.BigInteger, nullable=True),
                               sa.Column('hash', sa.BigInteger, nullable=True),
                               sa.PrimaryKeyConstraint('session_id', 'md5_digest', 'file_size',
                                                       'type'))
    Entity = op.create_table('telethon_entities',
                             sa.Column('session_id', sa.String, nullable=False),
                             sa.Column('id', sa.Integer, nullable=False),
                             sa.Column('hash', sa.Integer, nullable=False),
                             sa.Column('username', sa.String, nullable=True),
                             sa.Column('phone', sa.Integer, nullable=True),
                             sa.Column('name', sa.String, nullable=True),
                             sa.PrimaryKeyConstraint('session_id', 'id'))
    Version = op.create_table('telethon_version',
                              sa.Column('version', sa.Integer, nullable=False),
                              sa.PrimaryKeyConstraint('version'))
    conn = op.get_bind()
    sessions = [os.path.basename(f) for f in os.listdir(".") if f.endswith(".session")]
    for session in sessions:
        session_to_sqlalchemy(conn, session, Session, SentFile, Entity) 

def test_binary_types(self):
        specs = [
            (LargeBinary(3), mysql.TINYBLOB()),
            (LargeBinary(), mysql.BLOB()),
            (mysql.MSBinary(3), mysql.MSBinary(3)),
            (mysql.MSVarBinary(3), mysql.MSVarBinary(3)),
            (mysql.MSTinyBlob(), mysql.MSTinyBlob()),
            (mysql.MSBlob(), mysql.MSBlob()),
            (mysql.MSBlob(1234), mysql.MSBlob()),
            (mysql.MSMediumBlob(), mysql.MSMediumBlob()),
            (mysql.MSLongBlob(), mysql.MSLongBlob()),
        ]
        self._run_test(specs, []) 

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('id', sa.Unicode(length=30), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('directories',
    sa.Column('user_id', sa.Unicode(length=30), nullable=False),
    sa.Column('name', sa.Unicode(length=70), nullable=False),
    sa.Column('parent_user_id', sa.Unicode(length=30), nullable=True),
    sa.Column('parent_name', sa.Unicode(length=70), nullable=True),
    sa.CheckConstraint(u"left(name, 1) = '/'", name=u'directories_startwith_slash'),
    sa.CheckConstraint(u"length(regexp_replace(name, '[^/]+', '', 'g')) - 1= length(regexp_replace(parent_name, '[^/]+', '', 'g'))", name=u'directories_slash_count'),
    sa.CheckConstraint(u"right(name, 1) = '/'", name=u'directories_endwith_slash'),
    sa.CheckConstraint(u'(parent_name IS NULL AND parent_user_id IS NULL) OR (parent_name IS NOT NULL AND parent_user_id IS NOT NULL)', name=u'directories_null_user_id_match'),
    sa.CheckConstraint(u'position(parent_name in name) != 0', name=u'directories_parent_name_prefix'),
    sa.CheckConstraint(u'user_id = parent_user_id', name=u'directories_match_user_id'),
    sa.ForeignKeyConstraint(['parent_user_id', 'parent_name'], [u'directories.user_id', u'directories.name'], ),
    sa.ForeignKeyConstraint(['user_id'], [u'users.id'], ),
    sa.PrimaryKeyConstraint('user_id', 'name')
    )
    op.create_table('files',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.Unicode(length=40), nullable=False),
    sa.Column('user_id', sa.Unicode(length=30), nullable=False),
    sa.Column('parent_name', sa.Unicode(length=70), nullable=False),
    sa.Column('content', sa.LargeBinary(length=100000), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['user_id', 'parent_name'], [u'directories.user_id', u'directories.name'], ),
    sa.ForeignKeyConstraint(['user_id'], [u'users.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('checkpoints',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('file_id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), nullable=False),
    sa.ForeignKeyConstraint(['file_id'], [u'files.id'], onupdate=u'CASCADE', ondelete=u'CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ### 

def test_python_type(self):
        eq_(types.Integer().python_type, int)
        eq_(types.Numeric().python_type, decimal.Decimal)
        eq_(types.Numeric(asdecimal=False).python_type, float)
        eq_(types.LargeBinary().python_type, util.binary_type)
        eq_(types.Float().python_type, float)
        eq_(types.Interval().python_type, datetime.timedelta)
        eq_(types.Date().python_type, datetime.date)
        eq_(types.DateTime().python_type, datetime.datetime)
        eq_(types.String().python_type, str)
        eq_(types.Unicode().python_type, util.text_type)
        eq_(types.Enum("one", "two", "three").python_type, str)

        assert_raises(
            NotImplementedError, lambda: types.TypeEngine().python_type
        ) 

def test_literal_roundtrip(self, connection):
        compiled = select([cast(literal(util.b("foo")), LargeBinary)]).compile(
            dialect=testing.db.dialect, compile_kwargs={"literal_binds": True}
        )
        result = connection.execute(compiled)
        eq_(result.scalar(), util.b("foo")) 

def test_bind_processor_no_dbapi(self):
        b = LargeBinary()
        eq_(b.bind_processor(default.DefaultDialect()), None) 

def test_in_29(self):
        a, b, c = (
            column("a", Integer),
            column("b", String),
            column("c", LargeBinary),
        )
        t1 = tuple_(a, b, c)
        expr = t1.in_([(3, "hi", "there"), (4, "Q", "P")])
        self.assert_compile(
            expr,
            "(a, b, c) IN ([POSTCOMPILE_param_1])",
            checkparams={"param_1": [(3, "hi", "there"), (4, "Q", "P")]},
        ) 

def test_type_coercion_on_eq(self):
        a, b, c = (
            column("a", Integer),
            column("b", String),
            column("c", LargeBinary),
        )
        t1 = tuple_(a, b, c)
        expr = t1 == (3, "hi", "there")
        self._assert_types([bind.type for bind in expr.right.element.clauses]) 

def test_type_coercion_on_in(self):
        a, b, c = (
            column("a", Integer),
            column("b", String),
            column("c", LargeBinary),
        )
        t1 = tuple_(a, b, c)
        expr = t1.in_([(3, "hi", "there"), (4, "Q", "P")])

        eq_(len(expr.right.value), 2)
        self._assert_types(expr.right._expanding_in_types) 

def define_tables(cls, metadata):
        Table(
            "t1",
            metadata,
            Column(
                "id",
                sa.Integer,
                primary_key=True,
                test_needs_autoincrement=True,
            ),
            Column("data", sa.LargeBinary),
        ) 

def LargeBinary():
    """Return a fixed size binary array column type.

    Returns:
      A column type.
    """
    return sql.LargeBinary().with_variant(sql.LargeBinary(2 ** 31), "mysql") 

def upgrade():
    if not has_table('snapshots'):
        op.create_table(
            'snapshots',
            sa.Column('id', sa.Integer, primary_key=True, autoincrement=True, nullable=False),
            sa.Column('snapshot_id', sa.String(255), unique=True, nullable=False),
            sa.Column('snapshot_body', sa.LargeBinary, nullable=False),
            sa.Column('snapshot_type', sa.String(63), nullable=False),
        )

    if not has_column('runs', 'snapshot_id'):
        # Sqlite does not support adding foreign keys to existing
        # tables, so we are forced to fallback on this witchcraft.
        # See https://alembic.sqlalchemy.org/en/latest/batch.html#dealing-with-referencing-foreign-keys
        # for additional context
        with op.batch_alter_table('runs') as batch_op:
            batch_op.execute('PRAGMA foreign_keys = OFF;')
            batch_op.add_column(
                sa.Column(
                    'snapshot_id',
                    sa.String(255),
                    sa.ForeignKey(
                        'snapshots.snapshot_id', name='fk_runs_snapshot_id_snapshots_snapshot_id'
                    ),
                ),
            )
        op.execute('PRAGMA foreign_keys = ON;') 

def upgrade():
    op.add_column('kernels', sa.Column('container_log', sa.LargeBinary(), nullable=True))