SQLAlchemy + Database Link => ORA-04054
I’ve got this SQLAclhemy table definition that describes a table I’ll be accessing over an Oracle database link:
|
1 2 |
class SomeTable(Base): # __tablename__ = 'my_table' |
I first created a database link such as this:
|
1 |
CREATE database link LINK connect to <username> identified by <password> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = <fqdn>) (Port = 1566))) (CONNECT_DATA = (UR = A) (SERVICE_NAME = <service name>)))' |
In SQLAlchemy, access a table column (in a query) via that link would look like tihs
|
1 |
MY_TABLE@LINK.MY_FIELD |
I turns out that SQLAlchemy with Oracle database links isn’t a very good match, and easily results in errors such as this:
|
1 |
(cx_Oracle.DatabaseError) ORA-04054: database link LINK.MY_FIELD does not exist |
To work around this issue, I created a synonym like this:
|
1 |
create synonym my_synonym_name for MY_TABLE@LINK |
Now, accessing the table via the database link is as easy as doing this:
|
1 2 |
class SomeTable(Base): # __tablename__ = 'my_synonym_name' |