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.
