[sqlalchemy] Re: Having trouble getting a subquery to return entities
Could it be that a difference between the declarative for a table and
the underlying table could result in the failure as first described?
In the unit test, setUp deletes all tables in the setup, then
recreates all tables anew, then populates them. Since then there have
not been any issues.
(I guess I should look into Alembic, eh?)
Thanks for your help.
On Feb 6, 11:36 am, cbc clayton.cafi...@mondaylambson.com wrote:
Michael:
I created a full reproducing test (http://pastebin.com/vutfUgpk) and
the test ... WORKS!
So, there's obviously something else creeping in here somewhere. It
was useful for me to create the test. Thanks for the suggestion.
I will close the loop when I find the cause of my original issue.
THANKS
-C
On Feb 3, 9:45 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Feb 3, 2012, at 11:28 AM, cbc wrote:
Hi:
I'm having trouble getting a subquery to return entities.
class Question(Base):
__tablename__ = 'question'
question_id = Column(INTEGER(unsigned=True), primary_key=True)
...etc
q_answers = relationship(Answer, backref=question)
class Answer(Base):
__tablename__ = 'answer'
answer_id = Column(INTEGER(unsigned=True), primary_key=True)
user_id = Column(INTEGER(unsigned=True), ForeignKey('user.user_id'),
nullable=False)
question_id = Column(INTEGER(unsigned=True),
ForeignKey('question.question_id'), nullable=False)
...etc
stmt = session.query(Answer).filter(Answer.user_id ==
user_id).subquery()
answers = aliased(Answer, stmt)
query = session.query(Question, answers)\
.outerjoin(answers, Question.q_answers)\
.filter(Question.question_group_id == question_group_id)
questions = query.all()
This generates MySQL that returns all desired columns and returns NULL
if question has not yet been answered by the specified user. Groovy so
far.
I was expecting tuples in the list of questions (Answer, Question),
but the first element is always None. e.g.
dir(questions[0])
[None, 'Question', ... etc
So while I'm expecting the subquery results to be understood as
entities (Answer), this isn't happening. But I cannot figure out why.
Where have I failed?
The outerjoin here is from Question to Answer so you should never have None
in the first element of the tuple. I'd need a full reproducing test to see
exactly what would cause Question to be retuned as None.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Possible bug with join condition on self-referential outer joins
Ok, thank you! :) 2012/2/8 Michael Bayer mike...@zzzcomputing.com OK, a misunderstanding, the proof of concept is working as of a specific version (rf41aa3ad1da9) - if you've just checked out the branch then you're seeing a refactoring of code which is in flux, and that stack trace is specifically failing on new code that isn't actually doing anything other than being compared against what the usual relationship() mechanics produce given a particular input. It isn't yet implemented for the local_remote_side parameter in particular. The entire system is not nearly ready for testing in any case, just wanted to report that a strategy has been devised that solves the issue - thanks ! On Feb 8, 2012, at 5:31 AM, Pau Tallada wrote: Uhm, the test I attached on previous mail fails using this branch: $ python test_relationships.py Traceback (most recent call last): File test_relationships (1).py, line 53, in module c1 = Company() File string, line 2, in __init__ File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/instrumentation.py, line 309, in _new_state_if_none File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/util/langhelpers.py, line 485, in __get__ File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/instrumentation.py, line 157, in _state_constructor File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/event.py, line 274, in __call__ File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/mapper.py, line 2325, in _event_on_first_init File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/mapper.py, line 2250, in configure_mappers File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/mapper.py, line 1166, in _post_configure_properties File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/interfaces.py, line 128, in init File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/properties.py, line 917, in do_init File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/properties.py, line 945, in _create_new_thing File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/relationships.py, line 58, in __init__ File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/relationships.py, line 246, in _parse_joins File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/sql/visitors.py, line 234, in cloned_traverse File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/sql/visitors.py, line 227, in clone File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/sql/expression.py, line 2998, in _copy_internals File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/sql/visitors.py, line 230, in clone File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/relationships.py, line 241, in visit_binary File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/relationships.py, line 165, in _run_w_switch File /home/tallada/Projectes/PIC/multivac/trunk/lib/sqlalchemy_1401.zip/sqlalchemy/orm/relationships.py, line 222, in go NotImplementedError 2012/2/8 Pau Tallada tall...@pic.es Thank you very much! I'll test it ASAP :) 2012/2/8 Michael Bayer mike...@zzzcomputing.com I have good news on this front, in that I've nailed down how this will work, including a patch that gets this basic thing working as a proof of concept. However, the issue of being able to distinguish remote and foreign in a binary expression where a column points to itself can benefit from moving completely to the newer concept I'm working on, which is different enough that I think it should be for the next major SQLAlchemy release (currently it's called 0.8).I'll keep the ticket updated with progress reports. On Feb 1, 2012, at 10:49 AM, Michael Bayer wrote: This is essentially ticket #1401 and I've attached this there as well as moved up the priority, however this issue is extremely complicated and would require some serious rethinking of the relationship()'s inner workings.It would take several days to come up with a general solution so I can't give you a fix for this right now. http://www.sqlalchemy.org/trac/ticket/1401 On Feb 1, 2012, at 5:40 AM, Pau Tallada wrote: Hi! I have a table with a self-reference of two columns that represents a tree structure. I was trying to build an outerjoin to select all the nodes have children but NO grandchildren, but the SQL constructed was incorrect, as it was not aliasing properly one of the columns.
[sqlalchemy] PDF documentation 0.5
Hi there. I just tried to download the PDF documentation for 0.5 from the documentation page[0], and the link seems to be broken (404). Sorry if it has been brought to the list already, first time post here, I checked the archive and couldn't find anything related. [0] http://docs.sqlalchemy.org/en/rel_0_5/index.html -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How do I change enum values after previously creating a table?
On Feb 8, 2012, at 11:11 PM, Jackson, Cameron wrote: Mike, may I suggest this as an enhancement: If SQLA is creating a table with an Enum column, and if there's already an Enum with the specified name, it should spit out an error if the list of values for the existing and requested Enums don't match? Maybe, though would that same logic extend to other constructs ? Such as, if you had a Table, and you said metadata.create_all(), that particular Table already exists. Should SQLAlchemy go out, reflect that table and compare all the column names to the Table you have and report an error ?Seems like the same thing. Alembic does this comparison, but it's more of a helper to write migrations rather than a consistency checker, since we can't reliably match everything about the database to what's defined in Python. - DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. - -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] PDF documentation 0.5
Yeah perhaps my move to readthedocs was premature, as they really don't have the PDF building thing down as well as I'd hoped. I've sent an email to their list on this one. The 0.7 PDF also has the wrong version number in it. I've copied all the existing PDFs to my host here: http://www.sqlalchemy.org/doc_pdfs/ you can yank it from there. On Feb 9, 2012, at 10:18 AM, Claudio Freire wrote: Hi there. I just tried to download the PDF documentation for 0.5 from the documentation page[0], and the link seems to be broken (404). Sorry if it has been brought to the list already, first time post here, I checked the archive and couldn't find anything related. [0] http://docs.sqlalchemy.org/en/rel_0_5/index.html -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Understanding sqlalchemy memory usage and releasing unused memory
Hey Thanks for the answer even after my half researched questions which I should not have done. :) Yeah I realised that after posting the question and I dig deep into python memory issues and have improved knowledge a lot. In fact objgraph module is a great one for understanding these issues. It helped in understanding sqlalchemy a lot by drawing great detailed graphs. On Feb 7, 3:18 pm, Gunnlaugur Briem gunnlau...@gmail.com wrote: Hi Manav, the final question did touch SQLAlchemy: the one about expiring and expunging. 1. expiring an object creates a weak reference - this is inaccurate. All object references held by the the session (except for objects whose addition, deletion or attribute change is not yet flushed) are weak references in the first place, seehttp://docs.sqlalchemy.org/en/latest/orm/session.html#session-attributes and expiring an object just marks its attributes out of date, it does not change the session's reference to the object itself 2. Does expunging an object do the same - expunging an object means the session no longer holds a reference (weak or otherwise) to that object. But the reference was weak in the first place (unless the object was in new, dirty or deleted), so expunging neither helps nor hurts in getting the object collected. Your other questions do not involve SQLAlchemy, and that's why nobody here is answering them. You might find some other group where questions about python basics and process memory management are in scope ... but the One True Way to learn these things is to grit your teeth and google and read. That may be more work than you were hoping, but such is life. :) Also, this talk is good:http://blip.tv/pycon-us-videos-2009-2010-2011/pycon-2011-dude-where-s... - Gulli -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] PDF documentation 0.5
On Thu, Feb 9, 2012 at 1:28 PM, Michael Bayer mike...@zzzcomputing.com wrote: I've copied all the existing PDFs to my host here: http://www.sqlalchemy.org/doc_pdfs/ you can yank it from there. Got it. Thanks :-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Versioning of Many-to-Many relationships
*I have a many to many relationship between musician and genre indicating that a particular musician performs in the style of a particular genre: Musician -- musician_genre -- Genre Musician and Genre are both versioned using VersionedMeta so it is easy for me to display a history of the attributes for a particular record. The hard part is displaying the history of the associations between them. For any particular musician or genre, I want to be able to display the association historically, for example: Genere 17: Time1: [Musician 12] Time2: [Musician 12, Musician 3, Musician 5] Time3: [Musician 12, Musician 6] etc... Of course I would also want to perform the complimentary action: Musician 8: Time1: [Genre 8] Time2: [Genre 8, Genre 17] etc... Approaches that seem bad to me: 1) Serializing a list of musician IDs directly attached to the genre object and vice versa. 2) Using VersionedMeta on the association object, musician_genre. Does anyone have any other ideas as to how I might accomplish this? Thanks, Michael* -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] declarative versus classes mapped to multiple engines
Hi Again, I'm wondering if the use case I have is one that is supported... So, the situation is that I have a bunch of classes that I need to map to a bunch of tables, and I'd prefer to do that declaratively. The spicey bit is that I need to connect to several environments a lot of the time and not all of these tables are available in all environments. So, my plan is to have one engine per database I connect to. But what to do about tables? Am I right in thinking that I should have one MetaData object per engine, and that MetaData object should only have the tables in it that are actually present in that database? If so, what's the recommended pattern for doing that? Now, what about declarative? Declarative seems to like each class to have a MetaData object, but how do I tie that in with multiple engines and some tables not being present in some engines? What about the declarative registry? I guess having only one of those is fine since it just maps a string name to a class object, right? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] reflected declarative versus single table inheritance
On 09/02/2012 15:47, Chris Withers wrote:
My thinking was leaning towards actually abandoning the full declarative
base and coming up with a light weight one (or maybe even class
decorator) that basically added recorded the class in a sequence (much
like the declarative reflection does) and then calls
instrument_declarative with each class at the point of relfection.
Well, it's a bit vom-tastic but I got this working. Here it is minus the
imports:
# The registries
meta = MetaData()
registry = {}
# The declarative bases
class ReflectedMeta(type):
_classes_to_process = []
def __new__(meta, classname, bases, classDict):
cls = type.__new__(meta, classname, bases, classDict)
meta._classes_to_process.append(cls)
return cls
@classmethod
def prepare(meta, engine, metadata):
for cls in meta._classes_to_process:
tablename = vars(cls).get('__tablename__')
if tablename is not None:
try:
table = Table(
tablename,
MetaData(),
autoload=True,
autoload_with=engine,
)
except NoSuchTableError:
continue
for col in table.c:
if getattr(cls, col.name, None) is None:
col = col.copy()
if col is None:
raise Exception()
setattr(cls, col.name, col)
kw = vars(cls).get('__mapper_args__')
if kw is not None:
# deal with cases where we need to specify the primary key
# structure
primary_key_names = kw.pop('primary_key_names', None)
if primary_key_names:
kw['primary_key'] = pk = []
for name in primary_key_names:
pk.append(getattr(cls, name))
# Likewise for polymorphic_on
polymorphic_on_name = kw.pop('polymorphic_on_name', None)
if polymorphic_on_name:
kw['polymorphic_on'] = getattr(cls,
polymorphic_on_name)
instrument_declarative(cls, registry, metadata)
# The this one for reflected models:
class Reflected(object):
__abstract__ = True
__metaclass__ = ReflectedMeta
# Use this one for concretely mapped models:
Base = declarative_base(metadata=meta, class_registry=registry)
Then, once I have a connection and I'm ready to reflect, I do:
ReflectedMeta.prepare(engine, meta)
Is the above sane?
Chris
--
Simplistix - Content Management, Batch Processing Python Consulting
- http://www.simplistix.co.uk
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] reflected declarative versus single table inheritance
On Feb 9, 2012, at 1:33 PM, Chris Withers wrote:
On 09/02/2012 15:47, Chris Withers wrote:
My thinking was leaning towards actually abandoning the full declarative
base and coming up with a light weight one (or maybe even class
decorator) that basically added recorded the class in a sequence (much
like the declarative reflection does) and then calls
instrument_declarative with each class at the point of relfection.
OK I'm not sure if you are on 0.7.5 but the string name of the column is
accepted for polymorphic_on.The recipe just needs to fill in the
'inherits' keyword within prepare(), and that's it. See below:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.orm.util import _is_mapped_class
from sqlalchemy.ext.declarative import declarative_base, declared_attr
class DeclarativeReflectedBase(object):
_mapper_args = []
@classmethod
def __mapper_cls__(cls, *args, **kw):
Declarative will use this function in lieu of
calling mapper() directly.
Collect each series of arguments and invoke
them when prepare() is called.
cls._mapper_args.append((args, kw))
@classmethod
def prepare(cls, engine):
Reflect all the tables and map !
while cls._mapper_args:
args, kw = cls._mapper_args.pop(0)
klass = args[0]
# autoload Table, which is already
# present in the metadata. This
# will fill in db-loaded columns
# into the existing Table object.
if args[1] is not None:
table = args[1]
Table(table.name,
cls.metadata,
extend_existing=True,
autoload_replace=False,
autoload=True,
autoload_with=engine,
schema=table.schema)
for c in klass.__bases__:
if _is_mapped_class(c):
kw['inherits'] = c
break
klass.__mapper__ = mapper(*args, **kw)
Base = declarative_base()
class Reflected(DeclarativeReflectedBase, Base):
__abstract__ = True
class GeneralType(Reflected):
__tablename__ = 'single_inherits'
__mapper_args__ = dict(polymorphic_on='type')
class TypeOne(GeneralType):
__mapper_args__ = dict(polymorphic_identity='one')
class TypeTwo(GeneralType):
__mapper_args__ = dict(polymorphic_identity='two')
e = create_engine('sqlite://', echo=True)
e.execute(
create table single_inherits(
id integer primary key,
type varchar(30)
)
)
Reflected.prepare(e)
s = Session(e)
s.add_all([
TypeOne(type=one), TypeOne(type=one), TypeTwo(type=two)
])
s.commit()
s.close()
print s.query(GeneralType).all()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declarative versus classes mapped to multiple engines
On Feb 9, 2012, at 1:42 PM, Chris Withers wrote: Hi Again, I'm wondering if the use case I have is one that is supported... So, the situation is that I have a bunch of classes that I need to map to a bunch of tables, and I'd prefer to do that declaratively. The spicey bit is that I need to connect to several environments a lot of the time and not all of these tables are available in all environments. So, my plan is to have one engine per database I connect to. But what to do about tables? So what does it mean for your application to import a module, that has a class MyClass, which should be mapped to a table, but when the app is running, that table essentially doesn't exist ? Does that render MyClass useless and if so why import it ? Otherwise, if it is still useful, and I'm guessing you're using the declared reflection recipe, you'd need to enhance the usage of prepare() such that the class is not actually mapped, since there is no table. Or you do something else. This is all doable. Am I right in thinking that I should have one MetaData object per engine, and that MetaData object should only have the tables in it that are actually present in that database? If so, what's the recommended pattern for doing that? Check this post, read the section Model Setup: http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ Now, what about declarative? Declarative seems to like each class to have a MetaData object, but how do I tie that in with multiple engines and some tables not being present in some engines? What about the declarative registry? I guess having only one of those is fine since it just maps a string name to a class object, right? its all there, yup -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declarative versus classes mapped to multiple engines
On 09/02/2012 19:51, Michael Bayer wrote: So, my plan is to have one engine per database I connect to. But what to do about tables? So what does it mean for your application to import a module, that has a class MyClass, which should be mapped to a table, but when the app is running, that table essentially doesn't exist ? Does that render MyClass useless Yes. and if so why import it ? Thing's like Pyramid config's scan (not the case here) and nose (one of the issues here) mean that it should be importable but not cause anything (including prepare) to blow up. Otherwise, if it is still useful, and I'm guessing you're using the declared reflection recipe, What on earth would give you that idea? ;-) (yes, yes I am...) you'd need to enhance the usage of prepare() such that the class is not actually mapped, since there is no table. Yeah, I had this working with the Table call wrapped in a try/except. Check this post, read the section Model Setup: http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/ Almost, but... We can't have per-connection models, that would be semantically weird. Using a different metadata at session creation time, keyed off the dsn of the database connected to, and with irrelevant classes blowing up if used but ignored otherwise is semantically what I'm after. Clues as to how to implement gratefully received... Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declarative versus classes mapped to multiple engines
On Feb 9, 2012, at 3:02 PM, Chris Withers wrote: Almost, but... We can't have per-connection models, that would be semantically weird. Using a different metadata at session creation time, keyed off the dsn of the database connected to, and with irrelevant classes blowing up if used but ignored otherwise is semantically what I'm after. Clues as to how to implement gratefully received... do you have multiple, simultaneous engines when the app runs, where some engines might not be available, or just one engine, pointing to a database that has some subset of required tables ? this is confusing me. what's not working with the try/except on the reflection ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] declarative versus classes mapped to multiple engines
On 09/02/2012 20:06, Michael Bayer wrote: On Feb 9, 2012, at 3:02 PM, Chris Withers wrote: Almost, but... We can't have per-connection models, that would be semantically weird. Using a different metadata at session creation time, keyed off the dsn of the database connected to, and with irrelevant classes blowing up if used but ignored otherwise is semantically what I'm after. Clues as to how to implement gratefully received... do you have multiple, simultaneous engines when the app runs, Yes. Some will have totally different schemas, where I wouldn't imagine any mapped classes would interact with each other. For these, I'd imagine a separate MetaData instance as in the Model Setup section would work. However, some will have very similar schemas^1 with just a few tables deliberately missing. Here, it's okay (ie: programmer error) if queries involving classes mapped to these blow up because the wrong engine is used and so a table is missing. But, the reflection needs to not blow up. With one metadata object shared between these engines (which is what declarative with reflection appears to support), if the first engine used in an app (and these can be web apps and, just as annoyingly, unit test runs, where the order is arbitrary) is one where a table is missing, the class will be declaratively mapped, but the reflection will fail, so it won't ever get mapped and will blow up if later used with an engine that *does* have the table. So, in my head I'm thinking of something like (excuse the hand waving): data_per_dsn = dict() def getSession(dsn): if dsn not in data_per_dsn: engine = make_engine(dsn) # ^2 metadata = MetaData() reflect_tables_and_map_declarative_classes(metadata, engine) data_per_dsn[dsn] = engine, metadata engine, metadata = data_per_dsn[dsn] return make_session(engine, metadata?) ...which would solve both the cases above. The main problem I see is the case where the above would result in two metadata objects for one declarative class. Wow, I feel like I'm making a hash of describing this, I'm hope I'm vaguely succeeding in getting the info across :-S Chris ^1 Let's pretend that it's just missing tables, the fact that supposedly identical tables in different environments have evolved over time to have different primary keys^3 and numbers of columns is *not* something SQLAlchemy should have to deal with ;-) ^2 Oh for a function in SQLAlchemy to turn a dsn into a SQLAlchemy URL, and maybe back again... ^3 Let's also pretend that (seriously?!) some tables had not ended up with unique indexes that postgres is happy enough with but SQLAlchemy doesn't reflect correctly, because they're not *actually* primary keys - *sigh* - again, SA should not have to deal with this ;-) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] How do I change enum values after previously creating a table?
Mike, may I suggest this as an enhancement: If SQLA is creating a table with an Enum column, and if there's already an Enum with the specified name, it should spit out an error if the list of values for the existing and requested Enums don't match? Maybe, though would that same logic extend to other constructs ? Such as, if you had a Table, and you said metadata.create_all(), that particular Table already exists. Should SQLAlchemy go out, reflect that table and compare all the column names to the Table you have and report an error ?Seems like the same thing. Alembic does this comparison, but it's more of a helper to write migrations rather than a consistency checker, since we can't reliably match everything about the database to what's defined in Python. Yeah you're right, it's essentially the same thing. Personally, I was a bit surprised when I discovered that SQLAlchemy just ignores already-created tables, even the model is different to the preexisting table. However, it also means that if you have a script that imports and creates all models, you can use it no matter whether you have changed/added a single model or all of them, so there's that. I guess I just got a bit tripped up because my SQL/database knowledge perhaps isn't what it should be, so I didn't really understand how an enum would be implemented and that simply trying to redefine it would cause problems. SQLA makes things so easy, it can be easy to get complacent and assume that everything will just work, without thinking about the database! - DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. - -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
