[sqlalchemy] Re: Ordering results of a WHERE x in y query by y
import sqlalchemy def index_in(col, valuelist): return sqlalchemy.case([(value,idx) for idx,value in enumerate (valuelist)], value=col) session.query(C).filter(C.someattr.in_(valuelist)).order_by(index_in (C.someattr, valuelist)) Don't try to do this with huge lists of items. On Feb 25, 5:53 pm, Gunnlaugur Briem gunnlau...@gmail.com wrote: Hi all, having a x IN y query, with y supplied as input to the query: session.query(C).filter(C.someattr.in_(valuelist)) is there a way to tell SQLAlchemy to order the results according to valuelist? I.e. not by the natural order of someattr, but by the arbitrary order seen in valuelist? E.g.: session.add(C(someattr='Abigail')) session.add(C(someattr='Benjamin')) session.add(C(someattr='Carl')) valuelist = ['Benjamin', 'Abigail'] q = session.query(C).filter(C.someattr.in_(valuelist)).order_by(clever (valuelist)) q.all() # returns [C('Benjamin'), C('Abigail')] The solution I can think of is to create a temporary table with sess.execute('create temp table ...'), insert the valuelist into that temp table along with a sequence index, join to that temporary table and order by its index. Is there a less kludgy way? Regards, - Gulli --~--~-~--~~~---~--~~ 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: Creating SQL Expression
You can get the column object from the string, using: xyz.c[column_name] Do you mean that this: columns = [a,b,c] operators = ['+','-'] should result in xyz.c.a + xyz.c.b - xyz.c.c ? To do that, something like this works: columns = [a,b,c] operators = ['+','-'] colnames_and_ops = zip(columns[1:], operators) import operator opdict = { '+' : operator.add, '-' : operator.sub } columns = [xyz.c[name] for name in columns] operators = [opdict[name] for name in operators] ops_cols = zip(operators, columns[1:]) expr = reduce(lambda expr, op_col: op_col[0](expr, op_col[1]), ops_cols, columns[0]) Hope that helps, - Gulli On Thu, Feb 26, 2009 at 5:47 AM, Ashish Bhatia ashishsinghbha...@gmail.comwrote: This works fine But in the mine case columns = [a,b,c] operator = ['+','-'] comes in the list And it can go to n number. So while adding it creates a problem My approach looping on columns i append it in to the table and hence making the object i can join them with operator to form the a+b-c but in this a b c becomes string which is not desirable i want object here i hope this will clear the picture On Feb 25, 6:40 pm, Gunnlaugur Thor Briem gunnlau...@gmail.com wrote: You can sum the column objects directly, a+b, producing a sqlalchemy.sql.expression._BinaryExpression object. t = Table('bobloblaw', MetaData(), Column('a', Integer), Column('b', Integer), Column('c', Integer)) t.c.a + t.c.b # evaluates to sqlalchemy.sql.expression._BinaryExpression object at 0x1ec9ff0 print t.c.a + t.c.b # bobloblaw.a + bobloblaw.b On Wed, Feb 25, 2009 at 1:25 PM, Ashish Bhatia ashishsinghbha...@gmail.comwrote: The problem is still their. The two seprate list of columns = List of sqlalchem object operator = ['+'','-'] using join to join them will convert the columns object to string which is not desirable. Any way to fix this. On Feb 25, 3:54 pm, Ashish Bhatia ashishsinghbha...@gmail.com wrote: sorry its resolved and working On Feb 25, 12:20 pm, Ash ashishsinghbha...@gmail.com wrote: Hello , I am trying to make query like select (a+b) from xyz; to do this xyz = sqlalchemy.Table('xyz',metadata) a = sqlalchemy.Column('a', sqlalchemy.Integer) xyz.append_column(a) b = sqlalchemy.Column('b', sqlalchemy.Integer) xyz.append_column(b) column = [(a + b)] select = sqlalchemy.select(from_obj=xyz, columns=column,distinct=True) This works fine for me. Now when the columns a and b are dynamic (Enter by the user in form of string) and the operator too comes from user columns_list = ['a','b'] operator = ['+'] like this i get the input so i make the loop and make for both the columns something like this columns = [] for x in column_list : t = sqlalchemy.Column(x, sqlalchemy.Integer) xyz.append_column(a) columns.append(t) so now how to add + to make the quer run Thanks in the advance. --~--~-~--~~~---~--~~ 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: An instance's attributes lost while the other updated.
commit() by default expires all the attributes on all instances. See the documentation on Using the Session for details about this. Also a single Session should never be accessed by concurrent threads, referring to the previous email where you got a SQLite error regarding concurrency. On Feb 26, 2009, at 2:36 AM, 一首诗 wrote: A little more code to explain: Before commit, print u2 doesn't do trigger query. After commit, print u2 trigger a query, even if it has different primary key with u1. So this means : when u update one instance, the other instance of the same type will be in expired state. ## u1 = session.merge(u1) print -- print u2 session.commit() print -- print us ## On Feb 26, 2:17 pm, 一首诗 newpt...@gmail.com wrote: Hi all, I have 2 user instance in a session and after I have update one of it like this --- u = session.merge(u) session.commit session.refresh(u) --- Later, I found that another instance of the same class type in the session's attributes was cleared, and triggered a database query. (I found this because I am using sqlite, which forbids access the same database in more than one thread) I will make some debug to find out what happened. --~--~-~--~~~---~--~~ 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: What's the use of expunge?
On Feb 26, 2009, at 1:29 AM, 一首诗 wrote: The document says: Expunge removes an object from the Session, sending persistent instances to the detached state, and pending instances to the transient state: I hoped that if an instance was expunged from a session, I might use it safely as a container of some temp data without fearing trigger any database query. But I found that if an object is 'expunge'd , it's properties will not be accessible anymore. So, when should we use expunge? Or it is kept for sqlalchemy inner use only? you can reattach the object to a session later if you need it to be part of a transaction again.it can be used for caching scenarios, for example. But I agree most reasonable applications probably won't use expunge() very much. --~--~-~--~~~---~--~~ 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] SA ORM Tutorial: Query
Hello, A couple of questions on the tutorial: 1) Why .all() in 3rd (and some others) query code box, but not others? 2) Filter clause: Why '==' vs. '=' used in .filter() vs. .filter_by () 3) Common Filter Operators: Why or_(), and_(), in_(), but like(). (instead of like_() ) 4) Common Filter Operators: Is '!= None' the correct way to get a NOT NULL? (Not mentioned in tutorial) 5) Common Filter Operators: Using User.name.match raised an (OperationalError) unable to use function MATCH in the requested context... why? (using sqlite:memory is per tutorial) Thank you --~--~-~--~~~---~--~~ 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: SA ORM Tutorial: Query
On Feb 26, 2009, at 12:40 AM, a.fowler wrote: Hello, A couple of questions on the tutorial: 1) Why .all() in 3rd (and some others) query code box, but not others? .all() is essentially equivalent to list(query). Some of the examples already iterate the query, such as for x in query: print x, others don't - but the tutorial wants you to see the results. Ideally none of the examples would use all() since you generally iterate to get results. 2) Filter clause: Why '==' vs. '=' used in .filter() vs. .filter_by () the argument signature of filter() is: filter(argument) whereas that of filter_by() is: filter_by(**kwargs) the argument is a clause expression. SQLAlchemy generates clause expressions when you call overridden Python operators like == (__eq__()) and similar. The SQL expression tutorial spends a little more time on this concept. 3) Common Filter Operators: Why or_(), and_(), in_(), but like(). (instead of like_() ) or/and/in are Python reserved words, it's convention per pep8 to rename functions/variables with a trailing underscore if they conflict. 4) Common Filter Operators: Is '!= None' the correct way to get a NOT NULL? (Not mentioned in tutorial) that will work yes 5) Common Filter Operators: Using User.name.match raised an (OperationalError) unable to use function MATCH in the requested context... why? (using sqlite:memory is per tutorial) MATCH is not supported by every database, SQLite is apparently one of them. I'm a little surprised that operator found its way into the tutorial since I've never used it --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Ordering results of a WHERE x in y query by y
Thanks. But using a CASE clause becomes objectionable in exactly those cases where I would want to have the DB do the sorting — i.e. where the table is big enough that just sorting the result set in python code using array index (rows.sort(key=lambda row: values.index(row[0]))) would be a Bad Thing (since the key function is O(n)). But then, sorting on a reversed enumeration dict in python is algorithmically the same as the temp table approach. Something like: rows = session.query(...).all() value_to_index = dict((v,k) for (k,v) in enumerate(values)) rows.sort(key=lambda value: value_to_index[value]) so I suppose that's the cleanest solution here, unless one really prefers to make the DB do the sorting. I believe all of these approaches will gracefully handle the case where values are not unique (the order will just be arbitrary within each group with the same value). Regards, - Gulli On Thu, Feb 26, 2009 at 9:40 AM, Ants Aasma ants.aa...@gmail.com wrote: import sqlalchemy def index_in(col, valuelist): return sqlalchemy.case([(value,idx) for idx,value in enumerate (valuelist)], value=col) session.query(C).filter(C.someattr.in_(valuelist)).order_by(index_in (C.someattr, valuelist)) Don't try to do this with huge lists of items. On Feb 25, 5:53 pm, Gunnlaugur Briem gunnlau...@gmail.com wrote: Hi all, having a x IN y query, with y supplied as input to the query: session.query(C).filter(C.someattr.in_(valuelist)) is there a way to tell SQLAlchemy to order the results according to valuelist? I.e. not by the natural order of someattr, but by the arbitrary order seen in valuelist? E.g.: session.add(C(someattr='Abigail')) session.add(C(someattr='Benjamin')) session.add(C(someattr='Carl')) valuelist = ['Benjamin', 'Abigail'] q = session.query(C).filter(C.someattr.in_(valuelist)).order_by(clever (valuelist)) q.all() # returns [C('Benjamin'), C('Abigail')] The solution I can think of is to create a temporary table with sess.execute('create temp table ...'), insert the valuelist into that temp table along with a sequence index, join to that temporary table and order by its index. Is there a less kludgy way? Regards, - Gulli --~--~-~--~~~---~--~~ 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: What's the use of expunge?
Previously Michael Bayer wrote: On Feb 26, 2009, at 8:21 AM, Wichert Akkerman wrote: What happens if you do not call expunge on it, but pickle the object in a cache, load it later and then merge it? the state of the newly unpickled object, that is the current value of its mapped attributes, would be merged with the persistent version in the session. merge() will load the object from the database into an in-session, persistent instance before merging the external state. because your unpickled instance never actually enters the session, conflicts with its previous session or an already present in-session object are nicely avoided. I actually skip that and invalidate the cache entry on changes to prevent that SQL hit. What I meant was: does it matter if you never explicitly call expunge? Wichert. -- Wichert Akkerman wich...@wiggy.netIt is simple to make things. http://www.wiggy.net/ It is hard to make things simple. --~--~-~--~~~---~--~~ 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: What's the use of expunge?
On Feb 26, 2009, at 10:27 AM, Wichert Akkerman wrote: Previously Michael Bayer wrote: On Feb 26, 2009, at 8:21 AM, Wichert Akkerman wrote: What happens if you do not call expunge on it, but pickle the object in a cache, load it later and then merge it? the state of the newly unpickled object, that is the current value of its mapped attributes, would be merged with the persistent version in the session. merge() will load the object from the database into an in-session, persistent instance before merging the external state. because your unpickled instance never actually enters the session, conflicts with its previous session or an already present in-session object are nicely avoided. I actually skip that and invalidate the cache entry on changes to prevent that SQL hit. What I meant was: does it matter if you never explicitly call expunge? for pickling ? not at all. --~--~-~--~~~---~--~~ 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: SA ORM Tutorial: Query
A couple of questions on the tutorial: 1) Why .all() in 3rd (and some others) query code box, but not others? .all() is essentially equivalent to list(query). Some of the examples already iterate the query, such as for x in query: print x, others don't - but the tutorial wants you to see the results. Ideally none of the examples would use all() since you generally iterate to get results. Ah OK. Why or_(), and_(), in_(), but like(). (instead of like_() ) or/and/in are Python reserved words, it's convention per pep8 to rename functions/variables with a trailing underscore if they conflict. I see. 4) Common Filter Operators: Is '!= None' the correct way to get a NOT NULL? (Not mentioned in tutorial) that will work yes 5) Common Filter Operators: Using User.name.match raised an (OperationalError) unable to use function MATCH in the requested context... why? (using sqlite:memory is per tutorial) MATCH is not supported by every database, SQLite is apparently one of them. I'm a little surprised that operator found its way into the tutorial since I've never used it Thank you. A very clear explanation. :) I don't know if the docs team reads this, but here are a couple of comments: 1) Perhaps re-emphasize, in a few more places, SA's overloading of the logical operators. I had forgotten that by the time I got to the query filter section. 2) Since there are two alternative paths to take take in the class / table building part (mapper / declarative), it not clear what the prerequisites are for the 2nd path. To fix this, please show resetting the python interpreter after the mapper example, and re-importing all that is actually needed for the declarative version. 3) Mention the right way to filter by NOT NULL. 4) Mention the reservedword_() naming convention in passing. It should be known to Python programmers, but since this is a tutorial, it could be there are new Python users here as well. 5) Since the tutorial actually shows using SQLite, mention that the .match() directive is not supported by SQLite. I hope to finish the tutorial over the next few days, and may have more comments then. --~--~-~--~~~---~--~~ 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: SA ORM Tutorial: Query
On Feb 26, 2009, at 11:49 AM, a.fowler wrote: I don't know if the docs team reads this, but here are a couple of comments: I'm pretty much the documentation team :).but my time is very short these days, would you be interested in submitting a patch against the documentation source file ? It's written in ReST format and can be found in the distribution/current trunk in doc/build/ ormtutorial.rst . I can quickly review your patches and integrate them with the current documentation. the docs can also be built if you type make html in that directory. --~--~-~--~~~---~--~~ 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] Sybase DB-API driver uses @foo for placeholder names and expect parameter dict keys to be similarly named
I am doing some work on a SA engine for Sybase Adaptive Server Enterprise (ASE) on top of both pyodbc and the Sybase DB-API driver. The existing sybase engine for SA only works with Sybase Anywhere (ASA). There is a problem with named parameters with the Sybase driver in that the placeholders are prepended with an '@' *and* the execute method expects any dict paramers to have have keys that also have an '@'. I was able to get the placeholders generated correctly by subclassing the compiler. Any suggestions on how to get the execute method to work nicely or do I have to do some much around with copying parameters or monkeypatching the Sybase module with an implementation of execute that will work with 'ordinary' dictionaries? pjjH Error message is like this one .. note how the keys in the param dict do not start with an '@' There is no host variable corresponding to the one specified by the PARAM datastream. This means that this variable 'type_1' was not used in the preceding DECLARE CURSOR or SQL command. 'SELECT sysobjects.name \nFROM sysobjects \nWHERE sysobjects.name = @name_1 AND sysobjects.type = @type_1' {'type_1': 'U', 'name_1': 't1'} class SybaseSQLCompiler_Sybase(SybaseSQLCompiler): def __init__(self, *args, **params): super(SybaseSQLCompiler_Sybase, self).__init__(*args, **params) # This is a bit tedious: the Sybase module (i.e. the thing # that you get when you say 'import Sybase') names its # placeholders as '@foo'. if self.dialect.paramstyle == 'named': self.bindtemplate =@%(name)s --~--~-~--~~~---~--~~ 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: Sybase DB-API driver uses @foo for placeholder names and expect parameter dict keys to be similarly named
On Feb 26, 2009, at 3:55 PM, phrrn...@googlemail.com wrote: I am doing some work on a SA engine for Sybase Adaptive Server Enterprise (ASE) on top of both pyodbc and the Sybase DB-API driver. The existing sybase engine for SA only works with Sybase Anywhere (ASA). that is correct ; I've recently had to take a look at this driver and realized that it was not really written for Sybase at all, and the original author is whereabouts unknown. To that end I would like it to be replaced with an actual Sybase driver. There is a problem with named parameters with the Sybase driver in that the placeholders are prepended with an '@' *and* the execute method expects any dict paramers to have have keys that also have an '@'. I was able to get the placeholders generated correctly by subclassing the compiler. Any suggestions on how to get the execute method to work nicely or do I have to do some much around with copying parameters or monkeypatching the Sybase module with an implementation of execute that will work with 'ordinary' dictionaries? the attached patch, which represents my partial progress, addresses this. Unfortuantely I was not able to continue since I was developing from a Mac to a development server, and it turns out that connecting with the Sybase driver using FreeTDS renders bind parameters inoperable. After several days of attempting to get the developer edition of sybase ASE running in a virtual linux environment (apparently only works on older versions of ubuntu/fedora, but even after installing those, I was unsuccessful), I gave up. If you have access to a working Sybase ASE environment, you can have full reign over the sybase.py dialect - anything specific to SQL Anywhere can be removed, since its an obsolete product and if it were supported, it would be in its own dialect. The Sybase driver may be targeted towards the 0.6 release of SQLAlchemy. Version 0.6 is oriented around a dialect refactor and schema expression refactor (there are no ORM changes) and would be a much better place to start building out new drivers - there are some significant differences in how dialects are constructed between 0.5 versus 0.6. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- sybase.patch Description: Binary data
[sqlalchemy] Re: Sybase DB-API driver uses @foo for placeholder names and expect parameter dict keys to be similarly named
Thanks Michael. I have a sybase.py passing *some* unit tests with both pyodbc and the Sybase driver, both running on Solaris 10 x86 against ASE 15. This is a hack that seems to work for the Sybase DBAPI module. I do have access to lots and lots of different Sybase stuff so I will start from your patched version and reintegrate my schema introspection and other stuff. Do you have a ticket open for the sybase driver yet? Where should I send the patches? pjjH def do_execute(self, cursor, statement, parameters, context=None, **kwargs): if self.paramstyle == 'named': #prepend the arguments with an '@' hacked_args = dict((@+n, v) for n,v in parameters.items ()) super(SybaseSQLDialect_Sybase, self).do_execute(cursor, statement, hacked_args, context=context, **kwargs) else: super(SybaseSQLDialect_Sybase, self).do_execute(cursor, statement, parameters, context=context, **kwargs) def create_connect_args(self, url): opts = url.translate_connect_args() opts.update(url.query) self.autocommit = False if 'autocommit' in opts: self.autocommit = bool(int(opts.pop('autocommit'))) dictArgs = { 'datetime': 'python',# Stop the annoying diagnostics from the module 'auto_commit' : self.autocommit, # the named argument is called 'auto_commit' rather than 'autocommit' } if 'database' in opts: dictArgs['database'] = opts['database'] return ([opts['host'], opts['username'], opts['password']], dictArgs) On Feb 26, 4:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 26, 2009, at 3:55 PM, phrrn...@googlemail.com wrote: I am doing some work on a SA engine for Sybase Adaptive Server Enterprise (ASE) on top of both pyodbc and the Sybase DB-API driver. The existing sybase engine for SA only works with Sybase Anywhere (ASA). that is correct ; I've recently had to take a look at this driver and realized that it was not really written for Sybase at all, and the original author is whereabouts unknown. To that end I would like it to be replaced with an actual Sybase driver. There is a problem with named parameters with the Sybase driver in that the placeholders are prepended with an '@' *and* the execute method expects any dict paramers to have have keys that also have an '@'. I was able to get the placeholders generated correctly by subclassing the compiler. Any suggestions on how to get the execute method to work nicely or do I have to do some much around with copying parameters or monkeypatching the Sybase module with an implementation of execute that will work with 'ordinary' dictionaries? the attached patch, which represents my partial progress, addresses this. Unfortuantely I was not able to continue since I was developing from a Mac to a development server, and it turns out that connecting with the Sybase driver using FreeTDS renders bind parameters inoperable. After several days of attempting to get the developer edition of sybase ASE running in a virtual linux environment (apparently only works on older versions of ubuntu/fedora, but even after installing those, I was unsuccessful), I gave up. If you have access to a working Sybase ASE environment, you can have full reign over the sybase.py dialect - anything specific to SQL Anywhere can be removed, since its an obsolete product and if it were supported, it would be in its own dialect. The Sybase driver may be targeted towards the 0.6 release of SQLAlchemy. Version 0.6 is oriented around a dialect refactor and schema expression refactor (there are no ORM changes) and would be a much better place to start building out new drivers - there are some significant differences in how dialects are constructed between 0.5 versus 0.6. sybase.patch 12KViewDownload --~--~-~--~~~---~--~~ 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: Sybase DB-API driver uses @foo for placeholder names and expect parameter dict keys to be similarly named
we have ticket 785 for this: http://www.sqlalchemy.org/trac/ticket/785 On Feb 26, 2009, at 4:45 PM, phrrn...@googlemail.com wrote: Thanks Michael. I have a sybase.py passing *some* unit tests with both pyodbc and the Sybase driver, both running on Solaris 10 x86 against ASE 15. This is a hack that seems to work for the Sybase DBAPI module. I do have access to lots and lots of different Sybase stuff so I will start from your patched version and reintegrate my schema introspection and other stuff. Do you have a ticket open for the sybase driver yet? Where should I send the patches? pjjH def do_execute(self, cursor, statement, parameters, context=None, **kwargs): if self.paramstyle == 'named': #prepend the arguments with an '@' hacked_args = dict((@+n, v) for n,v in parameters.items ()) super(SybaseSQLDialect_Sybase, self).do_execute(cursor, statement, hacked_args, context=context, **kwargs) else: super(SybaseSQLDialect_Sybase, self).do_execute(cursor, statement, parameters, context=context, **kwargs) def create_connect_args(self, url): opts = url.translate_connect_args() opts.update(url.query) self.autocommit = False if 'autocommit' in opts: self.autocommit = bool(int(opts.pop('autocommit'))) dictArgs = { 'datetime': 'python',# Stop the annoying diagnostics from the module 'auto_commit' : self.autocommit, # the named argument is called 'auto_commit' rather than 'autocommit' } if 'database' in opts: dictArgs['database'] = opts['database'] return ([opts['host'], opts['username'], opts['password']], dictArgs) On Feb 26, 4:30 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 26, 2009, at 3:55 PM, phrrn...@googlemail.com wrote: I am doing some work on a SA engine for Sybase Adaptive Server Enterprise (ASE) on top of both pyodbc and the Sybase DB-API driver. The existing sybase engine for SA only works with Sybase Anywhere (ASA). that is correct ; I've recently had to take a look at this driver and realized that it was not really written for Sybase at all, and the original author is whereabouts unknown. To that end I would like it to be replaced with an actual Sybase driver. There is a problem with named parameters with the Sybase driver in that the placeholders are prepended with an '@' *and* the execute method expects any dict paramers to have have keys that also have an '@'. I was able to get the placeholders generated correctly by subclassing the compiler. Any suggestions on how to get the execute method to work nicely or do I have to do some much around with copying parameters or monkeypatching the Sybase module with an implementation of execute that will work with 'ordinary' dictionaries? the attached patch, which represents my partial progress, addresses this. Unfortuantely I was not able to continue since I was developing from a Mac to a development server, and it turns out that connecting with the Sybase driver using FreeTDS renders bind parameters inoperable. After several days of attempting to get the developer edition of sybase ASE running in a virtual linux environment (apparently only works on older versions of ubuntu/fedora, but even after installing those, I was unsuccessful), I gave up. If you have access to a working Sybase ASE environment, you can have full reign over the sybase.py dialect - anything specific to SQL Anywhere can be removed, since its an obsolete product and if it were supported, it would be in its own dialect. The Sybase driver may be targeted towards the 0.6 release of SQLAlchemy. Version 0.6 is oriented around a dialect refactor and schema expression refactor (there are no ORM changes) and would be a much better place to start building out new drivers - there are some significant differences in how dialects are constructed between 0.5 versus 0.6. sybase.patch 12KViewDownload --~--~-~--~~~---~--~~ 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: Problems/Bug in ordering_list (UNIQUE KEY violation)
Michael Bayer wrote: On Feb 19, 2009, at 4:33 PM, oberger wrote: Thank you Michael, but I am not able to bring this to work. Even with a flush and a commit after every Statement. I understand the problem with dependend UPDATES/DELETES. But how is the ordering_list suposed to work? When I delete on entry with: del short_trip.trip_stops[1] and then flush() and commit(). The ordering_list has to do some work in the corresponding database table. im not sure, perhaps Jason can chime in on this For this constraint configuration you might try making the DB constraint initially deferred. Given the ordering of statement execution in the unit of work, no other ideas are coming to mind. The ordering_list itself is totally ignorant of the ORM. It doesn't issue any flushes or deletions, though one could make an implementation that did embed that level of control over the unit of work. -j --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Class.query vs DBSession.query(Class)
Hi, There are two styles of writing code for querying: the assignmapper style, i.e. Class.query vs. the standard style as documented in SQLAlchemy tutorial, i.e. DBSession.query(Class). The assignmapper style seems simpler and intuitive. Curious to know why it is not the standard way. Are there any disadvantages? Also refer http://groups.google.co.in/group/turbogears/browse_thread/thread/8f3b4c4da33d69c8 thanks, Sanjay --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---