[sqlalchemy] Re: SQLAlchemy 1.0.0 Released

[Alexandre Conrad]

Congratulations Mike!

I must say that I am impressed (and always have been) by the amount of
dedication you have put into SQLAlchemy over the years and everything you
built around it, documentation, community, ... I can't think of any other
open source project that has such a high degree of quality given the
complexity of the library and the size of the team (I always saw it as a
1-man show).

Glad to see 1.0 is out! So, does this mean I can use SQLAlchemy in
production now? ;P


2015-04-16 12:38 GMT-07:00 Mike Bayer mike...@zzzcomputing.com:

  Hello list !

 It is my great pleasure to announce that SQLAlchemy release 1.0.0 is now
 available.

 Release 1.0.0 marks the tenth major SQLAlchemy series and occurs within
 the tenth year of the SQLAlchemy project overall;  development started in
 2005 and the release of 0.1.0 was in February of 2006.

 Calls for SQLAlchemy to go 1.0 started early on, as early as version 0.3
 (!).   However, the magnitude of the task taken on by SQLAlchemy was much
 broader than that; the development philosophy taken by the project is one
 of slowly building out a wide-reaching base of functionality, integrating
 many rounds of refactoring and rethinks over a long period of time and
 building new paradigms and features on top only as the foundation matures
 enough to support them.

 Users of 1.0 have the benefit of ten years of production deployments,
 total rethinks of core APIs in early versions, a vast number of API
 additions and refinements over later versions, at least a dozen major
 internal rearchitectures, and as always a relentless focus on improving
 performance.

 The SQLAlchemy project could not be what it is today without the
 unbelievable support, input, and sheer love of the user community - from
 the vast amounts of knowledge and improvements gained from tens of
 thousands of mailing list messages, to the improvements hammered out
 through over three thousand bug reports, to the amazing developers all
 around the world who have presented talks and tutorials on SQLAlchemy and
 of course the audiences who continue to attend them, to the bloggers and
 book authors supporting our community, to the tweeters sending gratitude
 our way, to our many hundreds of contributors of patches and pull requests,
 as well as financial contributors who have consistently supported
 SQLAlchemy's hosting costs, as well as more than a few burritos ;).

 In particular, SQLAlchemy's success was made possible by its original
 developer team, and I would like to express to all of them my very deep
 gratitude for their tremendous efforts towards contributing code and wisdom
 to the project, as well as support of my work from very early on:

 * Jason Kirtland
 * Gaëtan de Menten
 * Diana Clarke
 * Michael Trier
 * Philip Jenvey
 * Ants Aasma
 * Paul Johnston
 * Jonathan Ellis

 I'd also like to thank Simon King and Jonathan Vanasco for their ongoing
 contributions towards the mailing list, Alex Grönholm, creator of the
 excellent sqlacodegen [1] project, for his energetic and ubiquitous support
 of thousands of IRC users, and Sanjiv Singh, early developer of GeoAlchemy
 [2] for the awesome set of drink coasters I use every day :).

 Release 1.0.0 features an array of usability enhancements, new features,
 bug fixes, and considerable performance enhancements.   After five short
 beta releases, it is anticipated that the impact of upgrading from 0.9 or
 even 0.8 to 1.0.0 should be minimal; however in all cases, users are highly
 encouraged to carefully read through the behavioral enhancements and
 changes documented in the 1.0 migration notes, at What's new in 1.0? at
 http://www.sqlalchemy.org/docs/10/changelog/migration_10.html.

 Changelog for 1.0.0 is at:
 http://www.sqlalchemy.org/changelog/CHANGES_1_0_0

 SQLAlchemy 1.0.0 is available on the download page at
 http://www.sqlalchemy.org/download.html


 [1] https://pypi.python.org/pypi/sqlacodegen
 [2] http://geoalchemy.org/


  --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy-devel group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to sqlalchemy-devel+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy-de...@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy-devel.
 For more options, visit https://groups.google.com/d/optout.




-- 
Alex

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: SQLAlchemy 0.8.0 Released

[Alexandre Conrad]

Hey Mike,

Thanks the fantastic work you have been offering to the open source
community over the years ! I am personally very grateful and I am sure a
lot of people think the same.

Maybe should you jump directly to version SQLA 1.1 as people think 1.0
versions are still immature and they would rather wait and let others
stumble on bugs first -- you know, it's the first stable version after all.
;)

Cheers,



2013/3/9 Michael Bayer mike...@zzzcomputing.com

 Hey gang -

 After a long delay I've finally put out 0.8.0 final.Work on 0.8.0
 began almost a year ago, and since then the release has shaped up very
 nicely, including that we've had a very long beta period.   Due to the
 behavior of pip, lots of you have already been using 0.8.0b2 as pip
 doesn't honor the hidden flag on Pypi.

 For those on 0.7, upgrading to 0.8 shouldn't be very hard, though
 obviously it should be tested fully, as there are some behavioral changes.
  For a list of every known behavioral change, please refer to the
 Behavioral Changes section of the migration document at
 http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html .   For
 those who have been on 0.8.0b2, there are three very small additional
 behavioral changes that I doubt the vast majority of users will notice;
 they are also noted at the top of the changelog.

 Overall I think the impression you'll get from the changes is that we're
 really in the long tail of refinement at this point.  Whereas most previous
 major releases I've spent lots of time refactoring for performance, in this
 release I've gone back to refactoring for more correctness and clarity
 (albeit often very intricate clarity, necessarily) within many areas of the
 code, particularly the ORM.Whereas before, I'd focus on inlining and
 reducing method calls, at this stage I've been able to start breaking up
 some of the more monolithic sections back into small modular and more
 testable bits without any real impact on performance.  The mechanics of
 relationships as well as polymorphic querying have seen major changes, and
 I'm sort of blown away at the level of queries this thing can pull out in
 those areas (though there's *still* lots more things we need to do with
 querying). We've moved to support Python 2.5 at the lowest (and we will
 very quickly be moving that up to 2.6), rewritten a *lot* of tests (though
 we're over 5000 now, so there's plenty more tests that need to be pulled
 into the modern age), and planted the seeds for a growing ecosystem of
 external third party dialects, including those of SQLAlchemy's new friends,
 IBM (were we've helped them to modernize and release an all-new DB2
 dialect) and Akiban (a great new database company in Boston).

 The blog post at 
 http://www.sqlalchemy.org/blog/#sqlalchemy-0.8.0-releasedgives a top-level 
 sales pitch for 0.8.0 as well as a rundown of some of the
 biggest changes.   I will note that I am trying to push SQLAlchemy towards
 a 1.0 status as fast as possible at this point; there is a roadmap for
 0.9, but I may decide to first skip to 1.0 soon within the 0.8 series
 (basically it would be an 0.8.x renamed as 1.0),  then 0.9 would become 1.1
 (if that makes sense).

 It's only a few days before Pycon, where I'll be doing an Introduction to
 SQLAlchemy tutorial as well as a repeat of my Session in Depth talk, and
 am looking forward to seeing the whole gang this year.   I'd like to thank
 everyone who has helped with this release, starting with code contributions
 that began at last year's Pycon.   And a *huge* thanks to those folks
 tipping me on Gittip and Flattr, and the handful of professional clients
 who have contributed towards the codebase this year, as well as those folks
 chipping in on the mailing list and on StackOverflow.  I'd recommend
 everyone get onto Gittip (it's free to join of course!), on some of those
 Mondays where I see a new user has already been helped on the mailing list,
 allowing me to get on with my day, I'd love to send a few bytes over to
 those helpful folks in return.

 SQLAlchemy 0.8.0 can be downloaded at:
 http://www.sqlalchemy.org/download.html

 Full changelog: http://www.sqlalchemy.org/changelog/CHANGES_0_8_0

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy-devel group.
 To unsubscribe from this group and stop receiving emails from it, send an
 email to sqlalchemy-devel+unsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy-de...@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy-devel?hl=en.
 For more options, visit https://groups.google.com/groups/opt_out.





-- 
Alex

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at 

[sqlalchemy] Re: SQLAlchemy 0.8.0b1 released

[Alexandre Conrad]

Congrats Mike for all this great work!

2012/10/30 Michael Bayer mike...@zzzcomputing.com

 The first beta release of the SQLAlchemy 0.8 series, 0.8.0b1, is released
 for developer evaluation.

 0.8 represents the latest series of refinements to the SQLAlchemy Core and
 ORM libraries and features over 100 individual changes, consisting of major
 new features, bug fixes, and other enhancements. There are also major
 refactorings present in both the library and the test suite intended to
 improve maintainability, performance, and compability with future
 enhancements and external projects.

 The purpose of the beta releases is to give a wide range of developers a
 chance to evaluate the new version against existing applications, and to
 alert the SQLAlchemy team of any regressions or major upgrade issues being
 experienced. Each major release of SQLAlchemy includes a wide array of
 planned but backwards-incompatible changes, including the removal of
 deprecated features, as well as bug fixes which change behaviors that some
 applications may have been relying upon.

 Among many changes include improvements to the documentation system. The
 changelog and migration guides for SQLAlchemy are now integrated with the
 Sphinx documentation, where they can refer to each other as well as the
 rest of the SQLAlchemy documentation.

 Users should start by reading the What's New in 0.8 document, which
 introduces major new features as well as areas where applications may
 experience a behavioral change. The 0.8.0b1 Changelog will enumerate most
 changes in detail.

 Download: http://www.sqlalchemy.org/download.html

 What's New in 0.8:
 http://www.sqlalchemy.org/docs/08/changelog/migration_08.html

 0.8.0b1 changelog:
 http://www.sqlalchemy.org/docs/08/changelog/changelog_08.html#change-0.8.0b1

 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy-devel group.
 To post to this group, send email to sqlalchemy-de...@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy-devel+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy-devel?hl=en.




-- 
Alex | twitter.com/alexconrad

-- 
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] WITH (nolock) on all queries

[Alexandre Conrad]

Hi list,

So I am working on a project with SQLAlchemy using MSSQL as a back-end
DB and I need to add a WITH (nolock) statement to all my queries
implicitly. Even for the .get(pk_id) method. Mike actually pointed me
to the .with_hint() method on the Query object but I couldn't get it
to work.

PS: I also noticed that there was a .with_lockmode() and I was
wondering if that could help as this can be passed at the Session
level, which implies that it will affect all queries (whatever this
does).

Thanks,
-- 
Alex | twitter.com/alexconrad

-- 
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] WITH (nolock) on all queries

[Alexandre Conrad]

I must have it wrong, I admit I don't quite understand the arguments
of .with_hint()

session.query(User).with_hint(User, 'WITH (nolock)').get(1)

