Re: [sqlalchemy] Expiring pooled connections
On Wed, Mar 17, 2010 at 4:43 PM, Michael Bayer wrote: > > On Mar 16, 2010, at 11:24 PM, Julian Scheid wrote: > >> On Wed, Mar 17, 2010 at 4:15 PM, Michael Bayer >> wrote: >>> if your cleanup handler only expires connections that are older than the >>> "pool_expire" time, that should help. >> >> Sorry, I don't follow - what is pool_expire and where is it used? I >> don't see a reference to that in >> http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/pool.py or in >> the docs, and a Google search comes up empty. > > sorry I meant "recycle". its "pool_recycle" on create_engine(). Oh, right - that makes sense. Yes, it looks like this way I can avoid synchronisation. Thank you. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Expiring pooled connections
On Mar 16, 2010, at 11:24 PM, Julian Scheid wrote: > On Wed, Mar 17, 2010 at 4:15 PM, Michael Bayer > wrote: >> if your cleanup handler only expires connections that are older than the >> "pool_expire" time, that should help. > > Sorry, I don't follow - what is pool_expire and where is it used? I > don't see a reference to that in > http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/pool.py or in > the docs, and a Google search comes up empty. sorry I meant "recycle". its "pool_recycle" on create_engine(). > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] Expiring pooled connections
On Wed, Mar 17, 2010 at 4:15 PM, Michael Bayer wrote: > if your cleanup handler only expires connections that are older than the > "pool_expire" time, that should help. Sorry, I don't follow - what is pool_expire and where is it used? I don't see a reference to that in http://www.sqlalchemy.org/trac/browser/lib/sqlalchemy/pool.py or in the docs, and a Google search comes up empty. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Expiring pooled connections
On Mar 16, 2010, at 10:25 PM, Julian Scheid wrote: > On Wed, Mar 17, 2010 at 3:10 PM, Michael Bayer > wrote: >> Well the pool only holds at one time the number of connections that you >> configure - it has an "overflow" of connections if more are requested but >> those aren't held around. > > Yep, but "overflow" connections are discarded immediately and other > connections are held indefinitely - I'm looking for something in > between. > >> But as you mentioned, it seems you're really looking for a thread here that >> will reduce the size of the pool asynchronously, so simple enough to just >> run a background thread which iterates through the pool calling invalidate() >> on connections older than 30 seconds. Its likely a 10 liner. I'd >> appreciate if you can put it up on the UsageRecipes wiki page if you have >> success. > > Sure, I'm happy to do that. > > I realize there's not a lot of code involved but in my experience > everything that involves concurrency can be a bit tricky to get right, > what with race conditions and all, which is why I was hoping for an > existing, proven solution. Oh well, I'll see what I can come up with. if your cleanup handler only expires connections that are older than the "pool_expire" time, that should help. > > Thanks again, > > Julian > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] Expiring pooled connections
On Wed, Mar 17, 2010 at 3:10 PM, Michael Bayer wrote: > Well the pool only holds at one time the number of connections that you > configure - it has an "overflow" of connections if more are requested but > those aren't held around. Yep, but "overflow" connections are discarded immediately and other connections are held indefinitely - I'm looking for something in between. > But as you mentioned, it seems you're really looking for a thread here that > will reduce the size of the pool asynchronously, so simple enough to just run > a background thread which iterates through the pool calling invalidate() on > connections older than 30 seconds. Its likely a 10 liner. I'd appreciate > if you can put it up on the UsageRecipes wiki page if you have success. Sure, I'm happy to do that. I realize there's not a lot of code involved but in my experience everything that involves concurrency can be a bit tricky to get right, what with race conditions and all, which is why I was hoping for an existing, proven solution. Oh well, I'll see what I can come up with. Thanks again, Julian -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Expiring pooled connections
On Mar 16, 2010, at 9:46 PM, Julian Scheid wrote: > > I'm not sure if Connection.invalidate helps a lot either, as > invalidation/expiry should depend on when the connection was returned > to the pool so it seems that putting this functionality into a custom > Pool implementation is more appropriate. > > Does this make sense, am I missing something? Well the pool only holds at one time the number of connections that you configure - it has an "overflow" of connections if more are requested but those aren't held around. But as you mentioned, it seems you're really looking for a thread here that will reduce the size of the pool asynchronously, so simple enough to just run a background thread which iterates through the pool calling invalidate() on connections older than 30 seconds. Its likely a 10 liner. I'd appreciate if you can put it up on the UsageRecipes wiki page if you have success. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] Expiring pooled connections
On Wed, Mar 17, 2010 at 2:46 PM, Julian Scheid wrote: > Does this make sense, am I missing something? I guess I'm looking for something akin to the "idle object evictor thread" mentioned here: http://commons.apache.org/dbcp/configuration.html -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Expiring pooled connections
On Wed, Mar 17, 2010 at 2:31 PM, Michael Bayer wrote: > > On Mar 16, 2010, at 8:09 PM, Julian Scheid wrote: > >> I'm looking for a way to close a pooled connection client-side after a >> certain period, say when it wasn't used in 30 seconds. I don't think >> any of the Pool implementations that come with SQLAlchemy supports >> this. Does anybody know of a third-party implementation with this >> functionality? I guess it wouldn't be difficult to create one from >> scratch but I was hoping that there is an existing solution. > > > *All* of the pool implementations support this, with the caveat that its > recycled on next checkout. See "pool_recycle" at > http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine > . > > You can also force any connection to recycle immediately using > connection.invalidate(): > > http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine > > if you truly wanted a checked out connection to automatically invalidate > itself while checked out, you definitely need to forego using any > transactions with it (i.e. autocommit for everything), and you can create a > ConnectionProxy that calls invalidate() when an execute() call is received > more than 30 seconds since the last one. ConnectionProxy is at > http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html#sqlalchemy.interfaces.ConnectionProxy > . Thanks for your reply. To elaborate a bit, this is meant to reduce the number of open database connections held by long-lived clients that do not continuously access the database (while still avoiding having to open a new physical connection for every session when there are multiple sessions in quick succession.) I do not need to invalidate checked out connections - I would like to close connections that have been returned to the pool and have been sitting there for a while without being re-requested. If I understand recycling correctly, it doesn't really help with this as it only closes the connection right before opening another one. I'm not sure if Connection.invalidate helps a lot either, as invalidation/expiry should depend on when the connection was returned to the pool so it seems that putting this functionality into a custom Pool implementation is more appropriate. Does this make sense, am I missing something? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Expiring pooled connections
On Mar 16, 2010, at 8:09 PM, Julian Scheid wrote: > I'm looking for a way to close a pooled connection client-side after a > certain period, say when it wasn't used in 30 seconds. I don't think > any of the Pool implementations that come with SQLAlchemy supports > this. Does anybody know of a third-party implementation with this > functionality? I guess it wouldn't be difficult to create one from > scratch but I was hoping that there is an existing solution. *All* of the pool implementations support this, with the caveat that its recycled on next checkout. See "pool_recycle" at http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine . You can also force any connection to recycle immediately using connection.invalidate(): http://www.sqlalchemy.org/docs/reference/sqlalchemy/connections.html?highlight=create_engine#sqlalchemy.create_engine if you truly wanted a checked out connection to automatically invalidate itself while checked out, you definitely need to forego using any transactions with it (i.e. autocommit for everything), and you can create a ConnectionProxy that calls invalidate() when an execute() call is received more than 30 seconds since the last one. ConnectionProxy is at http://www.sqlalchemy.org/docs/reference/sqlalchemy/interfaces.html#sqlalchemy.interfaces.ConnectionProxy . > > Thanks, > > Julian > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] Re: ms sql server schema.sequence
Hello Tan, On Mar 16, 2010, at 7:57 PM, Tan Yi wrote: > I mean, I tried to debug a program, using session add() to insert new > records to database after I deleted all the old records from a table. > I used the schema.sequence() to build up a mapper > Can anyone help? > > On 3月16日, 下午2时05分, Tan Yi wrote: >> Whenever I try to use schema.sequence() function on ms sql server, the >> return serial number (after flush()) is not started from 0, instead, >> it is started from a random number or something. >> wondering how to work aroud this issue. try to specify start = 0 in >> sequence function, but no luck With MSSQL whenever you delete records from a table it does not reset the IDENTITY. If you would like the identity to reset back to one (zero is not an option), then you need to truncate the table with: TRUNCATE TABLE tablename; If this is not the issue then it might be helpful if you provide a test case that demonstrates the issue you are having. It will be easier to diagnose that way. Michael -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] Expiring pooled connections
I'm looking for a way to close a pooled connection client-side after a certain period, say when it wasn't used in 30 seconds. I don't think any of the Pool implementations that come with SQLAlchemy supports this. Does anybody know of a third-party implementation with this functionality? I guess it wouldn't be difficult to create one from scratch but I was hoping that there is an existing solution. Thanks, Julian -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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: ms sql server schema.sequence
I mean, I tried to debug a program, using session add() to insert new records to database after I deleted all the old records from a table. I used the schema.sequence() to build up a mapper Can anyone help? On 3月16日, 下午2时05分, Tan Yi wrote: > Whenever I try to use schema.sequence() function on ms sql server, the > return serial number (after flush()) is not started from 0, instead, > it is started from a random number or something. > wondering how to work aroud this issue. try to specify start = 0 in > sequence function, but no luck -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] ms sql server schema.sequence
Whenever I try to use schema.sequence() function on ms sql server, the return serial number (after flush()) is not started from 0, instead, it is started from a random number or something. wondering how to work aroud this issue. try to specify start = 0 in sequence function, but no luck -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] composite index using declarative
On Tue, Mar 16, 2010 at 10:29 AM, Michael Bayer
wrote:
> Daniel Robbins wrote:
>
>> People want to *start* with declarative, whereas you
>> as the architect started with the core parts of the framework and
>> recently added declarative. So you know all the underpinnings, but
>> most users don't, and they want to remain on as high a level
>> (declarative) as possible without having to get sidetracked by being
>> forced to master the lower-level parts in order to simply create an
>> index for their declarative tables. Make sense? How to tie the two
>> together is not always documented clearly. Think from the new user's
>> perspective and try to accommodate them via the docs.
>
> I appreciate the rant but we're only talking about adding a distinct
> section to the declarative documentation regarding indexes, which is
> already accepted, and specific examples regarding remote_side and such are
> non-controversial as well and we shall review the cases you present.
I'm not ranting, I'm offering constructive, critical feedback for your
excellent project.
> It doesn't make much sense for the declarative documentation to completely
> duplicate the entire mapping/relation/metadata sections of the
> documentation, however.
Complete duplication is not required. However. I've written a lot of
technical documentation, and I've found that some repetition, and
building upon ideas introduced in other sections, does make technical
documentation much easier to use.
>> Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
>> Endpoint.__table__.c.name)
>
> Index to my knowledge does accept declarative columns these days. You can
> pass in Endpoint.samplegroup_id and Endpoint.name directly. The above
> workaround was for a bug.
Then this should be documented in the declarative section, with an example.
>> Host.interfaces = relation("HostInterface",
>> collection_class=column_mapped_collection(HostInterface.name))
>
> I'm not aware of this requirement. you should be able to create the
> "interfaces" relation inside of Host, using a string to define
> "collection_class" or otherwise use attribute_mapped_collection("name").
> If this is not possible, then its a bug.
Looks like a bug in 0.6_beta1 then:
Traceback (most recent call last):
File "base.py", line 525, in
class HostInterface(Base):
File "base.py", line 533, in HostInterface
interfaces = relation('HostInterface',
collection_class=column_mapped_collection('HostInterface.name'))
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/collections.py",
line 132, in column_mapped_collection
cols = [expression._no_literals(q) for q in util.to_list(mapping_spec)]
File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/expression.py",
line 980, in _no_literals
"to indicate a SQL expression literal, or 'literal()' to indicate
a bound value." % element)
sqlalchemy.exc.ArgumentError: Ambiguous literal: 'HostInterface.name'.
Use the 'text()' function to indicate a SQL expression literal, or
'literal()' to indicate a bound value.
Regardless, documentation in the declarative section about how to do
this cleanly would be appreciated.
> I wouldn't consider Oracle to be a good candidate for "diving in". But we
> do have a prominent sidebox in the ORM tutorial now, describing the need
> for the Sequence construct when using Firebird or Oracle, which you can
> see at
> http://www.sqlalchemy.org/docs/ormtutorial.html#define-and-create-a-table
That is a great addition.
> I'd note that the metaclass stuff you've done has been worked into a
> simple built in feature, using our new "mixin" capability, which is
> described at:
I think Mix-Ins are a great idea and very elegant, and I'm very
thankful they were added by Chris, but again in this case, more
complex examples would be extremely helpful.
If you look closely at what I am trying to do -- naming the primary
key sequence based on the name of the table -- Mix-Ins provide no
obvious mechanism to do this, since I need to create the primary key
and sequence "on the fly" after the table name is provided. With the
primary key in the base class and the table name in the sub-class,
this becomes problematic.
If you know how to implement the code I provided using a Mix-in, then
I'd love to see an example on this mailing list, or even better, in
the docs :)
Regarding Oracle, if someone only has Oracle to work with, or wants to
create an Oracle-based solution, then they need to figure out how to
do sophisticated things like this in order to get meaningful work done
with SQLAlchemy and feel comfortable with the framework. I don't think
the extra code or design philosophy of SQLAlchemy is an issue, it's
the time required to harvest the necessary info from the docs and
compile it into a working skeleton. Once the skeleton has been
assembled, SQLAlchemy is a joy to use.
> We mention prominently in the intro and on the wiki that there's a place
> for recipes like these, which is the UsageRecipes section:
>
>
Re: [sqlalchemy] using in_ against multiple columns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 richard reitmeyer ha scritto: > Hello, all. > > I'm trying to use compound fields with in_ to achieve something like > this: > > update foo set svrid = NULL, asof=NULL > where (svrid,asof) > in (select svrid, asof from foo except select svrid, > asof from bar); > If you are using PostgreSQL, you can use the ROW constructor. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkufw6wACgkQscQJ24LbaUTiuQCfUhN26bZA7FTzWZK8wWYl7L73 q8MAn0vu60420NU9DwPlGQCdl2EuSNAA =fEMJ -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] composite index using declarative
Daniel Robbins wrote:
> On Tue, Mar 16, 2010 at 8:08 AM, Michael Bayer
> wrote:
>>
>> we definitely should because I am amazed at how often this question gets
>> asked. But the more puzzling thing is that it means the central tenet
>> of
>> declarative isn't getting across to people, which is that nothing
>> changes
>> at all when using declarative, its strictly a syntactical shortcut -
>> everything you do when not using declarative is still 100% doable, in
>> exactly the same way. I'm not sure why I see so many questions from
>> others of the form "but I'm using declarative!"
>
> My advice to you is to embrace the feedback and use it to make the
> project better.
I'm sure if you review our mailing list history for the past five years
you'll be pleased to know that the virtually all of the progress made on
the SQLAlchemy project post version 0.1 has been driven completely by
end-user need, but at the same time not turning the project into a
PHP-soup of flags and switches, distilling what end users need into an
ever expanding but coherent system that attempts to be as consistent as
possible.Rest assured that if we didn't do a pretty good job of
balancing these sides we wouldn't still be a prominent project today.
> People want to *start* with declarative, whereas you
> as the architect started with the core parts of the framework and
> recently added declarative. So you know all the underpinnings, but
> most users don't, and they want to remain on as high a level
> (declarative) as possible without having to get sidetracked by being
> forced to master the lower-level parts in order to simply create an
> index for their declarative tables. Make sense? How to tie the two
> together is not always documented clearly. Think from the new user's
> perspective and try to accommodate them via the docs.
I appreciate the rant but we're only talking about adding a distinct
section to the declarative documentation regarding indexes, which is
already accepted, and specific examples regarding remote_side and such are
non-controversial as well and we shall review the cases you present.
Declarative doesn't *require* that arguments are passed as strings,
either, this is optional. There's some discussion of some common gotchas
here on the FAQ at
http://www.sqlalchemy.org/trac/wiki/FAQ#ImusingDeclarativeandsettingprimaryjoinsecondaryjoinusinganand_oror_andIamgettinganerrormessageaboutforeignkeys
, but we can integrate this into the declarative docs too.
It doesn't make much sense for the declarative documentation to completely
duplicate the entire mapping/relation/metadata sections of the
documentation, however. It's still expected that users who want to deal
with Index, Sequence, etc. constructs will have read these sections.
Better hyperlinking, a constant work in progress (and very open to outside
contribution via patches, we transitioned to Sphinx largely for this
reason) also makes the docs easier to follow.
>
> Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
> Endpoint.__table__.c.name)
Index to my knowledge does accept declarative columns these days. You can
pass in Endpoint.samplegroup_id and Endpoint.name directly. The above
workaround was for a bug.
> # But it would be nice to access them as a dictionary, so we could
> grab a particular interface by typing:
> # myhost.interfaces["eth0"]. Here's how we create an "interfaces"
> reference in dictionary mode. This must be done
> # outside of the class after both tables have been defined:
>
> Host.interfaces = relation("HostInterface",
> collection_class=column_mapped_collection(HostInterface.name))
I'm not aware of this requirement. you should be able to create the
"interfaces" relation inside of Host, using a string to define
"collection_class" or otherwise use attribute_mapped_collection("name").
If this is not possible, then its a bug.
> I am working on a pretty simple DB project, with only about 12 tables,
> but here is my supporting/helper code, which is an order of magnitude
> more complex than the samples in the docs, but got declarative to the
> point where 1) I could actually use it with Oracle by adding
> auto-sequences to the declarative model (a BIG hurdle for new users
> who just want to dive in and are using it with a db that doesn't have
> auto-increment sequences)
I wouldn't consider Oracle to be a good candidate for "diving in". But we
do have a prominent sidebox in the ORM tutorial now, describing the need
for the Sequence construct when using Firebird or Oracle, which you can
see at
http://www.sqlalchemy.org/docs/ormtutorial.html#define-and-create-a-table
.
> and 2) where I could significantly reduce
> duplicated code, which is generally one of the benefits of using a
> class heirarchy.
>
> It took me quite a bit of time to piece this all together, where more
> complex examples in the docs would have helped me along:
I'd note that the metaclass stuff you've done has been worked into a
simple built in feature,
Re: [sqlalchemy] composite index using declarative
On Tue, Mar 16, 2010 at 8:08 AM, Michael Bayer wrote:
>
> we definitely should because I am amazed at how often this question gets
> asked. But the more puzzling thing is that it means the central tenet of
> declarative isn't getting across to people, which is that nothing changes
> at all when using declarative, its strictly a syntactical shortcut -
> everything you do when not using declarative is still 100% doable, in
> exactly the same way. I'm not sure why I see so many questions from
> others of the form "but I'm using declarative!"
My advice to you is to embrace the feedback and use it to make the
project better. People want to *start* with declarative, whereas you
as the architect started with the core parts of the framework and
recently added declarative. So you know all the underpinnings, but
most users don't, and they want to remain on as high a level
(declarative) as possible without having to get sidetracked by being
forced to master the lower-level parts in order to simply create an
index for their declarative tables. Make sense? How to tie the two
together is not always documented clearly. Think from the new user's
perspective and try to accommodate them via the docs.
I'm including a bunch of my sample code below, which you are welcome
to use in the docs or SQLAlchemy itself. This stuff deals with table
creation - you also need more examples for queries, but I don't have
enough useful examples stored up for those yet.
Here's one thing that was tricky to figure out - a self-referencing
table using declarative. Tricky because of the "remote_side" reference
using a string:
# The following code implements a self-referencing, heirarchical
table, and is tricky code
# to figure out for SQLAlchemy. You can append children to .children
or choose to create the
# child first and set its parent. Commit one and the parent/children
should be committed
# too.
class Location(Base):
__tablename__ = 'location'
parent_id = Column(Integer, ForeignKey('location.id'))
parent = relation('Location', backref=backref('children'),
remote_side='location.c.id')
name = UniqueString(25)
desc = Column(String(80))
Below, there are some examples of "bridging the divide" between
non-declarative and declarative use, to show people how to do it. It's
much easier to understand how to do this when you can actually see
code that does it. A lot of the examples in the docs are somewhat
trivial and don't really show you how the pieces fit together, such as
this example below, which shows how to reference __table__:
class Endpoint(Base):
__tablename__ = 'endp'
__table_args__ = [
UniqueConstraint( 'samplegroup_id', 'name' ),
]
samplegroup_id, samplegroup = ManyToOne(SampleGroup, nullable=False)
name = Column(String(80), nullable=False)
os_id, os = ManyToOne(EndpointOSType, nullable=False)
Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
Endpoint.__table__.c.name)
Here is another much-needed example:
# this table has a "peer_id" which can reference another
HostInterface. myhostinterface.peer will reference this peer, or
# None if no peer is set in peer_id. There is a backref to from the
peer back to myhostinferface using the .peered backref.
#
class HostInterface(Base):
__tablename__ = 'host_i'
name = Column(String(20), nullable=False)
host_id, host = ManyToOne(Host, nullable=False )
hostmac_id, hostmac = ManyToOne(HostMAC, nullable=False)
peer_id = Column(Integer, ForeignKey('host_i.id'), index=True)
peer = relation('HostInterface', backref=backref('peered'),
remote_side='host_i.c.id')
ip_id, ip = ManyToOne(IP)
class Host(Base):
__tablename__ = 'host'
owner_id, owner = ManyToOne(User, nullable=False, index=True)
type = Column(String(1), nullable=False)
hostid = UniqueString()
#Our host can have many HostInterfaces. By default, SQLAlchemy would
allow us to reference them as a list, such as:
# for int in myhost.interfaces:
#print int
# But it would be nice to access them as a dictionary, so we could
grab a particular interface by typing:
# myhost.interfaces["eth0"]. Here's how we create an "interfaces"
reference in dictionary mode. This must be done
# outside of the class after both tables have been defined:
Host.interfaces = relation("HostInterface",
collection_class=column_mapped_collection(HostInterface.name))
I am working on a pretty simple DB project, with only about 12 tables,
but here is my supporting/helper code, which is an order of magnitude
more complex than the samples in the docs, but got declarative to the
point where 1) I could actually use it with Oracle by adding
auto-sequences to the declarative model (a BIG hurdle for new users
who just want to dive in and are using it with a db that doesn't have
auto-increment sequences) and 2) where I could significantly reduce
duplicated code, which is generally
Re: [sqlalchemy] composite index using declarative
Daniel Robbins wrote: > On Mar 15, 2010, at 4:58 PM, Michael Bayer wrote: >> we have some index examples at >> http://www.sqlalchemy.org/docs/metadata.html#indexes . the Index is not >> related to declarative and is specified separately. > > Any plans to improve this? I would have thought that Indexes would be > defined under __table_args__ along with constraints, which are often > related. the word "improve" here makes me smile. Its the way it is intentionally to look the way table DDL does. We could make the Index construct capable of accepting non-table bound columns in its constructor which would allow you to stick it within your declarative class before the Table is generated...but we're literally talking about allowing a single indent. > > Since Index creation is such a common need, and the current means of > creating one is counter-intuitive, do you think you could add a sample > Index code snippet to the declarative documentation? we definitely should because I am amazed at how often this question gets asked. But the more puzzling thing is that it means the central tenet of declarative isn't getting across to people, which is that nothing changes at all when using declarative, its strictly a syntactical shortcut - everything you do when not using declarative is still 100% doable, in exactly the same way. I'm not sure why I see so many questions from others of the form "but I'm using declarative!" > > -Daniel > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] using in_ against multiple columns
richard reitmeyer wrote: > Hello, all. > > I'm trying to use compound fields with in_ to achieve something like > this: > > update foo set svrid = NULL, asof=NULL > where (svrid,asof) > in (select svrid, asof from foo except select svrid, > asof from bar); > > Use case is cleaning up entries in foo records that refer to damaged > or missing bar records, where the check is against a column pair. I'm > expecting ~99% of the records in foo to have a correspondence to > something in bar. > > My problem is in the where clause for the update; I need to apply > _in(inner_stmt) to something column-like, and I'm missing what that > would be. I've tried > sqlalchemy.sql.expression.ColumnCollection(foo.c.svrid, > foo.c.asof).in_(inner_stmt) and clearly ColumnCollection has a limited > set of supported operations. we have an 0.6 construct called tuple() that allows this. http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?highlight=tuple#sqlalchemy.sql.expression.tuple_ > > Suggestions? > > Richard > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] using in_ against multiple columns
Hello, all. I'm trying to use compound fields with in_ to achieve something like this: update foo set svrid = NULL, asof=NULL where (svrid,asof) in (select svrid, asof from foo except select svrid, asof from bar); Use case is cleaning up entries in foo records that refer to damaged or missing bar records, where the check is against a column pair. I'm expecting ~99% of the records in foo to have a correspondence to something in bar. My problem is in the where clause for the update; I need to apply _in(inner_stmt) to something column-like, and I'm missing what that would be. I've tried sqlalchemy.sql.expression.ColumnCollection(foo.c.svrid, foo.c.asof).in_(inner_stmt) and clearly ColumnCollection has a limited set of supported operations. Suggestions? Richard -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.
