[sqlalchemy] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
On 6/3/07, Michael Bayer [EMAIL PROTECTED] wrote: - filter_by() would gain a new positional argument, which is optional, which is either a string or list of strings indicating property names, indicating a join path. when this argument is present, a self-contained join among those mapped properties will be generated, and the keyword criterion of the filter_by() would be expressed against the endpoint of that join. by self-contained, i mean that the tables used in the join will be *aliased* anonymously, so that different filter_by()s which specify join paths that overlap can be used together. the existing behavior of being able to say query.join(['x', 'y', 'z']).filter_by(**kwargs) remains as well (but is different in that it doesnt create aliases). ... - the new positional argument of filter_by() is based on Gaetan's idea, as well as some light that was shed on the IRC channel. it looks like this: session.query(User).filter_by(['orders', 'items', 'keywords'], keyword_name='foo').filter_by(['orders', 'items'], item_price=49.50).list() above, we have created two joins from the users table, one of which joins from 'orders' to 'items' to 'keywords', and the other from 'orders' to 'items'. the two sets of joins are constructed using anonymous aliases, so that you get all users who have purchased an item that has the 'foo' keyword, and have also purchased an item that costs 49.50. the two paths across 'orders' and 'items' are isolated from each other. if aliases were not used above, the redundant 'orders' and 'items' tables would converge into one FROM clause each in the final statement since thats the behavior of select(), but then the semantics of the query would then be all users who purchased items that cost 49.50 and also have the 'foo' keyword. the above capability is currently available by constructing your own Alias object and building join criterion manually. But it was pointed out on IRC that the Django ORM constructs filtering queries using aliases as above. for a lot of cases the approach saves a lot of lines of code spent creating manual aliases. Hi there, I like where things are going, but I think I should clarify how the Django ORM does things to my knowledge. The filter method in the Django ORM only supports keyword args a la filter_by. Every query is based off a mapped object (like Post). Each filter can access other tables' columns via the relations, using a construct like: .filter(jobtitle__content__txt__icontains = keyword, domain__str__content__txt__icontains = keyword) Both jobtitle and domain__str (the domain relation and then the str relation) are of a mapped object Strids type, which maps to the strids table. This has a relation content to a table with contains the actual text to use for the current locale. (__icontains is there to perform a LIKE '%keyword%', since there's no ClauseElement equivalent. I'm not suggesting supporting that.) The joined tables are aliased as the path from the base object. In this case, the jobtitle join is: INNER JOIN strids AS positionprofiles__jobtitle ON positionprofiles.jobtitleid = positionprofiles__jobtitle.id The domain__str__content join is: INNER JOIN dimensions AS positionprofiles__domain ON positionprofiles.domainid = positionprofiles__domain.id INNER JOIN strids AS positionprofiles__domain__str ON positionprofiles__domain.strid = positionprofiles__domain__str.id INNER JOIN content AS positionprofiles__domain__str__content ON positionprofiles__domain__str.id = positionprofiles__domain__str__content.strid ie, it joins on domain, then str, then content, giving each one an alias based on the path from the mapped object. I'm a little worried that using anonymous aliases will make some queries impossible. For example, if you want to list all purchases of product type B made by a manufacturer with a particular discount agreement, since one would require a filter_by(['product', 'store'], ...) and the other will require a filter_by(['product', 'type'], ...). In the Django ORM, since the path to 'product' is the same, it's the same join. Not being able to rejoin on the same path may make certain kinds of query building impossible. Then again, using anonymous aliases for each kind can solve different types of problems that can't be done without them. I think the principle of least astonishment would be to not have a new join for the exact same path. But, given enough documentation, one can avoid astonishment. I hope this made the situation clearer, and not more confusing. Neil -- Neil Blakey-Milner http://nxsy.org/ [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
[sqlalchemy] Re: Left join or subselect syntax(more than one filter on join key)
finally I did sub query as follow, and worked. (Where, vcc_schema is my database schema0 subquery_stmt = select([vcc_schema.RoleElementsTable.c.roleId],and_(vcc_schema.RoleElementsTable.c.elementId == elementsId,vcc_schema.RoleElementsTable.c.elementLevelId == elementLevelId,vcc_schema.RoleElementsTable.isActive== 'Y')) query_stmt = select([vcc_schema.RolesTable.c.roleId,vcc_schema.RolesTable.c.name, vcc_schema.RolesTable.c.description], not_(vcc_schema.RolesTable.c.roleId.in_(subquery_stmt))) Hope it will help others like me.. On Jun 4, 9:14 am, aruna-cgx [EMAIL PROTECTED] wrote: SQL query for left join which give my desire result is as follow. i can use left join or sub select, though I prefer to use left join but I am Ok with subselect also. I don't know how to write this query in sqlalchemy. I refered document, but my query differes on join key as I have more than one filter on join key. . SELECT R.roleId, R.name, R.description from roles R left join roleElements RE on R.roleId = RE.roleId and RE.isActive = 'Y' and RE.elementId = 1 and RE.elementLevelId = 1 where RE.roleId IS NULL OR SELECT * FROM roles WHERE roleId not in (select roleId from roleElements where elementLevelId = 1 and elementId = 1 and isActive= Y) note: In actual query, values of RE.elementId and RE.elementLevelId would be variable. I really stuck here and tried a lot but couldn't get it. Hopping any help. Thank you in advance. Aruna Kathiriya --~--~-~--~~~---~--~~ 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
all seems ok. and ORM will become looking less SQLish (as .select being the most SQLish word IMO) --~--~-~--~~~---~--~~ 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
These sound like a good progression of the library. Would there be a way to turn on warnings if one were to use a deprecated interface? Or if the programmer wants to be even more strict, raise an exception? This wouldn't be the default, but rather an available option. --~--~-~--~~~---~--~~ 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
On Jun 3, 2007, at 10:33 PM, Michael Bayer wrote: one() raises an exception when theres not exactly one result, scalar () does not. currently we have selectfirst() and selectone(), people seem to like the dichotomy. [0] specifically adds LIMIT 1 OFFSET 0 to the query and i dont think scalar() or one() would do that (LIMIT is specifically a problem with DB's like Oracle that dont directly support it...more complex oracle queries cant handle it). scratch that partially, selectfirst() and selectone() *do* add the limit. however in the case of selectone(), it adds a limit of *two*. if a second row is present- exception. --~--~-~--~~~---~--~~ 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
The use of scalar() here seems out of place with both the common CS usage of the word (e.g. scalar == single-valued), and the use of scalar() in the SQL layer. Single row results in the ORM are rows, not a single datatype. It's another potential point of confusion, like the ORM .select() is/was. I would say drop scalar() in the ORM namespace, and for single-row results, use .first() -- returns first row .one() -- returns first row, raise exception if more than one result On 6/3/07, Michael Bayer [EMAIL PROTECTED] wrote: one() raises an exception when theres not exactly one result, scalar () does not. --~--~-~--~~~---~--~~ 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
On Jun 4, 2007, at 9:56 AM, Rick Morrison wrote: The use of scalar() here seems out of place with both the common CS usage of the word (e.g. scalar == single-valued), and the use of scalar() in the SQL layer. Single row results in the ORM are rows, not a single datatype. It's another potential point of confusion, like the ORM .select() is/was. I would say drop scalar() in the ORM namespace, and for single-row results, use .first() -- returns first row .one() -- returns first row, raise exception if more than one result then we sort of have to rename list() to be all(). its all fine with me if everyone out there doesnt mind changing their code by the time 0.5 comes out --~--~-~--~~~---~--~~ 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
Well, I don't really see the connection between .scalar() and .list(), other than they are kind of antonyms, but FWIW I always thought that .list() was an odd duck anyway, seeing as how the arguably more Pythonic list(query) works fine. On 6/4/07, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 4, 2007, at 9:56 AM, Rick Morrison wrote: The use of scalar() here seems out of place with both the common CS usage of the word (e.g. scalar == single-valued), and the use of scalar() in the SQL layer. Single row results in the ORM are rows, not a single datatype. It's another potential point of confusion, like the ORM .select() is/was. I would say drop scalar() in the ORM namespace, and for single-row results, use .first() -- returns first row .one() -- returns first row, raise exception if more than one result then we sort of have to rename list() to be all(). its all fine with me if everyone out there doesnt mind changing their code by the time 0.5 comes out --~--~-~--~~~---~--~~ 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] MaxDB database interface
Hi, Does someone know if there's a database interface for MySQL's MaxDB or if someone is developing it? If not - is it much effort to develop that? Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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
Well, I don't really see the connection between .scalar() and .list(), other than they are kind of antonyms, but FWIW I always thought that .list() was an odd duck anyway, seeing as how the arguably more Pythonic list(query) works fine. (expanding on this a bit) More generally, it's the iterative nature of a query object that makes a lot of the .first() and so on more semantic sugar than anything else anyway. I'll routinely write things like: for row in query: process(row) or results = [process(r) for r in query] instead of: results = query.list() for row in results: process(row) I would even be OK with losing all the .select() and .first() and etc. completely and deal with queries using a library of function STL-like first-order functions like first(query) one(query) etc. so color me radical, I guess. Rick On 6/4/07, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 4, 2007, at 9:56 AM, Rick Morrison wrote: The use of scalar() here seems out of place with both the common CS usage of the word (e.g. scalar == single-valued), and the use of scalar() in the SQL layer. Single row results in the ORM are rows, not a single datatype. It's another potential point of confusion, like the ORM .select() is/was. I would say drop scalar() in the ORM namespace, and for single-row results, use .first() -- returns first row .one() -- returns first row, raise exception if more than one result then we sort of have to rename list() to be all(). its all fine with me if everyone out there doesnt mind changing their code by the time 0.5 comes out --~--~-~--~~~---~--~~ 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
On Jun 4, 2007, at 10:47 AM, Rick Morrison wrote: Well, I don't really see the connection between .scalar() and .list (), other than they are kind of antonyms, but FWIW I always thought that .list() was an odd duck anyway, seeing as how the arguably more Pythonic list(query) works fine. I really like having a function on the end to give me all the results. having to go all the way back to the beginning of the line and put list() around the whole thing is not always 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: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
On Jun 4, 2007, at 3:27 AM, Neil Blakey-Milner wrote: The joined tables are aliased as the path from the base object. In this case, the jobtitle join is: INNER JOIN strids AS positionprofiles__jobtitle ON positionprofiles.jobtitleid = positionprofiles__jobtitle.id The domain__str__content join is: INNER JOIN dimensions AS positionprofiles__domain ON positionprofiles.domainid = positionprofiles__domain.id INNER JOIN strids AS positionprofiles__domain__str ON positionprofiles__domain.strid = positionprofiles__domain__str.id INNER JOIN content AS positionprofiles__domain__str__content ON positionprofiles__domain__str.id = positionprofiles__domain__str__content.strid ie, it joins on domain, then str, then content, giving each one an alias based on the path from the mapped object. those two joins are exclusive to each other (i.e. strids has a different alias in the second join than the first). this is the equivalent to what im proposing except i didnt have a deterministic (well, human-readable) naming convention in mind for tha alias names themselves. that naming scheme django is using cant really work anyway since it will very quickly go over the character limit of databases like oracle and firebird. I'm a little worried that using anonymous aliases will make some queries impossible. For example, if you want to list all purchases of product type B made by a manufacturer with a particular discount agreement, since one would require a filter_by(['product', 'store'], ...) and the other will require a filter_by(['product', 'type'], ...). In the Django ORM, since the path to 'product' is the same, it's the same join. i think you need to illustrate a clearer example since I dont understand the meaning of positionprofiles or strids above in relation to the names used in filter() (and what happened to Post?). from what I can see, the alias names are based on where the join is *going*, not where it came from so its not clear to me how this example would be accomplished. anyway, the filter on product/store and product/type example you just mention presents a third scenario, which I wouldnt consider to fall under the filter_by([attributes]) functionality, it would fall under the regular join() call which creates joins *without* aliasing. but, its not supported by the current attribute-joining functionality, because it asks to generate two joins from a common joinpoint that is *not* the root. the two ideas with regards to join() are the current way (join() moves the joinpoint which then never recedes) and the newer way (join() starts from the beginning each time). I just tried out an example of rejoining from the middle with the 0.4 code, with a mapping of User-Order-Keywords, Items create_session().query(User).join(['orders', 'items']).filter_by (id=5).join(['orders','keywords']).filter_by(id=7).list() and I got this: SELECT users.id AS users_id \nFROM users JOIN orders ON users.id = orders.user_id JOIN items ON orders.id = items.order_id JOIN orders ON users.id = orders.user_id JOIN keywords ON orders.id = keywords.order_id \nWHERE ((items.id = ?) AND (keywords.id = ?)) ORDER BY users.oid which is wrong, it joined on 'orders' twice (throws SQL error). but it seems like if join() were made a little smarter to see that 'orders' was already in there, which is pretty much required here since throwing an error is the least desireable option, youd get: SELECT users.id AS users_id \nFROM users JOIN orders ON users.id = orders.user_id JOIN items ON orders.id = items.order_id JOIN keywords ON orders.id = keywords.order_id \nWHERE ((items.id = ?) AND (keywords.id = ?)) ORDER BY users.oid where you can see the JOIN keywords is joining against the previous 'orders' table. I think that would work here and would be the approach for that scenario. --~--~-~--~~~---~--~~ 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
On Monday 04 June 2007 18:03:05 Rick Morrison wrote: Well, I don't really see the connection between .scalar() and .list(), other than they are kind of antonyms, but FWIW I always thought that .list() was an odd duck anyway, seeing as how the arguably more Pythonic list(query) works fine. (expanding on this a bit) More generally, it's the iterative nature of a query object that makes a lot of the .first() and so on more semantic sugar than anything else anyway. exactly. All() is much better than list(), in the means of list being just the current way/implementation of using ordered sequences. i guess it could return another container as well (like relation()). even if all these are gone: for row in query: process(row) break will give u .first() and for row in query: process(row) break else: raise someError will give u .one() svil --~--~-~--~~~---~--~~ 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
On 6/4/07, Michael Bayer [EMAIL PROTECTED] wrote: anyway, the filter on product/store and product/type example you just mention presents a third scenario, which I wouldnt consider to fall under the filter_by([attributes]) functionality, it would fall under the regular join() call which creates joins *without* aliasing. but, its not supported by the current attribute-joining functionality, because it asks to generate two joins from a common joinpoint that is *not* the root. the two ideas with regards to join() are the current way (join() moves the joinpoint which then never recedes) and the newer way (join() starts from the beginning each time). I just tried out an example of rejoining from the middle with the 0.4 code, with a mapping of User-Order-Keywords, Items create_session().query(User).join(['orders', 'items']).filter_by (id=5).join(['orders','keywords']).filter_by(id=7).list() and I got this: SELECT users.id AS users_id \nFROM users JOIN orders ON users.id = orders.user_id JOIN items ON orders.id = items.order_id JOIN orders ON users.id = orders.user_id JOIN keywords ON orders.id = keywords.order_id \nWHERE ((items.id = ?) AND (keywords.id = ?)) ORDER BY users.oid which is wrong, it joined on 'orders' twice (throws SQL error). but it seems like if join() were made a little smarter to see that 'orders' was already in there, which is pretty much required here since throwing an error is the least desireable option, youd get: SELECT users.id AS users_id \nFROM users JOIN orders ON users.id = orders.user_id JOIN items ON orders.id = items.order_id JOIN keywords ON orders.id = keywords.order_id \nWHERE ((items.id = ?) AND (keywords.id = ?)) ORDER BY users.oid where you can see the JOIN keywords is joining against the previous 'orders' table. I think that would work here and would be the approach for that scenario. Great! That's basically what I was trying to say. Sorry that I can't convey it in a more useful form. What do you think of me putting up the full pathological-case code from Django ORM and SQLAlchemy on the SQLAlchemy wiki, and maybe see if there are improvements to be found for that as well (since it uses ILIKE and not equality and a bunch of AND and OR statements, the new filter_by improvements can't be used for it). Just need to whittle it down to the bare minimum in terms of tables and code first. A bunch of real-life queries and the best code to achieve them would be invaluable on the wiki. Then, when changes to the API are discussed, the examples can serve as a baseline for the types of things that are being made easier or harder. Neil -- Neil Blakey-Milner http://nxsy.org/ [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] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
On Jun 4, 2007, at 12:09 PM, Neil Blakey-Milner wrote:
What do you think of me putting up the full pathological-case code
from Django ORM and SQLAlchemy on the SQLAlchemy wiki, and maybe see
if there are improvements to be found for that as well (since it uses
ILIKE and not equality and a bunch of AND and OR statements, the new
filter_by improvements can't be used for it).
h...i dont like ILIKE (which isnt even present i every database)
by default. i think its best special operators just remain as
clauseelements, i.e.
filter(Class.c.attr.like('foo'))
on the subject of case sensitivity, there is an aging but ambitious
ticket to address it in a generic way, #487.
A bunch of real-life queries and the best code to achieve them would
be invaluable on the wiki. Then, when changes to the API are
discussed, the examples can serve as a baseline for the types of
things that are being made easier or harder.
yes that would be awesome. let me know what i can do to help.
--~--~-~--~~~---~--~~
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] Changing my SQLAlchemy include point
First, let me admit that I'm not an expert at Python. I'm trying to test the newer versions of SQLAlchemy before I install it on my server. According to http://docs.python.org/inst/search-path.html, the PYTHONPATH should add paths to the beginning of sys.path. The docs at http://docs.python.org/lib/module-site.html do not mention PYTHONPATH at all (which is irritating). Anyway, what I'm seeing is that easy-install.pth has ./setuptools-0.6c3-py2.5.egg and ./SQLAlchemy-0.3.1-py2.5.egg When I check the sys.path value, I'm getting blank, setuptools, SQLAlchemy, and THEN the contents of PYTHONPATH. Really from the Python documentation, PYTHONPATH sounds like it will go at the front. But this isn't what is going on. What is the best way to have a system-installed SQLAlchemy (for others) and then override it by the user (me) to test new versions? --~--~-~--~~~---~--~~ 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: Changing my SQLAlchemy include point
according to some new bright python idea, .eggs override PYTHONPATH. so eiter kill the .egg. There was some thread about this before, search for PYTHONPATH in the group... On Monday 04 June 2007 19:36:32 Paul Kippes wrote: First, let me admit that I'm not an expert at Python. I'm trying to test the newer versions of SQLAlchemy before I install it on my server. According to http://docs.python.org/inst/search-path.html, the PYTHONPATH should add paths to the beginning of sys.path. The docs at http://docs.python.org/lib/module-site.html do not mention PYTHONPATH at all (which is irritating). Anyway, what I'm seeing is that easy-install.pth has ./setuptools-0.6c3-py2.5.egg and ./SQLAlchemy-0.3.1-py2.5.egg When I check the sys.path value, I'm getting blank, setuptools, SQLAlchemy, and THEN the contents of PYTHONPATH. Really from the Python documentation, PYTHONPATH sounds like it will go at the front. But this isn't what is going on. What is the best way to have a system-installed SQLAlchemy (for others) and then override it by the user (me) to test new versions? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new setuptools vs local SA copy
I found the thread about PYTHONPATH--interesting. It does seem that eggs are not only preventing the expected behavior, but they are also preventing the documented behavior. However, I don't think that using eggs is the best choice for a fast progressing library like SQLAlchemy--especially with this behavior. Plus, if the egg developer isn't participating in a discussion on this, why should that distribution method even be used? Paul --~--~-~--~~~---~--~~ 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 existing structure of a database
Hi, I m currently playing with using sqlalchemy to map a pre-existing database to objects. I ve had a quick look through the docs and not being able to find anything about this. Does SQLAlchemy support introspection (if thats the right word, probably not) into existing databases, if so how? If not is there any advice you have to actually use SQLAlchemy in such a way. I m trying to create an API to my database and dont really want to have to declare all the tables again. Many Thanks in advance, Nathan --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new setuptools vs local SA copy
On Jun 4, 1:58 pm, Paul Kippes [EMAIL PROTECTED] wrote: I found the thread about PYTHONPATH--interesting. It does seem that eggs are not only preventing the expected behavior, but they are also preventing the documented behavior. However, I don't think that using eggs is the best choice for a fast progressing library like SQLAlchemy--especially with this behavior. Plus, if the egg developer isn't participating in a discussion on this, why should that distribution method even be used? setuptools is the de-facto system for python distributions today. SA's presence within this system is already deeply embedded into the installation system of other products like turbogears and pylons. i think if a concerted effort to raise these issues on distutils-sig were made (there hasnt been), changes would be made. --~--~-~--~~~---~--~~ 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 existing structure of a database
On Jun 4, 2007, at 2:31 PM, nathan harmston wrote: Hi, I m currently playing with using sqlalchemy to map a pre-existing database to objects. I ve had a quick look through the docs and not being able to find anything about this. Does SQLAlchemy support introspection (if thats the right word, probably not) into existing databases, if so how? If not is there any advice you have to actually use SQLAlchemy in such a way. you want to use the autoload=True flag on Table. see the docs on Database Metadata. --~--~-~--~~~---~--~~ 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
On 6/4/07, Rick Morrison [EMAIL PROTECTED] wrote: The use of scalar() here seems out of place with both the common CS usage of the word (e.g. scalar == single-valued), and the use of scalar() in the SQL layer. Single row results in the ORM are rows, not a single datatype. It's another potential point of confusion, like the ORM .select() is/was. I would say drop scalar() in the ORM namespace, and for single-row results, use .first() -- returns first row .one() -- returns first row, raise exception if more than one result What if there are zero rows? Return None or raise an exception? I find the former useful enough, but I imagine some people prefer the latter. -- Mike Orr [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] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
That's one of the reasons I prefer to treat Query() like an iterator and roll my own first-order functions like first() etc. -- see my other message regarding this. On 6/4/07, Mike Orr [EMAIL PROTECTED] wrote: On 6/4/07, Rick Morrison [EMAIL PROTECTED] wrote: The use of scalar() here seems out of place with both the common CS usage of the word (e.g. scalar == single-valued), and the use of scalar() in the SQL layer. Single row results in the ORM are rows, not a single datatype. It's another potential point of confusion, like the ORM .select() is/was. I would say drop scalar() in the ORM namespace, and for single-row results, use .first() -- returns first row .one() -- returns first row, raise exception if more than one result What if there are zero rows? Return None or raise an exception? I find the former useful enough, but I imagine some people prefer the latter. -- Mike Orr [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] Re: new setuptools vs local SA copy
On 6/4/07, Paul Kippes [EMAIL PROTECTED] wrote: I found the thread about PYTHONPATH--interesting. It does seem that eggs are not only preventing the expected behavior, but they are also preventing the documented behavior. However, I don't think that using eggs is the best choice for a fast progressing library like SQLAlchemy--especially with this behavior. Plus, if the egg developer isn't participating in a discussion on this, why should that distribution method even be used? Are you talking about this thread? http://mail.python.org/pipermail/distutils-sig/2007-May/007513.html I'm not sure that this is especially relevant to SQLAlchemy per se. Python has a language-wide problem in that: 1) Setuptools has become a de-facto standard but is not bundled with Python, forcing users to find and and install ez_setup.py. Users also have to do tricks with their site.py to get a local egg directory separate from site-packages, or use workingenv.py or Virtual Python. People who aren't Python programmers but just want to run an application (e.g., sysadmins) don't understand why they should have to do this -- it seems like a grave defect in the language and it turns them off from Python. 2) The distutils code is apparently very patched up and in need of a rewrite before setuptools is integrated, but there are no programmer volunteers to do it. 3) A few people don't like the setuptools approach and do not want it in the standard library. 4) Setuptools does not have an uninstall option or clean up old bin/ scripts. You can have two versions of an egg installed simultaneously but only one set of bin/ scripts, the latest-installed ones overwriting the previous. Over time you end up with a version mess and have to start again with a fresh library directory to clean it up, plus cleaning out the bin/ directory by hand. -- Mike Orr [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] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
On Jun 4, 3:11 pm, Mike Orr [EMAIL PROTECTED] wrote: .first() -- returns first row .one() -- returns first row, raise exception if more than one result What if there are zero rows? Return None or raise an exception? I find the former useful enough, but I imagine some people prefer the latter. one() raises an exception if not exactly one 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: new setuptools vs local SA copy
On Jun 4, 2007, at 3:35 PM, Mike Orr wrote: On 6/4/07, Paul Kippes [EMAIL PROTECTED] wrote: I found the thread about PYTHONPATH--interesting. It does seem that eggs are not only preventing the expected behavior, but they are also preventing the documented behavior. However, I don't think that using eggs is the best choice for a fast progressing library like SQLAlchemy--especially with this behavior. Plus, if the egg developer isn't participating in a discussion on this, why should that distribution method even be used? Are you talking about this thread? http://mail.python.org/pipermail/distutils-sig/2007-May/007513.html FTR, my official this should be changed email is at: http://mail.python.org/pipermail/distutils-sig/2006-July/006492.html which was left unanswered by Eby. just had one response from someone who agrees with me. it all relies on this question: I cant think of a possible scenario where a path would explicitly exist in PYTHONPATH, non-egg or egg, where the user would still like the system-wide installation to take precedence, regardless of versioning or anything else. Otherwise why put the path on PYTHONPATH ? its also very easy to implement, as on my system where I just shoved an extra .pth file to make it happen. if it is in fact the case that setuptools thinks PYTHONPATH means something totally different for its purposes, which i think is Eby's take on this, it shouldnt be using PYTHONPATH for that purpose - it should use some other variable. --~--~-~--~~~---~--~~ 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 existing structure of a database
What kind of overhead is associated with using the autoload flag? What kind of overhead would be associated with this over a network? (with a remote database). Is there a way to dump the structure of a database to a file and import this as a kind of module? Thanks Nathan --~--~-~--~~~---~--~~ 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 existing structure of a database
On 6/4/07, nathan harmston [EMAIL PROTECTED] wrote: What kind of overhead is associated with using the autoload flag? What kind of overhead would be associated with this over a network? (with a remote database). Is there a way to dump the structure of a database to a file and import this as a kind of module? I've had to do this a few times. What I do is I use the autoload once to create the table objects, and then just print'em on the python console. That gives a fairly good starting point that can be copied to a file and fixed up. Arnar --~--~-~--~~~---~--~~ 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 existing structure of a database
Michael Bayer wrote:
On Jun 4, 2007, at 6:47 PM, nathan harmston wrote:
What kind of overhead is associated with using the autoload flag?
What kind of overhead would be associated with this over a network?
(with a remote database). Is there a way to dump the structure of
a database to a file and import this as a kind of module?
no, theres no built in way to dump to a file and re-import. youd have
to write that yourself. please contribute it if you do so since we
get this question once a month,
I would recommed using the AutoCode script from
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode rather then
autoload directly. It just gives that little bit more control by
tweaking the autocode.py script, but also the convenience of autoload.
However, if you still want to use autoload, this is what I use to do
with autoload metadata. Remember to delete the dump file when you change
your database.
==
from sqlalchemy import pool, create_engine, BoundMetaData, MetaData
import psycopg2 as psycopg
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode
psycopg = pool.manage(psycopg, pool_size=10)
engine =
create_engine('postgres://username:[EMAIL PROTECTED]/database',
strategy='threadlocal')
metadata = BoundMetaData(engine)
if os.path.exists('dump.metadata'):
meta_load = pickle.load(file('dump.metadata', 'rb'))
for table in meta_load.tables.values():
table.tometadata(meta)
branch_table = Table('branch', meta, autoload=True)
# define the reset of your tables.
if not os.path.exists('dump.metadata'):
meta_save = MetaData()
for table in meta.tables.values():
table.tometadata(meta_save)
pickle.dump(meta_save, file('dump.metadata', 'wb'))
--~--~-~--~~~---~--~~
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 and running custom sql.
wongobongo wrote:
You mean something like this?
K
---
# Running a SQL Statement using SQLAlchemy
from sqlalchemy import *
dsn = 'mysql://root:[EMAIL PROTECTED]:3306/tester'
# Create a database engine
db = create_engine(dsn)
# Do some SQL
text(INSERT INTO insertable SELECT NULL,txt FROM test_table;,
engine=db).execute()
Or directly off the engine itself, including bind parameters.
curs = db.text(select * from my_table where col1 = :col1_value and col2
= :col2_value order by col1 limit 10 offset
20).execute(col1_value=hello, col2_value=world)
rs = curs.fetchall()
Even better is to use the sqlalchemy sql constructs. It may seem more
long winded for short queries but complex queries are much easier to
express. Especially once the generative selects are introduced :-)
from sqlalchemy import select, and_
my_table = Table('my_table', metadata, )
col = my_table.c
criteria = and_(col.col1 == hello, col.col2 == world)
s = select([my_table], criteria, orderby=[col.col1], limit=10, offset=20)
curs = s.execute()
rs = curs.fetchal()
This will give you lists of ResultProxy (where each element can have
it's columns accessed as element[0], element['col1'], or element.col1)
or you can create a query object and get it to return instances of your
model object (once you map them of course).
eg using select above
query = session.query(MyClass)
rs = query.instances(s.execute())
Huy
On May 27, 1:18 pm, SamDonaldson [EMAIL PROTECTED] wrote:
I'd like to be able to run a custom query without having to use any of
the ORM stuff. I'd like to pass in a sql query string to some API
that returns back to me instances (it'd be great), or tuples. How do
I do 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
-~--~~~~--~~--~--~---