if that makes any sense (I wonder why I'd need to pass the User object again).

2011/6/28 Michael Bayer mike...@zzzcomputing.com:

 On Jun 28, 2011, at 12:55 PM, Alexandre Conrad wrote:

 Hi list,

 So I am working on a project with SQLAlchemy using MSSQL as a back-end
 DB and I need to add a WITH (nolock) statement to all my queries
 implicitly. Even for the .get(pk_id) method. Mike actually pointed me
 to the .with_hint() method on the Query object but I couldn't get it
 to work.

 PS: I also noticed that there was a .with_lockmode() and I was
 wondering if that could help as this can be passed at the Session
 level, which implies that it will affect all queries (whatever this
 does).


 well with_hint() should add it but if you really want to be heavyhanded you 
 can override the compilation of sqlalchemy.expression.sql.Select to do it, 
 using the form described at:

 http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs




 Thanks,
 --
 Alex | twitter.com/alexconrad

 --
 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.





-- 
Alex | twitter.com/alexconrad

-- 
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 can I change manually the value of field with onupdate option ? I would like override onupdate mechanism

[Alexandre Conrad]

Not sure on this one, but are you passing a formatted date string? Maybe you
should set a datetime object directly and let SA do the string conversion
during flush.

Alex

Sent from my fantastic HTC Hero

On Oct 28, 2010 1:41 AM, KLEIN Stéphane klein.steph...@gmail.com wrote:

Hi,

in my project, I use onupdate attribute :

foobar_table = Table(FooBar, meta.metadata,
...
   Column(created, DateTime(), default=datetime.datetime.now),
   Column(modified, DateTime(), default=datetime.datetime.now,
onupdate=datetime.datetime.now),
...
)

All work great.

However, my project have an importation feature and I need to set
original modified field value.

To do that, I've try this solution :

my_foobar_obj.modifield =
datetime.datetime.strptime(source_date_value, '%Y-%m-%d %H:%M:%S')
session.commit()

= not success, modified field not contain source_date_value but
current date

Other solution :

foobar_table.update().\
   where(foobar_table.c.id==my_foobar_obj.id).\

values(modified=datetime.datetime.strptime(source_date_value, '%Y-%m-
%d %H:%M:%S'))

= not success, modified field not contain source_date_value but
current date

Have you a tips to manually change modified field value ?

Thanks for your help,
Stephane

--
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.comsqlalchemy%2bunsubscr...@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] SQLAlchemy 0.6.5 Released

[Alexandre Conrad]

This is amazing work! Thanks Mike. I am truly flabbergasted (heh, I
always wanted to use that word somehow).

How many tests are there?


2010/10/24 Michael Bayer mike...@zzzcomputing.com:
 Greetings -

 SQLAlchemy 0.6.5 is released.     As we approach towards the start of 0.7, 
 where a good stack of branches are accumulating, the 0.6 series starts to 
 come in for a landing.  0.6 has been tremendously successful, with 0.6.4 
 logging 28,143 downloads from Pypi alone over a period of 7 weeks.   I've 
 rolled out the 0.6 series on two different very different projects - one a 
 sprawling, rapidly developed social media platform, the other a formal and 
 constrained financial analysis application.  Both exercise the ORM and Core 
 to a very deep degree in a broad range of situations - within web 
 applications which make heavy usage of the Beaker caching system and various 
 forms of eager loading, within backend services that run many concurrent 
 operations and transactions via multiprocessing, in bulk loader scripts which 
 efficiently flush through tens of thousands of interconnected objects, in 
 expressions that make great usage of dates, intervals, decimals, custom 
 types, generic transformations and subqueries, in models constructed around 
 joined and single table inheritance, update and delete cascades, lots of 
 transparent versioning.  So I'm pretty happy with it, and I hope most people 
 out there are as well.

 0.6.5 has the usual longer than expected list of changes, a bunch of new 
 features, fixes for many mostly relatively minor bugs.

 Download SQLAlchemy 0.6.5 at :

 http://www.sqlalchemy.org/download.html

 0.6.5
 =
 - orm
  - Added a new lazyload option immediateload.
    Issues the usual lazy load operation automatically
    as the object is populated.   The use case
    here is when loading objects to be placed in
    an offline cache, or otherwise used after
    the session isn't available, and straight 'select'
    loading, not 'joined' or 'subquery', is desired.
    [ticket:1914]

  - New Query methods: query.label(name), query.as_scalar(),
    return the query's statement as a scalar subquery
    with /without label [ticket:1920];
    query.with_entities(*ent), replaces the SELECT list of
    the query with new entities.
    Roughly equivalent to a generative form of query.values()
    which accepts mapped entities as well as column
    expressions.

  - Fixed recursion bug which could occur when moving
    an object from one reference to another, with
    backrefs involved, where the initiating parent
    was a subclass (with its own mapper) of the
    previous parent.

  - Fixed a regression in 0.6.4 which occurred if you
    passed an empty list to include_properties on
    mapper() [ticket:1918]

  - Fixed labeling bug in Query whereby the NamedTuple
    would mis-apply labels if any of the column
    expressions were un-labeled.

  - Patched a case where query.join() would adapt the
    right side to the right side of the left's join
    inappropriately [ticket:1925]

  - Query.select_from() has been beefed up to help
    ensure that a subsequent call to query.join()
    will use the select_from() entity, assuming it's
    a mapped entity and not a plain selectable,
    as the default left side, not the first entity
    in the Query object's list of entities.

  - The exception raised by Session when it is used
    subsequent to a subtransaction rollback (which is what
    happens when a flush fails in autocommit=False mode) has
    now been reworded (this is the inactive due to a
    rollback in a subtransaction message). In particular,
    if the rollback was due to an exception during flush(),
    the message states this is the case, and reiterates the
    string form of the original exception that occurred
    during flush. If the session is closed due to explicit
    usage of subtransactions (not very common), the message
    just states this is the case.

  - The exception raised by Mapper when repeated requests to
    its initialization are made after initialization already
    failed no longer assumes the hasattr case, since
    there's other scenarios in which this message gets
    emitted, and the message also does not compound onto
    itself multiple times - you get the same message for
    each attempt at usage. The misnomer compiles is being
    traded out for initialize.

  - Fixed bug in query.update() where 'evaluate' or 'fetch'
    expiration would fail if the column expression key was
    a class attribute with a different keyname as the
    actual column name.  [ticket:1935]

  - Added an assertion during flush which ensures
    that no NULL-holding identity keys were generated
    on newly persistent objects.
    This can occur when user defined code inadvertently
    triggers flushes on not-fully-loaded objects.

  - lazy loads for relationship attributes now use
    the current state, not the committed state,
    of foreign and primary key 

Re: [sqlalchemy] Re: self referencing column

[Alexandre Conrad]

This is the documentation for self referential mapper:

http://www.sqlalchemy.org/docs/mappers.html#adjacency-list-relationships

I guess you have found the recipes for doing it the declarative way.

2010/8/26 waugust waugustyn...@gmail.com:
 I'm guessing that the answer would be as at:
 http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeSelfReferencingTable

 ?

 On Aug 26, 8:02 pm, waugust waugustyn...@gmail.com wrote:
 I've been looking through the documentation and I could have sworn I
 saw it before but It seem I can't find it once more...
 I'm looking for how to set up a self referencing column like in this
 pseudo scenerio:

 class Action(DeclarativeBase):

            __tablename__ = 'action'

            id = Column(Integer, autoincrement=True, primary_key=True)

     onsuccess = Column(Integer, ForeignKey=('action.id')  

 I would want to have an id of an action upon the success of an
 action...

 --
 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.





-- 
Alex
twitter.com/alexconrad

-- 
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: session get last row id?

[Alexandre Conrad]

flush is not commit. After a flush, you can still rollback.

2010/7/24 manman ne.man...@gmail.com

 if use flush() then how to rollback ?

 --
 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.comsqlalchemy%2bunsubscr...@googlegroups.com
 .
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.




-- 
Alex
twitter.com/alexconrad

-- 
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] sqlalchemy 0.5.8 or 0.6.0?

[Alexandre Conrad]

2010/5/30 Krishnakant Mane krm...@gmail.com:
 So I asume that 0.6.0 is pritty stable.
 Is all the documentation upto date?

SQLAlchemy 0.6's documentation is up-to-date and probably is one of
the best documentation in the Python eco-system.

-- 
Alex
twitter.com/alexconrad

-- 
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] deleting

[Alexandre Conrad]

2010/4/14 Sebastian Elsner sebast...@risefx.com:
 I am unsure on how to delete correctly. Lets say I have a query's result,
 which is a list. del list[5] does not seem to do anything, right? I would
 always have to do session.delete(list[5])?

Correct. del list[5] only delete the instance in memory. If you want
to delete from the database, you have to explicitly tell the session
using its .delete() method.

 But what about when I created a mapped object, which is not yet persistent,
 how would I delete that? Just del someobject?

If it's not yet added to the Session, you can just del someobject or
let the GC make the job for you, if that makes sens in your scenario.

-- 
Alex
twitter.com/alexconrad

-- 
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] adding/deleting list of objects -- feature request/proposal

[Alexandre Conrad]

How about just a:

cities = session.query(City).all()
for city in cities:
session.delete(city)

or

for city in session.query(City):   #actually fires the query
session.delete(city)

or even (never tried that, read from the docs):

session.query(City).delete()

Alex

2010/2/26 Marcin Krol mrk...@gmail.com:
 Hello Michael,

 When I do:

    cities = session.query(City).all()
    session.delete(cities)

 I get:

 sqlalchemy.orm.exc.UnmappedInstanceError: Class '__builtin__.list' is not
 mapped

 Unless it's some sort of a problem, it would be nifty of SQLAlchemy to be
 able to add/delete in a session a list of SQA-mapped objects: from the POV
 of a noob like me just using this engine as a sort of 'black box' it would
 be rather logical.

 Regards,
 mk

 --
 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.





-- 
Alex
twitter.com/alexconrad

-- 
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] eagerload

[Alexandre Conrad]

2010/2/26 Marcin Krol mrk...@gmail.com:
 mapper(City, city_table, properties={'users':relation(User,
 order_by='user_.id', backref='city_backref')})

Isn't it

  order_by='user.id'

?

-- 
Alex
twitter.com/alexconrad

-- 
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] adding/deleting list of objects -- feature request/proposal

[Alexandre Conrad]

2010/2/26 Marcin Krol mrk...@gmail.com:
 Sure, it can be done and I'm doing that: I'm just saying that it wouldn't
 hurt if you could pass a list to session.add or session.delete instead of
 handing objects over one by one.

For adding, there's the .add_all(instances) method available.

There's no .delete_all() method available AFAIS.

Another one-liner alternative would be: map(session.delete, instances)

Or providing a patch implementing delete_all() I guess. Or waiting for
an explanation from Mike. :)

-- 
Alex
twitter.com/alexconrad

-- 
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] easy_install SQLAlchemy

[Alexandre Conrad]

Hello,

Problem figured out: I opened an issue on Distribute (the setuptools fork):

http://bitbucket.org/tarek/distribute/issue/99/easy_install-uses-setupcfg-if-in-working

Regards,
Alex

2009/12/2 Alexandre Conrad alexandre.con...@gmail.com:
 Ok, I have my finger on the problem. From a virtualenv at the root of
 my pylons project (where development.ini lives):

 --
 a...@alex-laptop:~/hg/papyrus/papyrus$ virtualenv --no-site-package foo
 New python executable in foo/bin/python
 Installing setuptoolsdone.
 a...@alex-laptop:~/hg/papyrus/papyrus$ source foo/bin/activate
 (foo)a...@alex-laptop:~/hg/papyrus/papyrus$ easy_install SQLAlchemy
 Searching for SQLAlchemy
 Reading http://www.pylonshq.com/download/
 Best match: SQLAlchemy 0.5.2
 Downloading http://www.pylonshq.com/download/0.9.7/SQLAlchemy-0.5.2.tar.gz
 Processing SQLAlchemy-0.5.2.tar.gz
 Running SQLAlchemy-0.5.2/setup.py -q bdist_egg --dist-dir
 /tmp/easy_install-IVPutW/SQLAlchemy-0.5.2/egg-dist-tmp-JR8Ktt
 no previously-included directories found matching 'doc/build/output'
 zip_safe flag not set; analyzing archive contents...
 sqlalchemy.databases.mysql: module MAY be using inspect.stack
 Adding SQLAlchemy 0.5.2 to easy-install.pth file

 Installed 
 /home/alex/hg/papyrus/papyrus/foo/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg
 Processing dependencies for SQLAlchemy
 Finished processing dependencies for SQLAlchemy
 --

 So, the download URL seems to be provided by pylons. Huh?

 Now, from a virtualenv outside my project:

 --
 a...@alex-laptop:~$ virtualenv --no-site-package foo
 New python executable in foo/bin/python
 Installing setuptoolsdone.
 a...@alex-laptop:~$ source foo/bin/activate
 (foo)a...@alex-laptop:~$ easy_install SQLAlchemy
 Searching for SQLAlchemy
 Reading http://pypi.python.org/simple/SQLAlchemy/
 Reading http://www.sqlalchemy.org
 Best match: SQLAlchemy 0.5.6
 Downloading 
 http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5=65c6d2007969bf5d70307afc65687391
 Processing SQLAlchemy-0.5.6.tar.gz
 Running SQLAlchemy-0.5.6/setup.py -q bdist_egg --dist-dir
 /tmp/easy_install-nrpteJ/SQLAlchemy-0.5.6/egg-dist-tmp-NZ5I2H
 no previously-included directories found matching 'doc/build/output'
 zip_safe flag not set; analyzing archive contents...
 sqlalchemy.test.orm: module MAY be using inspect.getframeinfo
 sqlalchemy.test.orm: module MAY be using inspect.stack
 sqlalchemy.databases.mysql: module MAY be using inspect.stack
 Adding SQLAlchemy 0.5.6 to easy-install.pth file

 Installed 
 /home/alex/foo/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg
 Processing dependencies for SQLAlchemy
 Finished processing dependencies for SQLAlchemy
 --

 That is correct. Now why is easy_install using pylons as the search
 path for SQLAlchemy? I guess that's an easy_install related problem.

 I'll ask elsewhere. Although if someone reading this knows, I'll be
 happy to know why.

 Regards,
 Alex


 2009/12/1 Mariano Mara mariano.m...@gmail.com:
 Excerpts from Tefnet Developers - Tomasz Jezierski's message of Tue Dec 01 
 19:06:31 -0300 2009:
 Dnia 2009-12-01, Wt o godzinie 18:45 +0100, Alexandre Conrad pisze:
  Hey there,
 
  just wondering:
 
      easy_install SQLAlchemy
 
  downloads and installs version 0.5.2. Shouldn't it be 0.5.6?
 
 # easy_install SQLAlchemy
 Searching for SQLAlchemy
 Reading http://pypi.python.org/simple/SQLAlchemy/
 Reading http://www.sqlalchemy.org
 Best match: SQLAlchemy 0.5.6
 Downloading
 http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5=
 65c6d2007969bf5d70307afc65687391
 Processing SQLAlchemy-0.5.6.tar.gz
 Running SQLAlchemy-0.5.6/setup.py -q bdist_egg
 --dist-dir /tmp/easy_install-biunCq/SQLAlchemy-0.5.6/egg-dist-tmp-S--F9w
 no previously-included directories found matching 'doc/build/output'
 zip_safe flag not set; analyzing archive contents...
 sqlalchemy.databases.mysql: module MAY be using inspect.stack
 sqlalchemy.test.orm: module MAY be using inspect.getframeinfo
 sqlalchemy.test.orm: module MAY be using inspect.stack
 Adding SQLAlchemy 0.5.6 to easy-install.pth file

 Installed /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg
 Processing dependencies for SQLAlchemy
 Finished processing dependencies for SQLAlchemy

 It seems it really depends on something related with
 easy_install (I'm not sure what):
  I run the process for SqlAlchemy from a virtual environ using python
 2.6 and it tried to install 0.5.6
  I then deactivate the virtualenv and tried from the easy_install from
 distribution (Ubuntu 9.04, python2.6) and told me the best match was
 0.5.3

 BTW, you can always do
  easy_install SqlAlchemy==0.5.6

 Mariano

 --

 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch

[sqlalchemy] easy_install SQLAlchemy

[Alexandre Conrad]

Hey there,

just wondering:

easy_install SQLAlchemy

downloads and installs version 0.5.2. Shouldn't it be 0.5.6?

Regards,
Alex

--

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] easy_install SQLAlchemy

[Alexandre Conrad]

Ok, I have my finger on the problem. From a virtualenv at the root of
my pylons project (where development.ini lives):

--
a...@alex-laptop:~/hg/papyrus/papyrus$ virtualenv --no-site-package foo
New python executable in foo/bin/python
Installing setuptoolsdone.
a...@alex-laptop:~/hg/papyrus/papyrus$ source foo/bin/activate
(foo)a...@alex-laptop:~/hg/papyrus/papyrus$ easy_install SQLAlchemy
Searching for SQLAlchemy
Reading http://www.pylonshq.com/download/
Best match: SQLAlchemy 0.5.2
Downloading http://www.pylonshq.com/download/0.9.7/SQLAlchemy-0.5.2.tar.gz
Processing SQLAlchemy-0.5.2.tar.gz
Running SQLAlchemy-0.5.2/setup.py -q bdist_egg --dist-dir
/tmp/easy_install-IVPutW/SQLAlchemy-0.5.2/egg-dist-tmp-JR8Ktt
no previously-included directories found matching 'doc/build/output'
zip_safe flag not set; analyzing archive contents...
sqlalchemy.databases.mysql: module MAY be using inspect.stack
Adding SQLAlchemy 0.5.2 to easy-install.pth file

Installed 
/home/alex/hg/papyrus/papyrus/foo/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg
Processing dependencies for SQLAlchemy
Finished processing dependencies for SQLAlchemy
--

So, the download URL seems to be provided by pylons. Huh?

Now, from a virtualenv outside my project:

--
a...@alex-laptop:~$ virtualenv --no-site-package foo
New python executable in foo/bin/python
Installing setuptoolsdone.
a...@alex-laptop:~$ source foo/bin/activate
(foo)a...@alex-laptop:~$ easy_install SQLAlchemy
Searching for SQLAlchemy
Reading http://pypi.python.org/simple/SQLAlchemy/
Reading http://www.sqlalchemy.org
Best match: SQLAlchemy 0.5.6
Downloading 
http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5=65c6d2007969bf5d70307afc65687391
Processing SQLAlchemy-0.5.6.tar.gz
Running SQLAlchemy-0.5.6/setup.py -q bdist_egg --dist-dir
/tmp/easy_install-nrpteJ/SQLAlchemy-0.5.6/egg-dist-tmp-NZ5I2H
no previously-included directories found matching 'doc/build/output'
zip_safe flag not set; analyzing archive contents...
sqlalchemy.test.orm: module MAY be using inspect.getframeinfo
sqlalchemy.test.orm: module MAY be using inspect.stack
sqlalchemy.databases.mysql: module MAY be using inspect.stack
Adding SQLAlchemy 0.5.6 to easy-install.pth file

