[Sqlalchemy-commits] sqlalchemy: oracle cleanup
Brought to you by:
zzzeek
Menu
▾
▴
[Sqlalchemy-commits] sqlalchemy: oracle cleanup
|
From: <co...@sq...> - 2010-03-20 02:29:33
|
details: http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/4ce359a25822 changeset: 6327:4ce359a25822 user: zzzeek date: Fri Mar 19 22:29:08 2010 -0400 description: oracle cleanup diffstat: lib/sqlalchemy/dialects/oracle/cx_oracle.py | 57 +++-- test/dialect/test_oracle.py | 271 ++++++++++++++++++--------- 2 files changed, 218 insertions(+), 110 deletions(-) diffs (truncated from 590 to 300 lines): diff -r aaed76e0c5f5 -r 4ce359a25822 lib/sqlalchemy/dialects/oracle/cx_oracle.py --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py Fri Mar 19 21:42:35 2010 -0400 +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py Fri Mar 19 22:29:08 2010 -0400 @@ -207,11 +207,19 @@ class OracleExecutionContext_cx_oracle(OracleExecutionContext): def pre_exec(self): - quoted_bind_names = getattr(self.compiled, '_quoted_bind_names', {}) + quoted_bind_names = \ + getattr(self.compiled, '_quoted_bind_names', None) if quoted_bind_names: + if not self.dialect.supports_unicode_binds: + quoted_bind_names = \ + dict( + (fromname, toname.encode(self.dialect.encoding)) + for fromname, toname in + quoted_bind_names.items() + ) for param in self.parameters: - for fromname, toname in self.compiled._quoted_bind_names.iteritems(): - param[toname.encode(self.dialect.encoding)] = param[fromname] + for fromname, toname in quoted_bind_names.items(): + param[toname] = param[fromname] del param[fromname] if self.dialect.auto_setinputsizes: @@ -219,14 +227,12 @@ # on String, including that outparams/RETURNING # breaks for varchars self.set_input_sizes(quoted_bind_names, - exclude_types=self.dialect._cx_oracle_string_types + exclude_types=self.dialect._cx_oracle_string_types ) - + + # if a single execute, check for outparams if len(self.compiled_parameters) == 1: - for key in self.compiled.binds: - bindparam = self.compiled.binds[key] - name = self.compiled.bind_names[bindparam] - value = self.compiled_parameters[0][name] + for bindparam in self.compiled.binds.values(): if bindparam.isoutparam: dbtype = bindparam.type.dialect_impl(self.dialect).\ get_dbapi_type(self.dialect.dbapi) @@ -238,6 +244,7 @@ " cx_oracle" % (name, bindparam.type) ) + name = self.compiled.bind_names[bindparam] self.out_parameters[name] = self.cursor.var(dbtype) self.parameters[0][quoted_bind_names.get(name, name)] = \ self.out_parameters[name] @@ -250,7 +257,10 @@ def get_result_proxy(self): if hasattr(self, 'out_parameters') and self.compiled.returning: - returning_params = dict((k, v.getvalue()) for k, v in self.out_parameters.items()) + returning_params = dict( + (k, v.getvalue()) + for k, v in self.out_parameters.items() + ) return ReturningResultProxy(self, returning_params) result = None @@ -264,10 +274,11 @@ result = base.ResultProxy(self) if hasattr(self, 'out_parameters'): - if self.compiled_parameters is not None and len(self.compiled_parameters) == 1: + if self.compiled_parameters is not None and \ + len(self.compiled_parameters) == 1: result.out_parameters = out_parameters = {} - for bind, name in self.compiled.bind_names.iteritems(): + for bind, name in self.compiled.bind_names.items(): if name in self.out_parameters: type = bind.type impl_type = type.dialect_impl(self.dialect) @@ -291,12 +302,14 @@ class OracleExecutionContext_cx_oracle_with_unicode(OracleExecutionContext_cx_oracle): """Support WITH_UNICODE in Python 2.xx. - WITH_UNICODE allows cx_Oracle's Python 3 unicode handling behavior under Python 2.x. - This mode in some cases disallows and in other cases silently - passes corrupted data when non-Python-unicode strings (a.k.a. plain old Python strings) - are passed as arguments to connect(), the statement sent to execute(), or any of the bind - parameter keys or values sent to execute(). This optional context - therefore ensures that all statements are passed as Python unicode objects. + WITH_UNICODE allows cx_Oracle's Python 3 unicode handling + behavior under Python 2.x. This mode in some cases disallows + and in other cases silently passes corrupted data when + non-Python-unicode strings (a.k.a. plain old Python strings) + are passed as arguments to connect(), the statement sent to execute(), + or any of the bind parameter keys or values sent to execute(). + This optional context therefore ensures that all statements are + passed as Python unicode objects. """ def __init__(self, *arg, **kw): @@ -373,17 +386,19 @@ if hasattr(self.dbapi, 'version'): cx_oracle_ver = tuple([int(x) for x in self.dbapi.version.split('.')]) - self.supports_unicode_binds = cx_oracle_ver >= (5, 0) - self._cx_oracle_native_nvarchar = cx_oracle_ver >= (5, 0) else: cx_oracle_ver = None def types(*names): - return set([getattr(self.dbapi, name, None) for name in names]).difference([None]) + return set([ + getattr(self.dbapi, name, None) for name in names + ]).difference([None]) self._cx_oracle_string_types = types("STRING", "UNICODE", "NCLOB", "CLOB") self._cx_oracle_unicode_types = types("UNICODE", "NCLOB") self._cx_oracle_binary_types = types("BFILE", "CLOB", "NCLOB", "BLOB") + self.supports_unicode_binds = cx_oracle_ver >= (5, 0) + self._cx_oracle_native_nvarchar = cx_oracle_ver >= (5, 0) if cx_oracle_ver is None: # this occurs in tests with mock DBAPIs diff -r aaed76e0c5f5 -r 4ce359a25822 test/dialect/test_oracle.py --- a/test/dialect/test_oracle.py Fri Mar 19 21:42:35 2010 -0400 +++ b/test/dialect/test_oracle.py Fri Mar 19 22:29:08 2010 -0400 @@ -33,8 +33,16 @@ def test_out_params(self): result = testing.db.execute(text("begin foo(:x_in, :x_out, :y_out, :z_out); end;", - bindparams=[bindparam('x_in', Numeric), outparam('x_out', Integer), outparam('y_out', Numeric), outparam('z_out', String)]), x_in=5) - assert result.out_parameters == {'x_out':10, 'y_out':75, 'z_out':None}, result.out_parameters + bindparams=[ + bindparam('x_in', Numeric), + outparam('x_out', Integer), + outparam('y_out', Numeric), + outparam('z_out', String)]), + x_in=5) + eq_( + result.out_parameters, + {'x_out':10, 'y_out':75, 'z_out':None} + ) assert isinstance(result.out_parameters['x_out'], int) @classmethod @@ -54,7 +62,9 @@ Column('parent_id', Integer, ForeignKey('ed.parent.id')), schema = 'ed') - self.assert_compile(parent.join(child), "ed.parent JOIN ed.child ON ed.parent.id = ed.child.parent_id") + self.assert_compile( + parent.join(child), + "ed.parent JOIN ed.child ON ed.parent.id = ed.child.parent_id") def test_subquery(self): t = table('sometable', column('col1'), column('col2')) @@ -62,7 +72,8 @@ s = select([s.c.col1, s.c.col2]) self.assert_compile(s, "SELECT col1, col2 FROM (SELECT " - "sometable.col1 AS col1, sometable.col2 AS col2 FROM sometable)") + "sometable.col1 AS col1, sometable.col2 " + "AS col2 FROM sometable)") def test_limit(self): t = table('sometable', column('col1'), column('col2')) @@ -73,7 +84,8 @@ s = select([t]).limit(10).offset(20) - self.assert_compile(s, "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn " + self.assert_compile(s, + "SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn " "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 " "FROM sometable) WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1" ) @@ -128,15 +140,17 @@ anon = a_table.alias() self.assert_compile( - select([other_table, anon]).select_from( other_table.outerjoin(anon) ).apply_labels(), "SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, " - "other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, " - "thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ " - "LEFT OUTER JOIN thirty_characters_table_xxxxxx AS thirty_characters_table__1 ON " - "thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id", + "other_thirty_characters_table_.thirty_characters_table_id AS " + "other_thirty_characters__2, " + "thirty_characters_table__1.id AS thirty_characters_table__3 FROM " + "other_thirty_characters_table_ " + "LEFT OUTER JOIN thirty_characters_table_xxxxxx AS thirty_characters_table__1 " + "ON thirty_characters_table__1.id = " + "other_thirty_characters_table_.thirty_characters_table_id", dialect=dialect ) self.assert_compile( @@ -145,10 +159,13 @@ other_table.outerjoin(anon) ).apply_labels(), "SELECT other_thirty_characters_table_.id AS other_thirty_characters__1, " - "other_thirty_characters_table_.thirty_characters_table_id AS other_thirty_characters__2, " - "thirty_characters_table__1.id AS thirty_characters_table__3 FROM other_thirty_characters_table_ " + "other_thirty_characters_table_.thirty_characters_table_id AS " + "other_thirty_characters__2, " + "thirty_characters_table__1.id AS thirty_characters_table__3 FROM " + "other_thirty_characters_table_ " "LEFT OUTER JOIN thirty_characters_table_xxxxxx thirty_characters_table__1 ON " - "thirty_characters_table__1.id = other_thirty_characters_table_.thirty_characters_table_id", + "thirty_characters_table__1.id = " + "other_thirty_characters_table_.thirty_characters_table_id", dialect=ora_dialect ) @@ -185,57 +202,84 @@ "SELECT mytable.myid, mytable.name, mytable.description, myothertable.otherid, " "myothertable.othername FROM mytable, myothertable WHERE " "(mytable.name = :name_1 OR mytable.myid = :myid_1 OR " - "myothertable.othername != :othername_1 OR EXISTS (select yay from foo where boo = lar)) " + "myothertable.othername != :othername_1 OR EXISTS (select yay " + "from foo where boo = lar)) " "AND mytable.myid = myothertable.otherid(+)", dialect=oracle.OracleDialect(use_ansi = False)) - query = table1.outerjoin(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid) - self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, " - "myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff " - "FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = myothertable.otherid LEFT OUTER " - "JOIN thirdtable ON thirdtable.userid = myothertable.otherid") - self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, " - "myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM " - "mytable, myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND " - "mytable.myid = myothertable.otherid(+)", dialect=oracle.dialect(use_ansi=False)) + query = table1.outerjoin(table2, table1.c.myid==table2.c.otherid).\ + outerjoin(table3, table3.c.userid==table2.c.otherid) + self.assert_compile(query.select(), + "SELECT mytable.myid, mytable.name, mytable.description, " + "myothertable.otherid, myothertable.othername, thirdtable.userid," + " thirdtable.otherstuff " + "FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid =" + " myothertable.otherid LEFT OUTER " + "JOIN thirdtable ON thirdtable.userid = myothertable.otherid") - query = table1.join(table2, table1.c.myid==table2.c.otherid).join(table3, table3.c.userid==table2.c.otherid) - self.assert_compile(query.select(), "SELECT mytable.myid, mytable.name, mytable.description, " - "myothertable.otherid, myothertable.othername, thirdtable.userid, thirdtable.otherstuff FROM " - "mytable, myothertable, thirdtable WHERE thirdtable.userid = myothertable.otherid AND " + self.assert_compile(query.select(), + "SELECT mytable.myid, mytable.name, mytable.description, " + "myothertable.otherid, myothertable.othername, thirdtable.userid," + " thirdtable.otherstuff FROM " + "mytable, myothertable, thirdtable WHERE thirdtable.userid(+) =" + " myothertable.otherid AND " + "mytable.myid = myothertable.otherid(+)", + dialect=oracle.dialect(use_ansi=False)) + + query = table1.join(table2, table1.c.myid==table2.c.otherid).\ + join(table3, table3.c.userid==table2.c.otherid) + self.assert_compile(query.select(), + "SELECT mytable.myid, mytable.name, mytable.description, " + "myothertable.otherid, myothertable.othername, thirdtable.userid, " + "thirdtable.otherstuff FROM " + "mytable, myothertable, thirdtable WHERE thirdtable.userid = " + "myothertable.otherid AND " "mytable.myid = myothertable.otherid", dialect=oracle.dialect(use_ansi=False)) - query = table1.join(table2, table1.c.myid==table2.c.otherid).outerjoin(table3, table3.c.userid==table2.c.otherid) + query = table1.join(table2, table1.c.myid==table2.c.otherid).\ + outerjoin(table3, table3.c.userid==table2.c.otherid) self.assert_compile(query.select().order_by(table1.c.name).limit(10).offset(5), "SELECT myid, name, description, otherid, othername, userid, " "otherstuff FROM (SELECT myid, name, description, " - "otherid, othername, userid, otherstuff, ROWNUM AS ora_rn FROM (SELECT " - "mytable.myid AS myid, mytable.name AS name, mytable.description AS description, " - "myothertable.otherid AS otherid, myothertable.othername AS othername, " - "thirdtable.userid AS userid, thirdtable.otherstuff AS otherstuff FROM mytable, " - "myothertable, thirdtable WHERE thirdtable.userid(+) = myothertable.otherid AND " - "mytable.myid = myothertable.otherid ORDER BY mytable.name) WHERE " - "ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1", dialect=oracle.dialect(use_ansi=False)) + "otherid, othername, userid, otherstuff, " + "ROWNUM AS ora_rn FROM (SELECT " + "mytable.myid AS myid, mytable.name AS name, " + "mytable.description AS description, " + "myothertable.otherid AS otherid, myothertable.othername " + "AS othername, " + "thirdtable.userid AS userid, thirdtable.otherstuff AS " + "otherstuff FROM mytable, " + "myothertable, thirdtable WHERE thirdtable.userid(+) = " + "myothertable.otherid AND " + "mytable.myid = myothertable.otherid ORDER BY " + "mytable.name) WHERE " + "ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1", + dialect=oracle.dialect(use_ansi=False)) subq = select([table1]).\ - select_from(table1.outerjoin(table2, table1.c.myid==table2.c.otherid)).alias() |