Relationships between different databases?

[Carl Brewer]

I have three databases to work with, a PostgreSQL database on one
server, and two MySQL databases on another.

I've set up three different engines to connect to and reflect each one,
the databases and tables within don't have relationships defined in the
databases themselves, there's not a lot of foreign keys!

I'm wondering if it's possible to use SQLAlchemy to map out
relationships between them? Only within the particular bit of software
I'm working on, I don't expect it to export back into the databases - at
the moment I'm only using read-only access to the databases anyway.

Possible within the SQLAlchemy environment?

Thank you

Carl

[Philip Semanchuk]

Hi Carl,
I have no experience with this so I might be making a poor suggestion, but Postgres’ foreign data wrappers might help you. They can expose the schema of other databases, so your Postgres database could be the host for all three databases that you need to connect to. That would make it look like one database to SQLAlchemy.

https://www.postgresql.org/docs/current/sql-createforeigndatawrapper.html


Good luck
Philip

[Carl Brewer]

On 17/08/2022 10:31 pm, Philip Semanchuk wrote:
>
>

>
> Hi Carl,
> I have no experience with this so I might be making a poor suggestion, but Postgres’ foreign data wrappers might help you. They can expose the schema of other databases, so your Postgres database could be the host for all three databases that you need to connect to. That would make it look like one database to SQLAlchemy.

Tempting, but I can only poke at the postgres database, I can't change it.

Worth a look though, thank you

Carl

[Mike Bayer]

you can query the three databases with one session, docs on that are at https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#simple-vertical-partitioning

from there, you can make *really basic* relationships between classes that are in two databases.  as far as how they load, it *in theory* should work with lazyload and immediateload and maybe even selectinload, as all of these strategies generally query one table at a time and should target to the appropriate database, though I've never tried it with selectinload.    persistence should work as it targets per-table database binds.

I dont know how often people do something like this since I never hear about it, but we've supported it since the beginning.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

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 sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f0a0380d-3c61-cf9c-8a5a-b1d8fc16d3b4%40bl.echidna.id.au.

[Carl Brewer]

On 17/08/2022 11:22 pm, Mike Bayer wrote:
> you can query the three databases with one session, docs on that are at

> https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#simple-vertical-partitioning <https://docs.sqlalchemy.org/en/14/orm/persistence_techniques.html#simple-vertical-partitioning>

Great, thank you!

Carl