Installed /home/alex/foo/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg
Processing dependencies for SQLAlchemy
Finished processing dependencies for SQLAlchemy
--

That is correct. Now why is easy_install using pylons as the search
path for SQLAlchemy? I guess that's an easy_install related problem.

I'll ask elsewhere. Although if someone reading this knows, I'll be
happy to know why.

Regards,
Alex


2009/12/1 Mariano Mara mariano.m...@gmail.com:
 Excerpts from Tefnet Developers - Tomasz Jezierski's message of Tue Dec 01 
 19:06:31 -0300 2009:
 Dnia 2009-12-01, Wt o godzinie 18:45 +0100, Alexandre Conrad pisze:
  Hey there,
 
  just wondering:
 
      easy_install SQLAlchemy
 
  downloads and installs version 0.5.2. Shouldn't it be 0.5.6?
 
 # easy_install SQLAlchemy
 Searching for SQLAlchemy
 Reading http://pypi.python.org/simple/SQLAlchemy/
 Reading http://www.sqlalchemy.org
 Best match: SQLAlchemy 0.5.6
 Downloading
 http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5=
 65c6d2007969bf5d70307afc65687391
 Processing SQLAlchemy-0.5.6.tar.gz
 Running SQLAlchemy-0.5.6/setup.py -q bdist_egg
 --dist-dir /tmp/easy_install-biunCq/SQLAlchemy-0.5.6/egg-dist-tmp-S--F9w
 no previously-included directories found matching 'doc/build/output'
 zip_safe flag not set; analyzing archive contents...
 sqlalchemy.databases.mysql: module MAY be using inspect.stack
 sqlalchemy.test.orm: module MAY be using inspect.getframeinfo
 sqlalchemy.test.orm: module MAY be using inspect.stack
 Adding SQLAlchemy 0.5.6 to easy-install.pth file

 Installed /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg
 Processing dependencies for SQLAlchemy
 Finished processing dependencies for SQLAlchemy

 It seems it really depends on something related with
 easy_install (I'm not sure what):
  I run the process for SqlAlchemy from a virtual environ using python
 2.6 and it tried to install 0.5.6
  I then deactivate the virtualenv and tried from the easy_install from
 distribution (Ubuntu 9.04, python2.6) and told me the best match was
 0.5.3

 BTW, you can always do
  easy_install SqlAlchemy==0.5.6

 Mariano

 --

 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

Re: [sqlalchemy] Re: SQLAlchemy 0.5.5 MySQL Issue

[Alexandre Conrad]

You may be using MyISAM storage engine which doesn't support transactions
and may make all flushes persistent. The other storage engine widely used is
InnoDB which does support transactions. Find out in the MySQL docs how to
figure out which storage engine you're using.

Sent from my fantastic HTC Hero

On Nov 28, 2009 7:27 AM, gizli mehm...@gmail.com wrote:

I am not sure about the storage engine but here goes:

mysql status
--
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using
readline 5.2

the mysql-python version is MySQL_python-1.2.3c1

On Nov 26, 11:47 pm, Alexandre Conrad alexandre.con...@gmail.com
wrote:
 2009/11/27 gizli mehm...@gmail.com:

   With mysql however, the insert done by the query() actually does a  
commit.. i turned on ec...

-- You received this message because you are subscribed to the Google Groups
sqlalchemy group. To...

--

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] SQLAlchemy 0.5.5 MySQL Issue

[Alexandre Conrad]

2009/11/27 gizli mehm...@gmail.com:
 With mysql however, the insert done by the query() actually does a
 commit.. i turned on echo, and i dont see an explicit commit but this
 is what seems to happen.. After the program is done, I can see Obj(5)
 in the mysql database.

 Is this a known problem? Or am I doing something wrong here?

Which MySQL storage engine are you using?

Alex

--

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: session.query object instead rowtuple

[Alexandre Conrad]

2009/9/28 Christian Démolis christiandemo...@gmail.com:
 I need it to avoid  write this in my code
 for e in s:
    print e[0]

 it's more difficult to read than

From a readability point of view, you could use unpacking in your loop:

 for foo, bar in ((foo1, bar1), (foo2, bar2)):
... print %s: %s % (foo, bar)
...
foo1: bar1
foo2: bar2

Alex

--~--~-~--~~~---~--~~
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: SQL ALCHEMY instantly refresh

[Alexandre Conrad]

In Python, you have to pass self as first argument to all methods of a class:

class MyQuery(sqlalchemy.orm.query.Query):

def __init__(self, *arg, **kw):
...

Alex

2009/9/18 Christian Démolis christiandemo...@gmail.com:
 Hello,

 Thx for the answer, thx to Alexandre to translate my mail.

 Sorry, i continue in english, i tried to do that at the end of my
 declaration file :

 Base.metadata.create_all(engine)
 import sqlalchemy.orm.query
 class MyQuery(sqlalchemy.orm.query.Query):
     def __init__(*arg, **kw):
    self._populate_existing = True
    super(MyQuery, self).__init__(*arg, **kw)
 Session = scoped_session(sessionmaker(autocommit=True, bind=engine))
 session = Session()
 print Le temps de déclaration SQL ALCHEMY, time.time()-xref

 Is it correct? i m not very good in subclassing :S
 It seems to not work.
 When i add a print in the __init__, i never see it during the execution of
 my program so MyQuery is not used i think.
 Can u tell me where should i subclass Query? In the declaration.py? in
 module sqlalchemy?
 I tried to add myquery here
 Session = scoped_session(sessionmaker(autocommit=True, bind=engine,
 query_cls=MyQuery))
 but it does error
   File
 C:\Python25\lib\site-packages\sqlalchemy-0.5.6-py2.5.egg\sqlalchemy\orm\
 session.py, line 899, in query
     return self._query_cls(entities, self, **kwargs)
   File Z:\Declaration.py, line 1451, in __init__
     self._populate_existing = True
 NameError: global name 'self' is not defined

 I can t touch to the sqlalchemy module because the interpreter and libraries
 are installed on multiple computers which execute one unique code on a
 shared network path.
 So it's more easy to change my source code than sqlalchemy code...

 i just want to change session.query behavior without change the code of
 sqlalchemy itself, please help me.

 2009/9/17 Michael Bayer mike...@zzzcomputing.com

 Alexandre Conrad wrote:
 
  Christian,
 
  2009/9/17 Christian Démolis christiandemo...@gmail.com:
  Bonjour,
 
  Tu es français je pense au vu de ton prénom.
  Je continue donc en français.
 
  Nice guess.
 
  I understand it feels more comfortable writing in French rather than
  in English, but many people are reading this list (or is only Mike
  doing support? ;) ) and may be interested at the topic. And more eyes
  and brains may answer your question. So please keep conversations on
  this list to its native language - English. If you really want to
  switch to a non-English language with someone particular, please
  exchange off-list, but I believe you'll dramatically reduce your
  chances of solving your problem.
 
  So for the record, you were explaining that you have 25000 lines of
  code and you'd like to avoid to add refresh or
  session.query().populate_existing() all around the place. You have
  attempted to override the query method but couldn't make it. You have
  pointed out the PreFilteredQuery
  http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery but
  was unsuccessful achieving what you wanted.
 
  Sorry, maybe someone else can help you there. I haven't played with
  Query overriding myself.

 a Query subclass which just says

 def __init__(*arg, **kw):
    self._populate_existing = True
    super(MyQuery, self).__init__(*arg, **kw)

 should do it





 
  Alex
 
  
 





 


--~--~-~--~~~---~--~~
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: SQL ALCHEMY instantly refresh

[Alexandre Conrad]

2009/9/17 Christian Démolis christiandemo...@gmail.com:
 How can i force sqlalchemy to refresh an object when i did a
 session.query???

You may want look at this:

http://www.sqlalchemy.org/docs/05/session.html#refreshing-expiring

You may, as well, look at expunging.

Alex

--~--~-~--~~~---~--~~
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: Dynamic loader versus lazy=True

[Alexandre Conrad]

Keep in mind that the method on your Bar class:

   def all_foo(self):
   foo_query.all()

will return a raw *list* of Foo objects. If you append more Foo
objects to it, they won't be seen by SQLAlchemy's session, thus not
being commited. Although, if you have set on your mapper:

  properties={
'all_foo': relation(Foo)
})

bar.all_foo will return an InstrumentedList object, which somehow
knows it's related to Bar and any new objects appended in here will be
seen by SA's session, thus changes will be commited.

(SA gurus, correct me if I'm wrong)

This behavior might help you make a choice on your lazy strategy.

Regards,
Alex

2009/9/15 Wolodja Wentland wentl...@cl.uni-heidelberg.de:
 On Thu, Sep 10, 2009 at 23:27 +0200, Wolodja Wentland wrote:
 Hi all,

 I observed that if I define a relation (foo_query) as lazy='dynamic' and
 access all referenced entities with foo_query.all() that the query will
 be executed every time i access it. That is not a big surprise ;-)

 In a library I am writing i want to provide methods that allow
 pre-filtering of referenced entities and also on that provides access to
 all entities. I am wondering if it is better/faster/.. to define *two*
 relations for filtering and accessing all entities respectively.

 I can't really decide between the following two approaches and would be
 happy if someone could provide some tips:

 Approach 1
 --

 Class Bar(object):

     def all_foo(self):
         foo_query.all()

     def foo_startwith(self, search_string):
         foo.query.filter(tbl.c.col.like('%s%%'% ...))

 mapper(Bar,
        ...
        properties={
          'foo_query': relation(Foo, lazy='dynamic')
          })

 Approach 2
 --

 Class Bar(object):

     def foo_startwith(self, search_string):
         foo.query.filter(tbl.c.col.like('%s%%'% ...))

 mapper(Bar,
        ...
        properties={
          'all_foo': relation(Foo)
          })
        properties={
          'foo_query': relation(Foo, lazy='dynamic')
          })

 Which one is faster? Does it make a difference, given the
 optimisation/cache in the database? Will it just mean more bloat in the
 mapper definition?

 Nobody can help with the decision?

 Wolodja Wentland

 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.9 (GNU/Linux)

 iEYEARECAAYFAkqvts4ACgkQc5LrxXrbkwjC+wCfeyV3pLGq2ZGxn3ZNYmmc3cLK
 M9cAniKtnGlxymFccEiUENy7UzOrFlFk
 =ydwe
 -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 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: Dynamic loader versus lazy=True

[Alexandre Conrad]

2009/9/10 Wolodja Wentland wentl...@cl.uni-heidelberg.de:
 Class Bar(object):

    def all_foo(self):
        foo_query.all()

    def foo_startwith(self, search_string):
        foo.query.filter(tbl.c.col.like('%s%%'% ...))

Note that a .startswith() method is already implemented in SA:

http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html?highlight=startswith#sqlalchemy.schema.Column.startswith

--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column

[Alexandre Conrad]

Hello Adrian,

In my case, I actually build up my SA model, flush it, and save the
file on disk using the id that's been populated on my object after
flush.

Sorry, your original question didn't really give enough details on
what you wanted to do. What you were trying to achieve is not common
practice and is database specific (thus not standard) and probably
there would be a better normalized solution for you. That's why I
pointed you foreign keys. I thought you'd be going the wrong way,
that's all. :)

Alex

2009/5/23 Adrian von Bidder avbid...@fortytwo.ch:
 On Friday 22 May 2009 23.00:05 Werner F. Bruhin wrote:
 What do you want to do with the autoincrement column?  Often these are
 used for primary keys, which in turn get used as foreign keys.

 I want to use the id as filename; the table will cache some info that comes
 from the file.  Using it as a filename directly has several advantages:
  * I don't have to care about a unique filename, since the db already takes
 care of that.
  * I don't have to create an additional column with the filename.

 Obviously, I can just first create the file with a temp filename, but I'd
 like to avoid this additional step if it's possible.  Less code == fewer
 bugs.  And I hoped that using the id from the beginning would be easily
 possible.

 Right now, I'm undecided if I should just drop portability and fetch the
 next value from the mytable_id_seq that postgres has generated for me with
 the autoincrement columns (from what I understand, even if I declare the
 column autoincrement, if I set the id manually it will Just Work(tm) like it
 should or if I should go the temporary filename route.  I'm leaning towards
 the former because I probably will drop portability at some point in any
 case, since PostgreSQL just has too many cool features waiting to be used
 :-)

 (And no, I'm not going to use blobs and store the file as a large object in
 the db.  Relational databases are not made for this, and besides it makes
 the db harder to maintain because db dumps get huge or the blobs have to be
 backed up separately, and debugging is harder because I can't use shell
 tools to get at the file data.)

 (So you see, I'm not trying to build foreign keys by hand or any such thing.
 I actually know relational databases quite well, it's just my knowledge of
 SQLAlchemy and Python which is lacking.)

 thanks
 -- vbi

 --
 Alle Wirksamkeit ist stärker am Mittelpunkt als gegen die
 Peripherie zu. Raum zwischen Mars und Jupiter.
                -- Goethe, Maximen und Reflektionen, Nr. 1336



--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column

[Alexandre Conrad]

Don't you want that non-null column to be a foreign key ?

2009/5/21 Adrian von Bidder avbid...@fortytwo.ch:
 Hi,

 Is it possible to fetch the values of an autoincrement field without
 flushing the object to the DB?

 (In postgres, I obviously can manually fetch nextval of the automatically
 generated sequence, but I lose the portability that way ...)

 Why?

 Because I need the id to generate data that will be filled into some (non-
 null) columns of the table row.  So I can't flush since I'll get an
 IntegrityError about non-null columns, and I can't fill those columns
 without knowing the id that's going to be assigned.

 (Yes, I can use dummy values, then flush(), and then update the row before
 committing.  But that's not exactly elegant...)

 cheers
 -- vbi

 --
 Fnord.



--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column

[Alexandre Conrad]

Hello Adrian,

2009/5/22 Adrian von Bidder avbid...@fortytwo.ch:
 On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote:
 Don't you want that non-null column to be a foreign key ?

 Would that make a difference?

That's what a foreign key is used for: http://en.wikipedia.org/wiki/Foreign_key

I think you should know what a foreign key is before using a tool like
SQLAlchemy. SQLAlchemy is an abstraction layer to deal with cells,
records, columns, tables, relations of databases. An abstraction layer
hides some details of lower level concepts. Abstraction layers like
SQLA are meant to ease your everyday work. But you still have to
understand lower level concepts in order to correctly use abstraction
tools like SQLAlchemy.

What you are trying to achieve is already a feature that the database
itself can handle for you. It's called a constraint. Read a few papers
about databases in general and how constraints and relations between
tables work. There's a lot of documentation about that out there.

Have fun! :)

Alex

--~--~-~--~~~---~--~~
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: Q: fetch value of autoincrement column

[Alexandre Conrad]

Could you please send your SQLAlchemy tables you are working with to
have a better idea of what's you want to achieve ?

