oracle dblink syntax
seems like an @ sign isn't provided to us by the SYNONYMS view:
diff -r e2697d547ec8c24c9a37a72fc60abe73b7dee81b lib/sqlalchemy/dialects/oracle/base.py
--- a/lib/sqlalchemy/dialects/oracle/base.py Sat Nov 24 16:14:58 2012 -0500
+++ b/lib/sqlalchemy/dialects/oracle/base.py Tue Nov 27 10:40:50 2012 -0500
@@ -841,6 +841,8 @@
actual_name = self.denormalize_name(table_name)
if not dblink:
dblink = ''
+ elif dblink[0](0) != '@':
+ dblink = "@" + dblink
if not owner:
owner = self.denormalize_name(schema or self.default_schema_name)
return (actual_name, owner, dblink, synonym)
Comments (12)
-
Account Deleted -
Account Deleted (original author: teddy) I'm tryed to change base.py like that:
*** basediff.py 2012-11-28 11:04:03.869209700 +0400 --- base.py 2012-11-28 11:02:45.559730700 +0400 *************** *** 882,890 **** c = connection.execute(sql.text( "SELECT column_name, data_type, %(char_length_col)s, data_precision, data_scale, " "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s " ! "WHERE table_name = :table_name AND owner = :owner " "ORDER BY column_id" % {'dblink': dblink, 'char_length_col':char_length_col}), ! table_name=table_name, owner=schema) for row in c: (colname, orig_colname, coltype, length, precision, scale, nullable, default) = \ --- 882,890 ---- c = connection.execute(sql.text( "SELECT column_name, data_type, %(char_length_col)s, data_precision, data_scale, " "nullable, data_default FROM ALL_TAB_COLUMNS%(dblink)s " ! "WHERE table_name = :table_name " "ORDER BY column_id" % {'dblink': dblink, 'char_length_col':char_length_col}), ! table_name=table_name) for row in c: (colname, orig_colname, coltype, length, precision, scale, nullable, default) = \
And now table reflected:
skv = Table("swells", meta, oracle_resolve_synonyms=True, autoload=True) >>> meta.tables immutabledict({'swells': Table('swells', MetaData(bind=Engine(...)), Column(...), schema=None)})
It's interesting that for now table name accepted in lowercase too...
-
Account Deleted (original author: teddy) And some info about table from database linked:
SQL> SELECT owner, table_name FROM all_tables WHERE table_name='СКВАЖИНЫ_ЦЕЛИКОМ'; OWNER TABLE_NAME ------------------------------ ------------------------------ SELENA СКВАЖИНЫ_ЦЕЛИКОМ
So, as we can see, Oracle doesn't shows owner if table is in remote db... Looks like this happens because owner of table is present only in remote base, there is no such user in local.
-
reporter can you try the attached patch against the latest default tip:
http://hg.sqlalchemy.org/sqlalchemy/archive/9d817406e2da0bda91c1e9a1411165f229e3d5c2.tar.gz
this patch attempts to ignore the "owner" parameter for all queries if dblink is present.
-
reporter - changed status to resolved
OK, well I was able to test this by creating a DBLINK in oracle XE to itself, though this test involved the same "owner" on both sides. I have it reading "username" from user_db_links right now in order to determine the username, we'll see how that goes. Reopen if futher issues.
-
Account Deleted (original author: teddy) Sorry, it take some time to test between work. Found a little bug, which appears when there is no owner. This patch for default tip.
-
Account Deleted - changed status to open
- removed status
(original author: teddy)
-
Account Deleted (original author: teddy) Oh, yeah, the bug was:
sqlalchemy.exc.StatementError: A value is required for bind parameter u'table_nameORDER' (original cause: InvalidRequestError: A value is required for bind parameter u'table_nameORDER')
Sorry for my stupidity at the night =)
-
reporter yeah, I fixed that already. try out the latest tip. just need to see if what I'm doing for "owner" works for you.
-
Account Deleted (original author: teddy) Thank you for your attention and help! With latest default tip (http://hg.sqlalchemy.org/sqlalchemy/archive/70d38af42e42016601ad81119b42f7bc381f5f43.tar.gz) everything seems to work fine (full log for debug):
ActivePython 2.7.2.5 (ActiveState Software Inc.) based on Python 2.7.2 (default, Jun 24 2011, 12:21:10) [v.1500 32 bit (Intel)](MSC) on win32 Type "help", "copyright", "credits" or "license" for more information. >>> from sqlalchemy import * >>> engine = create_engine('oracle://<user>:<pass>@<IP>:1521/omega', encoding = "windows-1251", echo='debug') >>> meta = MetaData(bind=engine) >>> skv = Table("WELLI", meta, oracle_resolve_synonyms=True, autoload=True) 2012-12-10 17:42:25,118 INFO sqlalchemy.engine.base.Engine SELECT USER FROM DUAL 2012-12-10 17:42:25,118 INFO sqlalchemy.engine.base.Engine {} 2012-12-10 17:42:25,121 DEBUG sqlalchemy.engine.base.Engine Col ('USER',) 2012-12-10 17:42:25,121 DEBUG sqlalchemy.engine.base.Engine Row (u'OMEGA',) 2012-12-10 17:42:25,128 INFO sqlalchemy.engine.base.Engine SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE synonym_name = :synonym_name 2012-12-10 17:42:25,128 INFO sqlalchemy.engine.base.Engine {'synonym_name': u'WELLI'} 2012-12-10 17:42:25,255 DEBUG sqlalchemy.engine.base.Engine Col ('OWNER', 'TABLE_OWNER', 'TABLE_NAME', 'DB_LINK', 'SYNONYM_NAME') 2012-12-10 17:42:25,256 DEBUG sqlalchemy.engine.base.Engine Row (u'OMEGA', None, u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b', u'SELENA', u'WELLI') 2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine SELECT username FROM user_db_links WHERE db_link=:link 2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine {'link': u'SELENA'} 2012-12-10 17:42:25,269 DEBUG sqlalchemy.engine.base.Engine Col ('USERNAME',) 2012-12-10 17:42:25,272 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS@SELENA WHERE table_name = :table_name ORDER BY column_id 2012-12-10 17:42:25,273 INFO sqlalchemy.engine.base.Engine {'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b'} 2012-12-10 17:42:29,828 DEBUG sqlalchemy.engine.base.Engine Col ('COLUMN_NAME', 'DATA_TYPE', 'CHAR_LENGTH', 'DATA_PRECISION', 'DATA_SCALE', 'NULLABLE', 'DATA_DEFAULT') 2012-12-10 17:42:29,924 DEBUG sqlalchemy.engine.base.Engine Row (u'\u0421\u041a\u0412\u0410\u0416_\u0418\u0414', u'NUMBER', 0, None, None, u'N', None) 2012-12-10 17:42:29,924 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041d\u041e\u041c\u0415\u0420_\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b', u'NUMBER', 0, None, None, u'N', None) 2012-12-10 17:42:29,926 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041b\u0418\u0422\u0415\u0420\u0410', u'VARCHAR2', 5, None, None, u'Y', None) 2012-12-10 17:42:29,926 DEBUG sqlalchemy.engine.base.Engine Row (u'\u0421\u0422\u0420\u0423\u041a\u0422\u0423\u0420\u041d\u042b\u0419_\u041d\u041e\u041c\u0415\u0420', u'VARCHAR2', 20, None, None, u'N', None) 2012-12-10 17:42:29,926 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041a\u041f\u041b_\u041a\u041f\u041b_\u0418\u0414', u'NUMBER', 0, None, None, u'N', None) 2012-12-10 17:42:29,927 DEBUG sqlalchemy.engine.base.Engine Row (u'\u041a\u041e\u0414_OIS', u'VARCHAR2', 20, None, None, u'Y', None) 2012-12-10 17:42:29,927 INFO sqlalchemy.engine.base.Engine SELECT ac.constraint_name, ac.constraint_type, loc.column_name AS local_column, rem.table_name AS remote_table, rem.column_name AS remote_column, rem.owner AS remote_owner, loc.position as loc_pos, rem.position as rem_pos FROM all_constraints@SELENA ac, all_cons_columns@SELENA loc, all_cons_columns@SELENA rem WHERE ac.table_name = :table_name AND ac.constraint_type IN ('R','P') AND ac.owner = loc.owner AND ac.constraint_name = loc.constraint_name AND ac.r_owner = rem.owner(+) AND ac.r_constraint_name = rem.constraint_name(+) AND (rem.position IS NULL or loc.position=rem.position) ORDER BY ac.constraint_name, loc.position 2012-12-10 17:42:29,927 INFO sqlalchemy.engine.base.Engine {'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b'} 2012-12-10 17:42:35,118 DEBUG sqlalchemy.engine.base.Engine Col ('CONSTRAINT_NAME', 'CONSTRAINT_TYPE', 'LOCAL_COLUMN', 'REMOTE_TABLE', 'REMOTE_COLUMN', 'REMOTE_OWNER', 'LOC_POS', 'REM_POS') 2012-12-10 17:42:35,121 DEBUG sqlalchemy.engine.base.Engine Row (u'\u0421\u041a\u0412\u0410\u0416_PK', u'P', u'\u0421\u041a\u0412\u0410\u0416_\u0418\u0414', None, None, None, 1, None) 2012-12-10 17:42:35,124 INFO sqlalchemy.engine.base.Engine SELECT a.index_name, a.column_name, b.uniqueness FROM ALL_IND_COLUMNS a, ALL_INDEXES b WHERE a.index_name = b.index_name AND a.table_owner = b.table_owner AND a.table_name = b.table_name AND a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position 2012-12-10 17:42:35,131 INFO sqlalchemy.engine.base.Engine {'table_name': u'WELLI', 'schema': u'OMEGA'} 2012-12-10 17:42:35,134 DEBUG sqlalchemy.engine.base.Engine Col ('INDEX_NAME', 'COLUMN_NAME', 'UNIQUENESS') 2012-12-10 17:42:35,138 INFO sqlalchemy.engine.base.Engine SELECT ac.constraint_name, ac.constraint_type, loc.column_name AS local_column, rem.table_name AS remote_table, rem.column_name AS remote_column, rem.owner AS remote_owner, loc.position as loc_pos, rem.position as rem_pos FROM all_constraints ac, all_cons_columns loc, all_cons_columns rem WHERE ac.table_name = :table_name AND ac.constraint_type IN ('R','P') AND ac.owner = :owner AND ac.owner = loc.owner AND ac.constraint_name = loc.constraint_name AND ac.r_owner = rem.owner(+) AND ac.r_constraint_name = rem.constraint_name(+) AND (rem.position IS NULL or loc.position=rem.position) ORDER BY ac.constraint_name, loc.position 2012-12-10 17:42:35,151 INFO sqlalchemy.engine.base.Engine {'owner': u'OMEGA', 'table_name': u'WELLI'} 2012-12-10 17:42:37,703 DEBUG sqlalchemy.engine.base.Engine Col ('CONSTRAINT_NAME', 'CONSTRAINT_TYPE', 'LOCAL_COLUMN', 'REMOTE_TABLE', 'REMOTE_COLUMN', 'REMOTE_OWNER', 'LOC_POS', 'REM_POS') >>> conn = engine.connect() >>> q = select([skv](skv)) >>> r = conn.execute(q) 2012-12-10 17:43:20,000 INFO sqlalchemy.engine.base.Engine SELECT "WELLI"."СКВАЖ_ИД", "WELLI"."НОМЕР_СКВАЖИНЫ", "WELLI"."ЛИТЕРА", "WELLI"."СТРУКТУРНЫЙ_НОМЕР", "WELLI"."КПЛ_КПЛ_ИД", "WELLI"."КОД_OIS" FROM "WELLI" 2012-12-10 17:43:20,000 INFO sqlalchemy.engine.base.Engine {} 2012-12-10 17:43:20,006 DEBUG sqlalchemy.engine.base.Engine Col ('\xd1\xca\xc2\xc0\xc6_\xc8\xc4', '\xcd\xce\xcc\xc5\xd0_\xd1\xca\xc2\xc0\xc6\xc8\xcd\xdb', '\xcb\xc8\xd2\xc5\xd0\xc0', '\xd1\xd2\xd0\xd3\xca\xd2\xd3\xd0\xcd\xdb\xc9_\xcd\xce\xcc\xc5\xd0', '\xca\xcf\xcb_\xca\xcf\xcb_\xc8\xc4', '\xca\xce\xc4_OIS') >>> t = r.fetchone() 2012-12-10 17:43:41,759 DEBUG sqlalchemy.engine.base.Engine Row (3175, 10114, None, u'10114', 527, None) >>> t (3175.0, 10114.0, None, u'10114', 527.0, None)
-
reporter - changed status to resolved
great, that worked perfectly, we can see that your DB returned no "username" for the DB_LINK:
2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine SELECT username FROM user_db_links WHERE db_link=:link 2012-12-10 17:42:25,259 INFO sqlalchemy.engine.base.Engine {'link': u'SELENA'} 2012-12-10 17:42:25,269 DEBUG sqlalchemy.engine.base.Engine Col ('USERNAME',)
and then we just didn't use OWNER in the queries:
2012-12-10 17:42:25,272 INFO sqlalchemy.engine.base.Engine SELECT column_name, data_type, char_length, data_precision, data_scale, nullable, data_default FROM ALL_TAB_COLUMNS@SELENA WHERE table_name = :table_name ORDER BY column_id 2012-12-10 17:42:25,273 INFO sqlalchemy.engine.base.Engine {'table_name': u'\u0421\u041a\u0412\u0410\u0416\u0418\u041d\u042b'} 2012-12-10 17:42:29,828
on my test setup, I get "username" back, probably because I made the dblink a fixed user.
-
reporter - removed milestone
Removing milestone: 0.8.0final (automated comment)
- Log in to comment
(original author: teddy) Hi! Thanks for your reply!
Here's output of
(Oracle required synonym_name in uppercase, otherwise it returns nothing):
Full dump from python console:
Results after applying a patch on /lib/sqlalchemy/dialects/oracle/base.py: