def test_binary_none(self):
# TODO: pyodbc does not seem to accept "None" for a VARBINARY
# column (data=None). error: [Microsoft][ODBC SQL Server
# Driver][SQL Server]Implicit conversion from data type varchar
# to varbinary is not allowed. Use the CONVERT function to run
# this query. (257) binary_table.insert().execute(primary_id=3,
# misc='binary_data_two.dat', data=None, data_image=None,
# data_slice=stream2[0:99], pickled=None)
stream2 = self.load_stream('binary_data_two.dat')
binary_table.insert().execute(
primary_id=3,
misc='binary_data_two.dat', data_image=None,
data_slice=stream2[0:99], pickled=None)
for stmt in \
binary_table.select(), \
text(
'select * from binary_table',
typemap=dict(
data=mssql.MSVarBinary(8000),
data_image=mssql.MSImage,
data_slice=types.BINARY(100), pickled=PickleType,
mypickle=MyPickleType),
bind=testing.db):
row = stmt.execute().first()
eq_(
row['pickled'], None
)
eq_(
row['data_image'], None
)
eq_(
row['data_slice'], stream2[0:99]
)
def _test_binary_none(self, deprecate_large_types):
engine = engines.testing_engine(
options={"deprecate_large_types": deprecate_large_types})
binary_table = self._fixture(engine)
stream2 = self._load_stream('binary_data_two.dat')
with engine.connect() as conn:
conn.execute(binary_table.insert(),
primary_id=3,
misc='binary_data_two.dat',
data_image=None,
data_slice=stream2[0:99],
pickled=None)
for stmt in \
binary_table.select(), \
text(
'select * from binary_table',
typemap=dict(
data=mssql.MSVarBinary(8000),
data_image=mssql.MSImage,
data_slice=types.BINARY(100),
pickled=PickleType,
mypickle=MyPickleType),
bind=testing.db):
row = conn.execute(stmt).first()
eq_(row['pickled'], None)
eq_(row['data_image'], None)
# the type we used here is 100 bytes
# so we will get 100 bytes zero-padded
paddedstream = list(stream2[0:99])
paddedstream.extend(['\x00'] * (100 - len(paddedstream)))
eq_(list(row['data_slice']), paddedstream)
def setup_class(cls):
global binary_table, MyPickleType
class MyPickleType(types.TypeDecorator):
impl = PickleType
def process_bind_param(self, value, dialect):
if value:
value.stuff = 'this is modified stuff'
return value
def process_result_value(self, value, dialect):
if value:
value.stuff = 'this is the right stuff'
return value
binary_table = Table(
'binary_table',
MetaData(testing.db),
Column('primary_id', Integer, Sequence('binary_id_seq',
optional=True), primary_key=True),
Column('data', mssql.MSVarBinary(8000)),
Column('data_image', mssql.MSImage),
Column('data_slice', types.BINARY(100)),
Column('misc', String(30)),
Column('pickled', PickleType),
Column('mypickle', MyPickleType),
)
binary_table.create()
def setup_class(cls):
global binary_table, MyPickleType
class MyPickleType(types.TypeDecorator):
impl = PickleType
def process_bind_param(self, value, dialect):
if value:
value.stuff = 'this is modified stuff'
return value
def process_result_value(self, value, dialect):
if value:
value.stuff = 'this is the right stuff'
return value
binary_table = Table(
'binary_table',
MetaData(testing.db),
Column('primary_id',
Integer,
Sequence('binary_id_seq', optional=True),
primary_key=True),
Column('data', mssql.MSVarBinary(8000)),
Column('data_image', mssql.MSImage),
Column('data_slice', Binary(100)),
Column('misc', String(30)),
# construct PickleType with non-native pickle module, since cPickle uses relative module
# loading and confuses this test's parent package 'sql' with the 'sqlalchemy.sql' package relative
# to the 'types' module
Column('pickled', PickleType),
Column('mypickle', MyPickleType))
binary_table.create()
def test_binary(self):
testobj1 = pickleable.Foo('im foo 1')
testobj2 = pickleable.Foo('im foo 2')
testobj3 = pickleable.Foo('im foo 3')
stream1 = self.load_stream('binary_data_one.dat')
stream2 = self.load_stream('binary_data_two.dat')
binary_table.insert().execute(
primary_id=1,
misc='binary_data_one.dat',
data=stream1,
data_image=stream1,
data_slice=stream1[0:100],
pickled=testobj1,
mypickle=testobj3,
)
binary_table.insert().execute(
primary_id=2,
misc='binary_data_two.dat',
data=stream2,
data_image=stream2,
data_slice=stream2[0:99],
pickled=testobj2,
)
# TODO: pyodbc does not seem to accept "None" for a VARBINARY
# column (data=None). error: [Microsoft][ODBC SQL Server
# Driver][SQL Server]Implicit conversion from data type varchar
# to varbinary is not allowed. Use the CONVERT function to run
# this query. (257) binary_table.insert().execute(primary_id=3,
# misc='binary_data_two.dat', data=None, data_image=None,
# data_slice=stream2[0:99], pickled=None)
binary_table.insert().execute(
primary_id=3,
misc='binary_data_two.dat', data_image=None,
data_slice=stream2[0:99], pickled=None)
for stmt in \
binary_table.select(order_by=binary_table.c.primary_id), \
text(
'select * from binary_table order by '
'binary_table.primary_id',
typemap=dict(
data=mssql.MSVarBinary(8000),
data_image=mssql.MSImage,
data_slice=types.BINARY(100), pickled=PickleType,
mypickle=MyPickleType),
bind=testing.db):
l = stmt.execute().fetchall()
eq_(list(stream1), list(l[0]['data']))
paddedstream = list(stream1[0:100])
paddedstream.extend(['\x00'] * (100 - len(paddedstream)))
eq_(paddedstream, list(l[0]['data_slice']))
eq_(list(stream2), list(l[1]['data']))
eq_(list(stream2), list(l[1]['data_image']))
eq_(testobj1, l[0]['pickled'])
eq_(testobj2, l[1]['pickled'])
eq_(testobj3.moredata, l[0]['mypickle'].moredata)
eq_(l[0]['mypickle'].stuff, 'this is the right stuff')
def _test_binary(self, deprecate_large_types):
testobj1 = pickleable.Foo('im foo 1')
testobj2 = pickleable.Foo('im foo 2')
testobj3 = pickleable.Foo('im foo 3')
stream1 = self._load_stream('binary_data_one.dat')
stream2 = self._load_stream('binary_data_two.dat')
engine = engines.testing_engine(
options={"deprecate_large_types": deprecate_large_types})
binary_table = self._fixture(engine)
with engine.connect() as conn:
conn.execute(
binary_table.insert(),
primary_id=1,
misc='binary_data_one.dat',
data=stream1,
data_image=stream1,
data_slice=stream1[0:100],
pickled=testobj1,
mypickle=testobj3,
)
conn.execute(
binary_table.insert(),
primary_id=2,
misc='binary_data_two.dat',
data=stream2,
data_image=stream2,
data_slice=stream2[0:99],
pickled=testobj2,
)
for stmt in \
binary_table.select(order_by=binary_table.c.primary_id), \
text(
'select * from binary_table order by '
'binary_table.primary_id',
typemap=dict(
data=mssql.MSVarBinary(8000),
data_image=mssql.MSImage,
data_slice=types.BINARY(100), pickled=PickleType,
mypickle=MyPickleType),
bind=testing.db):
with engine.connect() as conn:
result = conn.execute(stmt).fetchall()
eq_(list(stream1), list(result[0]['data']))
paddedstream = list(stream1[0:100])
paddedstream.extend(['\x00'] * (100 - len(paddedstream)))
eq_(paddedstream, list(result[0]['data_slice']))
eq_(list(stream2), list(result[1]['data']))
eq_(list(stream2), list(result[1]['data_image']))
eq_(testobj1, result[0]['pickled'])
eq_(testobj2, result[1]['pickled'])
eq_(testobj3.moredata, result[0]['mypickle'].moredata)
eq_(result[0]['mypickle'].stuff, 'this is the right stuff')
def _fixture(self, engine):
self.binary_table = binary_table = Table(
'binary_table',
MetaData(),
Column('primary_id', Integer, Sequence('binary_id_seq',
optional=True), primary_key=True),
Column('data', mssql.MSVarBinary(8000)),
Column('data_image', mssql.MSImage),
Column('data_slice', types.BINARY(100)),
Column('misc', String(30)),
Column('pickled', PickleType),
Column('mypickle', MyPickleType),
)
binary_table.create(engine)
return binary_table
def test_binary(self):
testobj1 = pickleable.Foo('im foo 1')
testobj2 = pickleable.Foo('im foo 2')
testobj3 = pickleable.Foo('im foo 3')
stream1 = self.load_stream('binary_data_one.dat')
stream2 = self.load_stream('binary_data_two.dat')
binary_table.insert().execute(
primary_id=1,
misc='binary_data_one.dat',
data=stream1,
data_image=stream1,
data_slice=stream1[0:100],
pickled=testobj1,
mypickle=testobj3,
)
binary_table.insert().execute(
primary_id=2,
misc='binary_data_two.dat',
data=stream2,
data_image=stream2,
data_slice=stream2[0:99],
pickled=testobj2,
)
for stmt in \
binary_table.select(order_by=binary_table.c.primary_id), \
text(
'select * from binary_table order by '
'binary_table.primary_id',
typemap=dict(
data=mssql.MSVarBinary(8000),
data_image=mssql.MSImage,
data_slice=types.BINARY(100), pickled=PickleType,
mypickle=MyPickleType),
bind=testing.db):
l = stmt.execute().fetchall()
eq_(list(stream1), list(l[0]['data']))
paddedstream = list(stream1[0:100])
paddedstream.extend(['\x00'] * (100 - len(paddedstream)))
eq_(paddedstream, list(l[0]['data_slice']))
eq_(list(stream2), list(l[1]['data']))
eq_(list(stream2), list(l[1]['data_image']))
eq_(testobj1, l[0]['pickled'])
eq_(testobj2, l[1]['pickled'])
eq_(testobj3.moredata, l[0]['mypickle'].moredata)
eq_(l[0]['mypickle'].stuff, 'this is the right stuff')
def test_character_binary(self):
self._test_round_trip(mssql.MSVarBinary(800), b("some normal data"))