2009/5/22 Adrian von Bidder avbid...@fortytwo.ch:
 On Friday 22 May 2009 13.58:34 Alexandre Conrad wrote:
 Hello Adrian,

 2009/5/22 Adrian von Bidder avbid...@fortytwo.ch:
  On Friday 22 May 2009 08.43:09 Alexandre Conrad wrote:
  Don't you want that non-null column to be a foreign key ?
 
  Would that make a difference?

 That's what a foreign key is used for:
 http://en.wikipedia.org/wiki/Foreign_key

 Oh, thanks a lot.  Not really helpful, though.

 I was quite simply asking if it's possibly to fetch the value of an
 autoincrement column (in a portable way, if possible) without causing a
 flush, because at the time when I want to use the value, the row is not
 complete yet.

 Oh, well...

 cheers
 -- vbi





 --
 featured link: http://www.pool.ntp.org



--~--~-~--~~~---~--~~
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: SQLAlchemy 0.5.4 Released

[Alexandre Conrad]

Mike,

Woooh ! it all sounds sounds great !

I was thinking that it would be nice to have an up-to-date version of
your post you did a while back:

http://techspot.zzzeek.org/?p=17

I know such a task takes time but it would be fun to see the results.
That would keep SA's marketing in shape (even if SA doesn't need any
marketing, I believe :-) ). Or at least an SQLAlchemy vs SQLAlchemy
ORM fight, if you still have the scripts that generated these
graphs...

Anyway, thanks again for sharing such a quality library with the community.

Alex

