[sqlalchemy] Re: Support for tuple expressions?
Thanks Michael. I won't be using or supporting SQL Server, so I'm quite fine with that. I guess the way forward would be to install SA in develop mode and make the changes directly? Excuse my ignorance, but why would you do it that way? Since I don't want to change any of the functionality of _Tuple I wouldn't think a subclass would be necessary. Also, since comparison are already captured by _CompareMixin and represented as _BinaryExpression, I would have thought that intercepting the compilation of _BinaryExpression in the sqlite dialect would be the way to go. --Buck On Apr 19, 6:51 am, Michael Bayer mike...@zzzcomputing.com wrote: there's a tuple_() operator: http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=tup... the object returns is _Tuple. if you wanted fancy per-dialect expression behavior, mmm tricky, you'd need to subclass _Tuple (or write a new object), intercept comparisons like __eq__(), then return *another* construct that represents a Tuple Comparison, and that object would need per-dialect compilation rules. or you could lobby the sqlite folks to add support for the construct in the first place. though i doubt SQL Server has it either. On Apr 18, 2011, at 4:57 PM, bukzor wrote: SQL-92 defines a row value constructor expression like (1,2,3) which looks and behaves exactly like a Python tuple, as far as I can tell. These are implemented correctly in mysql at least, and I believe PostgreSQL and Oracle as well, although I don't have access to those systems. What would be the best way to deal with this type of value in SQLAlchemy? Should I create a RowValue class which can be visited by the various dialects? If I wanted to provide emulation for dialects which don't directly support this standard, what would be the way to go? For example, I'd like to be able to expand RowValue((1,2)) = RowValue(colA, colB) to 1 colA or (1=colA and 2 = ColB) under sqlite. --Buck -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Support for tuple expressions?
SQL-92 defines a row value constructor expression like (1,2,3) which looks and behaves exactly like a Python tuple, as far as I can tell. These are implemented correctly in mysql at least, and I believe PostgreSQL and Oracle as well, although I don't have access to those systems. What would be the best way to deal with this type of value in SQLAlchemy? Should I create a RowValue class which can be visited by the various dialects? If I wanted to provide emulation for dialects which don't directly support this standard, what would be the way to go? For example, I'd like to be able to expand RowValue((1,2)) = RowValue(colA, colB) to 1 colA or (1=colA and 2 = ColB) under sqlite. --Buck -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Insert.params() broken?
On Nov 20, 7:39 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Nov 20, 2008, at 9:05 PM, bukzor wrote:
Would it make sense to rename Insert.values to Insert.params? Or make
Insert.params call Insert.values.
It seems quite strange for an object to have functions that aren't
usable...
its an entirely different function. If you said this:
t = table.update().where(table.c.col1==bindparam('x', value=5))
Saying this:
t2 = t.params('x', 12)
would in theory produce (with bind values inlined) UPDATE table WHERE
x=12
while saying this:
t3 = t.values('x', 12)
would produce UPDATE TABLE SET x=12 WHERE x=5
for an INSERT, the difference would apply to bind params that are
perhaps embedded in subqueries within the VALUES clause.
Thanks for explaining. My problem was that I thought the insert values
would be implemented as bindparams.
--~--~-~--~~~---~--~~
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.params() broken?
On Nov 20, 6:20 pm, Empty [EMAIL PROTECTED] wrote: Different, but related question: How do I print out the statement in copy-paste-able format? More specifically, how do i get an ordered list of bound values out of the statement? This code does what I want, but it's really clunky and fragile: def print_statement(stmt): s = str(stmt).replace(?, %s) from re import search args = search(\((.*?)\), s).group(1).split(, ) print s % tuple(repr(stmt.params[arg]) for arg in args) There's also a UsageRecipe for this: http://www.sqlalchemy.org/trac/wiki/DebugInlineParams Michael Thanks! That was exactly what I was looking for. I tried to dig though the dialects and compilers and everything to figure this out, but after a few hours, no success. It would be great if we had a sqlalchemy utility to print out our statements in an expanded way. Yes, it's unsafe, but it's pretty essential for debugging. --~--~-~--~~~---~--~~ 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] Insert.params() broken?
Sorry for double posting. I pressed Send before I was ready...
Please let me know if I'm doing something wrong here:
[code]
metadata = MetaData('sqlite:///first.sqlite')
table = Table('my_table', metadata, Column('text', Unicode(16)))
stmt = table.insert()
parameters=dict(text='Hello, World!')
print stmt.compile().params
print stmt.params(parameters).compile().params
[/code]
The first print gives:
{'text': None}
The second crashes with:
File /tools/aticad/1.0/external/python-2.4.1/lib/python2.4/site-
packages/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/sql/expression.py,
line 3515, in _copy_internals
self.parameters = self.parameters.copy()
AttributeError: 'NoneType' object has no attribute 'copy'
After doing this patch:
[patch]
--- /tool/tools/aticad/1.0/external/python-2.4.1/lib/python2.4/site-
packages/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/sql/
expression.py.orig2008-11-20 15:13:38.080922000 -0800
+++ /tool/tools/aticad/1.0/external/python-2.4.1/lib/python2.4/site-
packages/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/sql/expression.py
2008-11-20 15:13:57.008589000 -0800
@@ -3511,8 +3511,8 @@
return ()
def _copy_internals(self, clone=_clone):
-# TODO: coverage
-self.parameters = self.parameters.copy()
+if self.parameters is not None:
+self.parameters = self.parameters.copy()
@_generative
def prefix_with(self, clause):
[/patch]
I get {'text': None} for the second print too.
I took this directly from the docs at
sqlalchemy.sql.expression.Insert.params.
Should I use 0.4 instead? Is it more stable / less broken?
--~--~-~--~~~---~--~~
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] Insert.params() broken?
I expect I'm doing this wrong, but it seems broken to me. Please let me know. Here's what I'm doing: stmt = metadata.tables['tf_user'].insert() parameters=dict(id=1,user_name='bgolemon',password='badpass',display_name='Buck Golemon',created=None) print stmt.compile().params print stmt.params(parameters).compile().params --~--~-~--~~~---~--~~ 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.params() broken?
When you say generative, do you mean it returns a new object, as opposed to in-place changes? Would it make sense to rename Insert.values to Insert.params? Or make Insert.params call Insert.values. It seems quite strange for an object to have functions that aren't usable... Different, but related question: How do I print out the statement in copy-paste-able format? More specifically, how do i get an ordered list of bound values out of the statement? This code does what I want, but it's really clunky and fragile: def print_statement(stmt): s = str(stmt).replace(?, %s) from re import search args = search(\((.*?)\), s).group(1).split(, ) print s % tuple(repr(stmt.params[arg]) for arg in args) On Nov 20, 5:29 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 20, 2008, at 6:16 PM, bukzor wrote: The second crashes with: File /tools/aticad/1.0/external/python-2.4.1/lib/python2.4/site- packages/SQLAlchemy-0.5.0rc4-py2.4.egg/sqlalchemy/sql/expression.py, line 3515, in _copy_internals self.parameters = self.parameters.copy() AttributeError: 'NoneType' object has no attribute 'copy' insert.params() was not the intended usage of the params() feature. In r5313 I've added NotImplementedError when params() is called on all Insert/Update/Delete constructs, since this feature is intended for generative modification of select() statements and other clause fragments. In this case you almost certainly mean to say insert.values(**params). The feature can be implemented for I/U/D but would require test coverage, but also is concerning since the params() method modifies all clause elements and is much more expensive than just calling values(). I'd be concerned that people would inadvertently use it without realizing that they really want to be calling values(). Should I use 0.4 instead? Is it more stable / less broken? there is no difference in 0.4 with regards to this behavior (except that in 0.5, now its covered). 0.5 is overall much more stable/ performant than 0.4 and is very close to final release. --~--~-~--~~~---~--~~ 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: Beginner: printing out queries
Why is this so hard?? In SQLObject, this problem consists entirely of: print queryobj Bye all, --Buck On Jun 25, 11:23 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 25, 2008, at 2:14 PM, bukzor wrote: Thanks. Trying to do this in 0.5, it seems someone deleted the Query.compile() method without updating the rest of the code: Traceback (most recent call last): File ./test1.py, line 139, in ? try: exit(main(*argv)) File ./test1.py, line 121, in main print_query(q) File ./test1.py, line 20, in print_query print str(q) File /python-2.4.1/lib/python2.4/site-packages/ SQLAlchemy-0.5.0beta1-py2.4.egg/sqlalchemy/orm/query.py, line 1448, in __str__ return str(self.compile()) AttributeError: 'Query' object has no attribute 'compile' its been fixed in trunk. Keep an eye onhttp://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/CHANGES . beta2 is out very soon (like, this week). Reverting to 0.4, there are other problems. Statement.params is a function, so I added some ()'s, but it just returns the same query again. Statement.positiontup doesn't exist, and the string stmt doesn't have formatting to make use of python's % operator. my code example used the 0.5 APIs, but in all cases you have to get the compiled object first. This is described in the tutorials for both 0.4 and 0.5, such as athttp://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_insert . --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
On Jun 25, 10:50 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 25, 2008, at 1:24 PM, bukzor wrote: Thanks for that versioning overview. Sorry for changing the topic (Should I make a separate post?), but is there a way to make the joins more automatic? I'd like to just specify some filter against table A and another against table B and have the system join them, even if the join needs to go through C or D. Of course the results would be undefined if there was more than one path between A and B, but this is not the case in my database and I'm sure a good subset of most databases. Will I need to roll this myself? Would people appreciate it if I added this functionality to the mapper class? If so, what code would you suggest editing? I just need some representation of the database as a graph. While you're of course free to create your own Query subclass which implements a graph traversal of relations to achieve this effect, this actual functionality was long ago removed (in the form of the old join_by() method), since it amounts to guessing; issues were apparent almost immedately after its introduction and it was soon deprecated. It requires an expensive graph traversal each time it's used, and leads to applications that silently, randomly fail as soon as a new foreign key path between the two target tables is added. It fits perfectly the kind of behavior that's targeted by explicit is better than implicit. Sorry for being a pest, but I've looked at the documentation and really can't figure this out. If a mapped class is a node of our graph, where do I find the edges, and how do I get to the next node. Alternatively, should I do this at the table/sql level rather than the class/orm level? How did you yourself learn this? Is there some other reference I'm overlooking? --Buck --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
On Jun 26, 8:29 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 26, 2008, at 11:12 AM, bukzor wrote: Sorry for being a pest, but I've looked at the documentation and really can't figure this out. If a mapped class is a node of our graph, where do I find the edges, and how do I get to the next node. the mapper has a method called iterate_properties which returns all MapperProperty objects it contains. Each PropertyLoader subclass represents a relation() to another mapper. so you can walk among relations as follows: recursive = set() def walk (cls): print cls:, cls if cls in recursive: return recursive.add(cls) mapper = class_mapper(cls) for prop in mapper.iterate_properties: if isinstance(prop, PropertyLoader): print key, prop.key walk(prop.mapper.class_) Alternatively, should I do this at the table/sql level rather than the class/orm level? it depends on what information you're interested in. the use case here seems to be joining among configured relation()s so the ORM level would be better. How did you yourself learn this? Is there some other reference I'm overlooking? heres the docs for every API mentioned above: http://www.sqlalchemy.org/docs/05/sqlalchemy_orm.html#docstrings_sqla...http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_mapper.html#docstrin...http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_interfaces.html#docs...http://www.sqlalchemy.org/docs/05/sqlalchemy_orm_properties.html#docs... The same documentation can be had by using pydoc modulename, i.e. pydoc sqlalchemy.orm.mapperlib I also think you should consider carefully if you truly need automatic, implicit joining across arbitrarily long paths. Its a feature we explicitly removed for its non-pythonicness and unpredictable behavior. Thanks so much for the help! I need it because the interface I'm exposing lets (advanced) users select filters against arbitrary fields in the database. From these filters I need to construct a sql query. The easy way is just to always join every table in the database, but this is infeasible because the size of the database would make this query very slow. So, I need to figure out some sort of smart auto-join method. I'll only define one path between each table, so the result will be deterministic. I'm open to suggestions if you see a better way. I'll let you know how it goes... --Buck --~--~-~--~~~---~--~~ 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: Beginner: query.join not cooperating
Thanks! That works, but is the line I quoted just wrong or outdated or
what?
This is what I'm talking about, under def join()
http://www.sqlalchemy.org/docs/04/sqlalchemy_orm_query.html#docstrings_sqlalchemy.orm.query_Query
By the way, is 0.4 the recommended version to use for a new project?
On Jun 24, 11:52 pm, Kyle Schaffrick [EMAIL PROTECTED] wrote:
On Tue, 24 Jun 2008 18:22:49 -0700 (PDT)
bukzor [EMAIL PROTECTED] wrote:
The Query.join() documentations says:
def join(self, prop, id=None, aliased=False, from_joinpoint=False)
'prop' may be one of:
* a class-mapped attribute, i.e. Houses.rooms
What exactly counts as class-mapped? I've set up a ForeignKey in my
Files table as well as a backref relation in my Projects table, but
none of these work:
print session.query(File).join(Project).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(Project.files).filter_by(project='mario',
tapeout='sa11').one()
print session.query(File).join(Project.id).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(File.project).filter_by(project='mario',
tapeout='sa11').one()
print
session.query(File).join(File.project_id).filter_by(project='mario',
tapeout='sa11').one()
They all fail with:
sqlalchemy.exceptions.InvalidRequestError: Mapper 'Mapper|File|files'
has no property 'sqlalchemy.orm.attributes.InstrumentedAttribute
object at 0x2a9a283650'
It seems like .join(Project) should 'just work' after the information
I put in the sytem, but I don't know much...
If there's some documentation that makes this clear, feel free to just
link it.
If you want more code, let me know what to copy/paste.
The ORM tutorial [1] is a little more instructive here, what join()
actually wants is a string with the name of the relation you put on (in
this case) File's mapper.
I'll assume it's something like
properties = {
...
'project': relation(Project),
...}
in which case you want join('project'). SA's rationale for doing it this
way is that you can do things like setting up multiple relations from
File to Project, and tweak the parameters on each relation to get
different behavior, without the joins being ambiguous. [2] has an
example of a setup like this.
Thanks!
--Buck
HTH
-Kyle
[1]http://sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins
[2]http://sqlalchemy.org/docs/04
/mappers.html#advdatamapping_relation_customjoin_multiplejoin
--~--~-~--~~~---~--~~
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: Beginner: printing out queries
Thanks. Trying to do this in 0.5, it seems someone deleted the Query.compile() method without updating the rest of the code: Traceback (most recent call last): File ./test1.py, line 139, in ? try: exit(main(*argv)) File ./test1.py, line 121, in main print_query(q) File ./test1.py, line 20, in print_query print str(q) File /python-2.4.1/lib/python2.4/site-packages/ SQLAlchemy-0.5.0beta1-py2.4.egg/sqlalchemy/orm/query.py, line 1448, in __str__ return str(self.compile()) AttributeError: 'Query' object has no attribute 'compile' Reverting to 0.4, there are other problems. Statement.params is a function, so I added some ()'s, but it just returns the same query again. Statement.positiontup doesn't exist, and the string stmt doesn't have formatting to make use of python's % operator. After about an hour of looking, I can't figure out how to get my scalars out of the query object. On Jun 25, 7:25 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 24, 2008, at 9:27 PM, bukzor wrote: Is there a way to print out the query as it would execute on the server? I'd like to copy/paste it into the server to get the 'explain' output, and the '%s' variables are very unhelpful here. the string output of str(statement) is what's actually sent to the client lib. In some cases, that is the actual string that goes to the server, such as cx_oracle, which receives the bind parameters separately within the network conversation. The fact that MySQLDB and psycopg2 do an in-client substitution of the string before passing on is an implementation artifact of those libraries. Feel free to construct the string yourself (this is specific to MySQL's bind param style): stmt = str(statement) compiled = statement.compile() params = compiled.params stmt = stmt % [params[k] for k in compiled.positiontup] I'd also like to turn off the 'alias and backtick-escape every column' default behavior if I can. we don't backtick every column. We quote case sensitive idenfitier names, if that's what you mean, where case sensitive is any identifier that is spelled out in MixedCase - this is required for the column to be properly recognized by the database. Use all lower case letters to indicate a case insensitive identifier. --~--~-~--~~~---~--~~ 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] Beginner: query.join not cooperating
The Query.join() documentations says: def join(self, prop, id=None, aliased=False, from_joinpoint=False) 'prop' may be one of: * a class-mapped attribute, i.e. Houses.rooms What exactly counts as class-mapped? I've set up a ForeignKey in my Files table as well as a backref relation in my Projects table, but none of these work: print session.query(File).join(Project).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(Project.files).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(Project.id).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(File.project).filter_by(project='mario', tapeout='sa11').one() print session.query(File).join(File.project_id).filter_by(project='mario', tapeout='sa11').one() They all fail with: sqlalchemy.exceptions.InvalidRequestError: Mapper 'Mapper|File|files' has no property 'sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x2a9a283650' It seems like .join(Project) should 'just work' after the information I put in the sytem, but I don't know much... If there's some documentation that makes this clear, feel free to just link it. If you want more code, let me know what to copy/paste. Thanks! --Buck --~--~-~--~~~---~--~~ 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] Beginner: printing out queries
Is there a way to print out the query as it would execute on the server? I'd like to copy/paste it into the server to get the 'explain' output, and the '%s' variables are very unhelpful here. I'd also like to turn off the 'alias and backtick-escape every column' default behavior if I can. Thanks! --Buck Hopefully I'll be able to *answer* questions someday soon... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
