I just tried the custom compilation rule and it works great with my small test case, I'll try it out in my larger app. Thanks again for your help.
On Fri, May 2, 2014 at 1:06 PM, Rich Larson <[email protected]> wrote: > I had tried the __tablename__ and quote:False preivously and it doesn't > work. > > If you do the quote false and put the name in __tablename__ you will get > an oracle syntax error like this: > sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-04054: database link > REMOTE.DBLINK.employee_id does not exist > 'SELECT [email protected]_id .... > > (The from clause is correct but the column names are incorrect in the > select and the where clause. ) > > I will try the custom compilation rule and let you know. > > Thanks! > =Rich > > > On Fri, May 2, 2014 at 12:34 PM, Michael Bayer > <[email protected]>wrote: > >> this could be implemented as a SQLAlchemy feature, a per-table >> “oracle_dblink_name” attribute, but currently that’s not available. >> >> What happens if you just name the table like this: >> >> class Whatever(Base): >> __tablename__ = ‘[email protected]’ >> __table_args__ = {‘quote’: False} >> >> the “quote=False” will ensure that it doesn’t try to quote that name. >> >> Another approach might be a custom compilation rule for tables. Not sure >> if the @DBLINK needs to be mentioned everywhere in the statement or just in >> the FROM clause: >> >> from sqlalchemy.ext.compiler import compiles >> from sqlalchemy import Table >> >> @compiles(Table, "oracle") >> def visit_table(element, compiler, **kw): >> super_ = compiler.visit_table(element, **kw) >> dblink = element.info.get('dblink') >> if super_ and dblink: >> super_ = super_ + "@%s" % dblink >> return super_ >> >> from sqlalchemy import * >> m = MetaData() >> >> t1 = Table('user', m, Column('id', Integer, primary_key=True)) >> t2 = Table('employee', m, Column('id', Integer, primary_key=True), >> Column('user_id', Integer), >> info={'dblink': 'MYLINK'}) >> >> >> s1 = select([t1, t2]).select_from(t1.join(t2, t1.c.id == t2.c.id)) >> >> from sqlalchemy.dialects import oracle >> print s1.compile(dialect=oracle.dialect()) >> >> this outputs: >> >> SELECT "user".id, employee.id, employee.user_id >> FROM "user" JOIN employee@MYLINK ON "user".id = employee.id >> >> just some things to try, there’s more ways to do this as well if that’s >> not enough. >> >> >> >> >> On May 2, 2014, at 12:10 PM, Rich <[email protected]> wrote: >> >> Hi Sqlalchemy developers, >> I am having some trouble with using sqlalchemy 0.9.x with oracle 11 (XE) >> while using dblinks. >> >> Looking at past discussions of using dblinks on the sqlalchemy mailing >> list it seems as if sqlalchemy can only use them if there is a synonym to >> it in the schema one is connecting to. >> >> Is there a way to set the tablename to have the @dblink name appended to >> it without having a synonym? >> >> i.e. >> "local" schema contains table: >> >> create table "local".account ( >> account_id number(10,0) not null enable, >> employee_id number(10,0), >> ... >> ); >> >> and a dblink to our other schema: >> >> CREATE PUBLIC DATABASE LINK "REMOTE.DBLINK" >> CONNECT TO "foo" IDENTIFIED BY "bar" >> USING 'remote.dblink.name.in.tnsnames.ora'; >> >> While a remote schema named "remote" contains the table: >> >> create table "remote".employee ( >> employee_id number(10,0) not null enable, >> .... >> ); >> >> When I connect to the local schema I want to generate sql similar to: >> SELECT e.* >> FROM "remote"[email protected] e JOIN "local".account a ON >> a.employee_id = e.employee_id; >> >> >> >> >> from sqlalchemy.ext.declarative import declarative_base >> >> from sqlalchemy import create_engine, Column, Integer, String >> >> from sqlalchemy.orm import sessionmaker, relationship >> >> >> >> engine = create_engine('oracle://legown:[email protected]:1527/XE') >> >> Session = sessionmaker(bind=engine) >> >> Base = declarative_base(engine) >> >> >> class Account(Base): >> __tablename__ = 'account' >> account_id = Column(Integer, primary_key=True) >> employee_id = Column(Integer) >> remote_employee = relationship( "Employee", >> primaryjoin="Employee.employee_id == Account.employee_id, >> foreign_keys=[employee_id]) >> >> def __str__(self): >> print "%s %s %s " % ( account_id, employee_id, remote_employee) >> >> class Employee(Base): >> __tablename__ = 'employee' >> __table_args__ = {'schema':'remote'} >> employee_id = Column(Integer, primary_key=True) >> >> def __str__(self): >> print "Employee remote= %s " % ( employee_id) >> >> >> #Assume that there is an account record with Id=1 and employee_id=1 and >> also an Employee record with employee_id of 1 >> >> session = Session() >> retrieved = session.query(Account).filter_by(account_id=1).first() >> print retrieved >> >> # This does not work. To make it work I can create a synonym for >> employee in local and change the the schema of employee to local. But I >> really would prefer not to have to create synonyms in my local database. >> Is this possible to achieve with sqlalchemy at this time? >> >> thanks! >> -Rich >> >> >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