2009/5/18 Michael Bayer mike...@zzzcomputing.com:

 Hello list -

 SQLAlchemy 0.5.4 is released, and this release is *highly* recommended
 for all users.    For an indication of how high, lets just say, higher
 than 0.5.3, 0.5.2, and 0.5.1combined.

 Not to worry, there are no security holes or memory leaks in previous
 versions.   But we have neutralized some major, major speed bumps in
 the flush() process, as well as made significant improvements to
 memory usage.     Due to the removal of these bugs, large dataset
 scenarios that were more or less impossible to work with for any
 version of SQLAlchemy now run at top speed with the same rate
 performance regardless of how large the Session grows.    Other
 performance issues that were proportional to the number of
 interconnected mapped classes, memory/speed issues related to the
 number of relation()s set up on mappers during a load, or just
 wasteful overhead during the flush have been mitigated.   The
 improvements are of a magnitude such that some applications that had
 abaondoned the ORM due to latency related to large sets of objects may
 be able to come back to it and regain all its advantages.

 The key to all these improvements is, that i finally have a job using
 SQLAlchemy full time where I've gotten the opportunity to use the ORM
 with somewhat large amounts of data.   None of these issues were very
 deep and just required that I spend some more time with the profiler
 and bigger datasets.    My own use case here is a 6500 row spreadsheet
 of interconnected objects, representing about 25K rows - the process
 of ingesting that data, which requires that all of the objects need to
 stay present in the session, has gone from 33 minutes to 8.     The
 key is that the number of method calls to flush X number of objects is
 now the same for a session regardless of how many other non-dirty
 items are present.   Similarly, a mapping setup that has 30 mappers
 configured will not be slowed down by unnecessary traversal of all the
 mapper relations.

 The Session itself, which has for some time now has been weak
 referencing with regards to its contents, has been repaired such that
 the weak referencing behavior is now fully operational.  Previously,
 objects which were related via mutual backrefs would not get cleared
 from the session when all external references were lost until you
 expunged them.     That is no longer necessary - the Session now has
 no strong references whatsoever to its contents, as long as no changes
 are pending on those objects.   Pending changes as always are strongly
 referenced until flushed.   So now you can iterate through as many
 tens of thousands of objects as you like (keeping in mind an
 individual Query still loads each individual result fully in unless
 yield_per is enabled) and there's no need to expunge the session in
 between chunks.

 The loading of objects has also been sped up and reduced in memory
 overhead by killing a wasteful structure of callables that was
 generated on a per-relation()/per-object basis whenever
 query.options() was used.

 In other news I've backported a convenient extension from the 0.6
 series which allows you to create custom SQL expression elements with
 compiler functions.   This is the compiler extension and is
 described in the documentation.

 Download SQLAlchemy 0.5.4 (right now !!  get rid of whatever buggy old
 version you're using) at:

 http://www.sqlalchemy.org/download.html


 0.5.4
 =

 - orm
     - Significant performance enhancements regarding Sessions/flush()
       in conjunction with large mapper graphs, large numbers of
       objects:

       - Removed all* O(N) scanning behavior from the flush() process,
         i.e. operations that were scanning the full session,
         including an extremely expensive one that was erroneously
         assuming primary key values were changing when this
         was not the case.

         * one edge case remains which may invoke a full scan,
           if an existing primary key attribute is modified
           to a new value.

       - The Session's weak referencing behavior is now *full* -
         no strong references whatsoever are made to a mapped object
         or related items/collections in its __dict__.  Backrefs and
         other cycles in objects no longer affect the Session's ability
         to lose all references to unmodified objects.  Objects with
         pending 

[sqlalchemy] Re: How to write a self 2 self relationship?

[Alexandre Conrad]

 So, does sqlalchemy support self 2 self relationship ?
 If the answer is YES, how to do it?

You may want to look at the remote_side keyword argument.

http://www.sqlalchemy.org/docs/05/mappers.html#adjacency-list-relationships

Regards,
Alex

--~--~-~--~~~---~--~~
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: How to write a self 2 self relationship?

[Alexandre Conrad]

As I said in my previous mail, you really should look at the
remote_side keyword argument in your relation. This is what it was
done for and the paragraph adjacency-list-relationships from the
documentation covers all you need to know to deal with
self-referential relationships.

Here's the link again:
http://www.sqlalchemy.org/docs/05/mappers.html#adjacency-list-relationships

Regards,
Alex



2009/2/16 一首诗 newpt...@gmail.com:

 Like this ?
 
 class User(Base):
__tablename__ = 'users'

id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
sons = relation('User', order_by='User.id', backref=parent)

 

 I got an Exception:

 sqlalchemy.exc.ArgumentError: Could not determine join condition
 between parent/child tables on relation User.sons.  Specify a
 'primaryjoin' expression.  If this is a many-to-many relation,
 'secondaryjoin' is needed as well.


 On Feb 16, 5:08 pm, a...@svilendobrev.com wrote:
 put it as text, it will be eval()'uated later

 On Monday 16 February 2009 10:57:11 一首诗 wrote:

  I tried to write :

  #--
 -- class User(Base):
  __tablename__ = 'users'

  id = Column(Integer, primary_key=True)
  name = Column(String)
  fullname = Column(String)
  password = Column(String)
  sons = relation(User, order_by=User.id, backref=parent)
  #--
 --

  But the as 'User' is not defined at the line ... relation ...  is
  processed, the code above doesn't work.

  So, does sqlalchemy support self 2 self relationship ?
  If the answer is YES, how to do it?
 


--~--~-~--~~~---~--~~
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: Joining a table to itself.

[Alexandre Conrad]

Hello Nick,

Nick Murdoch wrote:
 I'm having trouble setting up a relationship between one table and
 itself.

Check out the Adjacency List Relationships chapter from the docs. It 
explains how to deal with self-referential relations.

http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_selfreferential

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: can't setup simple one to many relation

[Alexandre Conrad]

Hello Max,

AFAIR, I've had similar problems latelty when having a table with 
composite primary keys and running SQLite. I think Mike told me SQLite 
didn't support that. I'm no SQLite user, but I had this problem when I 
had to make a portable test case with SQLite in memory. If this can put 
you on track.

Regards,
-- 
Alexandre CONRAD


Max Ischenko wrote:
 Hello,
 
 I'm struggling with a simple one to many relation, pls help!
 
 
 active_meetup = MeetupEvent(...)
 reg = MeetupRegistration(meeting=active_meetup)
 
 db.save(reg)
 
 class 'sqlalchemy.exceptions.OperationalError': (OperationalError) 
 (1048, Column 'meeting_id' cannot be null) u'INSERT INTO 
 dou_meetup_registrants (meeting_id, name, email, phone) VALUES (%s, %s, 
 %s, %s)' [None, u'1.0', u'[EMAIL PROTECTED] 
 mailto:u'[EMAIL PROTECTED]', u'+38 063 2061046']
 
 Obviously SA somehow can't find meeting_id even thou reg.meeting is OK.
 
 Here is my mapping code:
 
 dou_meetupregs_tbl = Table('dou_meetup_registrants', meta,
 autoload=True)
 dou_meetups_tbl = Table('dou_meetups', meta, autoload=True)
 mapper(MeetupEvent, dou_meetups_tbl, properties={
 'registrants':relation(MeetupRegistration, backref='meetup'),
 })
 mapper(MeetupRegistration, dou_meetupregs_tbl, properties={
 })
 
 And classes (just in case):
 
 class MeetupEvent(object):
 regcount = property(fget=lambda self: len(self.registrants))
 
 def __repr__(self):
 return MeetupEvent %s %r % (self.id http://self.id, self.url)
 
 class MeetupRegistration(object):
 def __init__(self, meeting, name=None, email=None, phone=None):
 if not meeting:
 raise ValueError(Meeting event not set)
 if not email:
 raise ValueError(Email is required)
 self.meeting = meeting
 self.name http://self.name = name
 self.email = email
 self.phone = phone
 
 def __repr__(self):
 return MeetupRegistration %s (%s,%s) % (self.id 
 http://self.id, self.meeting.id http://self.meeting.id, self.email)
 
 -- 
 Max
 http://maxischenko.in.ua // http://www.linkedin.com/in/maksim
  



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: can't setup simple one to many relation

[Alexandre Conrad]

Then I don't know. :)

Max Ischenko wrote:

 On 29 янв, 10:47, Alexandre Conrad [EMAIL PROTECTED] wrote:
 
Hello Max,

AFAIR, I've had similar problems latelty when having a table with
composite primary keys and running SQLite. I think Mike told me SQLite
didn't support that. I'm no SQLite user, but I had this problem when I
had to make a portable test case with SQLite in memory. If this can put
you on track.
 
 
 Well, I'm using MySQL 5. Table defs:
 
 DROP TABLE IF EXISTS `dou_meetup_registrants`;
 CREATE TABLE `dou_meetup_registrants` (
   `id` int(11) NOT NULL auto_increment,
   `meeting_id` varchar(20) NOT NULL default '',
   `name` varchar(80) default NULL,
   `email` varchar(80) NOT NULL default '',
   `phone` varchar(24) default NULL,
   PRIMARY KEY  (`id`),
   UNIQUE KEY `meeting_id` (`meeting_id`,`email`),
   CONSTRAINT `dou_meetup_registrants_ibfk_1` FOREIGN KEY
 (`meeting_id`) REFERENCES `dou_meetups` (`id`) ON DELETE CASCADE ON
 UPDATE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
 
 CREATE TABLE `dou_meetups` (
   `id` varchar(16) NOT NULL,
   `url` varchar(255) NOT NULL,
   `created` timestamp NOT NULL default CURRENT_TIMESTAMP,
   `is_active` tinyint(1) NOT NULL default '0',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `id` (`id`),
   UNIQUE KEY `url` (`url`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 SQLAlchemy 0.4.2

-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Can't figure out the joins across many tables with select_from()

[Alexandre Conrad]

Michael Bayer wrote:
model.Player.query.join(['site', 'playlists', 'hotlinks',
'hotslot']).filter(model.SlotHot.c.id=='foo').all()


another thing im considering, along the lines of what I mentioned in 
http://groups.google.com/group/sqlalchemy/browse_thread/thread/6b5b1cda1b657723#
 , would look like this:

query.join(['site', 'playlists', ('someprop', table.join(subtable)),
MySubclass.someprop])

that is, join to 'someprop' using this selectable as the target.
which is the same thing as if the mapper has select_table on it in
this case, but is a way to specify at the query level rather than the
mapper level.
 
 
 this feature is added in 4091 if youd like to try.

I'll test that tomorrow. I didn't have time for that today. I had a 
quick look at it and the generated query doesn't look natural to me (at 
least for what I'd do). Or maybe I don't understand how this works.

I'll try to make a simple test case of my joins.

Joining using strings is nice, it keeps the stamtment short. But these 
strings reflect the the ORM relation. We should be able to join tables 
as strings too.

Here's are my relations again:

'players' many-to-one 'site'
'site' inherited by 'site_client(site)'
'site_client(site)' one-to-many 'playlists'

As I said before, my problem here is to join from the player, via the 
base 'site' to the inherited 'site_client' table.

I'd naturally want to do:

model.Player.join(['site', 'playlists']).filter(...)

But these strings seem to be the relation names. And of course, my Site 
object doesn't have a 'site_client' as SiteClient uses inherits=Site 
directly in the mapper(). So it's like transparent and there are no 
way to get a grab of that relation. Site neither has a 'playlists' 
relation, only SiteClient has.

So I've been playing with:

Player.query
.filter(Player.c.id_site==Site.c.id)
.filter(Site.c.id==SiteClient.c.id)
.filter(SiteClient.c.id==Playlist.c.id_site)

which doesn't work neither because .filter(Site.c.id==SiteClient.c.id) 
generates site.id = site.id rather than the expected site.id = 
site_client.id. Which I understand, as I suppose it's part of the 
inheritance process, that transparency I was talking about. So I 
explicitly need to use my tables directly:

.filter(Site.c.id==site_client_table.c.id)

That join(['site', 'playlists', ('someprop', table.join(subtable)), 
MySubclass.someprop]) you've proposed feels ugly.

It'd be great to be able to join using with strings and be able to grab 
the relation in-between inherited tables, like:

Player.join(['site', 'site_client', 'playlists']).filter(...)

If 'site' doesn't have a 'site_client' relation(), check if we have a 
'site_client' table as a fallback, and if we do, try to find the 
relation with it. Then figure out that the 'site_client' table is mapped 
to SiteClient, and keep on finding the next relation 'playlists' to join 
it with.

Good night, and good luck. :)

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Can't figure out the joins across many tables with select_from()

[Alexandre Conrad]

Hello,

still working intensivly with joined table inheritance. I have troubles 
making a long join across many tables. I need to query players from 
player_table where on the other end, slots_hot.id == 'foo':

player_table = Table('players', meta,
 Column('id', Integer, primary_key=True),
 Column('description', Unicode(20)),
 Column('id_site', None, ForeignKey('sites.id'), nullable=False),
)

site_table = Table('sites', meta,
 Column('id', Integer, primary_key=True),
 Column('name', Unicode(40), nullable=False, unique=True),
 Column('type', Unicode(8)),
)

# inherits site_table
site_client_table = Table('site_clients', meta,
 Column('id', None, ForeignKey('sites.id'), primary_key=True),
 Column('id_client', None, ForeignKey('clients.id')),
)

playlist_table = Table('playlists', meta,
 Column('id', Integer, primary_key=True),
 Column('type', Unicode(8), nullable=False),
 Column('name', Unicode(30), nullable=False),
 Column('description', Unicode(40)),
)

# inherits playlist_table
playlist_site_table = Table('playlists_site', meta,
 Column('id', None, ForeignKey('playlists.id'), primary_key=True),
 Column('id_site', None, ForeignKey('site_clients.id')),
)

hotlink_table = Table('hotlinks', meta,
 Column('id', Integer, primary_key=True),
 Column('id_playlist', None, ForeignKey('playlists_site.id')),
 Column('id_hotslot', None, ForeignKey('slots_hot.id')),
)

slot_table = Table('slots', meta,
 Column('id', Integer, primary_key=True),
 Column('type', Unicode(8), nullable=False),
 Column('position', Integer, nullable=False),
 Column('match_all_options', Boolean, default=False, nullable=False),
 Column('id_slot', None, ForeignKey('slots.id')),
)

# inherits slot_table
hot_slot_table = Table('slots_hot', meta,
 Column('id', None, ForeignKey('slots.id'), primary_key=True),
 Column('name', Unicode(30)),
)

I'm trying to build the query using 
model.Player.query.select_from(player_table.join(site_table.join(site_client_table)))...
 
but then I'm getting this error when trying further 
.join(playlist_site_table):

Can't find any foreign key relationships between 'sites' and 
'_FromGrouping object'

Any idea how I could do this long join ?

Again, I tried using the clearer-to-read syntax:

model.Player.query.join(['site', 'playlists', 'hotlinks', 
'hotslot']).filter(model.SlotHot.c.id=='foo').all()

but of course, as soon as I hit inherited relations, it fails, so I have 
to build using either only filters:

.filter(Foo.c.id==Bar.c.id_foo).filter(Bar.c.id==Baz.c.id_baz).filter(...)

which makes it a 3 liner long query, or using tables directly with 
select_from(), which Im trying to do now, as show above.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Michael Bayer wrote:

 that doesnt sound right.  taking out select_table, and doing:
 
 print  
 Media 
 .query 
 .select_from 
 (media_table 
 .join 
 (catalog_table 
 ).join 
 (catalog_channel_table 
 )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()
 
 leads to the SQL:
 
 SELECT medias.id AS medias_id, medias.name AS medias_name,  
 medias.id_catalog AS medias_id_catalog
 FROM medias JOIN catalogs ON catalogs.id = medias.id_catalog JOIN  
 catalog_channels ON catalogs.id = catalog_channels.id
 WHERE catalog_channels.id_channel = ? ORDER BY medias.oid
 
 which is entirely acceptable (and works in mysql).

Okay, my fault. I was editing the wrong code in my test case concerning 
the select_from alternative.

Let's go back to that new feature from r4060 you've proposed, concerning 
joins directly from classes. Attached is my updated test case.

If I wanted to join only from classes, I'd suppose we'd have the 
following syntax:

Media.query.join([Media.catalog, CatalogChannel.id, 
CatalogChannel.channel]).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()

This would mean join Media on Catalog, join CatalogChannel on Catalog, 
join CatalogChannel on Channel; WHERE CatalogChannel.c.id_channel==foo.

This is just an idea, but here the join with CatalogChannel.id would 
mean that is need to figure out that PK is also FK to Catalog.

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



SA_joined_inherited_class.py
Description: application/python

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Mike,

Michael Bayer wrote:
 a certain id_channel.   otherwise im still having trouble wrapping my  
 head around what youre trying to do there.

Enough guessing, here's the full test case with description of what I'm 
trying to do. :)

Although, I wasn't able to make it run with sqlite, so it's running a 
under a foo table with MySQL. (another hidden bug, I'm getting 
(IntegrityError) medias.id may not be NULL).

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



SA_joined_inherited_class.py
Description: application/python

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Alexandre Conrad wrote:
 under a foo table with MySQL. (another hidden bug, I'm getting 

a foo schema/db, not table.

-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Michael Bayer wrote:

 thats not hidden.  SQLite cannot autoincrement primary key columns if  
 the table contains a composite primary key which is the case here.

Ah, yes. Plus, I don't need composite primary_key here, it's articact 
from an old many-to-many secondary table (which I usually set as 
primary_key=True on both columns).

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Michael Bayer wrote:
 your best bet with this mapping right now is:
 
 print  
 Media 
 .query 
 .select_from 
 (media_table 
 .join 
 (catalog_table 
 ).join 
 (catalog_channel_table 
 )).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()
 
 which is really how select_from() was intended to be used.

This works with SQLite, but not MySQL: (1054, Unknown column 
'catalog_channels.id_channel' in 'on clause')

You'd say this is a MySQL bug ? Darn...

Also, select_from still makes us play with tables. At first, I was 
looking at an alternative to fully use classes rather than tables for 
doing the joins. I remember I already played with that select_from 
syntax, which was working.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Mike,

My day is over, I'm leaving the office. I'll read your replies tomorrow. 
Thanks for taking some time one that.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Michael Bayer wrote:
 I dont see where the type element youre mentioning is present in  
 this query.  if Media points to Catalog, thats the end of the story -  
 what is telling it about a CatalogChannel ?

Okay, okay Mike and Svilen. Of course ! I was thinking we could figure 
out that if a media being attached to a Catalog of type channel, thus 
a CatalogChannel polymorphically speaking, would figure out that 
Media.query.join([catalog, channel]) would automaticly join to the 
channel attribute of CatalogChannel. But of course, at this point 
we're still building up the query and are not on the ORM part. So we 
need to explicitly join tables between each other. Got it.

Anyway, my inital issue according to the subject's title, is that:

   .filter(Catalog.c.id==CatalogChannel.c.id)

generates the SQL query

   catalogs.id = catalogs.id

rather than the expected

   catalogs.id = catalog_channels.id

which

   .filter(Catalog.c.id==catalog_channel_table.c.id)

generates correctly when explcitly pointing the wanted table. Is that a 
bug Mike ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Michael Bayer wrote:
 
 On Jan 13, 2008, at 1:02 PM, Alexandre Conrad wrote:
Anyway, my inital issue according to the subject's title, is that:

  .filter(Catalog.c.id==CatalogChannel.c.id)

generates the SQL query

  catalogs.id = catalogs.id

rather than the expected

  catalogs.id = catalog_channels.id

which

  .filter(Catalog.c.id==catalog_channel_table.c.id)

generates correctly when explcitly pointing the wanted table. Is  
that a
bug Mike ?
 
 
 i dont really think it is.  the c collection is deprecated anyway;

c is deprecated ? On the class or the table ? I haven't seen warning 
being printed in my console during execution.

 the id attribute on Catalog and all of its subclasses refers to the  
 base id column on the catalog table; the other foreign key  
 versions of that primary key are factored out when the mapper  
 compiles.  that the id gets shoved into c is an implementation  
 artifact, but is also why c is deprecated.  since the join youre  
 doing intends to break through the abstraction of the polymorphic  
 mapping, using the literal tables is the appropriate thing to do here,  
 barring the existence of a new feature that would make what you're  
 trying to do easier (which we should probably add).

Ahh, good to hear. It would feel more natural to me. We'd expect that 
CatalogChannel's table is catalog_channel_table, specially when we 
earlier set mapper(CatalogChannel, catalog_channel_table). Plus, using 
tables makes me have extra imports in my code, which I didn't need until 
now because I'm only using mapped classes.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

svilen wrote:
 On Friday 11 January 2008 16:12:08 Alexandre Conrad wrote:
 
Channel - Playlist - Media
Channel - CatalogChannel(Catalog) - Media

(Media has a fk to Catalog, not CatalogChannel)
The only element I have, is playlist (instance of Playlist). At
this point, I need to find out the available Media of the
Playlist's Channel's catalog so I can attach them to the Playlist.

At first, I tryied out:

Media.query.join([catalog,
channel]).filter(Channel.c.id==playlist.id_channel).all()

But then it complains that channel is not part of the Catalog
mapper. Catalog ? I want it to be looking at CatalogChannel, this
is the one having the channel relation, not Catalog.
 
 i see what u want, but formally (computer languages are formal, SA is 
 a language) u are contradicting yourself. u said above that
 media points to catalog and not to catalogchannel. How u expect it to 
 find a .channel there?

I was expecting that SA would know that from the polymorphic type 
flag. I have a catalog relation on media. When I do media.catalog, it 
doesn't just return a Catalog object, but really a CatalogChannel object 
(which is the whole point of polymorphic inheritance). And I thought it 
could figure out channel from that. But Mike said no. :) That's why he 
talked about having some extra API query methods:

Media.query.join_to_subclass(CatalogChannel).join(channel).filter(Channel.c.id==playlist.id_channel).all()

We could even join classes only directly (isn't this ORM after all?):

Media.query.join([CatalogChannel, Channel])

 your query above is missing the isinstance-filter specifying that u 
 need catalogchannels and not just any catalogs. i'm not sure how this 
 would be expressed in SA but it has to be explicit - and probably 
 somewhere on level of tables. 
 have u tried 
  Media.query.join( [catalog, id, channel])... ???

Nope, that doesn't work, it's like doing ([catalog, catalog, channel])

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

Hi,

playing with inheritance, I figured out that an inherited mapped class 
passed to filter doesn't point to the correct table.

I have 2 classes, Catalog and CatalogChannel(Catalog).

Here is the syntax followed by the generated query:

   query.filter(Catalog.c.id==CatalogChannel.c.id)
   WHERE catalogs.id = catalogs.id

The generated query should be WHERE catalogs.id = catalog_channels.id. 
I can make this happend by explicitly using the table itself rather than 
the class:

   query.filter(Catalog.c.id==catalog_channel_table.c.id)
   WHERE catalogs.id = catalog_channels.id

Should I open a ticket for that ?

Normaly, I would join([A, B]) the tables between each other. But if 
a channel relation only exists on the CatalogChannel class, 
join(channel) wouldn't work as SA looks at superclass Catalog. I 
thought it would naturally find the relationship by looking at the 
polymorphic type column from Catalog, but it doesn't. Mike suggested 
we would need to extend the API with a new method like 
join_to_subclass() or so... Even though, I still think SA should figure 
out which relation I'm looking at...

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: filter() on inherited class doesn't point to the correct table

[Alexandre Conrad]

svilen wrote:

Here is the syntax followed by the generated query:

   query.filter(Catalog.c.id==CatalogChannel.c.id)
   WHERE catalogs.id = catalogs.id
 
 why u need such a query? 
 that's exactly what (inheritance) join does, and automaticaly - 
 just query( CatalogChannel).all() would give u the above query.

I have hidden the full concept I'm working on and only focused my 
problem. Here's my full setup the query is involved with:

Channel - Playlist - Media
Channel - CatalogChannel(Catalog) - Media

(Media has a fk to Catalog, not CatalogChannel)

The only element I have, is playlist (instance of Playlist). At this 
point, I need to find out the available Media of the Playlist's 
Channel's catalog so I can attach them to the Playlist.

At first, I tryied out:

Media.query.join([catalog, 
channel]).filter(Channel.c.id==playlist.id_channel).all()

But then it complains that channel is not part of the Catalog mapper. 
Catalog ? I want it to be looking at CatalogChannel, this is the one 
having the channel relation, not Catalog.

 as of the relations, they are quite automatic BUT magic does not 
 always work, so u have to explicitly specify some things manualy.

So right now, I'm building that awfully long query to explicitly tell it 
to look at CatalogChannel:

Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==CatalogChannel.c.id).filter(CatalogChannel.c.id_channel==c.playlist.id_channel).all()
# Pheeww...

But even this doesn't work well. The part where

   .filter(Catalog.c.id==CatalogChannel.c.id)

wrongly generates:

   catalogs.id = catalogs.id

So I need to use the table itself:

   .filter(Catalog.c.id==catalog_channel_table.c.id)

correctly generates:

   catalogs.id = catalog_channels.id

This works.

Regards,
-- 
Alexandre CONRAD - TLV FRANCE
Research  Development


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: 'lazy=False' with polymorphic joined tables

[Alexandre Conrad]

Michael Bayer wrote:

 i havent looked yet but this is likely a bug in eager loading when it  
 interacts with polymorphic.  are you on trunk ?

Nope. I just installed SQLAlchemy-0.4.2dev_r3952. And it's now working.

SA 0.4.1:
[...]
FROM companies JOIN suppliers ON companies.id = suppliers.id JOIN 
addresses ON addresses.id = companies.id_address, companies JOIN 
suppliers ON companies.id = suppliers.id LEFT OUTER JOIN addresses AS 
addresses_1 ON addresses_1.id = companies.id_address
[...]

SA 0.4.2dev_r3952:
[...]
FROM companies INNER JOIN suppliers ON companies.id = suppliers.id INNER 
JOIN addresses ON addresses.id = companies.id_address LEFT OUTER JOIN 
addresses AS addresses_1 ON addresses_1.id = companies.id_address
[...]

Sorry, I forgot to check the trunk before sending to the list...

Regards,
-- 
Alexandre CONRAD - TLV FRANCE
Research  Development


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] 'lazy=False' with polymorphic joined tables

[Alexandre Conrad]

Hi,

I was wondering why lazy=False on a polymorphic joined table relation 
worked fine, until I pass an extra join() to filter with like(%foo%) 
statments from my search() method.

I found an alternative where I just enable the relation's lazyness by 
providing lazyload():

#OK
session.query(Client).all()

#(OperationalError) ambiguous column name: companies.id_address
session.query(Client).join(address).filter(or_(*OR)).all()

#OK
session.query(Client).options(lazyload(address)).join(address).filter(or_(*OR)).all()

Is this how I should do it ? Or could this be detected, thus avoided ?

Test case attached. Thank you.

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



SA_lazy_polymorphic.py
Description: application/python

[sqlalchemy] Double joined polymorphic inheritance ?

[Alexandre Conrad]

Hi,

I'd like to know if it's possible to have multiple level of inheritance.

Please find attached a test case where I'd like to have Site to inherit 
from Company and also be the base table for SiteClient and SiteSupplier.

I guess I'd need a mix of the following syntax (from the test):

#site_mapper = mapper(Site, site_table, inherits=Company, 
polymorphic_identity='site')
site_mapper = mapper(Site, site_table, polymorphic_on=site_table.c.type, 
polymorphic_identity='site')

Is that possible ?

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



SA_double_inheritance.py
Description: application/python

[sqlalchemy] Re: alias problem with polymorphic joined table inheritance

[Alexandre Conrad]

Alexandre Conrad wrote:
 Here is the code I'm working on (sqlite and mysql errors at the bottom):
 
 http://rafb.net/p/HlZw3P26.html
 
 Here are the relations for clarity:
 
 client(company) -- one-to-many - site(company)
 client(company) -- one-to-many - channel(object)
 
 query == site - client - channels

Got it, I had to add 'aliased=True' to make the query happend.

print session.query(Channel).join([client, sites], 
aliased=True).filter(Site.c.id==2).all()

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Representation of boolean logic in a database

[Alexandre Conrad]

hi,

this is more a database design question than a SA one.

I have 2 tables, clients and options. A client can subscribe to 
multiple options, and options can be subscribed by multiple clients 
(many-to-many). Of course, we have a weak table for the many-to-many 
relationship.

Then we have a 3rd table, contents. A content item is related to 
options, but through boolean logic operators: OR, AND, NOT.

My problem is beeing able to represent and store relations between 
options and contents tables in a normalized way.

Say,
content A is destinated subscribers of option A,
content B for subscribers of option B,
content A_and_B for subscribers of option A and option B,
content A_or_B for subscribers of option A or option B,
content A_and_not_B for subscribers of option A and must not be 
subscribed to B,
content A_or_not_B for subscribers of option A or the ones not 
subscribed to option B if not already subscribed to option A,
content not_A_or_not_B for subscribers that neither have option A or 
option B.

I don't need nested logic, like: option A and (option B or option C).

Neither do I need to mix AND and OR. Although, each of them needs to 
be mixed with NOT.

This is pretty much mind twising. But maybe this has already been 
standardized somehow. Any idea how this can be achieved ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: (solved) Joined Table Inheritance and One-to-One headache

[Alexandre Conrad]

Hi list,

Here is my solution (thanks to jek from IRC).

# Mappers
channel_mapper = mapper(Channel, channel_table,
 properties = {
 playlists:relation(PlaylistChannel,
 backref=channel,
 primaryjoin=and_(channel_table.c.id==
 playlist_channel_table.c.id_channel)),
 active:relation(Playlist, post_update=True,
 primaryjoin=and_(channel_table.c.id_playlist==
 playlist_table.c.id))
 },
 order_by=channel_table.c.name,
)

That is one solution I was actually doing, but it turns out that I 
didn't know about the post_update argument... and this is not 
documented anywhere in the docs !

This solution without the argument post_update=True turns out to yield a 
CircularDependencyError:

sqlalchemy.exceptions.CircularDependencyError: Circular dependency 
detected [(PlaylistChannel: 2, Channel: Test TV), (Channel: Test 
TV, PlaylistChannel: 1), (Channel: Test TV, PlaylistChannel: 2)][]

ps: Ok, this is documented, but we need to look down in the generated 
API documentation of the relation() function. I think this should be 
pushed up somewhere in the docs. Here's a suggestion: 
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_customjoin

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Joined Table Inheritance and One-to-One headache

[Alexandre Conrad]

Hi,

I've attached a test script as I'm confused with what I'm trying to do.

I have the following scenario:

- A Channel object is TV channel:

class Channel:
 pass

- A Playlist object is a piece of a channel's TV program.

class Playlist:
 pass

- A Playlist can be declined as different Playlist types, (joined table 
inheritance). Thus, a PlaylistChannel object is a Playlist of type 
'channel':

class PlaylistChannel(Playlist):
 # type = channel
 pass

- A Channel object can only have (none or more) PlaylistChannel objects, 
available via it's playlists attribute:

chan = session.query(Channel).get(1)
chan.playlists # returns a list of PlaylistChannel objects.

- A Channel has an active attribute which points to one of the 
playlist (PlaylistChannel) of the channel.

chan.active # returns one of the playlist or None (scalar).




Channel.playlists - (one-to-many) - PlaylistChannel (list)
Channel.active - (one-to-one) - PlaylistChannel (scalar)

But SQLAlchemy seems lost because multiple heritance and foreign_keys 
are defined. It complains about:


sqlalchemy.exceptions.ArgumentError: Error determining primary and/or 
secondary join for relationship 'Channel.active (PlaylistChannel)'. If 
the underlying error cannot be corrected, you should specify the 
'primaryjoin' (and 'secondaryjoin', if there is an association table 
present) keyword arguments to the relation() function (or for backrefs, 
by specifying the backref using the backref() function with keyword 
arguments) to explicitly specify the join conditions. Nested error is 
Can't determine join between 'channels' and 'Join object on playlists 
playlist_channels'; tables have more than one foreign key constraint 
relationship between them. Please specify the 'onclause' of this join 
explicitly.


And I just can't figure how which alchemy will make my scenario work 
correctly. I've tried playing with primaryjoin/secondaryjoin, but I just 
can't figure it out.

Please see the attached test case for more details. Thanks for your help.

Regards,
-- 
Alex

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---



sqlalchemy_test.py
Description: application/python

[sqlalchemy] Re: FormAlchemy 0.1 released !

[Alexandre Conrad]

Hello,

 html = fa.FieldRender(bind=client, column='email').render()
 
 Nicely done, Alexandre!  I see you are already planning to support
 input validation.  Are you thinking of supporting alternate layouts
 such as a table grid?

Yes, I'm working on validation right now. I'm reorganizing the project's 
source code as the project grows. I already have an experimental version 
of table rendering from an item of a collection of items.

 From the latest trunk, you'll be able to do something like this:

import sqlalchemy as sa
import formalchemy as fa
import mymodel
session = sa.create_session()
client = session.query(mymodel.Client).get_by(clientid='aclient')

# For generating fields in a HTML fieldset
print fa.FieldSet(bind=client).render()

# For generating a table from a single item.
print fa.TableItem(bind=client).render()

# For generating a table from a collection of items.
client_list = session.query(mymodel.Client).all()
print fa.TableCollection(bind=mymodel.Client, collection=client_list)

The collection can be an empty list and the bound model can be 
uninstantiated.

But we don't want to take over the SQLAlchemy list. So let's rather talk 
about FormAlchemy on the FormAlchemy list:

   http://groups.google.com/group/formalchemy

:)

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] [ANN] FormAlchemy 0.1 released !

[Alexandre Conrad]

Dear SQLAlchemy users,

I am pleased to announce the first release of FormAlchemy !

FormAlchemy: Auto-generated, customizable HTML input form fields from 
your SQLAlchemy mapped classes.

FormAlchemy is a library written in Python that generates HTML form 
fields from your SQLAlchemy's mapped classes.

If you are using SQLAlchemy in a web environment requiring user input 
for your database(s), chances are that you will feel writing the same 
code for your HTML forms than the code you wrote for your SQLAlchemy 
tables. If you are tired of writing, updating, validating your HTML 
forms over and over again, FormAlchemy might get most of the work done 
for you. Get the tedious job done faster and easier without feeling to 
repeat yourself.

Checkout the quick tutorial for basic FormAlchemy usage:

   http://code.google.com/p/formalchemy/wiki/QuickTutorial

Home: http://formalchemy.googlecode.com
Mailing list: http://groups.google.com/group/formalchemy

Feedback is appreciated. :)

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Join vs. 2 selects?

[Alexandre CONRAD]

Martin wrote:

 a) 2 separate select-queries? One query going like:
 SELECT * from blog_posts WHERE post_id = my_post_id
 and the other sth. like
 SELECT * from comments WHERE parent_id = my_post_id.
 
 b) one join-query? Joining the two tables over my_post_id, so I only
 need 1 sql-query?

Two queries would take more time as the bottleneck will usually be the 
network. Communicating back and forth takes a while compared to CPU cycles.

Usually, for performance, you should try to retrieve a maximum amount of 
data in one shot.

I'm no database expert, anyone correct me if I'm wrong.

Using the ORM part of SA, you would want to set your post mapper with 
a non-lazy relation: relation('comments', ..., lazy=False). When you'll 
query for a post, it will generate some SQL that will retrieve your 
post *and* it's comments all in one query. Set echo=True on your 
engine and you'll see the actual query. Anyway, all this is nicely 
documented... :)

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: new session docs !

[Alexandre CONRAD]

Okay Mike,

about scoped_session(), I think I got it right this time. I was trying 
to get inspired from SAContext, how it's beeing wrapped around with 
Pylons. Since SAContext has not yet upgraded to SA 0.4, maybe I was just 
getting inspired from some different mechanism. Or SAContext it doing it 
wrong. SAContext says you have to call the clear() method on your 
session on each new request.


*Important:* Put this line at the beginning of your base controller's 
.__call__ method (myapp/lib/base.py)::

 model.sac.session.clear()

This erases any stray session data left from the previous request in 
this thread.  Otherwise you may get random errors or corrupt data.  Or 
del model.sac.session_context.current if you prefer.


It sounds to me that the session is global and needs to be cleared 
everytime. Which I think is wrong (or SessionContext works differently). 
I think a *new* session should be created and attached to every new 
request. The session is then deleted automaticly when the request ends, 
rather than shared from a global obect (the Pylons' model) and cleared 
(which is not thread-safe as I now understand).

Or I just don't get it. :)

Anyway, all seems to be working fine for me now.

ps: again, congrats with the new docs and the new SA04, they're great!

Regards,
Alex


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: refresh() with scoped_session ?

[Alexandre CONRAD]

Michael Bayer wrote:

 I've implemented all the missing class-level methods on  
 ScopedSession in r3212, so you should be able to call refresh().

Great, thanks. I'll check this out.

 But also, if you want to say Session(), then work with it, that is  
 also the intended usage model, although the Session.xxx methods  
 should in theory be all you need.

Why should I instantiate session = Session() if everything off the class 
is directly available ?

 Also i cannot reproduce your order_by keyword argument issue; the  
 mapper() function provided by ScopedSession passes all **kwargs  
 generically through to the actual mapper() function, so you'll have  
 to show me more specifically how you are getting that result.

By writing you a test case, I saw what was wrong. Here is the one that 
crashes:

Error reminder: TypeError: mapper() got an unexpected keyword argument 
'order_by'

## test_crash.py ##
from sqlalchemy import *
from sqlalchemy.orm import *

meta = MetaData()
Session = scoped_session(sessionmaker(autoflush=False, transactional=False))
# Uncomment and comment this line for testing...
Session = Session()
mapper = Session.mapper
engine = create_engine('sqlite://', echo=True)
meta.bind = engine
#Session.configure(bind=engine)

user_table = Table('users', meta,
 Column('id', Integer, primary_key=True),
 Column('email', Unicode(40), unique=True, nullable=False),
 Column('password', Unicode(20), nullable=False),
)

meta.create_all()

class User(object):
 pass

user_mapper = mapper(User, user_table, order_by=user_table.c.email)
## test_crash.py ##


And here is the one that works:

## test_ok.py ##
from sqlalchemy import *
from sqlalchemy.orm import *

meta = MetaData()
Session = scoped_session(sessionmaker(autoflush=False, transactional=False))
mapper = Session.mapper
# Uncomment and comment this line for testing...
Session = Session()
engine = create_engine('sqlite://', echo=True)
meta.bind = engine
#Session.configure(bind=engine)

user_table = Table('users', meta,
 Column('id', Integer, primary_key=True),
 Column('email', Unicode(40), unique=True, nullable=False),
 Column('password', Unicode(20), nullable=False),
)

meta.create_all()

class User(object):
 pass

user_mapper = mapper(User, user_table, order_by=user_table.c.email)
## test_ok.py ##


Basicly, it crashes when the mapper is taken from the Session object 
*after* it got instantiated. It works fine before.

This is a note you can have to put in the scoped_session docs when 
you'll be writing it. Or this is a bug you'll fix before writing the 
docs. :)

Don't forget to answer my question: What are the benefits of 
instantiating a Session object ?. Actually, I just feel better working 
*with* a instantiated object.

Regards,
Alex


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: refresh() with scoped_session ?

[Alexandre CONRAD]

Alexandre CONRAD wrote:

 Don't forget to answer my question: What are the benefits of 
 instantiating a Session object ?. Actually, I just feel better working 
 *with* a instantiated object.

Hey, I just set my code to instantiate the session, and I get the 
following error when I delete a client (which has a bunch of cascading 
all, delete-orphan rules).

   session = Session()

NB: I've mapped the session.delete function to my model object for 
convenience: model.delete = session.delete

File 'string', line 1 in lambda
File '/var/www/html/dev/tlv/mp/mp/lib/base.py', line 71 in wrapper
   return check(func, *args, **kwargs)
File '/var/www/html/dev/tlv/mp/mp/lib/base.py', line 68 in check
   return func(*args, **kwargs)
File '/var/www/html/dev/tlv/mp/mp/controllers/clients.py', line 89 in delete
   model.delete(client)
File 
'/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.0dev_r3224-py2.4.egg/sqlalchemy/orm/session.py',
 
line 621 in delete
   self.uow.register_deleted(c)
File 
'/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.0dev_r3224-py2.4.egg/sqlalchemy/orm/unitofwork.py',
 
line 156 in register_deleted
   self._validate_obj(obj)
File 
'/usr/lib/python2.4/site-packages/SQLAlchemy-0.4.0dev_r3224-py2.4.egg/sqlalchemy/orm/unitofwork.py',
 
line 121 in _validate_obj
   raise exceptions.InvalidRequestError(Instance '%s' is not attached 
or pending within this session % repr(obj))
InvalidRequestError: Instance 'mp.model.clients.Client object at 
0x18fd9d0' is not attached or pending within this session

I changed my code back to normal non-instantiated session:

   session = Session

And it works fine.

Regards,
Alex


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: refresh() with scoped_session ?

[Alexandre CONRAD]

Michael Bayer wrote:

Hey, I just set my code to instantiate the session, and I get the
following error when I delete a client (which has a bunch of cascading
all, delete-orphan rules).

   session = Session()

NB: I've mapped the session.delete function to my model object for
convenience: model.delete = session.delete
 
 that would be a likely suspect.  code works without that ?  send a  
 test case along if not.

Just to make sure, I made it very straight forward, calling the delete() 
method off the session directly and it still crashes. When the 
session.delete(client) is called, it fires all the SELECT queries that 
depend on the client's life (I also tried cutting off all depedencies). 
Once all the SELECTs are fired, I get the error:

InvalidRequestError: Instance 'mp.model.clients.Client object at 
0x18fd9d0' is not attached or pending within this session

I don't understand why. This only happend when the session is instantiated.

So I prepared a test case in a single file, but it works in both cases. 
My problem is in my Pylons app, and I suspect that the dispersed modules 
doing imports are making some trouble (session instantiated in a module, 
then imported in another one). I'm not sure though, my code doesn't look 
that hacky... :/

Regards,
-- 
Alex


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] refresh() with scoped_session ?

[Alexandre CONRAD]

Hi,

I'm migrating my Pylons application to the latest version of Pylons 
(0.9.6rc2) and SA (0.4.0dev-r3205) using the new scoped_session instead 
of the deprecated SessionContext. From the SA docs (0.4), there's a note 
about how .flush() works:

http://www.sqlalchemy.org/docs/04/unitofwork.html#unitofwork_api_flush_whatis

And here I saw that the refresh() method, I never payed attention to it.

Here is my scenario (abreviated):

-
Session = scoped_session(sessionmaker(autoflush=False, transactional=False))
mapper = Session.mapper

