[sqlalchemy] Re: SqlSoup joins broken in 0.5.3
You can't just throw filter expressions into the join call in modern sqla. Try db.devmap_device.join(db.devmap_manufacturer).filter(db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() -Jonathan On Tue, Mar 31, 2009 at 8:23 AM, Stu.Axon stu.a...@gmail.com wrote: Heres some input that used to work, and the error that now happens from sqlalchemy.ext.sqlsoup import SqlSoup, MetaData db_uri = 'postgres://user:passw...@127.0.0.1/games' db = SqlSoup(db_uri) db.devmap_device.join(db.devmap_manufacturer, db.devmap_device.manufacturer_id == db.devmap_manufacturer.id).first() Traceback (most recent call last): File console, line 1, in module File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\util.py, line 212, in go return fn(*args, **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 870, in join return self.__join(props, outerjoin=False, create_aliases=aliased, from_joinpoint=from_joinpoint) File string, line 1, in lambda File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 52, in generate fn(self, *args[1:], **kw) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\query.py, line 1069, in __join clause = orm_join(clause, right_entity, onclause, isouter=outerjoin, join_to_left=join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 439, in join return _ORMJoin(left, right, onclause, isouter, join_to_left) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\orm\util.py, line 416, in __init__ expression.Join.__init__(self, left, right, onclause, isouter) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2482, in __init__ self.onclause = self._match_primaries(self.left, self.right) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2528, in _match_primaries return sql_util.join_condition(primary, secondary) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\util.py, line 114, in join_condition col = fk.get_referent(b) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\schema.py, line 860, in get_referent return table.corresponding_column(self.column) File c:\python25\lib\site-packages\sqlalchemy-0.5.3-py2.5.egg \sqlalchemy\sql\expression.py, line 2683, in __getattr__ return getattr(self.element, attr) AttributeError: '_BinaryExpression' object has no attribute 'corresponding_column' --~--~-~--~~~---~--~~ 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] time to remove create_session?
I replaced create_session in sqlsoup with scoped_session, which appears to have been the only reference to it. -Jonathan --~--~-~--~~~---~--~~ 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: has() and multiple tables
On 4/7/08, Jonathan Ellis [EMAIL PROTECTED] wrote: I'm confused -- this sample does correlate actions with tasks, but not tasks with connections. Intuitively it seems that X.has(Y) should always add a clause to the the exists for y.y_id = x.y_id. No? Mike cleared this up for me in IRC: X.prop.has(criterion) just means, exists(select 1 from child where child.id=parent.x_id AND criterion) so the more elegant way to do this is session.query(Action).filter(Action.task.has(Task.connection.has(Connection.caller==caller1))) -Jonathan --~--~-~--~~~---~--~~ 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: has() and multiple tables
On 4/8/08, Tim Lesher [EMAIL PROTECTED] wrote: Yep--that's what I'm doing now. It has the effect of creating another nested subselect, but I'm not too concerned about that as both are relatively cheap EXISTS queries. I wouldn't be surprised if PG's optimizer can inline it anyway. --~--~-~--~~~---~--~~ 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: has() and multiple tables
On 4/7/08, Michael Bayer [EMAIL PROTECTED] wrote:
On Apr 7, 2008, at 4:54 PM, Tim Lesher wrote:
session
.query(Action).filter(Action.task.has(Connection.caller==caller1))
for this kind of thing, you're joining across three tables, so you can
put an extra join condition in the has():
filter(Action.task.has(and_(Connection.caller==caller1,
Task.connection_id==Connection.id)))
or do it with join():
query(Action).join('task').filter(Task.has(Connection.caller==caller1))
I have an intuitive sense that there should be some nice syntax to get
has() and any() to join across more tables but its not occuring to me
at the moment.
I'm confused -- this sample does correlate actions with tasks, but not
tasks with connections. Intuitively it seems that X.has(Y) should
always add a clause to the the exists for y.y_id = x.y_id. No?
-Jonathan
--~--~-~--~~~---~--~~
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] SQLAlchemy advanced tutorial at PyCon
Hi all, Michael and I will be presenting beginning and advanced SQLAlchemy tutorials at PyCon in March. The beginning session will cover similar ground to my OSCON tutorial last year: http://spyced.blogspot.com/2007/07/final-version-of-oscon-sqlalchemy.html What would you like to see covered in an advanced SQLAlchemy session? Here is what I threw together for the tutorial proposal, as a starting point. SQL layer - - building a select - updates - deletes Advanced queries - Selects, subselects - Labels and aliases - Self-referential - add_entity, resultset queries Advanced mapping - adjacency list relationship - ON DELETE CASCADE - composite types Inheritance --- - Inheritance patterns - Joined table inheritance - Optimization MapperExtensions - __init__ and create_instance - writing a second-level cache as an Extension -Jonathan --~--~-~--~~~---~--~~ 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: Implementing ranking
On 12/19/07, voltron [EMAIL PROTECTED] wrote: Could someone tell me how to simulate the rank() function? I am using PostgreSQL which does not have this a s a native function. SA doesn't give any special support for this. You'll have to rewrite your query. For example, http://archives.postgresql.org/pgsql-sql/2005-03/msg00294.php -Jonathan --~--~-~--~~~---~--~~ 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: SQLAlchemy 0.4 beta2 released
On 8/14/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: im not sure try/except is faster here - im pretty sure a missing key is likely and exception throws are very expensive. would be worth a try to see if the missing key exception actually occurs here. i'll have to check, last 3 years i've being doing everything assuming that a (lookup:exception) is faster than (lookup-if-lookup:lookup-else) in both attribute and dict lookups. Only if, like Mike said, the common case is for the key to be present. --~--~-~--~~~---~--~~ 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: Using a non-string in sqlalchemy
On 8/6/07, malkarouri [EMAIL PROTECTED] wrote: rec = Record() setattr(rec, col, val) This works if col is a string (the column subclassing SQLAlchemy's String) but not otherwise. I can identify the type of the column as I think most people just use a higher-level forms api. -Jonathan --~--~-~--~~~---~--~~ 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: right outer join (newbie)
There are good reasons to support full outer join. Some are suggested in the comments to that one article. :) I don't miss right joins though, I find left join more natural. On 8/1/07, Michael Bayer [EMAIL PROTECTED] wrote: we currently dont have a right outer join. but this is not by accident...theres really no use case for a right outer join, which are more easily written as left outer joins. some good articles on whats wrong with RIGHT outer join as well as FULL outer join: http://weblogs.sqlteam.com/jeffs/archive/2006/03/14/9289.aspx http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer- Joins.aspx?x=1 On Aug 1, 2007, at 5:56 PM, mc wrote: Hi, I read in the docs about joins and saw an example for left outer join, though I didn't understand where the left was specified. How is a right outer join specified? --~--~-~--~~~---~--~~ 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: SQLAlchemy 0.4 MERGED TO TRUNK
I can probably do this tonight. How many people are still using 2.3? Decorator syntax would be nice for this. Also it would be nice to not have to remember that I can't use genexps in SA code. But 2.3 is a pretty good base, I don't miss much else from 2.4. :) On 7/30/07, Michael Bayer [EMAIL PROTECTED] wrote: ive considered this so if someone is willing to submit a patch that would be great. On Jul 30, 2007, at 1:46 AM, Michael Pearson wrote: Hi, Are there plans to print warnings when deprecated methods are used? I've just spent the morning future-proofing our code against SA 0.4 and would have found this useful. I may attempt a patch myself, if people agree that it'd be a good idea. Regards, -- Michael Pearson --~--~-~--~~~---~--~~ 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: SQLAlchemy 0.4 MERGED TO TRUNK
I'd want to make sure this didn't hurt performance first. (Seems
innocuous enough, but if it's in any sort of inner loop the extra hash
lookup might be noticeable.)
On 7/27/07, svilen [EMAIL PROTECTED] wrote:
one suggesstion / request.
As your changing everything anyway, can u replace all important {} and
dict() with some util.Dict, and set() with util.Set?
util.Ones can point to dict/set.
The reason is so they can be further globally replaced by user with
OrderedOnes, for example to achieve repeatability - tests etc.
example ones are:
MetaData.tables
unitofwork.UOWTransaction.dependencies
unitofwork.UOWTask.objects
mapperlib.Mapper.save_obj(): table_to_mapper = {}
mapperlib.Mapper._compile_properties(): self.__props = {}
sqlalchemy.topological.QueueDependencySorter.sort(): nodes = {}
these give 90% repeatabliity, but i could not make it 100%. )-:
On Friday 27 July 2007 07:36:50 Michael Bayer wrote:
Hey ho -
after around 400 revisions the 0.4 branch is merged to trunk:
http://svn.sqlalchemy.org/sqlalchemy/trunk
--~--~-~--~~~---~--~~
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: no RowProxy.__getslice__
Well, the docs you link say slicing should be done via __getitem__ now. Which is also present in dicts of course. Why not approach the problem from the other direction? try: maybedict.keys() except AttributeError: ismapping = True else: ismapping = False ? On 7/25/07, Catherine [EMAIL PROTECTED] wrote: Whoops! Never mind; deprecation is a really good reason. http://docs.python.org/ref/sequence-methods.html __getslice__( self, i, j) Deprecated since release 2.0. The trouble is (and, sorry, this is getting beyond SQLAlchemy- specific), that leaves me without any good ideas for how to distinguish sequence types (lists, tuples, and user-defined objects resembling them) from mappings (dicts). Also, I don't need to at present, but it still leaves it impossible to take a slice from a RowProxy. It seems possibly useful. resultrow[1:2] TypeError: unhashable type So, for the moment, I'm looking into whether I could write a patch for that, then try-and-catch to do my duck typing. If you know a reason RowProxy's -shouldn't- support slicing, say so! - Catherine http://catherinedevlin.blogspot.com --~--~-~--~~~---~--~~ 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: Model to Dictionary
why not just pass model_instance.__dict__ ? On 7/23/07, HiTekElvis [EMAIL PROTECTED] wrote: Anybody know a way to change a model into a dictionary? For those to whom it means anything, I'm hoping to pass that dictionary into a formencode.Schema.from_python method. Any ideas? -Josh --~--~-~--~~~---~--~~ 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: Insert through ORM performance (was: Performance question)
On 7/19/07, Mike Orr [EMAIL PROTECTED] wrote: I'm amazed at the speed of mysqldump and its reloading. It packs a bunch of rows into one INSERT statement. I don't see why that's so much faster than than executemany but it provides another potential avenue for speed. I'm not sure if MySQL is the only engine that does this. Here's an exhaustive set of timings on the options you have with postgresql: http://www.depesz.com/index.php/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/ summary: COPY is fastest; that is what pg_dump uses. multi-row inserts in transactions got within a factor of 3. naive single-row inserts were 30x slower than COPY. --~--~-~--~~~---~--~~ 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: multiple mappers on une table problem.
On 6/11/07, Michael Bayer [EMAIL PROTECTED] wrote: secondly: a non-primary mapper is just an awkward way of defining an ORM query. Since we support generative queries now, you can just make a Query object with the criterion youre looking for and just hold onto it...youre just adding a single WHERE criterion, so my_secondary_query = query(MyClass).filter(table.c.tipo=='p'). much easier. ideally Query should be replacing 90% of all non_primary mapper use cases. So in your mind, is my non-primary example over at http://spyced.blogspot.com/2007/01/why-sqlalchemy-impresses-me.html part of the other 10%? I don't think holding onto a query is as elegant as being able to say user.max_order in a eager or lazy context and having that Just Work, but maybe I am missing something. --~--~-~--~~~---~--~~ 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: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
Cleaning out my inbox... FWIW I'm +1 on removing the old-style methods, +1 on .first instead of .scalar, +1 on adding .one, and +0 on renaming .list to .all. Did you make a decision for 0.4 Mike? -J --~--~-~--~~~---~--~~ 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] SQLAlchemy at OSCON
As I understand it, the schedule is still tentative at this point, but the fact that I'll get to teach a SQLAlchemy tutorial is not. (Like my PyCon tutorial, this will be aimed at those who have not yet used SA.) O'Reilly Open Source Convention 2007 at the Oregon Convention Center in Portland, Oregon, July 23-27: - Tutorial Title: SQLAlchemy: Taming ORM with Python Date: 07/24/2007 Time: 1:30pm to 5:00pm --~--~-~--~~~---~--~~ 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: Connection handling, re-establishing connections?
On 3/25/07, Michael Bayer [EMAIL PROTECTED] wrote: Still, all of these measures require that we actually get an error thrown to detect that a restart took place, which inconveniently usually happens not at the point of cursor() but at the point of execute(), and we dont have any frameworks in place (nor am i terribly comfortable with) to handle error was thrown, reconnect and try execute() again. lots of things can go wrong with that, namely transactional state getting lost, mis-interpreted errors resulting in double-executions, race conditions, etc. so i dont know if theres a way to recover from DB restarts 100% seamlessly. I agree, trying to make it seamless is a waste of time. I'd just like to see it be able to re-connect and work again on new queries, eventually. I'll have a look at the ticket but the Fairies intimidate me. :) -Jonathan --~--~-~--~~~---~--~~ 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: What should the future of SQLAlchemy Query look like ?
On 3/8/07, Michael Bayer [EMAIL PROTECTED] wrote: hey list - I continue to be troubled by the slightly fragmented nature of SA's Query object (and the cousin SelectResults). When I work with Hibernate, I can see that their querying interface is a little more consistent than ours. We have flags that are used for some things, generative methods for others. so id like to look into defining the next generation of query. Id like it to have a quasi-generative approach, like Hibernates. this means you can say: q = q.where(something).order_by(somethingelse) but also, its the same as: q.where(something) q.order_by(somethingelse) so its really the same instance (this is not how SelectResults works at the moment). I like the select results way better. That way you can make a base query and then modify it easily; if you're modifying in place, and you add another where clause, you can't easily re-use the pre-modified version again. --~--~-~--~~~---~--~~ 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: What should the future of SQLAlchemy Query look like ?
On 3/8/07, Michael Bayer [EMAIL PROTECTED] wrote: how often do you have a base query hanging around, vs. doing brand new query object each time? with the full generative way, if you are programatically building up your query, now you have to say: if somecriterion: q = q.where(something) if someother criterion: q = q.where(somethingelse) q = q.order_by(something else) q = q.options(foo) ie you have to keep remembering to say q = q.etc, otherwise you are just throwing away your newly generated query objects. this seems inconvenient to me. id like to see what cases make you want to have a base query that is reused, and consider how that might not be needed either. for instance, i have an application in charge of building zips for users to download from a queue of requests. the requests are prioritized, but to prevent starvation of low priority requests, i have another thread that just processes them in order of request time. so i have the base query that's just a module-level variable, and the main threadpool adds on the priority clause, while the other adds on the request-time clause. --~--~-~--~~~---~--~~ 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: sa newbie
try
db.books.select(db.books.c.book_skus.like('abcd%'))
On 3/5/07, dan [EMAIL PROTECTED] wrote:
I'm trying to track down the syntax for using a 'like' clause with sql
soup. I'm trying to do something like
select book_sku from books where book_sku like 'abcd%';
best i can tell, my syntax should look something like:
skus = db.books.select(book_skus.like('abcd%'))
but I'm getting an error, so obviously not. Pointers?
tia,
dan
--~--~-~--~~~---~--~~
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: How to do aggregates with mapped entity objects?
You don't, it doesn't work that way. You can map the aggregate as a (SA) property of another class, or make it a (Python) property/function of the Expense class. Either way you'll have to create a select object, either for the mapping or a manual query. On 2/27/07, Pradeep [EMAIL PROTECTED] wrote: I have a expenses table with a amount field. I have mapped that table to a Expense entity object. How do I sum up all the amounts using my entity object.? --~--~-~--~~~---~--~~ 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: Auto-joins via FK
On 2/27/07, Rick Morrison [EMAIL PROTECTED] wrote: Shouldn't SA be able to figure out the following joins without needing the explicit join params? no, SA never tries to guess how you want to join things because then you'd have to add a way to override it when it guesses wrong and it's simpler to just make things explicit in the first place. the join function will automatically do FK-based where conditions, that's probably what you want. --~--~-~--~~~---~--~~ 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: how to get column names in result
You can see what columns are part of a table (or a select!) with .columns.keys() or .c.keys(). On 2/24/07, vkuznet [EMAIL PROTECTED] wrote: Hi, a very simple question which I cannot find in documentation. How to get column names together with result. This is useful for web presentation of results using templates. I used use_labels=True, indeed it construct query with names, but my final result contains only column values. What I wanted is to get as a first row the column names. Thanks, Valentin. --~--~-~--~~~---~--~~ 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: Complicated Mapper with Count Function
you need to use an outer join (or a subselect) to get a row to come
back even when there is no match in the right table.
adding from_obj=[outerjoin(product_table, stock_product_table)] to
what you have now would probably work.
(personally i think a subselect would be better style here but i can't
predict if it would perform better. since you are so close to having
it working the other way maybe you should just run with that. :)
On 2/25/07, Koen Bok [EMAIL PROTECTED] wrote:
Dear all.
I have to make a complicated mapper, and I need a little help.
We have a list of products. These products have stock, each individual
stock item has an entry in the stockproduct table. So to get the total
stock we need to count the items in the stock database. We can filter
them by a particular stock.
So I made a function to create a mapper to do just that. But there are
two problems:
- It's not working well, because if the count function equals 0 (no
stock) the product does not appear in the results.
- I have the feeling this can be better optimized, but I can't see it
(maybe put it in a join or subquery?)
The function
def productStockMapper(stockList):
or_list = or_()
for stock in stockList:
or_list.append(stockproduct_table.c.stock_id==stock.id)
s = select([product_table,
func.count(stockproduct_table.c.id).label('stock')],
and_(
stockproduct_table.c.product_id==product_table.c.id,
or_list),
group_by=[c for c in product_table.c]).alias('count_select')
return mapper(Product, s, non_primary=True)
The tables:
product_table = Table('products', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False)
Column('code', Unicode(255), unique=True, nullable=False))
stockproduct_table = Table('stockproducts', metadata,
Column('id', Integer, primary_key=True),
Column('stock_id', Integer, ForeignKey(stocks.id), nullable=False),
Column('product_id', Integer, ForeignKey(products.id),
nullable=False))
stock_table = Table('stocks', metadata,
Column('id', Integer, primary_key=True),
Column('name', Unicode(255), nullable=False))
--~--~-~--~~~---~--~~
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: how to force a clean refresh of a lazy loaded attribute
you could certainly write a helper function if you find that you do this a lot. I'd be -1 on making it official, it just seems messy to me. (defining collection only as list is sucky; defining collection as anything you can iterate over is problematic because an instance that is itself in the session could define __iter__ and then your semantics are ambiguous.) On 2/26/07, Manlio Perillo [EMAIL PROTECTED] wrote: This works, thanks! However, for non scalar attribute, I would like to simply do: sess.expire(myinstance.somecollection) --~--~-~--~~~---~--~~ 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: select ... having problems
Something like this should work:
select(['count(distinct flow.node_id) as nodes', ip],
from_obj=[flow.join(id)], group_by=[flow.c.src_id], having=['nodes
5'], order_by=[desc('nodes')])
If you wanted to do it w/o text blocks I think you'd have to create a
subselect first to pull the nodes column off of.
On 2/23/07, Arthur Clune [EMAIL PROTECTED] wrote:
This should be easy, but I'm having real problems with it. I'd like
to do the following SQL (this is from MySQL) as a sqa query:
select count(distinct flow.node_id) as nodes, ip.ip_addr,
ip.country, ip.domain, ip.isp, ip.city from flow, ip where flow.src_id =
ip.id group by flow.src_id having nodes 5 order by nodes desc;
I can't work out how to do the 'having nodes 5' bit at all. All
help gratefully received!
For info, the tables look like this (with some fields missed out for
clarity):
Tables are pretty straightfoward. Slighty edited, they look like this:
owner_table = Table(node,
Column(id, Integer, primary_key=True),
[more stuff]
)
# location data based on the fields from ip2location
ip_table = Table(ip,
Column('id', Integer, primary_key=True),
Column('ip_addr', String(16), nullable=False, index=True,
unique=True),
Column('isp', Unicode, default=0.0),
Column('domain', Unicode, default=0.0),
Column('country', Unicode, default=None),
Column('city', Unicode, default=None),
)
flow_table = Table(flow,
Column(id, Integer, primary_key=True),
Column(owner_id, Integer, ForeignKey(honeypot.id),
nullable=False),
Column(ip_proto, Integer, default=6, nullable=False),
Column(src_id, Integer, ForeignKey(ip.id), nullable=False),
Column(dst_id, Integer, ForeignKey(ip.id), nullable=False),
)
Then I define the obvious mappers.
Cheers,
Arthur
--
Arthur Clune
--~--~-~--~~~---~--~~
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] Suggestion to support full outer joins
Instead of isouter argument to Join, have join_type. Default to 'inner' but could also be 'left', 'right', or 'outer.' (Or even 'natural' but that might be confusing b/c of Join's default behavior of joining by FK which is similar to but not the same as natural join.) (Of course, it's quite possible that SA already supports full outer joins and I just missed it, again. :) -Jonathan --~--~-~--~~~---~--~~ 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: Suggestion to support full outer joins
On 2/20/07, Jonathan Ellis [EMAIL PROTECTED] wrote: Instead of isouter argument to Join, have join_type. Default to 'inner' but could also be 'left', 'right', or 'outer.' should read ... or 'full,' of course. --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
On 2/16/07, jose [EMAIL PROTECTED] wrote: Gary Bernhardt wrote: Referential integrity isn't being violated here - SA is nulling the foreign key before deleting the row it points to. Try adding nullable=False to the declaration of attivita.cod_specie. That should make it fail in the way you expect, because SA will no longer be able to null the foreign key. This seems to me a trick to avoid integrity referential. I expected the nullable=False was the default behavior for any foreign key otherwise the referential integrity is violated here? I'm very, very surprised for this behavior. Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) --~--~-~--~~~---~--~~ 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: postgres, autoload, and odd datatypes
I'd prefer to not have them loaded at all (maybe with log.warning) than to have them loaded with a known-to-be-incorrect type. If you really don't want to manipulate them from Python, not loading them is the Right Thing. If you do want to manipulate them then the Right Thing is to add the necessary datatypes. On 2/16/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I found the discussion last month regarding the lack of support for specialised datatypes in the postgres reflection code. I have a lot of odd datatypes in my schemas... besides inet, there's postgis datatypes and tsearch2's tsvector, etc. However, most of these odd fields are never manipulated directly at the python level (they tend to be updated internally via triggers); and the ones that are touched (like 'inet' for example) work fine as text fields. Postgresql does the coersion. Thus, my quick little hack to make reflection work without having to tediously override the table columns, or create new datatypes, is simply to make all unknown datatypes be 'text' datatypes: --- postgres.py.origFri Feb 16 09:52:59 2007 +++ postgres.py Fri Feb 16 09:55:26 2007 @@ -392,7 +392,10 @@ elif attype == 'timestamp without time zone': kwargs['timezone'] = False -coltype = ischema_names[attype] +if ischema_names.has_key(attype): +coltype = ischema_names[attype] +else: +coltype = ischema_names['text'] coltype = coltype(*args, **kwargs) colargs= [] if default is not None: I realise this is a bit dodgy, but it has simplified things for me greatly. I'm wondering if something like this might not be a useful concept for an some sort of autoload option... rather than crashing with a KeyError. Either a default datatype to substitute, or even just ignoring fields of unknown datatypes. (Also it would be very helpful, especially when autoloading a lot of foreign key tables, if that KeyError exception was caught and returned a more meaningful error message... like what table and field name is the problem...) --~--~-~--~~~---~--~~ 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: Reflection including Relations
You say defined manually... automatically. It can't be both. :) SA won't try to guess what properties you want on your mapped classes, because it could guess wrong. (Believe me, whatever pattern you are thinking of, someone could find an exception where automatically setting it up is not the right thing to do.) Explicit is better than implicit... On 2/16/07, Andreas Jung [EMAIL PROTECTED] wrote: --On 16. Februar 2007 17:09:12 +0100 Andreas Jung [EMAIL PROTECTED] wrote: SA provide reflection support through Table(...autoload=True). Is there some extension for SA to include reflection support for relations? Otherwise you have specify the relationships using the 'properties' on your own. Sorry for this weird posting. Of course the mapped class should obtain the additional properties as defined manually through 'properties' automatically from the knowledge of the relations of the table instance. -aj --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. Sure, set a NOT NULL constraint on the column you don't want to be null. Which you should really be doing anyway. :) --~--~-~--~~~---~--~~ 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: SA skips integrity referential?
If you want to do things the hard way, you can do this by turning off cascade on the relationship (cascade='none'). But you can't have your cake and eat it too, you'll have to manually handle adding new subordinate objects to the session when saving, etc. On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: On 2/16/07, jose [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Guess it would surprise you to learn about the SQL 92 ON DELETE SET NULL functionality too. :) Seems to me the SQL92 'ON DELETE SET NULL' is an explicit functionality, instead in our case, SA does this functionality in implicit way. :-( I hope there's a sort of configuration to disable this functionality. Sure, set a NOT NULL constraint on the column you don't want to be null. Which you should really be doing anyway. :) No Jonathan, I don't want this column is set as NOT NULL, I have to allow null values for this column and I don't want enable the ON DELETE SET NULL functionality. I would like SA have the same behavior as PostgreSQL has, I like the message: pg delete from specie where codice='89'; ERROR: update or delete on specie violates foreign key constraint attivita_cod_specie_fkey on attivita DETAIL: Key (codice)=(89) is still referenced from table attivita. Is there another way to do that? jo --~--~-~--~~~---~--~~ 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: Announcing Elixir!
For instance, I remember reading somewhere that AM wasn't very good at playing well with the rest of SA when AM wasn't enough, so I never bothered looking at AM very hard. I don't see anything on the elixir site about this issue, but maybe I am looking in the wrong place. On 2/15/07, Jonathan LaCour [EMAIL PROTECTED] wrote: Jonathan Ellis wrote: Is there a what's new and improved in Elixir document anywhere? Well, its pretty much entirely new and improved over TurboEntity and ActiveMapper, in that it provides a totally different way of doing things. The extensive documentation and examples on the website will probably give a reasonable idea of how Elixir is different from both ActiveMapper and TurboEntity. -- Jonathan LaCour http://cleverdevil.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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how do I set an engine to autocommit?
On 2/1/07, Kumar McMillan [EMAIL PROTECTED] wrote: the fact that the deadlock was solved when I added the connection to the transaction tells me that psycopg runs in commit mode automatically (even though dbapi 2 says it should not). The psycopg docs reinforce this too : http://www.initd.org/tracker/psycopg/wiki/psycopg2_documentation#setting-transaction-isolation-levels Wrong; psycopg2 does not autocommit by default. It automatically _creates new transactions_ but you still have to explicitly commit() or rollback(). This is what DBAPI2 calls for (and is really the only sane thing to do in non-autocommit mode if you think about 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] pool echo
Minor question: wouldn't it be more consistent to either apply echo to all pool logging, or get rid of it and let logger settings control it? Here's a patch for the former. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- pool.patch Description: Binary data
[sqlalchemy] Re: any particular reason for creating unused lists?
Reasonable people can differ here, but I agree that if what you care about is a side effect, rather than a resulting list, using a for loop is more clear than a list comprehension. (I suspect it is also more performant since you are not allocating and populating a list object for no reason.) But in this case I think it's clear that the best way is to simply write t.extend(data) On 1/25/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: there are several places of such unused lists being made. i pick a random occurence, in this case InstrumentedAttribute: def _adapt_list(self, data): if self.typecallable is not None: t = self.typecallable() if data is not None: [t.append(x) for x in data] return t else: return data why not just for x in data: t.append(x) --~--~-~--~~~---~--~~ 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: is pool supposed to be able to handle its target db restarting?
I'm restarting it after the first fetchall() finishes.
My experience with postgresql/psycopg2 is that you can tell the
connection is dead when you next try to call cursor(), but that isn't
happening here because of the queue problem I described.
Here's the output with pool logging on, if that helps:
e = create_engine('postgres://[EMAIL PROTECTED]/mozy', pool_size=1,
max_overflow=0, pool_timeout=None, echo=True)
e.execute('select 1').fetchall()
2007-01-25 15:03:10,704 INFO sqlalchemy.pool.QueuePool.0x..b4
Created new connection connection object at 0xb79ca020; dsn:
'dbname=mozy host=db user=neptune', closed: 0
INFO:sqlalchemy.pool.QueuePool.0x..b4:Created new connection
connection object at 0xb79ca020; dsn: 'dbname=mozy host=db
user=neptune', closed: 0
2007-01-25 15:03:10,704 INFO sqlalchemy.pool.QueuePool.0x..b4
Connection connection object at 0xb79ca020; dsn: 'dbname=mozy host=db
user=neptune', closed: 0 checked out from pool
INFO:sqlalchemy.pool.QueuePool.0x..b4:Connection connection object at
0xb79ca020; dsn: 'dbname=mozy host=db user=neptune', closed: 0
checked out from pool
2007-01-25 15:03:10,705 INFO sqlalchemy.engine.base.Engine.0x..14 select 1
INFO:sqlalchemy.engine.base.Engine.0x..14:select 1
2007-01-25 15:03:10,705 INFO sqlalchemy.engine.base.Engine.0x..14 None
INFO:sqlalchemy.engine.base.Engine.0x..14:None
2007-01-25 15:03:10,709 INFO sqlalchemy.pool.QueuePool.0x..b4
Connection connection object at 0xb79ca020; dsn: 'dbname=mozy host=db
user=neptune', closed: 0 being returned to pool
INFO:sqlalchemy.pool.QueuePool.0x..b4:Connection connection object at
0xb79ca020; dsn: 'dbname=mozy host=db user=neptune', closed: 0 being
returned to pool
[(1,)]
# [db restarted]
e.execute('select 1').fetchall()
2007-01-25 15:03:30,690 INFO sqlalchemy.pool.QueuePool.0x..b4
Connection connection object at 0xb79ca020; dsn: 'dbname=mozy host=db
user=neptune', closed: 0 checked out from pool
INFO:sqlalchemy.pool.QueuePool.0x..b4:Connection connection object at
0xb79ca020; dsn: 'dbname=mozy host=db user=neptune', closed: 0
checked out from pool
2007-01-25 15:03:30,691 INFO sqlalchemy.engine.base.Engine.0x..14 select 1
INFO:sqlalchemy.engine.base.Engine.0x..14:select 1
2007-01-25 15:03:30,691 INFO sqlalchemy.engine.base.Engine.0x..14 None
INFO:sqlalchemy.engine.base.Engine.0x..14:None
Traceback (most recent call last):
File stdin, line 1, in ?
File /root/SQLAlchemy-0.3.4/lib/sqlalchemy/engine/base.py, line
686, in fetchall
for row in self.cursor.fetchall():
psycopg2.ProgrammingError: no results to fetch
e.execute('select 1').fetchall()
# [hangs]
On 1/25/07, Michael Bayer [EMAIL PROTECTED] wrote:
if you close all connections (i.e. return them all to the pool), and
then go to get them again, it can handle a restart *if* the dialect
knows how to detect the conditions whereby it should invalidate() the
connections. which is generally pretty spotty, i dont think PG knows
how to do it. also i dont understand the error condition you are
getting, the connections should all be getting returned above ( or some
exception would be thrown).
are you stopping the DB while the first fetchall() is executing ? or
in between ?
On Jan 25, 3:54 pm, Jonathan Ellis [EMAIL PROTECTED] wrote:
from sqlalchemy import *
e = create_engine('postgres://...', pool_size=1, max_overflow=0,
pool_timeout=None)
e.execute('select 1').fetchall()
# restart db
e.execute('select 1').fetchall()
# error gets raised, ConnectionRecord apparently doesn't get returned
to pool because:
e.execute('select 1').fetchall()
# hangs, pool overflow=1 (so new CR is not created) but nothing is in the
queue
--~--~-~--~~~---~--~~
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: SQLite, append_column?
SA doesn't support doing ADD COLUMN or almost any other ALTER command. If you ran the ALTER manually then created a new Table with autoload it would of course load the new version, but I'm at a loss to see why you'd need that. On 1/23/07, Basil Shubin [EMAIL PROTECTED] wrote: So, there is no way to add new column to existing table? Or I can use ALTER TABLE and how to save existing data on table alter? Thanks! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Extracting date parts from datetime with postgresql
date_part is a synonym for extract w/ more normal syntax
so you could write func.date_part('year', mydatetime)
On 1/22/07, laurent rahuel [EMAIL PROTECTED] wrote:
Hi,
Here is my little trouble. I want to extract some parts of a datetime
field in a postgresql database.
I played around with the func.year(), func.month() but it sounds like
postgresql doesn't understand the resulting query. For example, a
func.year() call is converted into :
select year(myDateTime) from mytable;
This works with MySQL but not with Postgresql. The error is function
year(timestamp without time zone) doesn't exist
I guess the resulting Postgresql query should be :
select EXTRACT (year FROM myDateTime) from mytable;
Any clue ???
Regards,
Laurent
--~--~-~--~~~---~--~~
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: transactions
yes, you should, and your commented-out code looks fine
On 1/20/07, jose [EMAIL PROTECTED] wrote:
Hi all,
I have a question about transactions
I need to insert data into two tables (anag and user), sice I need to
insert the generated primary key of anag into user.
I have to flush anag to have the id to insert into user table.
What happens if there's an interruption in the middle of this, for
example, immediately after to flush anag?
Should I use create_transaction() to avoid a such thing?
#transaction = session.create_transaction()
#try:
anag = Anag(
name = data.get('display_name')
)
session.save(anag)
session.flush()
user = User(
anag_id = anag.id
)
session.save(user)
session.flush()
#transaction.commit()
#except:
#transaction.rollback()
jo
--~--~-~--~~~---~--~~
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 transactions
On 1/17/07, Miki [EMAIL PROTECTED] wrote: # Notice we didn't commit yet This means that if someone is querying the table in the middle of the transaction it'll get wrong results. (I'm not a DB expert, this might be total nonsense) no, it just means that queries run in the same uncommitted transaction can see the uncommitted data. this is the way transactions are supposed to work; only connections _outside_ the transaction shouldn't be able to see 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Accessing DBAPI cursor object from connection
engine.raw_connection().cursor() On 1/18/07, Sean Davis [EMAIL PROTECTED] wrote: I would like to access the underlying psycopg2 connection to get at a DBAPI2 cursor with the ultimate goal of using the copy_from/copy_to protocol for moving large amounts of data to/from the database. I can't seem to find a way to do that from a db engine or connection object. Is there a way (assuming that I am using postgres)? Thanks, Sean --~--~-~--~~~---~--~~ 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: Postgre e pg_largeobject
On 1/17/07, Antonio [EMAIL PROTECTED] wrote:
and now, how can I retrieve the file (res.pdf.data) in a file or send it
as output in a html page (sendig the right headers) ?
Nothing magical:
file('foo.pdf', 'wb').write(res.pdf.data)
see your html framework's docs for instructions on sending back binary data...
--~--~-~--~~~---~--~~
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: Postgre e pg_largeobject
Oops, I didn't notice at first that you are using pg_largeobject...
That's not a good idea, you should really use bytea (for SA, that
means declaring filepdf as a Binary column itself rather than linking
to pg_largeobject) unless you are planning to manually seek inside the
lo (i.e. with lo_lseek, etc.), and it doesn't look like you are.
I don't believe SA really supports using pg_largeobject, they're kind
of obsoleted by bytea.
On 1/17/07, Antonio [EMAIL PROTECTED] wrote:
Hi all,
I'm trying to read a pdf file saved in a postgresql table :
pdf_tbl=sqa.Table('pg_largeobject', _mdata,
sqa.Column('loid', sqa.Integer, primary_key = True),
sqa.Column('pageno', sqa.Integer),
sqa.Column('data', sqa.Binary)
)
sqa.mapper(Pdf,pdf_tbl)
sqa.mapper(Pdf,pdf_tbl)
fax_tbl=sqa.Table('faxes', _mdata,
sqa.Column('id', sqa.Integer, primary_key = True),
sqa.Column('sender', sqa.TEXT),
sqa.Column('pages', sqa.SmallInteger),
sqa.Column('received', sqa.DateTime),
sqa.Column('filepdf', sqa.Integer,
sqa.ForeignKey('pg_largeobject.loid'))
)
sqa.mapper(Fax,fax_tbl,properties={ 'pdf': sqa.relation(Pdf) })
sess=sqa.create_session()
qry=sess.query(Fax)
res=qry.get_by(id=1)
and now, how can I retrieve the file (res.pdf.data) in a file or send it
as output in a html page (sendig the right headers) ?
tanks in advance ...
PS
sorry for my english ...
--
#include stdio.h
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf(%s,c);return 0;}
--~--~-~--~~~---~--~~
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: Profiling mode
I finally came back to this. Here's what I ended up with: # I tried to enable profiling on a per-engine level before resorting to this # hack. (Monkey-patching classes by scanning the gc! Woot!) # # Per-engine profile turns out to totally not work because there's so many # layers of clever stuff going on (well, primarily PoolConnectionProvider # returning proxies instead of real Connections) that it's really impossible to # decorate Connections in a general manner by relying on engine.connection_provider. # Too bad, because it was rather more elegant. # # This will work no matter how many layers of proxies there are... def enable_profiling(): import gc for o in gc.get_objects(): if isinstance(o, type): if o == Connection or Connection in o.__bases__: o._execute = _profilingexecute o._executemany = _profilingexecutemany where the _profilingexecute methods do pretty much what was discussed before. On 10/30/06, Michael Bayer [EMAIL PROTECTED] wrote: id look into building this as a ProxyEngine. _execute and _executemany might be better targets for profiling but its not super- important. On Oct 30, 2006, at 6:38 PM, Jonathan Ellis wrote: For me it has been useful in the past to track overall database query speed so I could optimize the query taking the most aggregate time. (I.e., execution time * times executed.) It looks to me like this could be hooked in to SA pretty easily, with just a minor change to Connection._execute_raw, using statement as the key to aggregate on. (You could even define two _execute_raws and pick one at runtime to avoid any overhead when not in profiling mode.) This seems to work fine: start = time.time() if parameters is not None and isinstance(parameters, list) and len(parameters) 0 and (isinstance(parameters[0], list) or isinstance(parameters[0], dict)): self._executemany(cursor, statement, parameters, context=context) else: self._execute(cursor, statement, parameters, context=context) end = time.time() self._autocommit(statement) profile_data[statement] = profile_data.get(statement, 0) + (end - start) Of course, this only tells you what generated SQL is slow, not what code caused those queries to run, but it's easy enough to grab caller info from the stack. But am I missing other code paths that would have to be tracked? -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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: Profiling mode
Thinking about it more, I should probably just override the methods of Connection itself and not worry about subclasses. If someone is overriding _execute*, he can do his own damn profiling. :) On 1/17/07, Jonathan Ellis [EMAIL PROTECTED] wrote: I finally came back to this. Here's what I ended up with: # I tried to enable profiling on a per-engine level before resorting to this # hack. (Monkey-patching classes by scanning the gc! Woot!) # # Per-engine profile turns out to totally not work because there's so many # layers of clever stuff going on (well, primarily PoolConnectionProvider # returning proxies instead of real Connections) that it's really impossible to # decorate Connections in a general manner by relying on engine.connection_provider. # Too bad, because it was rather more elegant. # # This will work no matter how many layers of proxies there are... def enable_profiling(): import gc for o in gc.get_objects(): if isinstance(o, type): if o == Connection or Connection in o.__bases__: o._execute = _profilingexecute o._executemany = _profilingexecutemany where the _profilingexecute methods do pretty much what was discussed before. --~--~-~--~~~---~--~~ 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: adding a foreign key constraint to an existing table
On 1/15/07, Michael Bayer [EMAIL PROTECTED] wrote: 2. no support for CASCADE in the DROP statement right now. someone fill me in, is DROP CASCADE part of the sql standard ? yes, at least for tables. --~--~-~--~~~---~--~~ 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: How to query like sys_id=42 AND ts_created 90 minutes ago ?
You can shorten it a little by having the db do the date operation:
History.c.ts_created func.now() - '90 minutes'
On 1/11/07, Chris Shenton [EMAIL PROTECTED] wrote:
I've got a bunch of history and other timestamped information I will
need to query against. The columns are created with type DateTime and
get set upon row creation:
history_table = Table(
'history', metadata,
Column('history_id', Integer,primary_key=True),
Column('system_id', Integer,
ForeignKey('system.system_id'), nullable=False),
Column('ts_created', DateTime,
default=func.current_timestamp()),
Column('ts_updated', DateTime,
onupdate=func.current_timestamp()),
)
I'm going to want to do lots of queries on this 'history' table for a
specific 'system_id' and a 'ts_created' within some duration in the
past -- like 5 or 60 minutes.
It's taken me a while to figure out the SQLAlchemy syntax to make this
work and it seems a bit verbose:
session.query(History).select(and_(History.c.system_id==42,
History.c.ts_created
datetime.datetime.now() - datetime.timedelta(minutes=90)))
Is there a better, more concise way to say this?
Thanks.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping special child rows
Okay, I have another question related to this.
Now that I have max_order defined, I want to do a query on it (give me
the users whose max_order==5). My code is
max_orders_by_user =
select([func.max(orders.c.order_id).label('order_id')],
group_by=[orders.c.user_id]).alias('max_orders_by_user')
max_orders =
orders.select(orders.c.order_id==max_orders_by_user.c.order_id).alias('max_orders')
mapper(User,
users,
properties={
'orders':relation(class_mapper(Order), backref='user'),
'max_order':relation(mapper(Order, max_orders,
non_primary=True), uselist=False),
'addresses':relation(mapper(Address, addresses), backref='user'),
})
It seemed like if I labeled the column something unique in
max_orders_by_user, then I should be able to use that in select_by, a
la
http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_selectrelations_relselectby
So I changed that to max_order_id and tried
max_orders_by_user =
select([func.max(orders.c.order_id).label('max_order_id')],
group_by=[orders.c.user_id]).alias('max_orders_by_user')
max_orders =
orders.select(orders.c.order_id==max_orders_by_user.c.max_order_id).alias('max_orders')
# mapper as above
session.query(User).select_by(max_order_id=5)
and got
sqlalchemy.exceptions.InvalidRequestError: Cant locate property named
'max_order_id'
I did get it to work with
session.query(User).select(max_orders.c.order_id==5, from_obj=[max_orders])
Is there a way to do this with select_by?
--~--~-~--~~~---~--~~
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: Composite primary key
Well, you could specify the primaryjoin as described here:
http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_customjoin
but I suspect that your existing mapper will Just Work if you switch
to a composite FK, rather than 3 keys on individual columns
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('var', Integer),
Column('val', Integer),
ForeignKeyConstraint(['key1', 'key2', 'key3'],
['enterprise.key1', 'enterprise.key2', 'enterprise.key3'])
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
PrimaryKeyConstraint('key1', 'key2', 'key3')
)
# similarly adjust surfaces
On 1/9/07, exhuma.twn [EMAIL PROTECTED] wrote:
Hi,
I have to load a table from 2 different data-sources witch each having
3 different primary keys. This is because those 2 datasources are
already exported from 3 databases from an archaic application.
From those 2 datasources I created - after normalising - 3 new tables.
The main table keeps the 3 primary keys as one composite primary key.
The two other tables have those 3 fields as foreign keys. Now, if I map
those table definitions onto a table with relations, sqlalchemy
complains with the following error:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'bovines' between mappers
'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. 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 Cant determine join between 'entreprises'
and 'bovines'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Ok, so I have to specify the onclause. But how do I do that? For
reference, here is the (non-working) data definition:
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer, primary_key=True),
Column('key2', Integer, primary_key=True, default=0),
Column('key3', String, primary_key=True),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
)
t_surfaces = Table( 'surfaces', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
metadata.create_all()
class Bovine(object):
pass
class Surface(object):
pass
class Enterprise(object):
def __repr__(self):
return [Entreprise %s %s %s] % (self.key1, self.key2,
self.key3)
usermapper = mapper(Bovine, t_bovines)
usermapper = mapper(Surface, t_surfaces)
usermapper = mapper(Enterprise, t_entreprises, properties={
'bovines': relation(Bovine),
'surfaces': relation(Surface)
})
--~--~-~--~~~---~--~~
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: customize table creation
On 1/9/07, Daniel Haus [EMAIL PROTECTED] wrote: Hi alchemists! Is there any way to have SA do some custom work before it creates a table? I tried to subclass Table and override create, which obviously doesn't work that easily. Any suggestions or ideas? The background is, I have some objects that dynamically add columns to the table on demand, which seems to work fine. Now, I need to ensure that these objects can do their work on table creation. And doing it manually would be painful... You're trying to dynamically add columns to the table in the database, or just to the Table instance? --~--~-~--~~~---~--~~ 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] Mapping special child rows
Say I have tables corresponding to users and orders from test/tables.py.
I have dozens or hundreds of orders per user. I want to show a list of
users with their most recent order, but because I have so many orders
doing an eager load to orders is a bad solution. Having a manually
defined python property to pull in the most recent order is also bad
because I only want to do a single query.
So, I tried this
mapper(User,
users,
properties={
'orders':relation(mapper(Order, orders), backref='user'),
'max_order':relation(mapper(Order, max_orders, non_primary=True)),
'addresses':relation(mapper(Address, addresses), backref='user'),
})
But that's about as far as I got -- I couldn't come up with a max_orders
select that would work. First I tried
max_order_id = select([func.max(orders.c.order_id)],
group_by=[orders.c.user_id], scalar=True)
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
but max_orders.select() generated
SELECT orders.order_id, orders.user_id, orders.description, orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(orders.order_id) GROUP BY orders.user_id)
which selected every order rather than just orders 4 and 5. (The
subselect, with scalar=True removed, correctly selected those IDs.)
So I thought maybe adding an alias would un-confuse the subselect-in-where:
o2 = orders.select().alias('o2')
max_orders = orders.select(orders.c.order_id==max_order_id).alias('max_orders')
but this made negative progress:
sqlalchemy.exceptions.SQLError: (OperationalError) no such column: orders.order_
id 'SELECT orders.order_id, orders.user_id, orders.description, orders.isopen
FROM orders
WHERE orders.order_id = (SELECT max(o2.order_id)
FROM (SELECT orders.order_id AS order_id, orders.user_id AS user_id,
orders.description AS description, orders.isopen AS isopen) AS o2
GROUP BY o2.user_id)' []
So:
- I take it SA doesn't really support subselects in WHERE clauses?
- Is there an alternative way to map max_order that I'm missing?
(I could work around the subselect problem with a postgresql function,
but I seem to run into a lot of places to use this kind of
optimization so I'd prefer to avoid that.)
--~--~-~--~~~---~--~~
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: How to order by a field in a different table?
A query that doesn't rely on broken GROUP BY might look like
select([forum_topics,
select([func.max(forum_posts.c.created)],
forum_posts.c.topic_id==forum_topics.c.id,
scalar=True).label('last_post')],
order_by=[desc('last_post')])
On 12/28/06, Mart [EMAIL PROTECTED] wrote:
Thanks, it worked!
On Dec 28, 6:47 pm, Michael Bayer [EMAIL PROTECTED] wrote:
actually the GROUP BY wont go through as a keyword argument to
query.select() at the moment, so youd have to use the full select
statement. but also, i dont see why youd want that GROUP BY in the
query, you dont have any aggregate functions in your column list and
also GROUP BY requires all non-aggregates to be part of the group (at
least in postgres).
In MySQL and SQLite the GROUP BY removes the duplicate entries. (For
every post in the topic there is a row of that topic in the result.)
--~--~-~--~~~---~--~~
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: remote nondirect access to DB
On 12/8/06, ml [EMAIL PROTECTED] wrote: Jonathan Ellis píše v Pá 08. 12. 2006 v 11:05 -0700: On 12/7/06, ml [EMAIL PROTECTED] wrote: I want to have a client application accessing a remote Postgres database but I don't want to distribute the user/password to the DB. I want clients to authenticate against some other table of passwords so there must be a stub between client and DB which will know the DB user/pwd. How can I manage this in SA? You can write your stub in SA easily; your client should interface with the stub not via SQL but via a simple protocol you define that limits it to predetermined actions. How? What protocol? Any clues or examples are welcome. You get to make one up! It's your app! The point is, if you trust the client to send valid SQL you are screwed whether or not the client logs into the database directly or not. So instead of insert into users (name) values (...) your client would sent NEWUSER ... or something. If this sounds intimidating, I would suggest picking up a book covering client/server programming. I believe Programming Python and Core Python both include chapters on socket programming. -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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: Proposal: session identity_map no longer weak referencing
On 12/8/06, Michael Bayer [EMAIL PROTECTED] wrote: when SA was first released, someone immediately suggested that the identity map of Session be weak referencing, which appeared to be an obvious improvement, so that you could load as many objects as you want from the session and whatever you didnt use would just go away. but now it appears that the more intuitive operation for a Session is that things that get loaded into it, stay there, until you say otherwise. So id like to make the session act this way, again like hibernate's...more explicit. what you load into the session stays there until you clean it out. that behavior is more predictable than what we have now. +1 Of course we can always add an option weak_identity_map if people really do want the old behavior. -1, it's only a matter of time before this would bite you -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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: sqlalchemy limiting
http://www.sqlalchemy.org/docs/plugins.myt#plugins_selectresults On 12/5/06, dischdennis [EMAIL PROTECTED] wrote: What exactly is the SelectResults extension and where can I get it? -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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: columns inheritance
On 12/4/06, ml [EMAIL PROTECTED] wrote: Hi! I'm looking for something like Postgresql's CREATE TABLE a ... LIKE b. Is there any similar construction in SA? I created a simple function def inherit_table(name, table, *args, **kwargs): args = list(args) for c in table.columns: args.append(c.copy()) for c in table.constraints: args.append(c.copy()) return Table(name, table._metadata, *args, **kwargs) but I is it clean? Other than calling this inherit_table when it doesn't really have much to do with inheritance (either the pg INHERITS or class heirarchies), I think this is as good as you're going to get. -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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] SQLAlchemy at pycon 07
My proposal for a talk on SqlSoup was accepted. It looks like someone else's talk on SA itself was accepted too. Woot! :) -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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: SQLAlchemy at pycon 07
Right now you can only see the status of proposals you submitted yourself, but the final schedule is probably only a couple days away from being announced. On 11/29/06, Karl Guertin [EMAIL PROTECTED] wrote: On 11/29/06, Jonathan Ellis [EMAIL PROTECTED] wrote: My proposal for a talk on SqlSoup was accepted. It looks like someone else's talk on SA itself was accepted too. Woot! :) I'm not seeing a list of accepted talks on us.pycon.org; any links? -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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: Clases with __slots__ attribute
On 11/7/06, juanvalino [EMAIL PROTECTED] wrote: Hi, I've detected that when a class has __slots__ attribute, a mapper cannot be build because the sqlalchemy tries to create a magic attribute and fails: File build/bdist.linux-x86_64/egg/sqlalchemy/orm/attributes.py, line 672, in init_attr AttributeError: 'MyClass' object has no attribute '_MyClass__sa_attr_state' I think you'll have better luck making a class w/o __slots__ than changing something this deep in the core of SA. (If you must have __slots__ for some reason, you can always subclass it to get a slots-less version.) -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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] Select statement should not be in its own _froms list
I'm getting this error trying to map Select objects. Here's a test case:
from sqlalchemy import *
e = create_engine('sqlite:///:memory:', echo=True)
md = BoundMetaData(e)
sql =
CREATE TABLE books (
id integer PRIMARY KEY, -- auto-SERIAL in sqlite
titletext NOT NULL,
published_year char(4) NOT NULL,
authors text NOT NULL
)
e.execute(sql)
b = Table('books', md, autoload=True)
s = select([published_year, count(*) as n], from_obj=[b],
group_by=[b.c.published_year])
s = s.alias('years_with_count')
class YearWithCount(object):
pass
mapper = mapper(YearWithCount, s)
session = create_session()
q = session.query(YearWithCount)
q.select_by(published_year='2006')
# sqlalchemy.exceptions.AssertionError: Select statement should not be
in its own _froms list
--
Jonathan Ellis
http://spyced.blogspot.com
--~--~-~--~~~---~--~~
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] Profiling mode
For me it has been useful in the past to track overall database query speed so I could optimize the query taking the most aggregate time. (I.e., execution time * times executed.) It looks to me like this could be hooked in to SA pretty easily, with just a minor change to Connection._execute_raw, using statement as the key to aggregate on. (You could even define two _execute_raws and pick one at runtime to avoid any overhead when not in profiling mode.) This seems to work fine: start = time.time() if parameters is not None and isinstance(parameters, list) and len(parameters) 0 and (isinstance(parameters[0], list) or isinstance(parameters[0], dict)): self._executemany(cursor, statement, parameters, context=context) else: self._execute(cursor, statement, parameters, context=context) end = time.time() self._autocommit(statement) profile_data[statement] = profile_data.get(statement, 0) + (end - start) Of course, this only tells you what generated SQL is slow, not what code caused those queries to run, but it's easy enough to grab caller info from the stack. But am I missing other code paths that would have to be tracked? -- Jonathan Ellis http://spyced.blogspot.com --~--~-~--~~~---~--~~ 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] Re: Select statement should not be in its own _froms list
Heh, that was easy.
I'm curious now, though, why it can't find the primary key with the
revised select you gave. (Works fine when I manually tell the mapper
what to use.)
On 10/30/06, Michael Bayer [EMAIL PROTECTED] wrote:
interesting ! that answers that question (i.e., the question above the
assertion statement) changed it to a continue in rev 2072.
however, your mapping isnt going to work with those textual columns anyway
since it cant intelligently construct an aliased select from it, you have to
say:
s = select([b.c.published_year, func.count('*').label('n')], from_obj=[b],
group_by=[b.c.published_year])
i guess you already knew that.
On Oct 30, 2006, at 6:19 PM, Jonathan Ellis wrote:
from sqlalchemy import *
e = create_engine('sqlite:///:memory:', echo=True)
md = BoundMetaData(e)
sql =
CREATE TABLE books (
id integer PRIMARY KEY, -- auto-SERIAL in sqlite
titletext NOT NULL,
published_year char(4) NOT NULL,
authors text NOT NULL
)
e.execute(sql)
b = Table('books', md, autoload=True)
s = select([published_year, count(*) as n], from_obj=[b],
group_by=[b.c.published_year])
s = s.alias('years_with_count')
class YearWithCount(object):
pass
mapper = mapper(YearWithCount, s)
session = create_session()
q = session.query(YearWithCount)
q.select_by(published_year='2006')
--
Jonathan Ellis
http://spyced.blogspot.com
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
