def test_rowcount_flag(self):
metadata = self.metadata
engine = engines.testing_engine(options={"enable_rowcount": True})
assert engine.dialect.supports_sane_rowcount
metadata.bind = engine
t = Table("t1", metadata, Column("data", String(10)))
metadata.create_all()
r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"})
r = t.update().where(t.c.data == "d2").values(data="d3").execute()
eq_(r.rowcount, 1)
r = t.delete().where(t.c.data == "d3").execute()
eq_(r.rowcount, 2)
r = t.delete().execution_options(enable_rowcount=False).execute()
eq_(r.rowcount, -1)
engine.dispose()
engine = engines.testing_engine(options={"enable_rowcount": False})
assert not engine.dialect.supports_sane_rowcount
metadata.bind = engine
r = t.insert().execute({"data": "d1"}, {"data": "d2"}, {"data": "d3"})
r = t.update().where(t.c.data == "d2").values(data="d3").execute()
eq_(r.rowcount, -1)
r = t.delete().where(t.c.data == "d3").execute()
eq_(r.rowcount, -1)
r = t.delete().execution_options(enable_rowcount=True).execute()
eq_(r.rowcount, 1)
r.close()
engine.dispose()
def test_foreignkey_missing_insert(self):
t1 = Table('t1', metadata, Column('id', Integer,
primary_key=True))
t2 = Table(
't2',
metadata,
Column(
'id',
Integer,
ForeignKey('t1.id'),
primary_key=True))
metadata.create_all()
# want to ensure that "null value in column "id" violates not-
# null constraint" is raised (IntegrityError on psycoopg2, but
# ProgrammingError on pg8000), and not "ProgrammingError:
# (ProgrammingError) relationship "t2_id_seq" does not exist".
# the latter corresponds to autoincrement behavior, which is not
# the case here due to the foreign key.
for eng in [
engines.testing_engine(options={'implicit_returning': False}),
engines.testing_engine(options={'implicit_returning': True})
]:
assert_raises_message(exc.DBAPIError,
'violates not-null constraint',
eng.execute, t2.insert())
def _init_dbs(self):
db1 = testing_engine("sqlite:///shard1.db", options=dict(pool_threadlocal=True))
db2 = testing_engine("sqlite:///shard2.db")
db3 = testing_engine("sqlite:///shard3.db")
db4 = testing_engine("sqlite:///shard4.db")
return db1, db2, db3, db4
def test_rowcount_flag(self):
metadata = self.metadata
engine = engines.testing_engine(
options={'enable_rowcount': True})
assert engine.dialect.supports_sane_rowcount
metadata.bind = engine
t = Table('t1', metadata, Column('data', String(10)))
metadata.create_all()
r = t.insert().execute({'data': 'd1'}, {'data': 'd2'}, {'data'
: 'd3'})
r = t.update().where(t.c.data == 'd2').values(data='d3'
).execute()
eq_(r.rowcount, 1)
r = t.delete().where(t.c.data == 'd3').execute()
eq_(r.rowcount, 2)
r = \
t.delete().execution_options(enable_rowcount=False).execute()
eq_(r.rowcount, -1)
engine.dispose()
engine = engines.testing_engine(options={'enable_rowcount'
: False})
assert not engine.dialect.supports_sane_rowcount
metadata.bind = engine
r = t.insert().execute({'data': 'd1'}, {'data': 'd2'}, {'data'
: 'd3'})
r = t.update().where(t.c.data == 'd2').values(data='d3'
).execute()
eq_(r.rowcount, -1)
r = t.delete().where(t.c.data == 'd3').execute()
eq_(r.rowcount, -1)
r = t.delete().execution_options(enable_rowcount=True).execute()
eq_(r.rowcount, 1)
r.close()
engine.dispose()
def test_bind_arguments(self):
users, Address, addresses, User = (self.tables.users,
self.classes.Address,
self.tables.addresses,
self.classes.User)
mapper(User, users)
mapper(Address, addresses)
e1 = engines.testing_engine()
e2 = engines.testing_engine()
e3 = engines.testing_engine()
sess = Session(e3)
sess.bind_mapper(User, e1)
sess.bind_mapper(Address, e2)
assert sess.connection().engine is e3
assert sess.connection(bind=e1).engine is e1
assert sess.connection(mapper=Address, bind=e1).engine is e1
assert sess.connection(mapper=Address).engine is e2
assert sess.connection(clause=addresses.select()).engine is e2
assert sess.connection(mapper=User,
clause=addresses.select()).engine is e1
assert sess.connection(mapper=User,
clause=addresses.select(),
bind=e2).engine is e2
sess.close()
def test_engine_param_stays(self):
eng = testing_engine()
isolation_level = eng.dialect.get_isolation_level(
eng.connect().connection)
level = self._non_default_isolation_level()
ne_(isolation_level, level)
eng = testing_engine(options=dict(isolation_level=level))
eq_(
eng.dialect.get_isolation_level(
eng.connect().connection),
level
)
# check that it stays
conn = eng.connect()
eq_(
eng.dialect.get_isolation_level(conn.connection),
level
)
conn.close()
conn = eng.connect()
eq_(
eng.dialect.get_isolation_level(conn.connection),
level
)
conn.close()
def test_foreignkey_missing_insert(self):
Table("t1", self.metadata, Column("id", Integer, primary_key=True))
t2 = Table(
"t2",
self.metadata,
Column("id", Integer, ForeignKey("t1.id"), primary_key=True),
)
self.metadata.create_all()
# want to ensure that "null value in column "id" violates not-
# null constraint" is raised (IntegrityError on psycoopg2, but
# ProgrammingError on pg8000), and not "ProgrammingError:
# (ProgrammingError) relationship "t2_id_seq" does not exist".
# the latter corresponds to autoincrement behavior, which is not
# the case here due to the foreign key.
for eng in [
engines.testing_engine(options={"implicit_returning": False}),
engines.testing_engine(options={"implicit_returning": True}),
]:
with expect_warnings(
".*has no Python-side or server-side default.*"
):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
eng.execute,
t2.insert(),
)
def _test_lastrow_accessor(self, table_, values, assertvalues):
"""Tests the inserted_primary_key and lastrow_has_id() functions."""
def insert_values(engine, table_, values):
"""
Inserts a row into a table, returns the full list of values
INSERTed including defaults that fired off on the DB side and
detects rows that had defaults and post-fetches.
"""
# verify implicit_returning is working
if engine.dialect.implicit_returning:
ins = table_.insert()
comp = ins.compile(engine, column_keys=list(values))
if not set(values).issuperset(
c.key for c in table_.primary_key
):
is_(bool(comp.returning), True)
result = engine.execute(table_.insert(), **values)
ret = values.copy()
for col, id_ in zip(
table_.primary_key, result.inserted_primary_key
):
ret[col.key] = id_
if result.lastrow_has_defaults():
criterion = and_(
*[
col == id_
for col, id_ in zip(
table_.primary_key, result.inserted_primary_key
)
]
)
row = engine.execute(table_.select(criterion)).first()
for c in table_.c:
ret[c.key] = row[c]
return ret
if testing.against("firebird", "postgresql", "oracle", "mssql"):
assert testing.db.dialect.implicit_returning
if testing.db.dialect.implicit_returning:
test_engines = [
engines.testing_engine(options={"implicit_returning": False}),
engines.testing_engine(options={"implicit_returning": True}),
]
else:
test_engines = [testing.db]
for engine in test_engines:
try:
table_.create(bind=engine, checkfirst=True)
i = insert_values(engine, table_, values)
eq_(i, assertvalues)
finally:
table_.drop(bind=engine)
def _init_dbs(self):
db1 = testing_engine(
'sqlite:///shard1_%s.db' % provision.FOLLOWER_IDENT,
options=dict(pool_threadlocal=True))
db2 = testing_engine(
'sqlite:///shard2_%s.db' % provision.FOLLOWER_IDENT)
db3 = testing_engine(
'sqlite:///shard3_%s.db' % provision.FOLLOWER_IDENT)
db4 = testing_engine(
'sqlite:///shard4_%s.db' % provision.FOLLOWER_IDENT)
self.dbs = [db1, db2, db3, db4]
return self.dbs
def _init_dbs(self):
self.db1 = db1 = testing_engine(
'sqlite:///shard1_%s.db' % provision.FOLLOWER_IDENT,
options=dict(pool_threadlocal=True))
self.db2 = db2 = testing_engine(
'sqlite:///shard2_%s.db' % provision.FOLLOWER_IDENT)
for db in (db1, db2):
self.metadata.create_all(db)
self.dbs = [db1, db2]
return self.dbs
def _init_dbs(self):
self.db1 = db1 = testing_engine(
"sqlite:///shard1_%s.db" % provision.FOLLOWER_IDENT
)
self.db2 = db2 = testing_engine(
"sqlite:///shard2_%s.db" % provision.FOLLOWER_IDENT
)
for db in (db1, db2):
self.metadata.create_all(db)
self.dbs = [db1, db2]
return self.dbs
def _assert_data_with_sequence_returning(self, table, seqname):
engine = engines.testing_engine(options={"implicit_returning": True})
with self.sql_execution_asserter(engine) as asserter:
with engine.connect() as conn:
conn.execute(table.insert(), {"id": 30, "data": "d1"})
conn.execute(table.insert(), {"data": "d2"})
conn.execute(table.insert(), {"id": 31, "data": "d3"}, {"id": 32, "data": "d4"})
conn.execute(table.insert(), {"data": "d5"}, {"data": "d6"})
conn.execute(table.insert(inline=True), {"id": 33, "data": "d7"})
conn.execute(table.insert(inline=True), {"data": "d8"})
asserter.assert_(
DialectSQL("INSERT INTO testtable (id, data) VALUES (:id, :data)", {"id": 30, "data": "d1"}),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES " "(nextval('my_seq'), :data) RETURNING testtable.id",
{"data": "d2"},
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{"id": 31, "data": "d3"}, {"id": 32, "data": "d4"}],
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), " ":data)" % seqname,
[{"data": "d5"}, {"data": "d6"}],
),
DialectSQL("INSERT INTO testtable (id, data) VALUES (:id, :data)", [{"id": 33, "data": "d7"}]),
DialectSQL("INSERT INTO testtable (id, data) VALUES (nextval('%s'), " ":data)" % seqname, [{"data": "d8"}]),
)
with engine.connect() as conn:
eq_(
conn.execute(table.select()).fetchall(),
[(30, "d1"), (1, "d2"), (31, "d3"), (32, "d4"), (2, "d5"), (3, "d6"), (33, "d7"), (4, "d8")],
)
def test_levels(self):
e1 = engines.testing_engine()
eq_(e1._should_log_info(), False)
eq_(e1._should_log_debug(), False)
eq_(e1.logger.isEnabledFor(logging.INFO), False)
eq_(e1.logger.getEffectiveLevel(), logging.WARN)
e1.echo = True
eq_(e1._should_log_info(), True)
eq_(e1._should_log_debug(), False)
eq_(e1.logger.isEnabledFor(logging.INFO), True)
eq_(e1.logger.getEffectiveLevel(), logging.INFO)
e1.echo = "debug"
eq_(e1._should_log_info(), True)
eq_(e1._should_log_debug(), True)
eq_(e1.logger.isEnabledFor(logging.DEBUG), True)
eq_(e1.logger.getEffectiveLevel(), logging.DEBUG)
e1.echo = False
eq_(e1._should_log_info(), False)
eq_(e1._should_log_debug(), False)
eq_(e1.logger.isEnabledFor(logging.INFO), False)
eq_(e1.logger.getEffectiveLevel(), logging.WARN)
def test_flag_on(self):
t = Table(
"t",
self.metadata,
Column("id", Integer, primary_key=True),
Column("data", String(50)),
)
t.create()
eng = engines.testing_engine(options={"fast_executemany": True})
@event.listens_for(eng, "after_cursor_execute")
def after_cursor_execute(
conn, cursor, statement, parameters, context, executemany
):
if executemany:
assert cursor.fast_executemany
with eng.connect() as conn:
conn.execute(
t.insert(),
[{"id": i, "data": "data_%d" % i} for i in range(100)],
)
conn.execute(t.insert(), {"id": 200, "data": "data_200"})
def _engine_uri(options, file_config):
from sqlalchemy.testing import engines, config
from sqlalchemy import testing
if options.dburi:
db_urls = list(options.dburi)
else:
db_urls = []
if options.db:
for db_token in options.db:
for db in re.split(r'[,\s]+', db_token):
if db not in file_config.options('db'):
raise RuntimeError(
"Unknown URI specifier '%s'. Specify --dbs for known uris."
% db)
else:
db_urls.append(file_config.get('db', db))
if not db_urls:
db_urls.append(file_config.get('db', 'default'))
for db_url in db_urls:
eng = engines.testing_engine(db_url, db_opts)
eng.connect().close()
config.Config.register(eng, db_opts, options, file_config, testing)
config.db_opts = db_opts
def test_ad_hoc_types(self):
"""test storage of bind processors, result processors
in dialect-wide registry."""
from sqlalchemy.dialects import mysql, postgresql, sqlite
from sqlalchemy import types
eng = engines.testing_engine()
for args in (
(types.Integer,),
(types.String,),
(types.PickleType,),
(types.Enum, "a", "b", "c"),
(sqlite.DATETIME,),
(postgresql.ENUM, "a", "b", "c"),
(types.Interval,),
(postgresql.INTERVAL,),
(mysql.VARCHAR,),
):
@profile_memory()
def go():
type_ = args[0](*args[1:])
bp = type_._cached_bind_processor(eng.dialect)
rp = type_._cached_result_processor(eng.dialect, 0)
bp, rp # strong reference
go()
assert not eng.dialect._type_memos
def go():
engine = engines.testing_engine(
options={'logging_name': 'FOO',
'pool_logging_name': 'BAR',
'use_reaper': False}
)
sess = create_session(bind=engine)
a1 = A(col2="a1")
a2 = A(col2="a2")
a3 = A(col2="a3")
a1.bs.append(B(col2="b1"))
a1.bs.append(B(col2="b2"))
a3.bs.append(B(col2="b3"))
for x in [a1, a2, a3]:
sess.add(x)
sess.flush()
sess.expunge_all()
alist = sess.query(A).all()
eq_(
[
A(col2="a1", bs=[B(col2="b1"), B(col2="b2")]),
A(col2="a2", bs=[]),
A(col2="a3", bs=[B(col2="b3")])
],
alist)
for a in alist:
sess.delete(a)
sess.flush()
sess.close()
engine.dispose()
def test_reset_rollback_two_phase_no_rollback(self):
# test [ticket:2907], essentially that the
# TwoPhaseTransaction is given the job of "reset on return"
# so that picky backends like MySQL correctly clear out
# their state when a connection is closed without handling
# the transaction explicitly.
eng = testing_engine()
# MySQL raises if you call straight rollback() on
# a connection with an XID present
@event.listens_for(eng, "invalidate")
def conn_invalidated(dbapi_con, con_record, exception):
dbapi_con.close()
raise exception
with eng.connect() as conn:
rec = conn.connection._connection_record
raw_dbapi_con = rec.connection
xa = conn.begin_twophase()
conn.execute(users.insert(), user_id=1, user_name='user1')
assert rec.connection is raw_dbapi_con
with eng.connect() as conn:
result = \
conn.execute(select([users.c.user_name]).
order_by(users.c.user_id))
eq_(result.fetchall(), [])
def test_large_stream_single_arraysize(self):
binary_table = self.tables.binary_table
eng = testing_engine(options={"arraysize": 1})
result = eng.execute(
binary_table.select().order_by(binary_table.c.id)
).fetchall()
eq_(result, [(i, self.stream) for i in range(1, 11)])
def test_row_case_sensitive_unoptimized(self):
ins_db = engines.testing_engine(options={"case_sensitive": True})
row = ins_db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive"),
text("3 AS screw_up_the_cols")
])
).first()
eq_(
list(row.keys()),
["case_insensitive", "CaseSensitive", "screw_up_the_cols"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
not_in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
eq_(row["screw_up_the_cols"], 3)
assert_raises(KeyError, lambda: row["Case_insensitive"])
assert_raises(KeyError, lambda: row["casesensitive"])
assert_raises(KeyError, lambda: row["screw_UP_the_cols"])
def test_coerce_to_unicode(self):
engine = testing_engine(options=dict(coerce_to_unicode=True))
value = engine.scalar("SELECT 'hello' FROM DUAL")
assert isinstance(value, util.text_type)
value = testing.db.scalar("SELECT 'hello' FROM DUAL")
assert isinstance(value, util.binary_type)
def test_per_connection(self):
from sqlalchemy.pool import QueuePool
eng = testing_engine(options=dict(
poolclass=QueuePool,
pool_size=2, max_overflow=0))
c1 = eng.connect()
c1 = c1.execution_options(
isolation_level=self._non_default_isolation_level()
)
c2 = eng.connect()
eq_(
eng.dialect.get_isolation_level(c1.connection),
self._non_default_isolation_level()
)
eq_(
eng.dialect.get_isolation_level(c2.connection),
self._default_isolation_level()
)
c1.close()
c2.close()
c3 = eng.connect()
eq_(
eng.dialect.get_isolation_level(c3.connection),
self._default_isolation_level()
)
c4 = eng.connect()
eq_(
eng.dialect.get_isolation_level(c4.connection),
self._default_isolation_level()
)
c3.close()
c4.close()
def test_special_encodings(self):
for enc in ['utf8mb4', 'utf8']:
eng = engines.testing_engine(
options={"connect_args": {'charset': enc, 'use_unicode': 0}})
conn = eng.connect()
eq_(conn.dialect._connection_charset, enc)
def test_native_datetime(self):
dbapi = testing.db.dialect.dbapi
connect_args = {
'detect_types': dbapi.PARSE_DECLTYPES | dbapi.PARSE_COLNAMES}
engine = engines.testing_engine(
options={'connect_args': connect_args, 'native_datetime': True})
t = Table(
'datetest', MetaData(),
Column('id', Integer, primary_key=True),
Column('d1', Date), Column('d2', sqltypes.TIMESTAMP))
t.create(engine)
try:
engine.execute(t.insert(), {
'd1': datetime.date(2010, 5, 10),
'd2': datetime.datetime(2010, 5, 10, 12, 15, 25)
})
row = engine.execute(t.select()).first()
eq_(
row,
(1, datetime.date(2010, 5, 10),
datetime.datetime(2010, 5, 10, 12, 15, 25)))
r = engine.execute(func.current_date()).scalar()
assert isinstance(r, util.string_types)
finally:
t.drop(engine)
engine.dispose()
def test_native_odbc_execute(self):
t1 = Table('t1', MetaData(), Column('c1', Integer))
dbapi = MockDBAPI()
engine = engines.testing_engine('mssql+mxodbc://localhost',
options={'module': dbapi, '_initialize': False})
conn = engine.connect()
# crud: uses execute
conn.execute(t1.insert().values(c1='foo'))
conn.execute(t1.delete().where(t1.c.c1 == 'foo'))
conn.execute(t1.update().where(t1.c.c1 == 'foo').values(c1='bar'
))
# select: uses executedirect
conn.execute(t1.select())
# manual flagging
conn.execution_options(native_odbc_execute=True).\
execute(t1.select())
conn.execution_options(native_odbc_execute=False).\
execute(t1.insert().values(c1='foo'
))
eq_(dbapi.log, [
'executedirect',
'executedirect',
'executedirect',
'executedirect',
'execute',
'executedirect',
])
def test_lobs_without_convert_many_rows(self):
engine = testing_engine(
options=dict(auto_convert_lobs=False, arraysize=1))
result = engine.execute(
"select id, data, bindata from z_test order by id")
results = result.fetchall()
def go():
eq_(
[
dict(
id=row["id"],
data=row["data"].read(),
bindata=row["bindata"].read()
) for row in results
],
self.data)
# this comes from cx_Oracle because these are raw
# cx_Oracle.Variable objects
if testing.requires.oracle5x.enabled:
assert_raises_message(
testing.db.dialect.dbapi.ProgrammingError,
"LOB variable no longer valid after subsequent fetch",
go
)
else:
go()
def test_twophase(self):
users, Address, addresses, User = (self.tables.users,
self.classes.Address,
self.tables.addresses,
self.classes.User)
# TODO: mock up a failure condition here
# to ensure a rollback succeeds
mapper(User, users)
mapper(Address, addresses)
engine2 = engines.testing_engine()
sess = create_session(autocommit=True, autoflush=False,
twophase=True)
sess.bind_mapper(User, testing.db)
sess.bind_mapper(Address, engine2)
sess.begin()
u1 = User(name='u1')
a1 = Address(email_address='u1@e')
sess.add_all((u1, a1))
sess.commit()
sess.close()
engine2.dispose()
assert users.count().scalar() == 1
assert addresses.count().scalar() == 1
def setup_bind(cls):
if config.requirements.independent_connections.enabled:
from sqlalchemy import pool
return engines.testing_engine(
options=dict(poolclass=pool.StaticPool))
else:
return config.db
def test_explode_in_initializer(self):
engine = engines.testing_engine()
def broken_initialize(connection):
connection.execute("select fake_stuff from _fake_table")
engine.dialect.initialize = broken_initialize
# raises a DBAPIError, not an AttributeError
assert_raises(exc.DBAPIError, engine.connect)
# dispose connections so we get a new one on
# next go
engine.dispose()
p1 = engine.pool
def is_disconnect(e, conn, cursor):
return True
engine.dialect.is_disconnect = is_disconnect
# invalidate() also doesn't screw up
assert_raises(exc.DBAPIError, engine.connect)
# pool was recreated
assert engine.pool is not p1
def setup_class(cls):
global metadata, cattable, matchtable
metadata = MetaData(testing.db)
cattable = Table('cattable', metadata, Column('id', Integer),
Column('description', String(50)),
PrimaryKeyConstraint('id', name='PK_cattable'))
matchtable = Table(
'matchtable',
metadata,
Column('id', Integer),
Column('title', String(200)),
Column('category_id', Integer, ForeignKey('cattable.id')),
PrimaryKeyConstraint('id', name='PK_matchtable'),
)
DDL("""CREATE FULLTEXT INDEX
ON cattable (description)
KEY INDEX PK_cattable""").\
execute_at('after-create', matchtable)
DDL("""CREATE FULLTEXT INDEX
ON matchtable (title)
KEY INDEX PK_matchtable""").\
execute_at('after-create', matchtable)
metadata.create_all()
cattable.insert().execute([{'id': 1, 'description': 'Python'},
{'id': 2, 'description': 'Ruby'}])
matchtable.insert().execute([
{'id': 1, 'title': 'Web Development with Rails', 'category_id': 2},
{'id': 2, 'title': 'Dive Into Python', 'category_id': 1},
{'id': 3, 'title': "Programming Matz's Ruby", 'category_id': 2},
{'id': 4, 'title': 'Guide to Django', 'category_id': 1},
{'id': 5, 'title': 'Python in a Nutshell', 'category_id': 1}])
DDL("WAITFOR DELAY '00:00:05'"
).execute(bind=engines.testing_engine())
def _test_binary_reflection(self, deprecate_large_types):
"Exercise type specification for binary types."
columns = [
# column type, args, kwargs, expected ddl from reflected
(mssql.MSBinary, [], {},
'BINARY(1)'),
(mssql.MSBinary, [10], {},
'BINARY(10)'),
(types.BINARY, [], {},
'BINARY(1)'),
(types.BINARY, [10], {},
'BINARY(10)'),
(mssql.MSVarBinary, [], {},
'VARBINARY(max)'),
(mssql.MSVarBinary, [10], {},
'VARBINARY(10)'),
(types.VARBINARY, [10], {},
'VARBINARY(10)'),
(types.VARBINARY, [], {},
'VARBINARY(max)'),
(mssql.MSImage, [], {},
'IMAGE'),
(mssql.IMAGE, [], {},
'IMAGE'),
(types.LargeBinary, [], {},
'IMAGE' if not deprecate_large_types else 'VARBINARY(max)'),
]
metadata = self.metadata
metadata.bind = engines.testing_engine(
options={"deprecate_large_types": deprecate_large_types})
table_args = ['test_mssql_binary', metadata]
for index, spec in enumerate(columns):
type_, args, kw, res = spec
table_args.append(Column('c%s' % index, type_(*args, **kw),
nullable=None))
binary_table = Table(*table_args)
metadata.create_all()
reflected_binary = Table('test_mssql_binary',
MetaData(testing.db), autoload=True)
for col, spec in zip(reflected_binary.c, columns):
eq_(
str(col.type), spec[3],
"column %s %s != %s" % (col.key, str(col.type), spec[3])
)
c1 = testing.db.dialect.type_descriptor(col.type).__class__
c2 = \
testing.db.dialect.type_descriptor(
binary_table.c[col.name].type).__class__
assert issubclass(c1, c2), \
'column %s: %r is not a subclass of %r' \
% (col.key, c1, c2)
if binary_table.c[col.name].type.length:
testing.eq_(col.type.length,
binary_table.c[col.name].type.length)
def _setup_engine(cls):
if getattr(cls, "__engine_options__", None):
opts = dict(cls.__engine_options__)
opts["scope"] = "class"
eng = engines.testing_engine(options=opts)
config._current.push_engine(eng, testing)
def setup_bind(cls):
return engines.testing_engine(options={"optimize_limits": True})
def async_engine(self):
return engines.testing_engine(asyncio=True, transfer_staticpool=True)
def setup(self):
super(BatchInsertsTest, self).setup()
self.engine = engines.testing_engine(options={"use_batch_mode": True})
def setup_bind(cls):
return engines.testing_engine(options=dict(strategy='threadlocal'))
def test_initial_transaction_state(self):
from psycopg2.extensions import STATUS_IN_TRANSACTION
engine = engines.testing_engine()
with engine.connect() as conn:
ne_(conn.connection.status, STATUS_IN_TRANSACTION)
def test_insert_page_size(self):
from psycopg2 import extras
opts = self.options.copy()
opts["executemany_batch_page_size"] = 500
opts["executemany_values_page_size"] = 1000
eng = engines.testing_engine(options=opts)
if eng.dialect.executemany_mode & EXECUTEMANY_VALUES:
meth = extras.execute_values
stmt = "INSERT INTO data (x, y) VALUES %s"
expected_kwargs = {
"fetch": False,
"page_size": 1000,
"template": "(%(x)s, %(y)s)",
}
elif eng.dialect.executemany_mode & EXECUTEMANY_BATCH:
meth = extras.execute_batch
stmt = "INSERT INTO data (x, y) VALUES (%(x)s, %(y)s)"
expected_kwargs = {"page_size": 500}
else:
assert False
with mock.patch.object(extras, meth.__name__,
side_effect=meth) as mock_exec:
with eng.begin() as conn:
conn.execute(
self.tables.data.insert(),
[
{
"x": "x1",
"y": "y1"
},
{
"x": "x2",
"y": "y2"
},
{
"x": "x3",
"y": "y3"
},
],
)
eq_(
mock_exec.mock_calls,
[
mock.call(mock.ANY, stmt, (
{
"x": "x1",
"y": "y1"
},
{
"x": "x2",
"y": "y2"
},
{
"x": "x3",
"y": "y3"
},
), **expected_kwargs)
],
)
def test_flag_turned_on(self):
e = engines.testing_engine(options={"implicit_returning": True})
assert e.dialect.implicit_returning is True
c = e.connect()
c.close()
assert e.dialect.implicit_returning is True
def test_bind_through_execute(
self, statement, expected_get_bind_args, expected_engine_name
):
users, Address, addresses, User = (
self.tables.users,
self.classes.Address,
self.tables.addresses,
self.classes.User,
)
mapper(User, users, properties={"addresses": relationship(Address)})
mapper(Address, addresses)
e1 = engines.testing_engine()
e2 = engines.testing_engine()
e3 = engines.testing_engine()
canary = mock.Mock()
class GetBindSession(Session):
def _connection_for_bind(self, bind, **kw):
canary._connection_for_bind(bind, **kw)
return mock.Mock()
def get_bind(self, **kw):
canary.get_bind(**kw)
return Session.get_bind(self, **kw)
sess = GetBindSession(e3, future=True)
sess.bind_mapper(User, e1)
sess.bind_mapper(Address, e2)
lambda_args = dict(
session=sess,
User=User,
Address=Address,
e1=e1,
e2=e2,
e3=e3,
addresses=addresses,
)
statement = testing.resolve_lambda(statement, **lambda_args)
expected_get_bind_args = testing.resolve_lambda(
expected_get_bind_args, **lambda_args
)
engine = {"e1": e1, "e2": e2, "e3": e3}[expected_engine_name]
with mock.patch(
"sqlalchemy.orm.context.ORMCompileState.orm_setup_cursor_result"
):
sess.execute(statement)
eq_(
canary.mock_calls,
[
mock.call.get_bind(**expected_get_bind_args),
mock.call._connection_for_bind(engine, close_with_result=True),
],
)
sess.close()
def test_large_stream_single_arraysize(self):
binary_table = self.tables.binary_table
eng = testing_engine(options={'arraysize': 1})
result = eng.execute(binary_table.select().order_by(
binary_table.c.id)).fetchall()
eq_(result, [(i, self.stream) for i in range(1, 11)])
def _assert_data_with_sequence_returning(self, table, seqname):
engine = engines.testing_engine(options={"implicit_returning": True})
with self.sql_execution_asserter(engine) as asserter:
with engine.begin() as conn:
conn.execute(table.insert(), {"id": 30, "data": "d1"})
conn.execute(table.insert(), {"data": "d2"})
conn.execute(
table.insert(),
[
{
"id": 31,
"data": "d3"
},
{
"id": 32,
"data": "d4"
},
],
)
conn.execute(table.insert(), [{"data": "d5"}, {"data": "d6"}])
conn.execute(table.insert().inline(), {"id": 33, "data": "d7"})
conn.execute(table.insert().inline(), {"data": "d8"})
asserter.assert_(
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{
"id": 30,
"data": "d1"
},
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES "
"(nextval('my_seq'), :data) RETURNING testtable.id",
{"data": "d2"},
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{
"id": 31,
"data": "d3"
}, {
"id": 32,
"data": "d4"
}],
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), "
":data)" % seqname,
[{
"data": "d5"
}, {
"data": "d6"
}],
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{
"id": 33,
"data": "d7"
}],
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (nextval('%s'), "
":data)" % seqname,
[{
"data": "d8"
}],
),
)
with engine.begin() as conn:
eq_(
conn.execute(table.select()).fetchall(),
[
(30, "d1"),
(1, "d2"),
(31, "d3"),
(32, "d4"),
(2, "d5"),
(3, "d6"),
(33, "d7"),
(4, "d8"),
],
)
def _assert_data_noautoincrement(self, table):
engine = engines.testing_engine(options={"implicit_returning": False})
# turning off the cache because we are checking for compile-time
# warnings
engine = engine.execution_options(compiled_cache=None)
with engine.begin() as conn:
conn.execute(table.insert(), {"id": 30, "data": "d1"})
with engine.begin() as conn:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
conn.execute,
table.insert(),
{"data": "d2"},
)
with engine.begin() as conn:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
conn.execute,
table.insert(),
[{
"data": "d2"
}, {
"data": "d3"
}],
)
with engine.begin() as conn:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
conn.execute,
table.insert(),
{"data": "d2"},
)
with engine.begin() as conn:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
conn.execute,
table.insert(),
[{
"data": "d2"
}, {
"data": "d3"
}],
)
with engine.begin() as conn:
conn.execute(
table.insert(),
[{
"id": 31,
"data": "d2"
}, {
"id": 32,
"data": "d3"
}],
)
conn.execute(table.insert().inline(), {"id": 33, "data": "d4"})
eq_(
conn.execute(table.select()).fetchall(),
[(30, "d1"), (31, "d2"), (32, "d3"), (33, "d4")],
)
conn.execute(table.delete())
# test the same series of events using a reflected version of
# the table
m2 = MetaData()
table = Table(table.name, m2, autoload_with=engine)
with engine.begin() as conn:
conn.execute(table.insert(), {"id": 30, "data": "d1"})
with engine.begin() as conn:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
conn.execute,
table.insert(),
{"data": "d2"},
)
with engine.begin() as conn:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
conn.execute,
table.insert(),
[{
"data": "d2"
}, {
"data": "d3"
}],
)
with engine.begin() as conn:
conn.execute(
table.insert(),
[{
"id": 31,
"data": "d2"
}, {
"id": 32,
"data": "d3"
}],
)
conn.execute(table.insert().inline(), {"id": 33, "data": "d4"})
eq_(
conn.execute(table.select()).fetchall(),
[(30, "d1"), (31, "d2"), (32, "d3"), (33, "d4")],
)
def test_autoincrement(self):
Table(
'ai_1', metadata,
Column('int_y', Integer, primary_key=True, autoincrement=True),
Column(
'int_n', Integer, DefaultClause('0'), primary_key=True))
Table(
'ai_2', metadata,
Column('int_y', Integer, primary_key=True, autoincrement=True),
Column('int_n', Integer, DefaultClause('0'), primary_key=True))
Table(
'ai_3', metadata,
Column('int_n', Integer, DefaultClause('0'), primary_key=True),
Column('int_y', Integer, primary_key=True, autoincrement=True))
Table(
'ai_4', metadata,
Column('int_n', Integer, DefaultClause('0'), primary_key=True),
Column('int_n2', Integer, DefaultClause('0'), primary_key=True))
Table(
'ai_5', metadata,
Column('int_y', Integer, primary_key=True, autoincrement=True),
Column('int_n', Integer, DefaultClause('0'), primary_key=True))
Table(
'ai_6', metadata,
Column('o1', String(1), DefaultClause('x'), primary_key=True),
Column('int_y', Integer, primary_key=True, autoincrement=True))
Table(
'ai_7', metadata,
Column('o1', String(1), DefaultClause('x'),
primary_key=True),
Column('o2', String(1), DefaultClause('x'),
primary_key=True),
Column('int_y', Integer, autoincrement=True, primary_key=True))
Table(
'ai_8', metadata,
Column('o1', String(1), DefaultClause('x'),
primary_key=True),
Column('o2', String(1), DefaultClause('x'),
primary_key=True))
metadata.create_all()
table_names = ['ai_1', 'ai_2', 'ai_3', 'ai_4',
'ai_5', 'ai_6', 'ai_7', 'ai_8']
mr = MetaData(testing.db)
for name in table_names:
tbl = Table(name, mr, autoload=True)
tbl = metadata.tables[name]
# test that the flag itself reflects appropriately
for col in tbl.c:
if 'int_y' in col.name:
is_(col.autoincrement, True)
is_(tbl._autoincrement_column, col)
else:
eq_(col.autoincrement, 'auto')
is_not_(tbl._autoincrement_column, col)
# mxodbc can't handle scope_identity() with DEFAULT VALUES
if testing.db.driver == 'mxodbc':
eng = \
[engines.testing_engine(options={
'implicit_returning': True})]
else:
eng = \
[engines.testing_engine(options={
'implicit_returning': False}),
engines.testing_engine(options={
'implicit_returning': True})]
for counter, engine in enumerate(eng):
engine.execute(tbl.insert())
if 'int_y' in tbl.c:
assert engine.scalar(select([tbl.c.int_y])) \
== counter + 1
assert list(
engine.execute(tbl.select()).first()).\
count(counter + 1) == 1
else:
assert 1 \
not in list(engine.execute(tbl.select()).first())
engine.execute(tbl.delete())
def test_round_trip(
self,
metadata,
type_,
data,
expected,
deprecate_large_types,
slice_,
zeropad,
):
if (
testing.db.dialect.deprecate_large_types
is not deprecate_large_types
):
engine = engines.testing_engine(
options={"deprecate_large_types": deprecate_large_types}
)
else:
engine = testing.db
binary_table = Table(
"binary_table",
metadata,
Column("id", Integer, primary_key=True),
Column("data", type_),
)
binary_table.create(engine)
if isinstance(data, str) and (
data == "binary_data_one.dat" or data == "binary_data_two.dat"
):
data = self._load_stream(data)
if slice_ is not None:
data = data[0:slice_]
if expected is None:
if zeropad:
expected = data[0:slice_] + b"\x00"
else:
expected = data
with engine.begin() as conn:
conn.execute(binary_table.insert(), dict(data=data))
eq_(conn.scalar(select(binary_table.c.data)), expected)
eq_(
conn.scalar(
text("select data from binary_table").columns(
binary_table.c.data
)
),
expected,
)
conn.execute(binary_table.delete())
conn.execute(binary_table.insert(), dict(data=None))
eq_(conn.scalar(select(binary_table.c.data)), None)
eq_(
conn.scalar(
text("select data from binary_table").columns(
binary_table.c.data
)
),
None,
)
def _setup_engine(cls):
if getattr(cls, "__engine_options__", None):
eng = engines.testing_engine(options=cls.__engine_options__)
config._current.push_engine(eng, testing)
def test_autoincrement(self, metadata, connection):
Table(
"ai_1",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_2",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_3",
metadata,
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
)
Table(
"ai_4",
metadata,
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
Column("int_n2", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_5",
metadata,
Column("int_y", Integer, primary_key=True, autoincrement=True),
Column("int_n", Integer, DefaultClause("0"), primary_key=True),
)
Table(
"ai_6",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, primary_key=True, autoincrement=True),
)
Table(
"ai_7",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
Column("int_y", Integer, autoincrement=True, primary_key=True),
)
Table(
"ai_8",
metadata,
Column("o1", String(1), DefaultClause("x"), primary_key=True),
Column("o2", String(1), DefaultClause("x"), primary_key=True),
)
metadata.create_all(connection)
table_names = [
"ai_1",
"ai_2",
"ai_3",
"ai_4",
"ai_5",
"ai_6",
"ai_7",
"ai_8",
]
mr = MetaData()
for name in table_names:
tbl = Table(name, mr, autoload_with=connection)
tbl = metadata.tables[name]
# test that the flag itself reflects appropriately
for col in tbl.c:
if "int_y" in col.name:
is_(col.autoincrement, True)
is_(tbl._autoincrement_column, col)
else:
eq_(col.autoincrement, "auto")
is_not(tbl._autoincrement_column, col)
# mxodbc can't handle scope_identity() with DEFAULT VALUES
if testing.db.driver == "mxodbc":
eng = [
engines.testing_engine(
options={"implicit_returning": True}
)
]
else:
eng = [
engines.testing_engine(
options={"implicit_returning": False}
),
engines.testing_engine(
options={"implicit_returning": True}
),
]
for counter, engine in enumerate(eng):
connection.execute(tbl.insert())
if "int_y" in tbl.c:
eq_(
connection.execute(select(tbl.c.int_y)).scalar(),
counter + 1,
)
assert (
list(connection.execute(tbl.select()).first()).count(
counter + 1
)
== 1
)
else:
assert 1 not in list(
connection.execute(tbl.select()).first()
)
connection.execute(tbl.delete())
def _assert_data_autoincrement_returning(self, table):
engine = engines.testing_engine(options={"implicit_returning": True})
with self.sql_execution_asserter(engine) as asserter:
with engine.begin() as conn:
# execute with explicit id
r = conn.execute(table.insert(), {"id": 30, "data": "d1"})
eq_(r.inserted_primary_key, (30, ))
# execute with prefetch id
r = conn.execute(table.insert(), {"data": "d2"})
eq_(r.inserted_primary_key, (1, ))
# executemany with explicit ids
conn.execute(
table.insert(),
[
{
"id": 31,
"data": "d3"
},
{
"id": 32,
"data": "d4"
},
],
)
# executemany, uses SERIAL
conn.execute(table.insert(), [{"data": "d5"}, {"data": "d6"}])
# single execute, explicit id, inline
conn.execute(table.insert().inline(), {"id": 33, "data": "d7"})
# single execute, inline, uses SERIAL
conn.execute(table.insert().inline(), {"data": "d8"})
asserter.assert_(
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{
"id": 30,
"data": "d1"
},
),
DialectSQL(
"INSERT INTO testtable (data) VALUES (:data) RETURNING "
"testtable.id",
{"data": "d2"},
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{
"id": 31,
"data": "d3"
}, {
"id": 32,
"data": "d4"
}],
),
DialectSQL(
"INSERT INTO testtable (data) VALUES (:data)",
[{
"data": "d5"
}, {
"data": "d6"
}],
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{
"id": 33,
"data": "d7"
}],
),
DialectSQL("INSERT INTO testtable (data) VALUES (:data)",
[{
"data": "d8"
}]),
)
with engine.begin() as conn:
eq_(
conn.execute(table.select()).fetchall(),
[
(30, "d1"),
(1, "d2"),
(31, "d3"),
(32, "d4"),
(2, "d5"),
(3, "d6"),
(33, "d7"),
(4, "d8"),
],
)
conn.execute(table.delete())
# test the same series of events using a reflected version of
# the table
m2 = MetaData()
table = Table(table.name, m2, autoload_with=engine)
with self.sql_execution_asserter(engine) as asserter:
with engine.begin() as conn:
conn.execute(table.insert(), {"id": 30, "data": "d1"})
r = conn.execute(table.insert(), {"data": "d2"})
eq_(r.inserted_primary_key, (5, ))
conn.execute(
table.insert(),
[
{
"id": 31,
"data": "d3"
},
{
"id": 32,
"data": "d4"
},
],
)
conn.execute(table.insert(), [{"data": "d5"}, {"data": "d6"}])
conn.execute(table.insert().inline(), {"id": 33, "data": "d7"})
conn.execute(table.insert().inline(), {"data": "d8"})
asserter.assert_(
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
{
"id": 30,
"data": "d1"
},
),
DialectSQL(
"INSERT INTO testtable (data) VALUES (:data) RETURNING "
"testtable.id",
{"data": "d2"},
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{
"id": 31,
"data": "d3"
}, {
"id": 32,
"data": "d4"
}],
),
DialectSQL(
"INSERT INTO testtable (data) VALUES (:data)",
[{
"data": "d5"
}, {
"data": "d6"
}],
),
DialectSQL(
"INSERT INTO testtable (id, data) VALUES (:id, :data)",
[{
"id": 33,
"data": "d7"
}],
),
DialectSQL("INSERT INTO testtable (data) VALUES (:data)",
[{
"data": "d8"
}]),
)
with engine.begin() as conn:
eq_(
conn.execute(table.select()).fetchall(),
[
(30, "d1"),
(5, "d2"),
(31, "d3"),
(32, "d4"),
(6, "d5"),
(7, "d6"),
(33, "d7"),
(8, "d8"),
],
)
conn.execute(table.delete())
def _test_setinputsizes(
self, datatype, value, sis_value, set_nchar_flag=False
):
class TestTypeDec(TypeDecorator):
impl = NullType()
def load_dialect_impl(self, dialect):
if dialect.name == "oracle":
return dialect.type_descriptor(datatype)
else:
return self.impl
m = self.metadata
# Oracle can have only one column of type LONG so we make three
# tables rather than one table w/ three columns
t1 = Table("t1", m, Column("foo", datatype))
t2 = Table(
"t2", m, Column("foo", NullType().with_variant(datatype, "oracle"))
)
t3 = Table("t3", m, Column("foo", TestTypeDec()))
m.create_all()
class CursorWrapper(object):
# cx_oracle cursor can't be modified so we have to
# invent a whole wrapping scheme
def __init__(self, connection_fairy):
self.cursor = connection_fairy.connection.cursor()
self.mock = mock.Mock()
connection_fairy.info["mock"] = self.mock
def setinputsizes(self, *arg, **kw):
self.mock.setinputsizes(*arg, **kw)
self.cursor.setinputsizes(*arg, **kw)
def __getattr__(self, key):
return getattr(self.cursor, key)
if set_nchar_flag:
engine = testing_engine(options={"use_nchar_for_unicode": True})
else:
engine = testing.db
with engine.connect() as conn:
connection_fairy = conn.connection
for tab in [t1, t2, t3]:
with mock.patch.object(
connection_fairy,
"cursor",
lambda: CursorWrapper(connection_fairy),
):
conn.execute(tab.insert(), {"foo": value})
if sis_value:
eq_(
conn.info["mock"].mock_calls,
[mock.call.setinputsizes(foo=sis_value)],
)
else:
eq_(
conn.info["mock"].mock_calls,
[mock.call.setinputsizes()],
)
def test_lobs_without_convert(self):
engine = testing_engine(options=dict(auto_convert_lobs=False))
t = self.tables.z_test
row = engine.execute(t.select().where(t.c.id == 1)).first()
eq_(row["data"].read(), "this is text 1")
eq_(row["bindata"].read(), b("this is binary 1"))
def test_dispose(self):
eng = testing_engine(options=dict(strategy='threadlocal'))
result = eng.execute(select([1]))
eng.dispose()
eng.execute(select([1]))
def test_default_level(self):
eng = testing_engine(options=dict())
isolation_level = eng.dialect.get_isolation_level(
eng.connect().connection)
eq_(isolation_level, self._default_isolation_level())
def test_flag_turned_off(self):
e = engines.testing_engine(options={'implicit_returning': False})
assert e.dialect.implicit_returning is False
c = e.connect()
c.close()
assert e.dialect.implicit_returning is False
def _engine_fixture(self, length=IDENT_LENGTH):
eng = engines.testing_engine()
eng.dialect.max_identifier_length = length
return eng