address_table = Table(..)
class Address(object): pass
mapper(Address, address_table, props={...}, order_by=name)

user_table = Table(..)
class User(object): pass
mapper(User, user_table, props={...}, order_by=email)


user = User.query.get(id)
address = Address()

# This will auto-query the DB to get the address listing.
user.addresses.append(address)

Session.flush() # Uninstantiated 'Session' object

# This returns the user's addresses, but the newly appended address will
# be at the end of the list, not ordered correctly.
#return user.addresses

# So I re-build my query and re-fetch from the database that will return
# the user's addresses correctly ordered
return model.Address.query.filter_by(user=user).all()
-

I guess the last line is OK, but I thought using the refresh() method as 
it's proposed. But refresh() doesn't seem to be available from my 
uninstantiated scoped_session. Instantiating the Session gives me a 
refresh() method (and a bunch of others) but the code breaks earlier at 
the mapper() stage complaining:

TypeError: mapper() got an unexpected keyword argument 'order_by'

I might not be doing a correct usage of scoped_session though. Please 
let me know if I'm doing something wrong.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: *all* *new* *tutorials* !!!!

[Alexandre CONRAD]

This *IS* some piece of work ! Mike, you are the One ! :)

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] how to test a Boolean column ?

[Alexandre CONRAD]

Hi,

just wondering is there could be a method to a Column object to know if 
it's a Boolean column ?

I'm trying to retrieve a list of boolean columns from a table and I'm 
doing the following:

from sqlalchemy.types import Boolean

for col in table.c.keys():
 if isinstance(table.c[col].type, Boolean):
 ...

I have a similar test for PK columns. But the Column object provides a 
way to test PK columns directly:

if table.c[col].primary_key:
 ...

Same for FK and nullable columns:
if table.c[col].foreign_key:
if table.c[col].nullable:

So I was wondering if there could be some convenient methods available 
to test the type of a column ?

if table.c[col].is_bool:
if table.c[col].is_integer:
if table.c[col].is_string:
if table.c[col].is_date:
if table.c[col].is_time:
if table.c[col].is_datetime:
if table.c[col].is_binary:

I understand that dialect specific or custum types can't have their 
methods here...

This is just some request from me to cultivate my lazyness.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: UPDATE intelligence

[Alexandre CONRAD]

Hello Boris,

I might not be well waken up, but as I'm looking at your generated SA 
logs, it doesn't show that SA updates the row when the data has not 
changed. It only seems to update fields that have changed.

Or maybe your talking about the empty BEGIN / COMMIT block that is 
beeing sent even when the age has not changed ?

Mike: is this is actually sent to the database ? If it is, maybe we 
could avoid this, saving a little network trafic. Although, it has the 
benefit to show that .flush() has correctly been triggered, but is 
useless for the DB server.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: UPDATE intelligence

[Alexandre CONRAD]

Alexandre CONRAD wrote:

 I might not be well waken up, but as I'm looking at your generated SA 
 logs, it doesn't show that SA updates the row when the data has not 
 changed. It only seems to update fields that have changed.

Or maybe should I say that I don't see the data beeing updated even if 
the value is the same.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Hierachical data

[Alexandre CONRAD]

Alexandre CONRAD wrote:

 Ok, maybe I got influenced by articles about nested sets beeing better, 
 as pointed Mike. Now I got you guys advices, I'll look deeper into 
 adjacency list. I'm glad I've had such feedback on my problem. This 
 defenitly helps, even more when you just don't know from where to start.
 
 Also these optimized_al.py and adjacency_list.py examples will help 
 me building my own SA models for this part of my project.

All these hierarchical possiblities are a lot of information at the same 
time for me. Althought, I seem to be realizing that I will have another 
issue: I need to keep track of the playlist's nodes ordering. And it 
seems that the adjacency list method doesn't allow me to keep track of 
the ordering. Is this correct ?

For now, we have mostly talked about vertical, top/down navigation 
style. From the XML DOM specs, this would correspond to the parentNode 
(single node), childNodes (list of nodes) of the Node object. But 
how about horizontal navigation? Having a childNodes() method to my Node 
class would return an unorder list of nodes (or ordered by id, which has 
no logic with the playlist of course).

XML DOM refers to methods like previousSibling, nextSibling, 
firstChild, lastChild, insertBefore.

 From my XML example, I wouldn't want to have outro.avi read before 
intro.avi, this is primordial:

main
videointro.avi/video
...
videooutro.avi/video
/main

Somehow, I need to have a field that keeps an ordering number for the 
nodes. Meaning that every create/move/delete action would still need me 
to update the ordering number of other nodes, like the Nested Set model 
does. But at least, I could just update the ordering number of the nodes 
from the same level rather than the whole tree.

main
videointro.avi/video [0]
group [1]
  videofoo.avi/video [0]
  videobar.avi/video [1]
/group
group [2]
  videoweather_intro.avi/video [0]
  imagemorning.jpg/image [1]
  imageafternoon.jpg/image [2]
  videobrought_to_you_by.avi/video [3]
  media_list [4]
videobrand_A.avi/video [0]
videobrand_B.avi/video [1]
videobrand_C.avi/video [2]
  /media_list
/group
videooutro.avi/video [3]
/main

Any thoughts ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Hierachical data

[Alexandre CONRAD]

Arnar Birgisson wrote:

 If I understand correctly the OP has a need to store a set of
 hierarchical Nodes. Only that some node types (video, image) can't
 have children while others can (group, media_list).

Correct, group and media_list will have childen. I'm also going to 
integrate a playlist type of node to nest a playlist into another one. 
Actually, a playlist node won't have children, you'll need to click 
the playlist to open it in a new page.

 Alexandre, why do you find that the nested set model suits your needs
 better? The nested set model is particularly suited to the following
 type of queries:
 1. find all children of a particular node, without needing to know
 their generation
 2. test if node A is a descendant of node B
 
 These are queries that are expensive with adjacency lists but
 inexpensive with nested sets. Nested sets however may require you to
 update a lot of nodes if your tree changes. Traditionally you would do
 that with triggers or stored procedures - but those don't play well
 with ORMs which assumes that an update of a row won't invalidate
 cached instances of other rows.
 
 Do you have specific needs for those kind of queries? I don't know
 your application but I'd probably go with adjacency lists and a common
 Node superclass that provides access to child and parent nodes.
 Derive other classes from that and use joined table polymorphism as
 Simon suggested.

The nested looks more efficient. But, things are still a little confused 
in my head. I need to well put down the pros and cons of each technic 
for my needs. I was using XML and I'm now switching to a flat database 
with technics I've never used yet.

Basicly, my application is a web-based interface that displays a 
playlist. So at this point, I'll need to walk down the whole tree to 
retrieve each node's data (type, name, duration, etc.) and build up the 
HTML representation (probably with HTML lists ul and li tags).
Then, the user (playlist manager) will have multiple tools available to 
manipulate the playlist:

- create node somewhere specific in the tree (image, video, group, 
playlist, media_list)
- move node N sibling up
- move node N sibling down
- move node top
- move node bottom
- edit node's data (change media, schedule info)
- duplicate node
- delete node

All action would be recursive, so if a group node is moved up or 
deleted, children will follow (either up, or to /dev/null).

I do understand that nested sets take more ressource when 
creating/moving/deleting items as every node on the right needs to be 
updated.

When this is done, the whole tree will be exported to XML, concatening 
all nested playlists if any, and sent to the player. On the player 
side, the parser will walk down each node, read the media (if on 
schedule) and move on to the next node until the last one before walking 
the tree all over again. But we're talking XML now, which is off-topic.

 There is another way to store hierarchical data than adjacency lists
 and nested sets though. If you have an invariant id on your objects
 (numerical or textual) you can store an objects path, governed by
 
 object.path = object.parent.path + delimiter + object.id
 
 This might not be efficient for deeply nested trees maybe, but it can
 help in answering the types of queries mentioned above efficiently
 without requiring you to touch multiple rows upon inserting or
 deleting. Also, it would be easy to implement in SA and you could have
 all sorts of convenience methods on a Node class, like
 get_direct_children
 get_all_descendants
 is_parent
 is_child
 is_descendant

This reminds me of the XML XPath which I used pretty much for playlist 
manipulation. Although, you example feels less consistent because if a 
media is removed from the catalog, I would like the database to remove 
the playlist node that refers to that video. Also, how big should my 
path column be ? I guess I should make it big enough to store a long 
very long path, but it is not accurate.

Thanks for your suggestions. I'll look at joined table polymorphism 
closely and try to figure out what are the pros and cons for each 
hierarchical database technics.

ps: I've ordered today the Joe Celko's Trees and Hierarchies in SQL for 
Smarties.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Hierachical data

[Alexandre CONRAD]

Ok, maybe I got influenced by articles about nested sets beeing better, 
as pointed Mike. Now I got you guys advices, I'll look deeper into 
adjacency list. I'm glad I've had such feedback on my problem. This 
defenitly helps, even more when you just don't know from where to start.

Also these optimized_al.py and adjacency_list.py examples will help 
me building my own SA models for this part of my project.

Thanks,
Alex


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] all() or list() ?

[Alexandre CONRAD]

Hello,

I'm realizing that I've been using .list() to query objects. But all the 
examples in the docs talk about .all().

What's the difference ? Should I switch to .all() rather than .list() ? 
Will list be deprecated in 0.4 ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: all() or list() ?

[Alexandre CONRAD]

Gaetan de Menten wrote:
What's the difference ? Should I switch to .all() rather than .list() ?
Will list be deprecated in 0.4 ?
 
 Exactly. list() is the old way, all() is the 0.4 way.

Thanks Gaetan, I'll clean up my code to be compliant with 0.4.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: cannot read msg...

[Alexandre CONRAD]

yes

Glauco wrote:

 can someone read me?
 
 
 Glauco
 

-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: whats going to break in 0.4

[Alexandre CONRAD]

Hi Mike,

I've been reading all the changes, it all sounds like a good clean up of 
the current SQLAlchemy. I'm not concerned in all the modifications, but 
mostly about point #2, assignmapper. Moving everything under query() 
really helps against conflicting names that you may give to your class 
methods/attributes.

About #3, I did start playing with myobj.flush(), but it did crashe 
sometimes because I wasn't flushing the right objects in the right 
order. It was just a pain. I can even remember some situation (in a for 
loop, AFAIR) where I just couldn't figure out how to flush objects 
correctly. So I just session.flush() all of it. SA does the work for me.

I really don't like #7.

I like what you'll be doing on #13.

It's nice seeing such effort. Thanks Mike for this great toolkit.

Regards,
-- 
Alexandre CONRAD


Michael Bayer wrote:

 hi gang -
 
 seems like I am getting a grip on whats really going to be different  
 and such in 0.4.  I think it would be a good idea for me to put out  
 there some of the things i want to remove, as well as a few notable  
 backwards-incompatible changes, just to give a heads up.  Note that  
 this list is only things that youll *have* to do in order to use  
 0.4...im not including things that are deprecated but will still work  
 throughout 0.4.
 
 1. import structure
 
 The biggest thing, and im not sure if people are ready for this one,  
 is separating sqlalchemy from sqlalchemy.orm.  Its been the case  
 for a long time that you can do your imports like this:
 
 from sqlalchemy import *
 from sqlalchemy.orm import *
 
 and obviously you can import the specific classes explicitly, i.e.
 
 from sqlalchemy import Table, Column
 from sqlalchemy.orm import mapper, relation, backref
 
 in 0.4, sqlalchemy is no longer going to pull in the whole list of  
 sqlalchemy.orm into its namespace.  this means, to use mappers, you  
 *have* to import from the sqlalchemy.orm package explicitly as  
 above.  this is partially to raise awareness of the fact that there  
 is a pretty strict separation between the two packages, and to  
 encourage better organization of concerns.  so that means if you use  
 mapper(), relation(), backref(), create_session(), eagerload()/ 
 lazyload(), you have to import them from sqlalchemy.orm.  this  
 upcoming change has been mentioned on the tutorial page for several  
 months now too.
 
 what the frameworks and such can do *right now*, is to start  
 importing as appropriate from 'sqlalchemy.orm' for object-relational  
 functionality.  that way this wont present an issue with an 0.4 upgrade.
 
 like i mentioned, if theres really some severe issue with this, we  
 can perhaps come up with a hack to backfill 'sqlalchemy.orm' into  
 'sqlalchemy' for some software package that cant be updated, but i  
 really want to try to get just this one little cleanup of concerns  
 out there.
 
 2. assignmapper query methods
 
 The next  biggest thing is assignmapper.  OK, im not taking  
 assignmapper away.  But I am going to change the interface.  All  
 querying will be available off of a single attribute, query.  most  
 likely the parenthesis (i.e. class.query()) will not be needed.  so:
 
 MyClass.query.filter_by(street='123 green street').all()
 
 this is because we cant just keep putting every single method from  
 Query on the mapped class.  plus with Query being generative, it  
 makes even less sense for any of the methods to be off of the class  
 directly.   id like to just take all the other select(), select_by()  
 methods off of it, because i really want people to stop using them.   
 you can start using MyClass.query() right now, which will still work  
 with the parens in 0.4, and in 0.3.9 ill try to get  
 MyClass.query.foo to work as well so you can be totally forwards  
 compatible with 0.3.9.
 
 3. assignmapper myobject.flush()
 
 this is the other thing I really want to get rid of on assignmapper.   
 this is the most overused and anti-patternish thing out there.  it  
 doesnt predictably handle the things attached to it (which is not for  
 any strong technical reason, just that its a complicated case which  
 id rather not have to bother with), and it works against the kinds of  
 patterns the Session is intended to be used for.  you still can flush  
 an individual or group of instances, which is valid in certain cases,  
 by calling session.flush([objects])...but that ensures that you  
 really mean to do that.
 
 4. global_connect() / default_metadata
 
 as well as the ability to say Table('sometable', Column(...)...)  
 etc without using any MetaData.  This one i know is going to raise  
 some ire.  But I look at it this way:  someday, Guido is going to  
 take a look at SQLAlchemy, and when that day comes, i dont want there  
 to be a trace that this ugly thing ever existed...it screams SA cant  
 decide how its API should look.  Plus it used DynamicMetaData which  
 is a totally misunderstood object that isnt going away

[sqlalchemy] Re: Many to many: No column ... is configured on mapper (since 0.3.7)

[Alexandre CONRAD]

David Leuschner wrote:
this is a bug that was introduced in r2556, where an optimization to
the SQL used to query the remote table was not properly accounting
for a many-to-many join criterion, fixed in rev 2625.
 
 Thanks for the fast response! I've just found the new entry in the
 Changelog and am looking forward to the next release!

Ok, I just bumped into the same problem. My old code was working fine 
back at 0.3.6. I've upgraded using:

   easy_install -U SQLAlchemy==dev

and now is works again using the current trunk (r2640). Thanks Mike. 
Didn't you have a test case for that ? :)

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: simple many-to-many select

