Databases
StackOverflow2013
Postsdbo
POSQLAlchemy and Multiple Databases
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQLAlchemy and Multiple Databases
    primarykey
    data
    text
    <p>I have an assortment of similar (but not identical) databases, and would like to use SQLAlchemy as a way to "standardize" access. The databases can differ very slightly, such as having a unique prefix on the column names, or they can differ more dramatically and be missing columns (or for old databases, missing entire tables).</p> <p>What I'm looking for help on isn't so much an SQLAlchemy problem as it is a Python/Organizational one. How can I have multiple databases setup that can be reused in projects easily?</p> <p>I've read about SQLAlchemy sessions, but can't see a way of using those without instantiating each of them every project.</p> <p>My question is this: How can I go about making a module/package that will contain many database model setups to be used in SQLAlchemy that can be easily imported/used in another python project?</p> <p>I'm not so worried about dealing with the missing columns/tables as of yet. I can tackle that problem later, but it is something that needs to be kept in mind as I can't use the exact same model for each database.</p> <p>Any resources, pointers, or reading material on this topic would be truly appreciated. Thanks in advance, and I'm sorry if this has been answered elsewhere, searches didn't show anything relating to this.</p> <p><em><strong>EDIT</strong>: I've left the original intact, and am adding more content based on Paul's advice.</em></p> <p>RE: SA ORM - Yes, I plan on using the SQLAlchemy ORM. For what are likely obvious reasons, I can't provide real databases. However, assume these three fictitious databases, aptly named DB1, DB2, and DB3 (we'll assume one table in each, with only a few columns, real world would have significantly more of both).</p> <p>Each database has a user table with a few columns in each. The following is some SQL notation for the tables/columns:</p> <pre><code>DB1.user --&gt; DB1.user.id, DB1.user.username, DB1.user.email DB2.user --&gt; DB2.user.id, DB2.user.user_name, DB2.user.email DB3._user --&gt; DB3._user.userid, DB3._user.username, DB3.user.email_address </code></pre> <p>At present, I am trying to separate out these databases to 'modular', and be able to just add additional databases as I go.</p> <p>I've consider a couple different file organization aspects (assume __init__.py exists where needed, but omitted for brevity's sake), including:</p> <pre><code>Databases | Databases | Databases DB1.py | DB1 | DB1 DB2.py | models.py | models DB3.py | DB2 | user.py | models.py | anothertable.py | DB2 | ... | models.py | DB3 | | models | | user.py | | anothertable.py </code></pre> <p>I'd love to be able to access these with the SA ORM, and do so with as little importing/declarations as possible when it comes time to use these databases in a python file. Needing to do something similar to:</p> <pre><code>from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from Database import DB1, ..., DB[N] db1_engine = create_engine('connect_string/db1') db1_session = sessionmaker(bind=db1_engine)() ... db3_engine = create_engine('connect_string/db3') db3_session = sessionmaker(bind=db3_engine)() </code></pre> <p>would be incredibly cumbersome as I'll be dealing with far more than just three databases. I'd much rather have that already taken care of for me (in the __init__.py file, maybe?)</p> <p>Being able to access and use it similar to:</p> <pre><code>import Databases Databases.DB1.session.query('blahblah') </code></pre> <p>would be infinitely better.</p> <p><strong>EDIT2</strong>: I also know how to get around the variants in the naming conventions of the databases/columns when setting up my models. That isn't an issue, but I did mention it so that it was known I can't just use one model set for multiple databases.</p> <p>I hope by expanding this I didn't muddy the waters or make this too confusing. Thanks for taking the time to read it!</p> <p><strong>EDIT3</strong>: I've managed to spend a little more time on this. I've set up the project in the following way:</p> <pre><code>Databases __init__.py databases.py settings.py DB1 __init__.py models.py ... DB3 __init__.py models.py </code></pre> <p>At present, I have a tupple of databases that are 'installed' in the settings.py file. Each entry is would like like <code>INSTALLED_DATABASES = ('DB1', ..., 'DB3')</code>. As I complete more models, and they get added to the list of tupples. This allows me to add or remove content as I go.</p> <p>I have the engine and sessios setup inside the models.py file, and have the <strong>init.py</strong> file for each database setup to <code>from models import *</code>.</p> <p>In the databases.py file I have the following </p> <pre><code>class Databases(object): def __init__(self): for database in INSTALLED_DATABASES: setattr(self, database, __import__(database)) </code></pre> <p>I can now use these via:</p> <pre><code>from databases import Databases db = Databases() for qr in db.DB1.query(db.DB1.User): print qr.userid, qr.username </code></pre> <p>SQLAlchemy is allowing me to manually specify column names when defining the models, which is a huge bonus to the standardization I wanted.</p> <p>I've got a lot of work ahead of me still. I would like to create objects that force model validation (ie, is a field present? does a non-present field have a default value? etc.) and better incorporate how this works with my IDE (it kind of doesn't at present). But I'm off to the right track. I figured I'd update this for anyone who might by chance be wondering how to do the same thing I was.</p> <p>Sorry this has become so long!</p> <p>Cheers!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRejected
    1. USRejected
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQLAlchemy and Multiple Databases
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQLAlchemy and Multiple Databases
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQLAlchemy and Multiple Databases
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI've added some additional information to this as EDIT3. I've got it *kinda* working how I want it, but will need to continue to improve it. I'll have to make continuations on this problem into different questions, as the scope of the original has been answered. So, I don't expect I'll update this unless I change something drastic, or make serious advances on how it work. If anyone has any suggestions on how to do this better though, I'm all ears. Thanks for reading.
      singulars
      1. POSQLAlchemy and Multiple Databases
      1. USRejected
    2. COI am not sure this can help your problem, but maybe it can add something for your. This is a post from zzeek and while reading your question I was partly reminded about it: http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy
      singulars
      1. POSQLAlchemy and Multiple Databases
      1. USjavex
    3. COI would earnestly recommend taking your last edit and making it the answer - it's a good one and answering your own question is smiled on around here :-)
      singulars
      1. POSQLAlchemy and Multiple Databases
      1. USSean Vieira
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload