def test_implicit_execution(self): metadata = MetaData() table = Table( "test_table", metadata, Column("foo", Integer), test_needs_acid=True, ) conn = testing.db.connect() metadata.create_all(bind=conn) try: trans = conn.begin() metadata.bind = conn t = table.insert() assert t.bind is conn table.insert().execute(foo=5) table.insert().execute(foo=6) table.insert().execute(foo=7) trans.rollback() metadata.bind = None assert ( conn.execute("select count(*) from test_table").scalar() == 0 ) finally: metadata.drop_all(bind=conn)
def _dont_test_numeric_nan_decimal(self): m = self.metadata t1 = Table( "t1", m, Column("intcol", Integer), Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True)), ) t1.create() t1.insert().execute( [ dict(intcol=1, numericcol=decimal.Decimal("NaN")), dict(intcol=2, numericcol=decimal.Decimal("-NaN")), ] ) eq_( select([t1.c.numericcol]) .order_by(t1.c.intcol) .execute() .fetchall(), [(decimal.Decimal("NaN"),), (decimal.Decimal("NaN"),)], ) eq_( testing.db.execute( "select numericcol from t1 order by intcol" ).fetchall(), [(decimal.Decimal("NaN"),), (decimal.Decimal("NaN"),)], )
def test_misordered_lastrow(self): metadata = self.metadata related = Table( "related", metadata, Column("id", Integer, primary_key=True), mysql_engine="MyISAM", ) t6 = Table( "t6", metadata, Column( "manual_id", Integer, ForeignKey("related.id"), primary_key=True, ), Column( "auto_id", Integer, primary_key=True, test_needs_autoincrement=True, ), mysql_engine="MyISAM", ) metadata.create_all() r = related.insert().values(id=12).execute() id_ = r.inserted_primary_key[0] eq_(id_, 12) r = t6.insert().values(manual_id=id_).execute() eq_(r.inserted_primary_key, [12, 1])
def test_numeric_infinity_float(self): m = self.metadata t1 = Table( "t1", m, Column("intcol", Integer), Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)), ) t1.create() t1.insert().execute( [ dict(intcol=1, numericcol=float("inf")), dict(intcol=2, numericcol=float("-inf")), ] ) eq_( select([t1.c.numericcol]) .order_by(t1.c.intcol) .execute() .fetchall(), [(float("inf"),), (float("-inf"),)], ) eq_( testing.db.execute( "select numericcol from t1 order by intcol" ).fetchall(), [(float("inf"),), (float("-inf"),)], )
def test_numeric_nan_float(self): m = self.metadata t1 = Table( "t1", m, Column("intcol", Integer), Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)), ) t1.create() t1.insert().execute( [ dict(intcol=1, numericcol=float("nan")), dict(intcol=2, numericcol=float("-nan")), ] ) eq_( [ tuple(str(col) for col in row) for row in select([t1.c.numericcol]) .order_by(t1.c.intcol) .execute() ], [("nan",), ("nan",)], ) eq_( [ tuple(str(col) for col in row) for row in testing.db.execute( "select numericcol from t1 order by intcol" ) ], [("nan",), ("nan",)], )
def test_quoted_column_unicode(self): metadata = self.metadata table = Table( "atable", metadata, Column(u("méil"), Unicode(255), primary_key=True), ) metadata.create_all() table.insert().execute({u("méil"): u("’é")}) result = testing.db.execute( table.select().where(table.c[u("méil")] == u("’é"))).scalar() eq_(result, u("’é"))
def test_quoted_column_non_unicode(self): metadata = self.metadata table = Table( "atable", metadata, Column("_underscorecolumn", Unicode(255), primary_key=True), ) metadata.create_all() table.insert().execute({"_underscorecolumn": u("’é")}) result = testing.db.execute(table.select().where( table.c._underscorecolumn == u("’é"))).scalar() eq_(result, u("’é"))
def test_numerics(self): m = self.metadata t1 = Table( "t1", m, Column("intcol", Integer), Column("numericcol", Numeric(precision=9, scale=2)), Column("floatcol1", Float()), Column("floatcol2", FLOAT()), Column("doubleprec", oracle.DOUBLE_PRECISION), Column("numbercol1", oracle.NUMBER(9)), Column("numbercol2", oracle.NUMBER(9, 3)), Column("numbercol3", oracle.NUMBER), ) t1.create() t1.insert().execute( intcol=1, numericcol=5.2, floatcol1=6.5, floatcol2=8.5, doubleprec=9.5, numbercol1=12, numbercol2=14.85, numbercol3=15.76, ) m2 = MetaData(testing.db) t2 = Table("t1", m2, autoload=True) for row in ( t1.select().execute().first(), t2.select().execute().first(), ): for i, (val, type_) in enumerate( ( (1, int), (decimal.Decimal("5.2"), decimal.Decimal), (6.5, float), (8.5, float), (9.5, float), (12, int), (decimal.Decimal("14.85"), decimal.Decimal), (15.76, float), ) ): eq_(row[i], val) assert isinstance(row[i], type_), "%r is not %r" % ( row[i], type_, )
def setup(self): self.engine = engines.reconnecting_engine() self.meta = MetaData(self.engine) table = Table( "sometable", self.meta, Column("id", Integer, primary_key=True), Column("name", String(50)), ) self.meta.create_all() table.insert().execute([{ "id": i, "name": "row %d" % i } for i in range(1, 100)])
def test_create_same_names_implicit_schema(self): meta = self.metadata parent = Table("parent", meta, Column("pid", Integer, primary_key=True)) child = Table( "child", meta, Column("cid", Integer, primary_key=True), Column("pid", Integer, ForeignKey("parent.pid")), ) meta.create_all() parent.insert().execute({"pid": 1}) child.insert().execute({"cid": 1, "pid": 1}) eq_(child.select().execute().fetchall(), [(1, 1)])
def test_limit_offset_for_update(self): metadata = self.metadata # oracle can't actually do the ROWNUM thing with FOR UPDATE # very well. t = Table( "t1", metadata, Column("id", Integer, primary_key=True), Column("data", Integer), ) metadata.create_all() t.insert().execute( { "id": 1, "data": 1 }, { "id": 2, "data": 7 }, { "id": 3, "data": 12 }, { "id": 4, "data": 15 }, { "id": 5, "data": 32 }, ) # here, we can't use ORDER BY. eq_( t.select().with_for_update().limit(2).execute().fetchall(), [(1, 1), (2, 7)], ) # here, its impossible. But we'd prefer it to raise ORA-02014 # instead of issuing a syntax error. assert_raises_message( exc.DatabaseError, "ORA-02014", t.select().with_for_update().limit(2).offset(3).execute, )
def test_insert_from_select_fn_defaults(self, connection): data = self.tables.data counter = itertools.count(1) def foo(ctx): return next(counter) table = Table( "sometable", self.metadata, Column("x", Integer), Column("foo", Integer, default=foo), Column("y", Integer), ) table.create(connection) sel = select([data.c.x, data.c.y]) ins = table.insert().from_select(["x", "y"], sel) connection.execute(ins) # counter is only called once! eq_( list(connection.execute(table.select().order_by(table.c.x))), [(2, 1, 5), (7, 1, 12)], )
def test_interval(self): metadata = self.metadata interval_table = Table( "intervaltable", metadata, Column( "id", Integer, primary_key=True, test_needs_autoincrement=True ), Column("day_interval", oracle.INTERVAL(day_precision=3)), ) metadata.create_all() interval_table.insert().execute( day_interval=datetime.timedelta(days=35, seconds=5743) ) row = interval_table.select().execute().first() eq_(row["day_interval"], datetime.timedelta(days=35, seconds=5743))
def _run_test(self, *arg, **kw): metadata = self.metadata implicit_returning = kw.pop("implicit_returning", True) kw["primary_key"] = True if kw.get("autoincrement", True): kw["test_needs_autoincrement"] = True t = Table( "x", metadata, Column("y", self.MyInteger, *arg, **kw), Column("data", Integer), implicit_returning=implicit_returning, ) with testing.db.connect() as conn: t.create(conn) r = conn.execute(t.insert().values(data=5)) # we don't pre-fetch 'server_default'. if "server_default" in kw and ( not testing.db.dialect.implicit_returning or not implicit_returning): eq_(r.inserted_primary_key, [None]) else: eq_(r.inserted_primary_key, ["INT_1"]) eq_(conn.execute(t.select()).first(), ("INT_1", 5))
def test_fixed_char(self, char_type): m = self.metadata t = Table( "t1", m, Column("id", Integer, primary_key=True), Column("data", char_type(30), nullable=False), ) if py2k and char_type is NCHAR: v1, v2, v3 = u"value 1", u"value 2", u"value 3" else: v1, v2, v3 = "value 1", "value 2", "value 3" with testing.db.begin() as conn: t.create(conn) conn.execute( t.insert(), dict(id=1, data=v1), dict(id=2, data=v2), dict(id=3, data=v3), ) eq_( conn.execute(t.select().where(t.c.data == v2)).fetchall(), [(2, "value 2 ")], ) m2 = MetaData() t2 = Table("t1", m2, autoload_with=conn) is_(type(t2.c.data.type), char_type) eq_( conn.execute(t2.select().where(t2.c.data == v2)).fetchall(), [(2, "value 2 ")], )
def test_long_type(self): metadata = self.metadata t = Table("t", metadata, Column("data", oracle.LONG)) metadata.create_all(testing.db) testing.db.execute(t.insert(), data="xyz") eq_(testing.db.scalar(select([t.c.data])), "xyz")
def test_no_embed_in_sql(self): """Using a DefaultGenerator, Sequence, DefaultClause in the columns, where clause of a select, or in the values clause of insert, update, raises an informative error""" t = Table( "some_table", MetaData(), Column("id", Integer), Column("col4", String()), ) for const in ( sa.Sequence("y"), sa.ColumnDefault("y"), sa.DefaultClause("y"), ): assert_raises_message( sa.exc.ArgumentError, "SQL expression object expected, got object of type " "<.* 'list'> instead", t.select, [const], ) assert_raises_message( sa.exc.InvalidRequestError, "cannot be used directly as a column expression.", str, t.insert().values(col4=const), ) assert_raises_message( sa.exc.InvalidRequestError, "cannot be used directly as a column expression.", str, t.update().values(col4=const), )
def test_reflect_nvarchar(self): metadata = self.metadata Table( "tnv", metadata, Column("nv_data", sqltypes.NVARCHAR(255)), Column("c_data", sqltypes.NCHAR(20)), ) metadata.create_all() m2 = MetaData(testing.db) t2 = Table("tnv", m2, autoload=True) assert isinstance(t2.c.nv_data.type, sqltypes.NVARCHAR) assert isinstance(t2.c.c_data.type, sqltypes.NCHAR) if testing.against("oracle+cx_oracle"): assert isinstance( t2.c.nv_data.type.dialect_impl(testing.db.dialect), cx_oracle._OracleUnicodeStringNCHAR, ) assert isinstance( t2.c.c_data.type.dialect_impl(testing.db.dialect), cx_oracle._OracleNChar, ) data = u("m’a réveillé.") with testing.db.connect() as conn: conn.execute(t2.insert(), dict(nv_data=data, c_data=data)) nv_data, c_data = conn.execute(t2.select()).first() eq_(nv_data, data) eq_(c_data, data + (" " * 7)) # char is space padded assert isinstance(nv_data, util.text_type) assert isinstance(c_data, util.text_type)
def test_rowid(self): metadata = self.metadata t = Table("t1", metadata, Column("x", Integer)) t.create() t.insert().execute(x=5) s1 = select([t]) s2 = select([column("rowid")]).select_from(s1) rowid = s2.scalar() # the ROWID type is not really needed here, # as cx_oracle just treats it as a string, # but we want to make sure the ROWID works... rowid_col = column("rowid", oracle.ROWID) s3 = select([t.c.x, rowid_col]).where( rowid_col == cast(rowid, oracle.ROWID) ) eq_(s3.select().execute().fetchall(), [(5, rowid)])
def test_longstring(self): metadata = MetaData(testing.db) testing.db.execute( """ CREATE TABLE Z_TEST ( ID NUMERIC(22) PRIMARY KEY, ADD_USER VARCHAR2(20) NOT NULL ) """ ) try: t = Table("z_test", metadata, autoload=True) t.insert().execute(id=1.0, add_user="******") assert t.select().execute().fetchall() == [(1, "foobar")] finally: testing.db.execute("DROP TABLE Z_TEST")
def test_func_embedded_valuesbase(self, connection): """test can use next_value() in values() of _ValuesBase""" metadata = self.metadata t1 = Table("t", metadata, Column("x", Integer)) t1.create(testing.db) s = Sequence("my_sequence") connection.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(connection.scalar(t1.select()))
def test_autoincrement_single_col(self, connection): single = Table("single", self.metadata, Column("id", Integer, primary_key=True)) self.metadata.create_all(connection) r = connection.execute(single.insert()) id_ = r.inserted_primary_key[0] eq_(id_, 1) eq_(connection.scalar(sa.select([single.c.id])), 1)
def test_reflect_unicode_no_nvarchar(self): metadata = self.metadata Table("tnv", metadata, Column("data", sqltypes.Unicode(255))) metadata.create_all() m2 = MetaData(testing.db) t2 = Table("tnv", m2, autoload=True) assert isinstance(t2.c.data.type, sqltypes.VARCHAR) if testing.against("oracle+cx_oracle"): assert isinstance( t2.c.data.type.dialect_impl(testing.db.dialect), cx_oracle._OracleString, ) data = u("m’a réveillé.") t2.insert().execute(data=data) res = t2.select().execute().first()["data"] eq_(res, data) assert isinstance(res, util.text_type)
def test_table_round_trip(self): oracle.RESERVED_WORDS.remove("UNION") metadata = self.metadata table = Table( "t1", metadata, Column("option", Integer), Column("plain", Integer, quote=True), # test that quote works for a reserved word # that the dialect isn't aware of when quote # is set Column("union", Integer, quote=True), ) metadata.create_all() table.insert().execute({"option": 1, "plain": 1, "union": 1}) eq_(testing.db.execute(table.select()).first(), (1, 1, 1)) table.update().values(option=2, plain=2, union=2).execute() eq_(testing.db.execute(table.select()).first(), (2, 2, 2))
def test_raw_roundtrip(self): metadata = self.metadata raw_table = Table( "raw", metadata, Column("id", Integer, primary_key=True), Column("data", oracle.RAW(35)), ) metadata.create_all() testing.db.execute(raw_table.insert(), id=1, data=b("ABCDEF")) eq_(testing.db.execute(raw_table.select()).first(), (1, b("ABCDEF")))
def test_select_doesnt_pollute_result(self): class MyType(TypeDecorator): impl = Integer def process_result_value(self, value, dialect): raise Exception("I have not been selected") t1 = Table("t1", self.metadata, Column("x", MyType())) t2 = Table("t2", self.metadata, Column("x", Integer)) self.metadata.create_all(testing.db) with testing.db.connect() as conn: conn.execute(t1.insert().values(x=5)) stmt = (t2.insert().values( x=select([t1.c.x]).as_scalar()).returning(t2.c.x)) result = conn.execute(stmt) eq_(result.scalar(), 5)
def test_create_same_names_explicit_schema(self): schema = testing.db.dialect.default_schema_name meta = self.metadata parent = Table( "parent", meta, Column("pid", Integer, primary_key=True), schema=schema, ) child = Table( "child", meta, Column("cid", Integer, primary_key=True), Column("pid", Integer, ForeignKey("%s.parent.pid" % schema)), schema=schema, ) meta.create_all() parent.insert().execute({"pid": 1}) child.insert().execute({"cid": 1, "pid": 1}) eq_(child.select().execute().fetchall(), [(1, 1)])
def test_int_not_float(self): m = self.metadata t1 = Table("t1", m, Column("foo", Integer)) t1.create() r = t1.insert().values(foo=5).returning(t1.c.foo).execute() x = r.scalar() assert x == 5 assert isinstance(x, int) x = t1.select().scalar() assert x == 5 assert isinstance(x, int)
def test_inserted_pk_no_returning_no_lastrowid(self): """test inserted_primary_key contains [None] when pk_col=next_value(), implicit returning is not used.""" metadata = self.metadata t1 = Table("t", metadata, Column("x", Integer, primary_key=True)) t1.create(testing.db) e = engines.testing_engine(options={"implicit_returning": False}) s = Sequence("my_sequence") with e.connect() as conn: r = conn.execute(t1.insert().values(x=s.next_value())) eq_(r.inserted_primary_key, [None])
def test_inserted_pk_implicit_returning(self): """test inserted_primary_key contains the result when pk_col=next_value(), when implicit returning is used.""" metadata = self.metadata s = Sequence("my_sequence") t1 = Table("t", metadata, Column("x", Integer, primary_key=True)) t1.create(testing.db) e = engines.testing_engine(options={"implicit_returning": True}) with e.connect() as conn: r = conn.execute(t1.insert().values(x=s.next_value())) self._assert_seq_result(r.inserted_primary_key[0])