[sqlalchemy] Re: Differentiate ANSIIdentifierPreparer.format_column select clause from where clause??
It's not about case in the resultproxy, it's about case-insensitive for server-side compare, such as in the where clause and when ordering results. comparisonslike literal text injected into the SQL? why not use bind parameters ?there are cases where literal text should not be quoted, i.e. if you hardcoded where x = 'SomeString'..but its possible that the quoting is too eager right now due to some recent issues. in any case need an example what you mean. It's not just the literal text or the bind param. It is how the server compares character based data. In MySQL, MS-SQL, Sybase -- case does not matter. In Postgres, Oracle and DB2 it does. DB2 and Oracle (since version 10 I think) have some server-side settings to help, but Postgres does not. Assuming I have a table named people: fname lname = Troy Kruthoff albert einstein and the query: select fname from people order by fname asc in MySQL: - albert - Troy in Postgres: - Troy - albert and the query: select fname from people where fname='troy' in MySQL: - Troy in Postgres: - [no records found/returned because Troy!=troy] So, we need to tell postgres: select fname from people order by lower(fname) asc and select fname from people where lower(fname)='troy' Notice I am not needing to lower (or upper) the column as part of the select list, because I want the data to return to the app as it exists in the db server. Does this help? Maybe I can buy you a beer at PyCon and we can talk it through? Thanks, Troy --~--~-~--~~~---~--~~ 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 and Elixir at FOSDEM 2007
Just to let everyone who might be interested know, I'll be giving a talk about SQLAlchemy and Elixir at the FOSDEM 2007. It'll be in the python devroom. -- 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: Differentiate ANSIIdentifierPreparer.format_column select clause from where clause??
On Feb 19, 2007, at 4:52 AM, Troy wrote: It's not just the literal text or the bind param. It is how the server compares character based data. In MySQL, MS-SQL, Sybase -- case does not matter. In Postgres, Oracle and DB2 it does. DB2 and Oracle (since version 10 I think) have some server-side settings to help, but Postgres does not. OK totally different issue. MySQL does have a case-sensitivity setting using COLLATE. not sure what MS-SQL has and we dont yet have sybase support. we've had people report the whole lower() issue in the past and im not sure that should be automatic within SA.You can just explicitly say lower() across the board for case-insensitive comparisons. if SA implemented some operator for this (which could be doable, such as table.c.compare_insensitive(foo)), it would still have to use lower() for every dialect since in particular for MySQL we have no idea what the COLLATE rules are on the given table. --~--~-~--~~~---~--~~ 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] Asynchronous SQLAlchemy--Anybody using Twisted, sAsync?
Howdy Group, I'm playing out with a few things now and I wanted to see if anyone else has used SQLAlchemy in an asynchronous manner? For example, you could create a service which responded to asynchronous requests for data, and could be used by a web client, desktop client, other types of clients, etc. The sAsync project at http://foss.eepatents.com/sAsync/ seems ideally suited for this but I haven't seen any comments about it here. Thanks, Matt --~--~-~--~~~---~--~~ 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] Asynchronous SQLAlchemy--Anybody using Twisted, sAsync?
I use sasync in production with twisted and I'm quite happy with it. My application reads and writes a lot and I haven't run into any major problem. Stéphane - Message d'origine De : Matt Culbreth [EMAIL PROTECTED] À : sqlalchemy sqlalchemy@googlegroups.com Envoyé le : Lundi, 19 Février 2007, 19h11mn 35s Objet : [sqlalchemy] Asynchronous SQLAlchemy--Anybody using Twisted, sAsync? Howdy Group, I'm playing out with a few things now and I wanted to see if anyone else has used SQLAlchemy in an asynchronous manner? For example, you could create a service which responded to asynchronous requests for data, and could be used by a web client, desktop client, other types of clients, etc. The sAsync project at http://foss.eepatents.com/sAsync/ seems ideally suited for this but I haven't seen any comments about it here. Thanks, Matt ___ Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses http://fr.answers.yahoo.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: Three questions from first usage
On Feb 19, 2007, at 12:16 AM, David Bolen wrote: 1. I have a simple add operation which does the basic object allocation, session.save() and session.flush(). The issue was when I tried to add an object with a SQL violation (duplicate value on unique column). No problems with the SQLError exception, and rollback in the session transaction. But what caught me a bit by surprise was the fact that the bad object was still in the session. So a subsequent attempt to add a new (valid) object through the same session failed on the earlier object - still apparently in the UOW scope - before even getting to the valid object. This seemed common enough that I expected to find easy references to it, but I've had trouble finding information on it. My fix was to trap the exception and explicitly session.expunge() the faulty object. It works, but seems a little fragile - I'm not sure how this would extrapolate to a larger UOW (e.g., how do I discover the object at fault), or if I need to be doing this in a blanket except: clause - e.g., are there other sorts of failures that can leave a persistently failed object in the identity map. I saw a prior note on this list about flush exceptions sometimes occurring instead of SQLError and am not sure if conditions resulting in the Flush version of the exception could cause the same problem. best practice when an error during flush() occurs is to clear the session using session.clear() and start over. this is also the advice of the hibernate camp which is what we've based our Session on. SA does not make any assumptions whatsoever what youd like to do with the objects in your session if a flush failed. 2. I'm unclear what (if anything) to expect with respect to my object instances in memory post-save for primary key or default value columns. My initial expectation - particularly with the warning about flush() in the docs and related objects - is that the instance I had saved() to the session would be untouched and I'd have to retrieve it from the database again. But in a quick test I saw the primary key and defaults seemed to have information after the flush() without any further steps. column-based attributes get populated with newly generated primary key, default, and foreign key values. object-based attributes (those configured via relation()) do not change. a related doc is at http://www.sqlalchemy.org/docs/ unitofwork.myt#unitofwork_api_flush_whatis although i am amazed that it is not more explicit about the column-based attributes being populated. Of course, then it bit me later when I found that my DateTime columns (with a default of func.current_timestamp()) of my instances had string values and not datetime as I expected. use func.current_timestamp(type=DateTime) 3. I was implementing an archive of deleted objects, sharing the object definition with two mappers - one primary and a second to a deleted table with a different entity name (basically an audit table for deleted instances). I was excited to see the entity_name availability with alternate mappers, and figured it would be an elegant way to handle the archive. I hoped there would be an easy way to delete an object through the primary mapper and then save it to the alternate mapper, while using the same object instance (to avoid having to manually duplicate the object). But SQLAlchemy was very good at knowing I was playing with an instance that it already had associated with the first mapper. In the end, rather than poke too much at artifically clearing SA state fields, I just copied my object (all non-_ prefixed attributes) to a new instance, and saved that to the alternate entity_name. you might be the first person to have actually used the entity_name feature. Anyway, I was wondering if there was a clean way to use a single object instance in this way, or even a supported way of clearing an instance so SA doesn't think it used to be associated somewhere. havent put much thought into it, the easiest way is to just change the _entity_name attribute to whatever, and if you remove _instance_key then SA will see the instance as pending or transient (meaning it will do an INSERT next time it tries to persist it). just play around with dir(myobject) a little to see what SA sticks on there. --~--~-~--~~~---~--~~ 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: Differentiate ANSIIdentifierPreparer.format_column select clause from where clause??
OK totally different issue. MySQL does have a case-sensitivity setting using COLLATE. not sure what MS-SQL has and we dont yet have sybase support. we've had people report the whole lower() issue in the past and im not sure that should be automatic within SA.You can just explicitly say lower() across the board for case-insensitive comparisons. if SA implemented some operator for this (which could be doable, such as table.c.compare_insensitive(foo)), it would still have to use lower() for every dialect since in particular for MySQL we have no idea what the COLLATE rules are on the given table. Even though MySQL allows the setting of COLLATE, it does not support functional indexes, so if your code explicitly calls lower you technically now have code that will work for both MySQL and Postgres, but MySQL is going to take a big performance hit and perform table scans regardless of any created indexes. Realistically, I can't see anyone setting MySQL collate rules to case-sensitive without the support of functional indexes, which MySQL does not have. If Postgres allowed case-insensitive collation all would be good. But it does not, and I've never seen anyone make MySQL case-sensitive and deal with the headaches (nor should they, I think case-sensitivity in a database server is a throw-back), for this reason I think it should somehow be approachable from the dialect to deal with how different db servers handle collation support. In simple terms, if the Postgres dialect supported compare_insensitive=True|False|[upper|lower]? then apps written in sqlalchemy easily support Postgres and MySQL with the same code in out- of-the-box configurations. But even if it didn't, if there was a way to override the default postgres dialect I'd be a happy camper. Infact, that is exactly what I have done. I added a dialect called lowergres, but I'm stuck because I can not seem to find the appropriate hook to alter the column to a func.lower when the column is not part of the selected column list. format_column looks to me like the right place to do it without converting a column to a function, but the column object in that function has no context as to where it lies in the sql statement. I'm curious as to others experiences with writing an app that supports both Postgres and MySQL with sqlalchemy, because if someone else is doing this then I must be missing something, or maybe not. At first, our unit tests all passed, then when we added real world data with mixed case, tests started to fail on everything doing sorts and where's on character data. How about a Pepsi (at PyCon)? Troy --~--~-~--~~~---~--~~ 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: Asynchronous SQLAlchemy--Anybody using Twisted, sAsync?
We did a quick proof-of-concept with it and it appeared to work as advertised :) Troy On Feb 19, 10:11 am, Matt Culbreth [EMAIL PROTECTED] wrote: Howdy Group, I'm playing out with a few things now and I wanted to see if anyone else has used SQLAlchemy in an asynchronous manner? For example, you could create a service which responded to asynchronous requests for data, and could be used by a web client, desktop client, other types of clients, etc. The sAsync project athttp://foss.eepatents.com/sAsync/seems ideally suited for this but I haven't seen any comments about it here. Thanks, Matt --~--~-~--~~~---~--~~ 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: Getting list of referencing table
I believe this is what your looking for (from the sqlalchemy recipes section of the wiki) http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DependentTables Troy On Feb 19, 12:11 am, Andreas Jung [EMAIL PROTECTED] wrote: Is there a way to get a list of tables or table names that reference a given table through a foreign key relationship? Andreas application_pgp-signature_part 1KDownload --~--~-~--~~~---~--~~ 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: Differentiate ANSIIdentifierPreparer.format_column select clause from where clause??
On Feb 19, 2:32 pm, Troy [EMAIL PROTECTED] wrote: Even though MySQL allows the setting of COLLATE, it does not support functional indexes, so if your code explicitly calls lower you technically now have code that will work for both MySQL and Postgres, but MySQL is going to take a big performance hit and perform table scans regardless of any created indexes. Realistically, I can't see anyone setting MySQL collate rules to case-sensitive without the support of functional indexes, which MySQL does not have. right...so some explicitness is required, somewhere (since SA cant just put lower() across the board). In simple terms, if the Postgres dialect supported compare_insensitive=True|False|[upper|lower]? on a Column-by-Column or even per-operation context is probably more useful. sqlalchemy easily support Postgres and MySQL with the same code in out- of-the-box configurations. But even if it didn't, if there was a way to override the default postgres dialect I'd be a happy camper. Infact, that is exactly what I have done. I added a dialect called lowergres, but I'm stuck because I can not seem to find the appropriate hook to alter the column to a func.lower when the column is not part of the selected column list. format_column looks to me like the right place to do it without converting a column to a function, but the column object in that function has no context as to where it lies in the sql statement. for this approach, id advise setting state on the compiler when you are processing the column clause of a SELECT (or dealing with insert/ update column lists too), vs. when you are processing WHERE, ORDER BY, etc. that flag can be used to determine where youre traversing. the compiler knows the context since its the one generating the statement. a more accurate way to do is to wrap the Column itself, as it goes into a comparison operation or ORDER BY, in a new construct such as CaseInsensitive...heres some pseudo-ish code based on the __eq__() method you see in sqlalchemy/sql.py _CompareMixin (assume self is a Column): def __eq__(self, other): return _BooleanExpression(CaseInsensitive(self), other, '==') Better yet its configurable on Column: def __eq__(self, other): if self.case_insensitive_compare: return _BooleanExpression(CaseInsensitive(self), other, '==') else: return _BooleanExpression(self, other, '==') CaseInsensitive looks a lot like _Function and is just: class CaseInsensitive(_CalculatedClause): def __init__(self, target): self.target = target def accept_visitor(self, visitor): self.target.accept_visitor(visitor) visitor.visit_case_insensitive(self) ansicompiler provides the string representation of the underlying target with no modification: def visit_case_insensitive(self, object): self.strings[object] = self.strings[object.target] postgres compiler with case_insensitive provides it as: def visit_case_insensitive(self, object): self.strings[object] = lower(%s) % self.strings[object.target] other dialects can have whatever flags to turn on/off the lower() wrapping as well. what we're really talking about here is a func.lower() that has special meaning to the compiler, i.e. that it should be conditionally applied based on dialect flags. i think the flag on Column to have the wrapper applied might be pretty slick. I'm curious as to others experiences with writing an app that supports both Postgres and MySQL with sqlalchemy, because if someone else is doing this then I must be missing something, or maybe not. At first, our unit tests all passed, then when we added real world data with mixed case, tests started to fail on everything doing sorts and where's on character data. yeah i dont think anyone has gotten too far with this issue, also ive no idea what the giant universe of Hibernate users do either (i think there just arent db-platform-neutral J2EE apps). How about a Pepsi (at PyCon)? sure thing ! --~--~-~--~~~---~--~~ 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: An update and some questions
Hi, 4) I'm considering submitting a patch to make _CompoundClause.compare tell you if the clause has the same semantics as the comparison (at im curious how this function would work for more complex situations, i.e. with more operators, more levels of nesting, etc. without re- implementing a full SQL expression parser. Well, with a change to _BinaryClause as well, it can work in 99% of situations. You have a set of operators that are known to be commutative and if the operator is commutative, you do an order-insensitive compare. One case that would be a real pain: is x 10 equal to 10 x still curious as well why this is needed. Ok, the issue was noticed due to a unit test failure (engine.reflection.ReflectionTest.testcompositefk) where the test checks if the condition created by the join matches the condition it knows. The test was incorrectly failing because the order is different. Your call - do I just get the test working the simple way, or it this semantic compare worth doing? 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] Re: An update and some questions
On Feb 19, 2007, at 3:39 PM, Paul Johnston wrote: Ok, the issue was noticed due to a unit test failure (engine.reflection.ReflectionTest.testcompositefk) where the test checks if the condition created by the join matches the condition it knows. The test was incorrectly failing because the order is different. Your call - do I just get the test working the simple way, or it this semantic compare worth doing? ah, the order is different. how come ? doesnt MS-SQL return the columns from a table in the order they were given in the CREATE ? id think thats pretty crucial. if two pairs of Table objects have their columns in the same order, then the onclause generated for each pair should calculate to be the same, right ? id rather not get into the semantic compare thing unless we're prepared to make clause comparison a real feature, build out the unit tests, etc. doesnt seem worth it for a single unit testid rather change the unit test to just explicitly look for the correct foreign keys instead of just comparing join clauses. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
