[sqlalchemy] Re: What should the future of SQLAlchemy Query look like ?
On 3/10/07, Michael Bayer [EMAIL PROTECTED] wrote: Well initial response to this post has been overwhelmingly low, but thats fine with me. After trying to tackle this today, I realized that I actually didnt want to add a brand new query object and go through a painful deprecation procedure again and all that...and I also observed that the notion of a query being against a single entity is pretty important..things like get(), load() and all that dont make much sense for multiple entities. Also, all the things Ive wanted to do are not actually that hard as we already have code to do most of it. So I did not add any new classes or modules, no new methods on session, I didnt go with my modified generative approach (its just like it always was) and changed absolutely nothing about Query thats already there (well one little thing i dont think anyone was using). But I added a whole lot of new methods to Query, essentially everything SelectResults was able to do. I was concerned about having just too much crap on Query but i think it turned out fine. Plus I added a little bit of hibernate-like capabilities to query multiple entities and extra columns too. Well, this is just Great ! I also felt those methods belonged in the core somehow. Thanks for all your work! -- Gaëtan de Menten http://openhex.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: pyodbc and tables with triggers
I needed to change the connectionstring to use integrated security anyway), FWIW if someone were to be able to review / commit my patch on ticket 488 (http://www.sqlalchemy.org/trac/ticket/488) the integrated security would be there anyway. Haven't got round to patching the SCOPE_IDENTITY stuff yet. TJG --~--~-~--~~~---~--~~ 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] Warning with MySQL
table = Table('boo', metadata,
Column('id', Integer, primary_key=True),
mysql_engine='InnoDB')
When sqlalchemy creates the table, MySQL generates a warning cause
sqlalchemy is using TYPE='InnoDB' which is deprecated.
It should be using ENGINE='InnoDB'.
--~--~-~--~~~---~--~~
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 ?
I like it all!
Arnar
On 3/10/07, Michael Bayer [EMAIL PROTECTED] wrote:
Well initial response to this post has been overwhelmingly low, but
thats fine with me. After trying to tackle this today, I realized
that I actually didnt want to add a brand new query object and go
through a painful deprecation procedure again and all that...and I
also observed that the notion of a query being against a single entity
is pretty important..things like get(), load() and all that dont make
much sense for multiple entities. Also, all the things Ive wanted to
do are not actually that hard as we already have code to do most of
it.
So I did not add any new classes or modules, no new methods on
session, I didnt go with my modified generative approach (its just
like it always was) and changed absolutely nothing about Query thats
already there (well one little thing i dont think anyone was using).
But I added a whole lot of new methods to Query, essentially
everything SelectResults was able to do. I was concerned about having
just too much crap on Query but i think it turned out fine. Plus I
added a little bit of hibernate-like capabilities to query multiple
entities and extra columns too.
So...with that, heres what Query can do now.
First of all, the same stuff that youve always used is just like it
always was:
query.select_by()
query.select()
query.get()
query.count()
query.select_by_XXX()
A couple of methods that Ive never liked because they are klunky, are
still there anyway:
query.join_to()
query.join_via()
As it stands, Query has two generative methods already (which also i
had to fix a little bit since generative-ness would wipe out what
was previously there). these return for you a new Query with
modifications to its state:
query.options()
query.with_lockmode()
So we add a lot of new generative methods taken from SelectResults,
all of which return a brand new Query. the things you add here will
also take effect on subsequent calls to the regular select(), count(),
select_by(), etc:
query.filter() - adds criterion
query.filter_by() - ditto
query.join() - joins to a property name, or a list
query.outerjoin()
query.order_by()
query.group_by()
query.distinct() - applies DISTINCT
query.offset()
query.limit()
query[3:5] - applies offset 3, limit 2
like SelectResults, we have some executors -
query.list()
list(query)
or just call query.select(), selectfirst(), selectone(), etc. to
execute whatever has been built up.
the aggregates, which take a Column (not sure how popular these are):
query.avg()
query.sum()
.. and others
So a generative example, including join which im really excited
about. join can act either like join_to():
q =
session.query(Person).filter_by(people.c.name.like('%2')).join('status').filter_by(name=active)
print q.list()
or like join_via (which is more deterministic), if you send a list:
l = q.filter(orderitems.c.item_name=='item 4').join(['orders',
'items']).list()
and then, some brand new stuff - better support for querying more
than one thing at a time. the instances() method, which was able to
take a list of *mappers, now returns the results the way the docs say
they do, as a list of tuples, each tuple having an entry for each
mapper. additionally, when that option is used, the uniquing of the
result is turned off - this so that the results you get back
correspond to the rows that went in. and, you can stick arbitrary
columns, not just mappers, in the list too...it will just pull them
from the row.
in addition to the changes on instances(), you can add extra entities/
columns to the compilation as well:
query.add_entity() - adds columns to the select criterion
query.add_column() - adds columns to the select criterion
so what can we do with this ? things like, query an entity and an
aggregate function at the same time:
q = sess.query(User)
q = q.group_by([c for c in
users.c]).outerjoin('addresses').add_column(func.count(addresses.c.address_id).label('count'))
l = q.list()
which will return for you a list of tuples:
[
(user1, 3),
(user2, 5),
(user3, 0),
..etc
]
note the label is needed right now for a function, it makes it
possible for Query to target the column correctly.
another one from the unit tests. the users table has users 7, 8, 9
in it. the addresses table has addresses 1, 2, 3, 4. user #7
refereces address #1, user #8 references addresses 2, 3 and 4.
(user7, user8, user9) = sess.query(User).select()
(address1, address2, address3, address4) =
sess.query(Address).select()
q = sess.query(User)
q = q.add_entity(Address).outerjoin('addresses')
l = q.list()
assert l == [
(user7, address1),
(user8, address2),
[sqlalchemy] Re: Warning with MySQL
in at least version 0.3.5, it passes through whatever is after
mysql_. so mysql_engine will pass through ENGINE.
On Mar 10, 2007, at 6:51 AM, Rene Rattur wrote:
table = Table('boo', metadata,
Column('id', Integer, primary_key=True),
mysql_engine='InnoDB')
When sqlalchemy creates the table, MySQL generates a warning cause
sqlalchemy is using TYPE='InnoDB' which is deprecated.
It should be using ENGINE='InnoDB'.
--~--~-~--~~~---~--~~
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 create the following mapping - static tables
then you have to look up an existing Category and use that.
theres a recipe to make this automatic:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject
On Mar 10, 2007, at 9:29 AM, sqad wrote:
I have 2 tables.
One that maps categories (schema: Category) that are created/
initialized when database is first created:
--
---
-category_id (primary-key),
-category_name
[sample STATIC-CONSTANT table mappings]
1 , 'Local'
2 , 'Global'
etc.,
Another that contains (schema: Event):
--
---
-event_id (primary-key),
-category_id (foreign-key),
-date,
-etc,
This is a one to one relationship. I am using Pylons MVC framework
with SA. When creating an Event object which requires association with
a category, how do I map it?
I have the following mappers:
assign_mapper(session_context, Category, category_table)
assign_mapper(session_context,
Event,
event_table,
properties = {'category' : relation(Category, cascade=all,
delete-orphan)}
Basically, what's the syntax to create the event with some category?
This doesn't work.
event.category = Category(Local)
It creates a NEW entry in the Category table, which is not what I
wanted. I want to basically link the Event table to the Static
Category table through reference of the primary/foreign-key
relationship.
Thanks!
/sqad
--~--~-~--~~~---~--~~
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: Warning with MySQL
Yeah I guess I'm running 0.3.3 or something, got to checkout the newest
version :)
On 3/10/07, Michael Bayer [EMAIL PROTECTED] wrote:
in at least version 0.3.5, it passes through whatever is after
mysql_. so mysql_engine will pass through ENGINE.
On Mar 10, 2007, at 6:51 AM, Rene Rattur wrote:
table = Table('boo', metadata,
Column('id', Integer, primary_key=True),
mysql_engine='InnoDB')
When sqlalchemy creates the table, MySQL generates a warning cause
sqlalchemy is using TYPE='InnoDB' which is deprecated.
It should be using ENGINE='InnoDB'.
--~--~-~--~~~---~--~~
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: pyodbc and tables with triggers
Module selection in MSSQL is a bit ugly right now. Mike has proposed a clean-up of the way that DB-API modules are loaded and used, so this will get better soon, I hope. I'll have a look at the patch. Rick On 3/9/07, polaar [EMAIL PROTECTED] wrote: Yes, but I'm starting to think I'm doing something wrong ;-) I suppose I should call create_engine with the module=pyodbc? I was just using the creator argument (as I was doing already because I needed to change the connectionstring to use integrated security anyway), and just switched that from adodbapi to pyodbc. So maybe it's still using the default adodbapi settngs... Hmm, seems to make sense... oops... (well, it's not really clear from the docs that this is used for anything else than determining which module to use to create the connection, which seems unnecessary if you create it yourself) I'll try it on monday... On 9 mrt, 22:08, Rick Morrison [EMAIL PROTECTED] wrote: This is still with pyodbc? The MSSQL module should already set sane_rowcount to False for that dialect, as per the pyodbc site, they don't implement rowcount. Rick --~--~-~--~~~---~--~~ 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] matz blogged us
who can read japanese ??? http://www.rubyist.net/~matz/20070302.html#p04 we've been noticed --~--~-~--~~~---~--~~ 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: matz blogged us
babelfish.altavista.com (JP to EN): _SQLAlchemy - The Database Toolkit for Python The library which synthesizes SQL from usual system. As for such technology you think that well enough it is convenient. --~--~-~--~~~---~--~~ 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: matz blogged us
Michael Bayer [EMAIL PROTECTED] writes: who can read japanese ??? http://www.rubyist.net/~matz/20070302.html#p04 we've been noticed http://translate.google.com/translate?u=http%3A%2F%2Fwww.rubyist.net%2F%7Ematz%2F20070302.html%23p04langpair=ja%7Cenhl=pt-BRsafe=offie=UTF-8oe=UTF-8prev=%2Flanguage_tools _ SQLAlchemy - The Database Toolkit for Python The library which synthesizes SQL from usual system. As for such technology you think that well enough it is convenient. -- Jorge Godoy [EMAIL PROTECTED] --~--~-~--~~~---~--~~ 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] major oracle BLOB fix in the trunk
hey oraclers - turns out I was underestimating cx_Oracle in the last go-around with storing BLOBs, and it actually is possible to store any number of blobs in one row with any size (or at least ive tested in the 10s of Ks). During an INSERT, there was a glitch whereby it wasnt pulling in cx_Oracle.BLOB for the setinputsizes call and it was instead pulling in cx_Oracle.BINARY from the base type object. also the auto_setinputsizes stuff was kind of broken in the case of some types too so it was largely useless. and my silly workaround with the RAWTOHEX conversion was only because it was stuck on BINARY and wasnt using BLOB, so thats out. so in rev 2402its fixed ! the oracle dialect will now have auto_setinputsizes default to True (meaning it calls cursor.setinputsizes() with the appropriate type for most columns before executing), it will use cx_Oracle.BLOB for blobs and cx_Oracle.CLOB for clobsand then you can INSERT any number of columns at once each with a size well over 4K. the fetch side was already working as we had our special ResultProxy working around the fetchall() issue. --~--~-~--~~~---~--~~ 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: Feature request: Session.get_local()
Michael Bayer wrote: my only concern is that you now have more than one way to do it. i need to deal with things in the identity map. do i go look at the session.identity_map ? (which is documented, its part of the public API) oh no, i dont have the exact kind of key to use, now i have to go use a method called find() (which again, does that mean, find it in the database ? where is it looking ?) These are good points. Maybe the problem is in my brain--I've always had a disconnect between the session.identity_map and the mapper.identity_key() function. I guess it's clearly documented that they are compatible and can be used like this: key = MyClass.mapper.identity_key(pk_value) itm = session.identity_map.get(key) It just seemed like that was digging a bit too deep into what I thought were implementation details of the mapper and the session. If those things (i.e. mapper.identity_key and session.identity_map) are clearly documented as part of the interface of SA, and they are meant to work together like that then maybe this proposal isn't even necessary. After all, it's just two lines instead of one. However, upon looking at the documentation, this is all I find on the identity_key method of the Mapper class: def identity_key(self, primary_key) deprecated. a synonym for identity_key_from_primary_key. Now I thought identity_key was OK (if a bit obscure due to lack of documentation), but identity_key_from_primary_key is not so great IMHO. This is not a method name that will come to mind when I'm trying to get the identity key of a given object. It's just too long. Would it be OK to un-deprecate identity_key and just state clearly in the documentation that it requires a primary key as it's argument? Change it like this: def identity_key(self, pk=None, instance=None) Return the identity key given a primary key OR an instance Either the pk or instance keyword argument must be supplied. An error will be raised if both instance and pk are given or if both are None. Note that this is backward-compatible with the previous version of identity_key, which took a primary key as its first argument. Then do this: identity_key_from_primary_key - deprecated identity_key_from_instance - deprecated Finally, we also need to clearly document in the section that talks about the identity_map that the keys used in that map may be obtained directly from the mapper of the object by using mapper.identity_key(). If those things were cleared up I would see no reason why we need a session.get_local() or session.find() or whatever... And we have one clear way to do it. What do you think of this? ~ Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