[Alexandre CONRAD]

Michael Bayer wrote:

 My preference with assign_mapper at this point is to say:
 
 Client.query.filter_by(sites=siteobj)

For the mailing list's archives correctness:

   Client.query().filter_by(sites=siteobj)

 i.e. i dont think constantly adding methods to assignmapper is going  
 to scale, as the chances of conflicts with existing user classes  
 grows.  im into hierarchies of names rather  than huge straight down  
 lists.

I understand. I also think that having it in the query() is a good 
practice. Then maybe the methods directly accessible from Client.x() 
should be set as deprecated ?

Regards,
-- 
Alexandre CONRAD


 On Apr 4, 2007, at 5:40 AM, Alexandre CONRAD wrote:
 
 
Glauco wrote:


Alexandre CONRAD ha scritto:


Okay, thanks. Any idea if .filter_by() and other new generative  
method
will be available in an assign_mapper object?

Yes..it's available, the final object has identical property as  a  
mapper

Well, model.Client.filter_by(sites=siteobj) doesn't work for me...

   AttributeError: type object 'Client' has no attribute 'filter_by'

Or am I misunderstanding how to use it ?

Regards,
-- 
Alexandre CONRAD



 
 
  
 
 
 ---
 Texte inséré par Platinum 2007:
 
  S'il s'agit d'un mail indésirable (SPAM), cliquez sur le lien suivant pour 
 le reclasser : http://127.0.0.1:6083/Panda?ID=pav_33314SPAM=true
 ---
 
 




--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: simple many-to-many select

[Alexandre CONRAD]

Okay, thanks. Any idea if .filter_by() and other new generative method 
will be available in an assign_mapper object?

http://www.sqlalchemy.org/docs/plugins.html#plugins_assignmapper

I'm figuring out that join_via and join_to will no longer be used as 
it's not documented anymore and will be replaced .join() / .outerjoin().

Regards,
-- 
Alexandre CONRAD


Michael Bayer wrote:
 
 
 On Apr 3, 12:39 pm, Alexandre CONRAD [EMAIL PROTECTED] wrote:
 
And I'd like to find all attachments from one client. But as there's a
weak (secondary) table in between, I can no longer have something like:

   model.Attachment.id_client==c.client.id

How can I achieve this ?
 
 
 using a criterion like
 and_(Attachment.c.attachment_id==attachment_has_clients.c.id_attachment,
 attachment_has_clients.c.id_client=Client.c.cllient_id)
 
 or you could use the built in stuff:
 
 session.query(Attachment).filter_by(clients=someclient).list()
 
 
ps: I'm using assign_mapper.

Regards,
--
Alexandre CONRAD
 
 
 
  
 
 
 ---
 Texte inséré par Platinum 2007:
 
  S'il s'agit d'un mail indésirable (SPAM), cliquez sur le lien suivant pour 
 le reclasser : http://127.0.0.1:6083/Panda?ID=pav_33248SPAM=true
 ---
 
 



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: simple many-to-many select

[Alexandre CONRAD]

Glauco wrote:

 Alexandre CONRAD ha scritto:
 
Okay, thanks. Any idea if .filter_by() and other new generative method 
will be available in an assign_mapper object?
 
 Yes..it's available, the final object has identical property as  a mapper

Well, model.Client.filter_by(sites=siteobj) doesn't work for me...

   AttributeError: type object 'Client' has no attribute 'filter_by'

Or am I misunderstanding how to use it ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: order_by computed on wrong table with many-to-many

[Alexandre CONRAD]

Michael Bayer wrote:
 use 'sites':relation(Site, backref=backref('attachments',
 order_by=attachment_table.c.name)) for now.

Ok, should I open a ticket for that ?


 On Mar 30, 5:50 am, Alexandre CONRAD [EMAIL PROTECTED] wrote:
 
Hello,

I have a problem with order_by on a many-to-many relationship (using
assign_mapper):

--
attachment_table = Table('attachments', meta,
 Column('id', Integer, primary_key=True),
 Column('file', Binary, nullable=False),
 Column('name', Unicode(40), nullable=False),
 Column('type', Unicode(30)),
 Column('date', DateTime, default=datetime.now),
 Column('size', Integer, nullable=False),
 Column('description', Unicode(40)),
)

attachments_has_sites = Table('attachments_has_sites', meta,
 Column('id_attachment', None, ForeignKey('attachments.id'),
primary_key=True),
 Column('id_site', None, ForeignKey('sites.id'), primary_key=True),
)

class Attachment(object):
 pass

attachment_mapper = assign_mapper(ctx, Attachment, attachment_table,
 properties={
 'file':deferred(attachment_table.c.file),
 'sites':relation(Site, backref=attachments,
secondary=attachments_has_sites, cascade=save-update),
 },
 order_by=attachment_table.c.name,
)
--

So I have a Site object where I can ask for it's attachments:

   s = model.Site.get(1)
   attachment_list = s.attachments

But it fires the following QUERY:

SELECT attachments.name AS attachments_name, attachments.description AS
attachments_description, attachments.date AS attachments_date,
attachments.type AS attachments_type, attachments.id AS attachments_id,
attachments.size AS attachments_size
FROM attachments, attachments_has_sites
WHERE %s = attachments_has_sites.id_site AND attachments.id =
attachments_has_sites.id_attachment ORDER BY
attachments_has_sites.id_attachment

the ORDER BY is computed against the weak table (secondary)
attachments_has_sites.

Regards,
--
Alexandre CONRAD
 
 
 
  
 
 
 ---
 Texte inséré par Platinum 2007:
 
  S'il s'agit d'un mail indésirable (SPAM), cliquez sur le lien suivant pour 
 le reclasser : http://127.0.0.1:6083/Panda?ID=pav_32965SPAM=true
 ---
 
 

-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] order_by computed on wrong table with many-to-many

[Alexandre CONRAD]

Hello,

I have a problem with order_by on a many-to-many relationship (using 
assign_mapper):


--
attachment_table = Table('attachments', meta,
 Column('id', Integer, primary_key=True),
 Column('file', Binary, nullable=False),
 Column('name', Unicode(40), nullable=False),
 Column('type', Unicode(30)),
 Column('date', DateTime, default=datetime.now),
 Column('size', Integer, nullable=False),
 Column('description', Unicode(40)),
)

attachments_has_sites = Table('attachments_has_sites', meta,
 Column('id_attachment', None, ForeignKey('attachments.id'), 
primary_key=True),
 Column('id_site', None, ForeignKey('sites.id'), primary_key=True),
)

class Attachment(object):
 pass

attachment_mapper = assign_mapper(ctx, Attachment, attachment_table,
 properties={
 'file':deferred(attachment_table.c.file),
 'sites':relation(Site, backref=attachments, 
secondary=attachments_has_sites, cascade=save-update),
 },
 order_by=attachment_table.c.name,
)
--



So I have a Site object where I can ask for it's attachments:

   s = model.Site.get(1)
   attachment_list = s.attachments

But it fires the following QUERY:

SELECT attachments.name AS attachments_name, attachments.description AS 
attachments_description, attachments.date AS attachments_date, 
attachments.type AS attachments_type, attachments.id AS attachments_id, 
attachments.size AS attachments_size
FROM attachments, attachments_has_sites
WHERE %s = attachments_has_sites.id_site AND attachments.id = 
attachments_has_sites.id_attachment ORDER BY 
attachments_has_sites.id_attachment

the ORDER BY is computed against the weak table (secondary) 
attachments_has_sites.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: cascading question

[Alexandre CONRAD]

Humm, this doesn't help as if a site is deleted, it deletes the options 
that where related to that option.

I want to be able to:
- delete an option without deleting a site
- delete a site without deleting an option

just delete (clean up) the related rows inside the weak 
options_has_sites table.

I just can't figure it out...


Michael Bayer wrote:

 
 youd need to add some delete cascades to your relationship, maybe  
 on just the backref (using the backref() function), e.g.
 
 option_mapper = assign_mapper(ctx, Option, option_table,
   properties={
   'sites':relation(Site, backref=backref(options,  
 cascade=save-update, delete),
 secondary=options_has_sites, cascade=save-update),
   },
   order_by=option_table.c.name,
 )
 
 
 
 On Mar 16, 2007, at 10:38 AM, Alexandre CONRAD wrote:
 
 
Hello,

I have a many-to-many relation between an option table and a site  
table.

Deleting an option correctly deletes the related rows in
options_has_sites table.

But when I delete a site, I have some orphan rows in the
options_has_sites table. How can I avoid this ?


# SITE TABLE ---
site_table = Table('sites', meta,
 Column('id', Integer, primary_key=True),
 Column('name', Unicode(20), nullable=False, unique=True),
)

class Site(object):
 pass

site_mapper = assign_mapper(ctx, Site, site_table,
 order_by=site_table.c.name,
)


# OPTION TABLE 
option_table = Table('options', meta,
 Column('id', Integer, primary_key=True),
 Column('name', Unicode(20), unique=True, nullable=False),
)

options_has_sites = Table('sites_has_options', meta,
 Column('id_site', None, ForeignKey('sites.id'),  
primary_key=True),
 Column('id_option', None, ForeignKey('options.id'),  
primary_key=True),
)

class Option(object):
 pass

option_mapper = assign_mapper(ctx, Option, option_table,
 properties={
 'sites':relation(Site, backref=options,
secondary=options_has_sites, cascade=save-update),
 },
 order_by=option_table.c.name,
)


Should I play with backref() ?

Regards,
-- 
Alexandre CONRAD



 
 
  
 
 
 ---
 Texte inséré par Platinum 2007:
 
  S'il s'agit d'un mail indésirable (SPAM), cliquez sur le lien suivant pour 
 le reclasser : http://127.0.0.1:6083/Panda?ID=pav_31913SPAM=true
 ---
 
 

-- 
Alexandre CONRAD - TLV FRANCE
Research  Development


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Deleting an object with assignmapper fails

[Alexandre CONRAD]

Hello,

I'm using SQLAlchemy 0.3.4. I'm having the following problem when trying 
to delete an object:

  client = model.Client.get(1)
  client
mp.models.clients.Client object at 0x1088210
  client.delete()
Traceback (most recent call last):
   File console, line 1, in ?
   File build/bdist.linux-x86_64/egg/sqlalchemy/ext/assignmapper.py, 
line 16, in do
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/session.py, line 
323, in delete
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/mapper.py, line 
1126, in cascade_iterator
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/properties.py, 
line 154, in cascade_iterator
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, 
line 686, in get_history
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, 
line 85, in get_history
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, 
line 190, in get
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, 
line 526, in commit_attribute
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, 
line 28, in lambda
   File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, 
line 383, in __iter__
TypeError: iteration over non-sequence

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: table_foo.c.column_foo.autoincrement returning True always

[Alexandre CONRAD]

Sanjay wrote:

In order to know whether a column is an autoincrement column, I am
checking the above flag. But it is returning True always, irrespective
of the column. I am using SQLAlchemy 0.3.3, PostgreSQL 8.1.4 and
psycopg2 on fedora 5.


Here is a similar post that was replied:

http://groups.google.fr/group/sqlalchemy/browse_thread/thread/4ba17b080d97aaf5

Regards,
--
Alexandre CONRAD - TLV FRANCE
Research  Development


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] count()

[Alexandre CONRAD]

Hello,

I would like to query a big table and display the information. But at 
the top of the list I'm going to display, I need a little counter that 
tell how many items where returned.

What would be the most efficient way to count the results ?

client_list = session.query(Client).select()

Should I:

1/ Use the python len() function:

 len(client_list)


2/ Use the count() query method. But doesn't it re-query the database ?

Thanks for your advices.

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
 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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Design question: option mechanism

[Alexandre CONRAD]

Hello,

this might be a little off topic and is more related to database design.

I have 2 tables, clients and sites with a one-to-many relation (1 
client has many sites).

I'd like to design an option mechanism where I could create options 
(bool or multivalues strings, ie. checkbox or dropdown list) to be 
assigned for a client, and where every new site hinerits from it's 
client's option values as default. Then the site can save it's own 
custum values if default values are not satisfying.

Any idea how I could design this ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
 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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Performing a search

[Alexandre CONRAD]

Hello,

what would be the best way to perform a search against columns of a 
table ? I have the following code that works fine:

pattern = %bla%

client_list = self.query.select(or_(model.Client.c.name.like(pattern), 
model.Client.c.contact.like(pattern), model.Client.c.email.like(pattern)))

This generates the following SQL:

SELECT clients.name AS clients_name, clients.contact AS clients_contact, 
clients.email AS clients_email, clients.id_client AS clients_id_client
FROM clients
WHERE clients.name LIKE %s OR clients.contact LIKE %s OR clients.email 
LIKE %s ORDER BY clients.name
['%bla%', '%bla%', '%bla%']

Is this the correct way to do it ? Isn't there a way I could give a list 
of columns to search in rather than doing it like my example ? Or have a 
short way to search for the given pattern in all columns ?

Regards,
-- 
Alexandre CONRAD


--~--~-~--~~~---~--~~
 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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] CheckConstraint

[Alexandre CONRAD]

Hello,

I was reading about the CheckConstraint documentation, and it looks 
promising. The examples in the docs are doing contraint with integers. 
But the cool thing would be able to define you're own contrainsts using 
ConstraintTypes, or already have pre-defined constraints, ie. 
EmailConstraint which would just be a some kind of regex matching on a 
given string.

A la django.

We could also have custum restrictions based on pre-defined contraints, 
ie. EmailConstraint(contains=[@mycomp.com, @mycomp.fr])

This would check if the given email is really at a valid email format 
and check it also contains either the given strings in the constraint.

If validation fails, we could catch the Column's name or a list of all 
failed Columns at the Table level.

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---

[sqlalchemy] ForeignKeyConstraint or ForeignKey ?

[Alexandre CONRAD]

Hello,

What's the difference between using object ForeignKeyConstraint or 
using object Column with a ForeignKey object as argument ?

 From this question follows this other one: what's the difference 
between using onupdate=CASCADE, ondelete=CASCADE from 
ForeignKeyConstraint and argument cascade=all, delete-orphan from 
the relation() function ?

I don't know which should be used between those 2 explanations in the docs:

http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_relations_lifecycle

and

http://www.sqlalchemy.org/docs/metadata.myt#metadata_tables_onupdate

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---

[sqlalchemy] automatic datatype for a ForeignKey

[Alexandre CONRAD]

Hello,

I was wondering why not having an automatic datatype assigned to a FK 
column instead of repeating twice the data type that has to be set...

a user table would have:

   Column('user_id', Integer, primary_key=True),

and an address table refereing to a user would have:

   Column('user_id', ForeignKey(users.user_id)),

Here, I don't specify that the FK column is an Integer, because some 
clever mechanics would do that for me.

I suppose that this was already thougt before, but I was just wondering...

Regards,
-- 
Alexandre CONRAD

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~--~~~~--~~--~--~---