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.
