Changing the sqlalchemy schema

[PierreRochard]

Hello,

(a) this is simultaneously one of the most amazing and under-appreciated free / open source libraries since the beginning of time 💯

(b) I'm trying to change the schema so that I can have the OFX tables in the same database as the rest of my tables, but separate because there are so many. FWIW I'm using PostgreSQL. I've been putzing around with models.py but continue to get this error:

sqlalchemy.exc.NoForeignKeysError: Can't find any foreign key relationships between 'acctfrom' and 'bankacctfrom'.

Here's what I did: PierreRochard@afebb4b

I'd love to hear any advice you might have, I don't have experience with the design patterns, though I'm learning a lot from this.

Thanks,

Pierre

[csingley]

Thanks for the kind words. Unfortunately I'm quite ignorant about databases and have never used schemata...but it sounds like a fine enhancement.

First, don't do that with the ForeignKey declarations. This is a job that needs to be done at the table level on the Base class.

Seond, when modifying the Base class, I don't think that's the right SQLAlchemy syntax. Perusing the docs, I think you'd need your function to return something like {'metadata': MetaData(schema='ofx')}

Third, it probably won't matter if you're just working with bank statements - but be aware that defining __table_args__ on the Base class is likely to break on the investment classes. Search for "careful" within models.py for warnings about the fragility introduced by the crazily baroque inheritance within my alleged design pattern.

If you can get something working (by, say, commenting out everything investment-related in models.py and Parser.py), I'll try to find some time to sort through that mess.

[csingley]

On further review, you're going to run into problems defining __table_args__ on the Base class even just working with bank statements. The problem is that ACCTFROM inherits from both Inheritor (which defines its own __table_args__) and Base.

A good solution (which would allow me to delete all those "careful" warnings) would be to append all __table_args__ found along the method resolution order... but this gets tricky interacting with SQLAlchemy's delayed bindings.

[PierreRochard]

Thank you for the fast and thorough response. With your advice I got it to work! PierreRochard@d8c6cea I went the low-risk route of adding the schema to each __table_args__. It's not pretty but I'll revisit it as my knowledge of inheritance improves. Using {'metadata': MetaData(schema='ofx')} caused the following recursion error:

File "/Users/Rochard/src/pacioli/venv2/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 571, in foreign_keys return self.foreign_keys File "/Users/Rochard/src/pacioli/venv2/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 747, in __get__ obj.__dict__[self.__name__] = result = self.fget(obj) RuntimeError: maximum recursion depth exceeded

I used {"schema": "cbtools"} in another project of mine (https://github.com/PierreRochard/cbtools/blob/master/cbtools/models.py#L21), not sure where I got it from.

Thank you again for the pointers, it was a quick fix!