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.
