Re: [sqlalchemy] Synchronizing data, want to track what has changed. Advice please.
Thanks for your responses. I've been doing quite a bit of probing and I
think I have what I need now to get the job done. Sorry for the confusion,
I wasn't being very clear.
The problem with setting active to 0 after the fact stems from the fact that
I'm only querying each database for the "active" records. The SIS database
has thousands of records of students that are no longer in the district, and
I don't need data on former students. So I query the SIS for the active
students, the ones that are going to school on the day my script runs. But
I have a list of "active" students also in my local database. When I query
for active students there, I'm getting the kids that were active a few days
ago, the last time the script was run. We may have had a student leave, and
I need to account for that so I can disable their user accounts.
I think with session.is_modified and with your suggestion to use
sqlalchemy.orm.attributes.get_history I can get all the information I need.
So, now if I start out by setting Student.active = 0 and then set
Student.active = 1 for each record I get back from the SIS, is_modified will
go back to false unless one of the other attributes changes, which is
exactly what I was hoping I could do in the first place. Then before my
commit, I can get a list or dictionary or whatever of the Student objects
that didn't get set back to active.
In doing all my probing, I found that setting a date attribute with a string
like '8/26/2011' causes is_modified to be true even if the date object
equaled date(2011, 8, 26) (ie. Student.AUPdate = '8/26/2011'), but if I
set it using datetime.strptime('8/26/2011', '%m/%d/%Y').date() then
is_modified stays false. This is important because I was seeing all sorts
of UPDATEs on records I knew had not changed when I was doing the merge, so
it was really throwing me and I couldn't tell what had truly changed.
Thanks again.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/Bsbp_EKqJoMJ.
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] Synchronizing data, want to track what has changed. Advice please.
SQLA itself can only give you "what's changed" in memory. so if you had an X(), and you set X.y to "bar" where it used to be "foo", before a flush you can see that happen with session.dirty and attributes.get_history(x, "y"). I don't entirely understand the actual task here but if it has to do with comparing the contents of one database to another, the general way to do that is to load the objects, populate them with the data from the second database, then use .dirty to see that. Clearly, if you are using that technique, you should do the "compare" job on clean objects, otherwise you can't be sure what changes are from the compare and what changes might have been local. I don't really understand the problem with the "0" and doing it later - if there's some state on the object that determines the "0" and that state is blown away by setting new attributes from the other database, then either don't overwrite those particular attributes or save the important state elsewhere on the object or in a dictionary. On Sep 27, 2011, at 6:56 PM, Brian wrote: > I'm not sure how I could do that because I'd want to set active to 0 only on > the students that are no longer going to school here. > > Maybe merge isn't the right thing to do, or maybe I need to rethink my > algorithm. The bottom line is I need to know what records get added to the > local database table (new users), what ones were active before -- but now I > don't see them in the query (disable users), and what records were changed > (modify/update users). > > What I'm trying right now is to use session.expunge() on each active student, > THEN merge the data from the SIS. That way (I hope) that only records that > changed or added would be "dirty" with an active attribute of 1 and the dirty > objects with an attribute of 0 would need to be removed. > > If anyone has a bright idea I'm all ears. I just don't know SA well enough > to know all the tools available to me for this. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/Qk7ieEmaC98J. > 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] Synchronizing data, want to track what has changed. Advice please.
I'm not sure how I could do that because I'd want to set active to 0 only on the students that are no longer going to school here. Maybe merge isn't the right thing to do, or maybe I need to rethink my algorithm. The bottom line is I need to know what records get added to the local database table (new users), what ones were active before -- but now I don't see them in the query (disable users), and what records were changed (modify/update users). What I'm trying right now is to use session.expunge() on each active student, THEN merge the data from the SIS. That way (I hope) that only records that changed or added would be "dirty" with an active attribute of 1 and the dirty objects with an attribute of 0 would need to be removed. If anyone has a bright idea I'm all ears. I just don't know SA well enough to know all the tools available to me for this. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Qk7ieEmaC98J. 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] deferrable constraints
the issue is, the "deferrable" flag on FK generates the keyword on SQLite
during DDL and is not accepted by SQLite ? seems like we'd just get the SQLite
dialect to not render that keyword (i.e. should be core behavior). Also as a
workaround you could possibly use a @compiles("sqlite") on CreateConstraint...
(I seem to have missed this thread early on sorry for that !)
On Sep 27, 2011, at 6:04 PM, Wichert Akkerman wrote:
> I hate to do this kind of thing, but I haven't gotten any feedback on this
> and I would love to hear some feedback/opinions. How do other people handle
> deferrable constraints?
>
>
> On 2011-9-14 13:59, Wichert Akkerman wrote:
>> On 09/14/2011 12:25 PM, Wichert Akkerman wrote:
>>> Constraints marked as deferrable result in a syntax error when using
>>> SQLite. Is this deliberate, or a bug in the sqlite dialect?
>>
>> As a workaround I figured I could use events to only add deferrable
>> constraint variants on PostgreSQL and use the non-deferrable version on
>> other database. That resulted in this code:
>>
>> _generic_constraints = [UniqueConstraint('article_id', 'colour, 'size')]
>> _ deferrable_variant_constraints = [
>> UniqueConstraint('article_id', 'uuid', deferrable=True,
>> initially='DEFERRED'),
>> ]
>>
>> class Article(BaseObject):
>> __table_args = (_generic_constraints[0], _deferrable_constraints[0], {})
>>
>> def deferrable_supported(ddl, target, bind, **kw):
>> """Check if deferrable constraints are supported.
>>
>> This function can be used as a callable for
>> :ref:`execute_if` to
>> only run DDL statements on databases that support deferrable constraints.
>> """
>> return bind.dialect == 'postgresql'
>>
>>
>> def deferrable_not_supported(ddl, target, bind, **kw):
>> """Check if deferrable constraints are not supported.
>>
>> This function can be used as a callable for
>> :ref:`execute_if` to
>> only run DDL statements on databases that do not support deferrable
>> constraints.
>> """
>> return not deferrable_supported(ddl, target, bind, **kw)
>>
>> for constraint in _generic_variant_constraints:
>> listen(Article.__table__, 'after_create',
>> AddConstraint(constraint)
>> .execute_if(callable_=deferrable_not_supported))
>>
>> for constraint in _deferrable_variant_constraints:
>> listen(Article.__table__, 'after_create',
>> AddConstraint(constraint)
>> .execute_if(callable_=deferrable_supported))
>>
>>
>>
>> But this fails as well since SQLite does not support ALTER TABLE .. ADD
>> CONSTRAINT. Is there another way to create deferrable constraints only
>> on databases that support it?
>>
>> Wichert.
>>
>>
>>
>
>
> --
> Wichert AkkermanIt is simple to make things.
> http://www.wiggy.net/ It is hard to make things simple.
>
> --
> 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] deferrable constraints
I hate to do this kind of thing, but I haven't gotten any feedback on
this and I would love to hear some feedback/opinions. How do other
people handle deferrable constraints?
On 2011-9-14 13:59, Wichert Akkerman wrote:
On 09/14/2011 12:25 PM, Wichert Akkerman wrote:
Constraints marked as deferrable result in a syntax error when using
SQLite. Is this deliberate, or a bug in the sqlite dialect?
As a workaround I figured I could use events to only add deferrable
constraint variants on PostgreSQL and use the non-deferrable version on
other database. That resulted in this code:
_generic_constraints = [UniqueConstraint('article_id', 'colour, 'size')]
_ deferrable_variant_constraints = [
UniqueConstraint('article_id', 'uuid', deferrable=True,
initially='DEFERRED'),
]
class Article(BaseObject):
__table_args = (_generic_constraints[0], _deferrable_constraints[0], {})
def deferrable_supported(ddl, target, bind, **kw):
"""Check if deferrable constraints are supported.
This function can be used as a callable for
:ref:`execute_if` to
only run DDL statements on databases that support deferrable constraints.
"""
return bind.dialect == 'postgresql'
def deferrable_not_supported(ddl, target, bind, **kw):
"""Check if deferrable constraints are not supported.
This function can be used as a callable for
:ref:`execute_if` to
only run DDL statements on databases that do not support deferrable
constraints.
"""
return not deferrable_supported(ddl, target, bind, **kw)
for constraint in _generic_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_not_supported))
for constraint in _deferrable_variant_constraints:
listen(Article.__table__, 'after_create',
AddConstraint(constraint)
.execute_if(callable_=deferrable_supported))
But this fails as well since SQLite does not support ALTER TABLE .. ADD
CONSTRAINT. Is there another way to create deferrable constraints only
on databases that support it?
Wichert.
--
Wichert AkkermanIt is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
--
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] Validation of new objects before committing
On Sep 27, 2011, at 1:57 PM, Kirk Strauser wrote:
> Does SA natively support (or is there a module on PyPI that supports)
> client-side validation of SQLAlchemy objects? For example, I have this
> declarative class:
>
>class ImportedPayment(Base):
>__tablename__ = 'importedpayment'
>__table_args = {'schema': 'public'}
>paymentid = Column(Integer, primary_key=True)
>externalid = Column(String(16), nullable=False)
>line = Column(Integer, nullable=False)
>invoicestatus = Column(String(32), nullable=False)
>quantity = Column(Numeric(scale=2), nullable=False)
>rate = Column(Numeric(scale=2), nullable=False)
>
>
> Is there an easy way to do this?
OK so the aspect you're looking for here is to define those validations just
once, this is easy enough through a recipe like this:
def notnull(key):
def validate(obj):
if getattr(obj, key) is None:
return "Object %s key %s is None" % (obj, key)
else:
return False
return validate
_validators = {}
def get_validators(someobject):
mapper = object_mapper(someobject)
if mapper in _validators:
return _validators[mapper]
_validators[mapper] = v = []
for prop in mapper.iterate_properties():
if hasattr(prop, "columns"):
col = prop.columns[0]
if not col.nullable:
v.append(notnull(prop.key))
# ... ad nauesum, i.e.
# if :
# v.append()
return v
def validate(someobject):
for validator in get_validators(someobject):
msg = validator()
if msg:
log(msg)
# etc., i.e.
# alert_the_authorities()
> If not, why?
So the theme for today is "why does SQLA have recipes", basically when we can
provide the core fragment of a feature but not a fully polished, documented,
tested, packaged result, something that can just as easily be delivered as a
small batch of customizable source code gets the job done pretty well, and
would be better suited as a separate library if fully fleshed out.
The above recipe lacks a lot of features one might want, such as customizable
ways of defining the validation failure, behavior on the receipt of a failed
validation, etc.A full blown "validation" library might use the idea above
but expand upon it in a much bigger way.I've had other ad-hoc validation
use cases that wouldn't work with the above structure, instead needing a
slightly different structure, so having a small thing just as code for now is
more flexible than a built in "feature" that only handles a small subset of use
cases.
> And if the answer to that is "because you haven't written it yet", would
> anyone be interested in using it if I were to create such a thing?
You might want to check around if similar things don't exist already, I did
find http://pypi.python.org/pypi/SAValidation/ and
http://pypi.python.org/pypi/sqlalchemy_elixir_validations/ for example, there
might be features there that are of use. But by all means, produce a better
validation library for SQLAlchemy, the more the merrier and I'd love to see
more.
--
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] Synchronizing data, want to track what has changed. Advice please.
On Sep 27, 2011, at 11:42 AM, Brian wrote: > hello all, > > I'm grabbing data from a Student Information System (SIS) about students and > then saving that data in a local database. I then query my local database to > create/modify/disable accounts in Active Directory and Google Apps. > > What I've been doing so far is the following: > > Set all students in the local database as inactive-- > for activestudent in am.session.query(am.Student).filter_by(active=1): > activestudent.active = 0 > Get all "active" records from SIS using an engine.execute() > Merge data from SIS into the Student objects from the local database. > > This works well for synchronizing the data, any students leaving the school > get marked inactive and any updates in name or status are reflected in the > local database. > > What I can't do, though, is tell exactly what changed. I'd really like to > run functions based on what the changes are: disable students made inactive. > > I tried using session.dirty, but I think because I initially set active to 0, > every record object is marked, even if active reverts back to 1 through the > merge. > > I'd appreciate any ideas, or guidance in the right direction. hm what if you set active to "0" after you've learned what you can about attribute change status ? > > Thanks! > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/Mq9tsqsugk0J. > 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] Possible bug with subqueryload
On Sep 27, 2011, at 1:16 PM, King Simon-NFHD78 wrote: > > Great, thanks a lot :-) I only discovered it in a toy application, and > the workaround (including order_by on the query) is not a problem. > > In this toy application, I was also wondering if there existed a > mechanism for doing some sort of "lazy subqueryload". ie. I'm loading a > collection of objects and I don't know ahead of time if I'm going to > access a particular relationship (so I don't want to eagerload it). > However, if I *do* access it, I'm going to access it on each object in > the collection, so I'd like to load all the related objects in a single > hit. It's just like a subqueryload, except it is only executed when the > relationship is accessed for the first time. > > Is that a silly idea? Or perhaps it already exists and I've missed it. It actually exists in Hibernate, but not for us.We do sort of have the infrastructure in place to make it possible, i.e.the subqueryload right now prepares a Query object at query time that fires off during load time, with a mapper option it would need to stick it as some kind of memo in each InstanceState, it would be very tricky to implement.Keeping that state and keeping it plugged into the InstanceStates, then what if the loader was fired after many of the other states have been garbage collected, just a lot of corner cases to deal with. it can be handrolled of course, the general technique when you want to construct objects such that they appear "loaded" is to use attributes.set_commited_value() to set an attribute such that the ORM sees it as "what was loaded from the database". an example of that is where we first introduced the "subquery" concept here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisjointEagerLoading > > Thanks, > > Simon > > -- > 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] Validation of new objects before committing
Does SA natively support (or is there a module on PyPI that supports)
client-side validation of SQLAlchemy objects? For example, I have this
declarative class:
class ImportedPayment(Base):
__tablename__ = 'importedpayment'
__table_args = {'schema': 'public'}
paymentid = Column(Integer, primary_key=True)
externalid = Column(String(16), nullable=False)
line = Column(Integer, nullable=False)
invoicestatus = Column(String(32), nullable=False)
quantity = Column(Numeric(scale=2), nullable=False)
rate = Column(Numeric(scale=2), nullable=False)
I'm reading data from a directory full of spreadsheets and generating millions
of these objects. If one contains invalid data, it's OK to just log it and move
on. Unfortunately, that pretty much means that I have to commit after every
insertion so that I can catch any potential exceptions and this makes the whole
process take ages to run.
Now, I've already defined the error conditions I'm likely to encounter and that
I can easily handle: they're the constraints I defined in the class above. I'd
love for the objects I'm creating to validate themselves through a method call
like:
newobject = ImportedPayment(externalid='foo', line=None, [...])
try:
newobject.validate()
except ValueError as e:
print e.column, e.errmsg
else:
session.add(newobject)
yielding
'line', 'None in not-nullable column'
or similar. Granted, I could write these tests easily myself:
if externalid is None or len(externalid) > 16:
return False
but I've already specified them once and I don't want to repeat myself as it's
a lot of extra typing and a lot harder to maintain (if I change "invoicestatus"
to a String(64), I have to update every module which manually validates that
data). Note that I'm not talking about higher-level checks like "the
emailaddress Column contains a valid email address", but just the simple data
type checks that can be inferred from class definitions (strings of appropriate
length, not null, etc.).
Is there an easy way to do this? If not, why? And if the answer to that is
"because you haven't written it yet", would anyone be interested in using it if
I were to create such a thing?
Kirk
--
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] delcarative with autoload when you have no connection
On Sep 27, 2011, at 1:21 PM, Chris Withers wrote: > On 27/09/2011 18:10, Michael Bayer wrote: >> >>> That looks like it should ship with SA itself... >>> >>> Does it? >> >> on the website, sure :) >> >> Many of these things are better as recipes > > Meh, that used to be true of mixins. > I'm very glad that's now in the core. > (Note to readers: Chris and I hang out each year at Pycon. He's a great guy, so forgive the colloquial tone I take with him !) > The trouble with keeping this as a recipe is that if you want to use it > anywhere seriously, *every* project you want to use it on you have to: > > - find somewhere to put it OK you should check out my talk: http://techspot.zzzeek.org/2011/09/25/sqlalchemy-an-architectural-retrospective/ it's a core value of SQLAlchemy that if you are writing a non-trivial application, you should always have a foundation which includes those idioms you're going to build on. SQLAlchemy doesn't provide idioms out of the box, just the tools to create them and plenty of examples and guidance. > - write unit tests for for you would have unit tests against your models being able to persist data to and from the database. The functionality of mapping and reflecting would be rolled into that automatically so no additional tests are needed. > - try and remember why it's slightly different in one project than another if you have two projects - one uses reflection to do everything, the other doesn't, those two projects are already going to be slightly different - except one will have this weird "mymodel.prepare()" call buried deep inside of it. With the recipe being something explicit, its easier to see that project B is doing things differently. > - generally end up cursing and swearing... you need to get over that > >> - it is easier for us to support just the "__mapper_cls__" argument rather >> than a full usage pattern. > > Why? How would the "full usage pattern" differ? Generally when I come up with a recipe to solve a problem in 5 minutes, its not ready to be a core feature.If it becomes the prominent way to do things, we would need to ensure that all other methods are de-emphasized, etc. This recipe in particular builds upon a set of APIs, especially that it conceals Table(..., autoload=True) that makes it extremely dangerous to confuse people with. Declarative already has an attribute called __autoload__, which I yanked out of the docs.You can already say __table__ = Table(..., autoload=True), and __autoload__ was redundant and less-featured. Users who needed to reflect the Table, use declarative, but also needed to customize the Table a bit as described at http://www.sqlalchemy.org/docs/core/schema.html#overriding-reflected-columns would be totally confused by __autoload__, it hides what should be obvious. User comes to SQLAlchemy, wants to autoload a database and map to it.Right now, the path is clear - understand that you're mapping to a Table, understand how to autoload a Table, understand how to apply it to a mapping. One way, everyone gets it. New way is introduced, use __tablename__ and some special directive, plus prepare(). Ok that other way works, but if you want to have your Engine later, type less (who doesn't ?) now you can do it *THIS* way ! great. I can guarantee you users are already completely confused by the use case - my Engine isn't there yet ? I thought I make the Engine at the top of the module, why wouldn't I have it ? Do I need to use this pattern or not ? What if I need x, y, z, q, p, r that isn't supported here ?I can try to find you a big long email from a few months ago where someone was already totally confused about the Session docs, describing that you can say sessionmaker.configure(engine) instead of Session(bind=engine) - he was *totally* mystified by that whole thing and the purpose. Very, very hard to describe convenience features, because they apply to certain use cases that most users don't have, and aren't sure they have when they first read the docs. Usage recipes make it a *ton* more clear that these are specific applications for specific problems. This new way doesn't let me customize the Table as http://www.sqlalchemy.org/docs/core/schema.html#overriding-reflected-columns talks about. This new way doesn't let me use other forms of __mapper_cls__ unless I fight with it, read its source code, etc. to get my way. Im confused should I use the "less typing" way or not? Also, a lot of apps that use reflection *can't* use this method - they have hundreds of tables and reflection is too slow. For them, we often advise reflect everything and pickle it into a serialized file, then upon app startup use that pickled file. Or use an autogen tool that derives from reflection. There's *lots* of ways to use reflection and hardcoding just one recipe in the core, just isn't appropriate at this point. Once a recipe bec
Re: [sqlalchemy] what pool to use when using pgbouncer?
On Sep 27, 2011, at 10:55 AM, Chris Withers wrote:
> Hi,
>
> I'm wondering what the recommended incantation of create_engine is in SA
> 0.7.3 if:
>
> - you're using pg_bouncer, and so essentially don't want a pool
for that you use NullPool
> - you have a DSN, eg: 'host=xx dname=xx user=xx password=xx', rather
> than a SA url
Hm is this a pgbouncer thing ? I thought that pgbouncer was transparent.
If you're using the psycopg2 dialect and need to use a form of
psycopg.connect() that isn't the usual set of individual kw arguments, you'd
currently need to use the "creator()" function, where you give us back a new
DBAPI connection:
import psycopg2
def create():
return psycopg2.connect(my_dsn_argument_thing="host=xx;dbname=xx;..")
engine = create_engine("postgresql+psycopg2://", creator=create)
see
http://www.sqlalchemy.org/docs/core/engines.html#custom-dbapi-connect-arguments
--
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] delcarative with autoload when you have no connection
On 27/09/2011 18:10, Michael Bayer wrote: That looks like it should ship with SA itself... Does it? on the website, sure :) Many of these things are better as recipes Meh, that used to be true of mixins. I'm very glad that's now in the core. The trouble with keeping this as a recipe is that if you want to use it anywhere seriously, *every* project you want to use it on you have to: - find somewhere to put it - write unit tests for for - try and remember why it's slightly different in one project than another - generally end up cursing and swearing... - it is easier for us to support just the "__mapper_cls__" argument rather than a full usage pattern. Why? How would the "full usage pattern" differ? (you *know* I'll end up finding some way of hitting it anyway ;-) ) In particular, the recipe would need modifications if it were being integrated with other __mapper_cls__ types of recipes, Is there a list of these anywhere? (other than ones about to be superceded) A recipe is also a great way to keep the library as "just one way to do it" - the variety of overlapping use cases regarding mapping and reflection can remain as applications on top of a more succinct set of building blocks. Makes the library easier to understand when its boundaries are clear. Sure, but that's not reason not to have a select of these recipes with unit tests in, say, sa.ext.declarative.ext ...ok, I'm being facetious with the package name, but you know what I mean. cheers, Chris PS: On the flip side, it's awesome that the recipe is there and works! -- 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] Possible bug with subqueryload
> -Original Message- > From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] > On Behalf Of Michael Bayer > Sent: 27 September 2011 16:24 > To: sqlalchemy@googlegroups.com > Subject: Re: [sqlalchemy] Possible bug with subqueryload > > Hi Simon - > > yeah that looks pretty buglike to me, mapper.order_by is not a > frequently used feature so this one may need some adjustment. > > I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a > look at this and so far I'm targeting it at 0.6.9/0.7.3. > Great, thanks a lot :-) I only discovered it in a toy application, and the workaround (including order_by on the query) is not a problem. In this toy application, I was also wondering if there existed a mechanism for doing some sort of "lazy subqueryload". ie. I'm loading a collection of objects and I don't know ahead of time if I'm going to access a particular relationship (so I don't want to eagerload it). However, if I *do* access it, I'm going to access it on each object in the collection, so I'd like to load all the related objects in a single hit. It's just like a subqueryload, except it is only executed when the relationship is accessed for the first time. Is that a silly idea? Or perhaps it already exists and I've missed it. Thanks, Simon -- 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] delcarative with autoload when you have no connection
On Sep 27, 2011, at 12:49 PM, Chris Withers wrote: > On 27/09/2011 16:58, Michael Bayer wrote: >> >> ah, hm. interesting ! basically, not rea..^H^H^H OK actually this is >> very easy, using a technique I used previously to create abstract concrete >> mappers. This should probably be how we recommend people use reflection >> with mappings since this is way better than what you'd get with classical >> mappings: >> >> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeReflectedBase > > That looks like it should ship with SA itself... > > Does it? on the website, sure :) Many of these things are better as recipes - it is easier for us to support just the "__mapper_cls__" argument rather than a full usage pattern.Users are better off knowing how these things work, if we're just talking about 10 lines of code.In particular, the recipe would need modifications if it were being integrated with other __mapper_cls__ types of recipes, such as the "abstract base class" recipe (though that one *is* being superceded by a built in). A recipe is also a great way to keep the library as "just one way to do it" - the variety of overlapping use cases regarding mapping and reflection can remain as applications on top of a more succinct set of building blocks. Makes the library easier to understand when its boundaries are clear. -- 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] delcarative with autoload when you have no connection
On 27/09/2011 16:58, Michael Bayer wrote: ah, hm. interesting ! basically, not rea..^H^H^H OK actually this is very easy, using a technique I used previously to create abstract concrete mappers. This should probably be how we recommend people use reflection with mappings since this is way better than what you'd get with classical mappings: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeReflectedBase That looks like it should ship with SA itself... Does it? 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.
[sqlalchemy] Synchronizing data, want to track what has changed. Advice please.
hello all, I'm grabbing data from a Student Information System (SIS) about students and then saving that data in a local database. I then query my local database to create/modify/disable accounts in Active Directory and Google Apps. What I've been doing so far is the following: Set all students in the local database as inactive-- for activestudent in am.session.query(am.Student).filter_by(active=1): activestudent.active = 0 Get all "active" records from SIS using an engine.execute() Merge data from SIS into the Student objects from the local database. This works well for synchronizing the data, any students leaving the school get marked inactive and any updates in name or status are reflected in the local database. What I can't do, though, is tell exactly what changed. I'd really like to run functions based on what the changes are: disable students made inactive. I tried using session.dirty, but I think because I initially set active to 0, every record object is marked, even if active reverts back to 1 through the merge. I'd appreciate any ideas, or guidance in the right direction. Thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Mq9tsqsugk0J. 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] delcarative with autoload when you have no connection
On Sep 27, 2011, at 10:50 AM, Chris Withers wrote: > Hi All, > > Say I have a class such as: > > > class MyObj(Base) >__tablename__='mytable' >__table_args__=dict(autoload=True) > > ...but the Base's metadata isn't bound, and won't be until the app has > started (ie: the model has been imported) as the connection string comes from > the environment. > > Is there any way I can set the above to defer the autoloading until the > metadata is bound to an engine? ah, hm. interesting ! basically, not rea..^H^H^H OK actually this is very easy, using a technique I used previously to create abstract concrete mappers. This should probably be how we recommend people use reflection with mappings since this is way better than what you'd get with classical mappings: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeReflectedBase -- 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] Incorrect SQL generated for INSERT into PostgreSQL
On Sep 27, 2011, at 1:12 AM, Nathan Robertson wrote:
> On Mon, Sep 26, 2011 at 11:05 PM, Michael Bayer wrote:
>> On Sep 26, 2011, at 2:50 AM, Nathan Robertson wrote:
>>
>>> Column('custid', Integer, Sequence('test.customer_custid_seq'),
>>> primary_key=True),
>>
>> for the Sequence, as with all schema items, you need to specify the "schema"
>> portion separately so that SQLAlchemy knows where each token starts and ends:
>>
>> Sequence("customer_custid_seq", schema="test")
>
> Actually, I just noticed that the same thing doesn't apply to foreign
> keys. I've actually got code in production which does something like:
>
> Column('custid', BigInteger, ForeignKey('test.customer.custid'),
> primary_key=True)
>
> What's the reason for the distinction requiring the schema to be split
> out in a Sequence, but not in the case of a ForeignKey?
OK that's a great question. This is a case where looking at it from my
perspective, I'd never see it that way, but from someone coming into it from
the API usage side, yeah that seems pretty obvious. Maybe ForeignKey and
ForeignKeyConstraint should accept a "schema" argument instead, though if that
were the case I'd deprecate the other method since we try not to have multiple
ways to do something...but that would be hard at this point since there's
thousands of apps that do it the current way.Maybe having "schema" as an
option and just having two ways to do itarg. Would have to think about
it. I added http://www.sqlalchemy.org/trac/ticket/2288 for this idea.
Basically from the ForeignKey perspective it's an element of a Table, and the
directives to generate a foreign key constraint don't include the schema where
the constraint itself lives; that's part of its parent Table and is already
handled. The column it references, that belongs to a table which may have a
schema, but ForeignKey doesn't need the separate schema information because it
ultimately is pointing to an actual Column object on another Table, which also
has "schema" taken care of. The string "x.y.z" sent to ForeignKey is only used
internally to locate another Table/Column within the MetaData. Whereas Table,
Sequence, we need to quote the individual elements so "schema" has always been
separate at that level.
--
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 subqueryload
Hi Simon -
yeah that looks pretty buglike to me, mapper.order_by is not a frequently used
feature so this one may need some adjustment.
I've created http://www.sqlalchemy.org/trac/ticket/2287 to take a look at this
and so far I'm targeting it at 0.6.9/0.7.3.
On Sep 27, 2011, at 7:29 AM, King Simon-NFHD78 wrote:
> Hi,
>
> I think there may be a bug in the interaction between 'subqueryload' and
> having a default 'order_by' defined on a mapped class. When the subquery
> is run, it looks like the ORDER BY is being placed on the outer query,
> whereas it should be on the inner query. The full test case is below,
> but here are the 2 queries (produced using hg revision 62e97372a028):
>
> Main query
> --
> SELECT master.id AS master_id, master.dummy AS master_dummy
> FROM master
> ORDER BY master.id DESC
> LIMIT 2 OFFSET 0
>
>
> Subquery
>
> SELECT detail.id AS detail_id, detail.master_id AS detail_master_id,
> anon_1.master_id AS anon_1_master_id
> FROM (SELECT master.id AS master_id
> FROM master
> LIMIT 2 OFFSET 0) AS anon_1
> JOIN detail ON anon_1.master_id = detail.master_id
> ORDER BY anon_1.master_id
>
>
> Since the ORDER BY is not on the inner query, a different set of
> 'master' rows is referenced than in the main query.
>
> Cheers,
>
> Simon
>
>
>
> import sqlalchemy as sa
> import sqlalchemy.orm as saorm
>
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> class Master(Base):
>__tablename__ = 'master'
>id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
>dummy = sa.Column(sa.Integer)
>__mapper_args__ = {'order_by': sa.desc(id)}
>
> class Detail(Base):
>__tablename__ = 'detail'
>id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
>master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id))
>master = saorm.relationship(Master, backref='details')
>
> def test():
>dburi = 'sqlite://'
>engine = sa.create_engine(dburi, echo=True)
>Base.metadata.drop_all(bind=engine)
>Base.metadata.create_all(bind=engine)
>session = saorm.create_session(bind=engine)
>
>session.begin()
># Insert 5 masters, each with 1 detail
>for i in range(5):
>master = Master(dummy=i)
>master.details.append(Detail())
>session.add(master)
>session.commit()
>session.close()
>
># Load back 2 masters, using subqueryload to load the detail
># rows. If you uncomment the '.order_by' line here, the test
># passes.
>master_query = (session.query(Master)
>#.order_by(sa.desc(Master.id))
>.limit(2)
>.options(saorm.subqueryload('details')))
>
># Display the details for each master
>for item in master_query:
>print 'Master %s: %s' % (item.id, item.details),
>if len(item.details) == 0:
>print 'FAIL'
>else:
>print 'PASS'
>
> if __name__ == '__main__':
>test()
>
>
> --
> 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] what pool to use when using pgbouncer?
Hi, I'm wondering what the recommended incantation of create_engine is in SA 0.7.3 if: - you're using pg_bouncer, and so essentially don't want a pool - you have a DSN, eg: 'host=xx dname=xx user=xx password=xx', rather than a SA url 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.
[sqlalchemy] delcarative with autoload when you have no connection
Hi All, Say I have a class such as: class MyObj(Base) __tablename__='mytable' __table_args__=dict(autoload=True) ...but the Base's metadata isn't bound, and won't be until the app has started (ie: the model has been imported) as the connection string comes from the environment. Is there any way I can set the above to defer the autoloading until the metadata is bound to an engine? 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.
[sqlalchemy] Possible bug with subqueryload
Hi,
I think there may be a bug in the interaction between 'subqueryload' and
having a default 'order_by' defined on a mapped class. When the subquery
is run, it looks like the ORDER BY is being placed on the outer query,
whereas it should be on the inner query. The full test case is below,
but here are the 2 queries (produced using hg revision 62e97372a028):
Main query
--
SELECT master.id AS master_id, master.dummy AS master_dummy
FROM master
ORDER BY master.id DESC
LIMIT 2 OFFSET 0
Subquery
SELECT detail.id AS detail_id, detail.master_id AS detail_master_id,
anon_1.master_id AS anon_1_master_id
FROM (SELECT master.id AS master_id
FROM master
LIMIT 2 OFFSET 0) AS anon_1
JOIN detail ON anon_1.master_id = detail.master_id
ORDER BY anon_1.master_id
Since the ORDER BY is not on the inner query, a different set of
'master' rows is referenced than in the main query.
Cheers,
Simon
import sqlalchemy as sa
import sqlalchemy.orm as saorm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Master(Base):
__tablename__ = 'master'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
dummy = sa.Column(sa.Integer)
__mapper_args__ = {'order_by': sa.desc(id)}
class Detail(Base):
__tablename__ = 'detail'
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
master_id = sa.Column(sa.Integer, sa.ForeignKey(Master.id))
master = saorm.relationship(Master, backref='details')
def test():
dburi = 'sqlite://'
engine = sa.create_engine(dburi, echo=True)
Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)
session = saorm.create_session(bind=engine)
session.begin()
# Insert 5 masters, each with 1 detail
for i in range(5):
master = Master(dummy=i)
master.details.append(Detail())
session.add(master)
session.commit()
session.close()
# Load back 2 masters, using subqueryload to load the detail
# rows. If you uncomment the '.order_by' line here, the test
# passes.
master_query = (session.query(Master)
#.order_by(sa.desc(Master.id))
.limit(2)
.options(saorm.subqueryload('details')))
# Display the details for each master
for item in master_query:
print 'Master %s: %s' % (item.id, item.details),
if len(item.details) == 0:
print 'FAIL'
else:
print 'PASS'
if __name__ == '__main__':
test()
--
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.
