[sqlalchemy] transform a set to an array
Hello, I want to call a stored procedure which takes an array as input. Provides sqlalchemy an opportunity to call a procedure with an array as parameter? Also, I'm looking to find a solution to transform a set into an array string (ARRAY[..,..]), but I can't find it in sqlalchemy. Thanks Kai --~--~-~--~~~---~--~~ 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.select() and tablename.select() why are they different?
On 9/6/07, Lukasz Szybalski [EMAIL PROTECTED] wrote: On 9/6/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Thursday 06 September 2007 23:03:35 Lukasz Szybalski wrote: Hello, So it seems to me there are two select function that I can use but they are different First: s=Users.select(Users.c.LASTNAME=='Smith') but when you want to select only two columns via : s=Users.select([Users.c.LASTNAME, Users.c.FIRSTNAME], Users.c.LASTNAME =='Smith') you get an error : File /usr/lib/python2.4/site-packages/sqlalchemy/ext/assignmapper.py, line 7, in do return getattr(query, name)(*args, **kwargs) TypeError: select() takes at most 2 arguments (3 given) Second: import sqlalchemy s2=sqlalchemy.select(Users.c.LASTNAME=='Smith') s3=s2.execute() This works just fine: s2=sqlalchemy.select([Users.c.LASTNAME, Users.c.FIRSTNAME], Users.c.LASTNAME =='Smith') s3=s2.execute() Is this difference suppose to be there? or is it a bug in assign_mapper? Lucas the first is the ORM query().select() taking only where clause, the second is plain sql select(columns,where,from). To disambiguate, first one is discontinued and is replaced by filter(). So basically the first one does select * from x where y =? And you can only substitute x,y,? (Sounds very limiting) To me TABLENAME.select() should just be a a wrapper to sqlalchemy.select().execute() That way the first accepts the same parameters as before but it will also be able to do where and from..??? Yes No? I am not sure what filter() will be doing differently. I check the docs and it sounds to me like you want to filter the results already selected?Doesn't that complicate things even more. No I have to know a filter function and find out what parameters does it take? Will filter() do the actual select ? How will it be different from query() select()? Why are they separated? I guess this is similar conversation: http://groups.google.com/group/sqlalchemy/browse_thread/thread/be0b4d6d793cd6f7/4a283669530e1e86?#4a283669530e1e86 From the docs it seems as with filter you will have to do this to get your select statement. That seems to me is over complicating things. First you call query then subfunction filter then subfunction order then subfunction all? how many levels does it go to? session.query(User).filter(User.c.user_name.in_('Ed', 'Harry','Mary')).order_by(User.c.user_name).all() Wouldn't it be easier if we do: s2=Users.select( Users.c.LASTNAME =='Smith' ) or s2=Users.select( [Users.c.LASTNAME, Users.c.FIRSTNAME], Users.c.LASTNAME =='Smith' ) or s2=Users.select( [Users.c.LASTNAME, Users.c.FIRSTNAME], Users.c.LASTNAME =='Smith', order_by(User.c.FIRSTNAME) ) or s2=Users.select( [Users.c.LASTNAME, Users.c.FIRSTNAME], Users.c.LASTNAME =='Smith', order_by(User.c.FIRSTNAME,ASC), limit(1) ) there would be another option for join, etc that could be included in the list if one needed to use more. Each of them are optional. Otherwise questions arise: which one do i do filter(), filter_by(), query() select() select_by() Is order_by a passed in parameter or function of a filter() or query() or is it ? Why do I have to specify all()? Shouldn't that be a default? What is the order of functions to call? Which one is used on what? What you guys think? Lucas ok. So this time I am trying to get data from my widget from database that has two compound keys, using assign_mapper. #Initialize: User_table = sqlalchemy.Table('User', metadata, autoload=True) class User(object): pass usermapper=assign_mapper(session.context,User,user_table) #get my record: x=model.User.get(343,2) Why do I get an error? What am I doing wrong here? File /home/lucas/web/tg/quote/quote/controllers.py, line 126, in upddriver x=model.Bdriver.get(343,2) File /usr/lib/python2.4/site-packages/sqlalchemy/ext/assignmapper.py, line 7, in do return getattr(query, name)(*args, **kwargs) TypeError: get() takes exactly 2 arguments (3 given) Lucas --~--~-~--~~~---~--~~ 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: bisect.insort
Jean-Philippe Dutreve wrote: Thanks Jason for your clear explanation. Is there any mean to do your suggestion to call the pure Python version without coping/pasting it into my module? Not that I know of- the Python functions get overwritten by the C implementations when the module is imported. --~--~-~--~~~---~--~~ 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 with turbogears and assign_mapper: select
Hi, And if I wanted to select a year and group by year? select User.Year from User group by User.Year db.execute(select([User.Year]) ??? Have a look at http://www.sqlalchemy.org/docs/04/sqlexpression.html 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: bisect.insort
On Friday 07 September 2007 13:54:03 Jean-Philippe Dutreve wrote: I was using SA 0.3.9 to insert an item in an ordered list with bisect method insort (py 2.5): mapper(Entry, table_entries) mapper(Account, table_accounts, properties = dict( entries = relation(Entry, lazy=True, backref=backref('account', lazy=False), collection_class=ordering_list('position'), order_by=[table_entries.c.position]) )) bisect.insort(account.entries, an_entry) This is not working anymore with SA 0.4 beta5 : the list owns the item but not the other way. assert account.entries[0] is an_entry # TRUE assert an_entry.account is account # FALSE, currently is None Remark: it's working if I copy/paste the bisect method in my module. collections were reworked in 0.4, so see what methods the insort() uses from your list, and see if they are simulated/wrapped in orm.collections.py. maybe there some unhooked one. --~--~-~--~~~---~--~~ 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: bisect.insort
Jean-Philippe Dutreve wrote: I was using SA 0.3.9 to insert an item in an ordered list with bisect method insort (py 2.5): mapper(Entry, table_entries) mapper(Account, table_accounts, properties = dict( entries = relation(Entry, lazy=True, backref=backref('account', lazy=False), collection_class=ordering_list('position'), order_by=[table_entries.c.position]) )) bisect.insort(account.entries, an_entry) This is not working anymore with SA 0.4 beta5 : the list owns the item but not the other way. assert account.entries[0] is an_entry # TRUE assert an_entry.account is account # FALSE, currently is None Remark: it's working if I copy/paste the bisect method in my module. This is a Python bug: the C version of insort ignores overridden 'insert' methods on classes that derive from list, bypassing SQLAlchemy's collection hooks. In prior SQLAlchemy versions, collections weren't real lists and insort does handle that case properly. I'd suggest using the pure Python versions of the bisect functions going forward. --~--~-~--~~~---~--~~ 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: bisect.insort
Thanks Jason for your clear explanation. Is there any mean to do your suggestion to call the pure Python version without coping/pasting it into my module? On 7 sep, 16:28, jason kirtland [EMAIL PROTECTED] wrote: Jean-Philippe Dutreve wrote: I was using SA 0.3.9 to insert an item in an ordered list with bisect method insort (py 2.5): mapper(Entry, table_entries) mapper(Account, table_accounts, properties = dict( entries = relation(Entry, lazy=True, backref=backref('account', lazy=False), collection_class=ordering_list('position'), order_by=[table_entries.c.position]) )) bisect.insort(account.entries, an_entry) This is not working anymore with SA 0.4 beta5 : the list owns the item but not the other way. assert account.entries[0] is an_entry # TRUE assert an_entry.account is account # FALSE, currently is None Remark: it's working if I copy/paste the bisect method in my module. This is a Python bug: the C version of insort ignores overridden 'insert' methods on classes that derive from list, bypassing SQLAlchemy's collection hooks. In prior SQLAlchemy versions, collections weren't real lists and insort does handle that case properly. I'd suggest using the pure Python versions of the bisect functions going forward. --~--~-~--~~~---~--~~ 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.select() and tablename.select() why are they different?
ok. So this time I am trying to get data from my widget from database that has two compound keys, using assign_mapper. #Initialize: User_table = sqlalchemy.Table('User', metadata, autoload=True) class User(object): pass usermapper=assign_mapper(session.context,User,user_table) #get my record: x=model.User.get(343,2) Why do I get an error? What am I doing wrong here? File /home/lucas/web/tg/quote/quote/controllers.py, line 126, in upddriver x=model.Bdriver.get(343,2) File /usr/lib/python2.4/site-packages/sqlalchemy/ext/assignmapper.py, line 7, in do return getattr(query, name)(*args, **kwargs) TypeError: get() takes exactly 2 arguments (3 given) u are giving 2 args here, it wants one arg (apart of the self). try give it as tuple .get( (343,2) ), but i'm not sure what is the exact way for composite keys. --~--~-~--~~~---~--~~ 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.select() and tablename.select() why are they different?
ok. So this time I am trying to get data from my widget from database that has two compound keys, using assign_mapper. #Initialize: User_table = sqlalchemy.Table('User', metadata, autoload=True) class User(object): pass usermapper=assign_mapper(session.context,User,user_table) #get my record: x=model.User.get(343,2) I guess double parenthesis did the job x=model.User.get((343,2)) Why do I get an error? What am I doing wrong here? File /home/lucas/web/tg/quote/quote/controllers.py, line 126, in upddriver x=model.Bdriver.get(343,2) File /usr/lib/python2.4/site-packages/sqlalchemy/ext/assignmapper.py, line 7, in do return getattr(query, name)(*args, **kwargs) TypeError: get() takes exactly 2 arguments (3 given) Lucas --~--~-~--~~~---~--~~ 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: problem with Numeric type and mysqll
remi jolin wrote: Hello, I have the following definition (using Elixir) class Manifestation(Entity): has_field('price', Numeric) The DB is mysql and something like m = Manifestation(price=10.0) then when accessing to this manifestation again (after flush, clear, etc...) I have this strange behavior m.price 10 False m.price 10.0 True m.price 12.34 True The same definition works great when using a sqlite DB. Any idea ? what's the repr() of m.price? --~--~-~--~~~---~--~~ 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: rev 3449 breaks MapperExtension?
we've got plenty of MapperExtensions running. i dont see how you are getting mapper.extension to be your actual mapper, its supposed to point to a container called ExtensionCarrier (unless, you are saying mapper.extension = _MapExt(). not supported, unless you want to provide a property-based patch for it). On Sep 7, 2007, at 11:12 AM, [EMAIL PROTECTED] wrote: or has something in MapperExt protocol changed? File dbcook/usage/samanager.py, line 189, in query_BASE_instances return session.query( m.plain ) File sqlalchemy/orm/session.py, line 638, in query q = self._query_cls(mapper_or_class, self, **kwargs) File sqlalchemy/orm/query.py, line 31, in __init__ self._extension = self.mapper.extension.copy() AttributeError: '_MapExt' object has no attribute 'copy' i have very simple one, just for sanity checks: class _MapExt( sqlalchemy.orm.MapperExtension): def before_insert( self, mapper, connection, instance): assert (instance.__class__ is not mapper.class_, 'load_only_object - no save: ' + str( instance.__class__) + ':'+str(mapper) ) before_update = before_delete = before_insert _mapext = _MapExt() and using that one on all mappers which should be readonly. svilen --~--~-~--~~~---~--~~ 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: problem with Numeric type and mysqll
numeric types are going to come out using decimal.Decimal objects in 0.4 but not exactly sure whats happening there...do a repr(m.price). On Sep 7, 2007, at 12:57 PM, remi jolin wrote: Hello, I have the following definition (using Elixir) class Manifestation(Entity): has_field('price', Numeric) The DB is mysql and something like m = Manifestation(price=10.0) then when accessing to this manifestation again (after flush, clear, etc...) I have this strange behavior m.price 10 False m.price 10.0 True m.price 12.34 True The same definition works great when using a sqlite DB. Any idea ? --~--~-~--~~~---~--~~ 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: transform a set to an array
PG arrays ? you can do it like this: from sqlalchemy.databases.postgres import PGArray engine.execute(select([func.my_procedure(literal(['a', 'b', 'c'], type_=PGArray(String)))])) On Sep 7, 2007, at 8:22 AM, Kai_002 wrote: Hello, I want to call a stored procedure which takes an array as input. Provides sqlalchemy an opportunity to call a procedure with an array as parameter? Also, I'm looking to find a solution to transform a set into an array string (ARRAY[..,..]), but I can't find it in sqlalchemy. Thanks Kai --~--~-~--~~~---~--~~ 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] Changeset 2795
It seems that the bug fixed by changeset 2795 (column_prefix with synonym) is still active in 0.4 branch. --~--~-~--~~~---~--~~ 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] rev 3449 breaks MapperExtension?
or has something in MapperExt protocol changed? File dbcook/usage/samanager.py, line 189, in query_BASE_instances return session.query( m.plain ) File sqlalchemy/orm/session.py, line 638, in query q = self._query_cls(mapper_or_class, self, **kwargs) File sqlalchemy/orm/query.py, line 31, in __init__ self._extension = self.mapper.extension.copy() AttributeError: '_MapExt' object has no attribute 'copy' i have very simple one, just for sanity checks: class _MapExt( sqlalchemy.orm.MapperExtension): def before_insert( self, mapper, connection, instance): assert (instance.__class__ is not mapper.class_, 'load_only_object - no save: ' + str( instance.__class__) + ':'+str(mapper) ) before_update = before_delete = before_insert _mapext = _MapExt() and using that one on all mappers which should be readonly. svilen --~--~-~--~~~---~--~~ 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: problem with Numeric type and mysqll
le 07.09.2007 19:47 remi jolin a écrit: le 07.09.2007 19:27 Michael Bayer a écrit: numeric types are going to come out using decimal.Decimal objects in 0.4 but not exactly sure whats happening there...do a repr(m.price). it gives Decimal(10.00) and I'm using SA 0.3.10 Is there a difference regarding Numeric between 0.3.7 and 0.3.10 because the system where I have sqlite DB is using 0.3.7 ?? I've just tested with 0.3.10 and sqlite : repr(m.price) gives 10.0 so it is not a difference due the SA versions but only DB access implementation. On Sep 7, 2007, at 12:57 PM, remi jolin wrote: Hello, I have the following definition (using Elixir) class Manifestation(Entity): has_field('price', Numeric) The DB is mysql and something like m = Manifestation(price=10.0) then when accessing to this manifestation again (after flush, clear, etc...) I have this strange behavior m.price 10 False m.price 10.0 True m.price 12.34 True The same definition works great when using a sqlite DB. Any idea ? --~--~-~--~~~---~--~~ 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: problem with Numeric type and mysqll
remi jolin wrote: le 07.09.2007 19:47 remi jolin a écrit: le 07.09.2007 19:27 Michael Bayer a écrit: numeric types are going to come out using decimal.Decimal objects in 0.4 but not exactly sure whats happening there...do a repr(m.price). it gives Decimal(10.00) and I'm using SA 0.3.10 Is there a difference regarding Numeric between 0.3.7 and 0.3.10 because the system where I have sqlite DB is using 0.3.7 ?? I've just tested with 0.3.10 and sqlite : repr(m.price) gives 10.0 so it is not a difference due the SA versions but only DB access implementation. NUMERIC columns will return Decimals on some db-apis, but not sqlite. In 0.4, the sqlalchemy Numeric type adapts as needed to ensure Decimal across all db-apis. But in 0.3, what the db-api returns is what you get for numerics. Comparing against Decimals will have the results you expect: m.price Decimal('12.34') False -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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: rev 3449 breaks MapperExtension?
On Friday 07 September 2007 20:25:50 Michael Bayer wrote: we've got plenty of MapperExtensions running. i dont see how you are getting mapper.extension to be your actual mapper, its supposed to point to a container called ExtensionCarrier ( unless, you are saying mapper.extension = _MapExt(). not supported, unless you want to provide a property-based patch for it). yes, i am setting it this way... okay, if thats not the proper way anymore, i'll have a look. Any other way to force a readonly mapper? its for mapping of (virtual) base classes that must not have instances. On Sep 7, 2007, at 11:12 AM, [EMAIL PROTECTED] wrote: or has something in MapperExt protocol changed? File dbcook/usage/samanager.py, line 189, in query_BASE_instances return session.query( m.plain ) File sqlalchemy/orm/session.py, line 638, in query q = self._query_cls(mapper_or_class, self, **kwargs) File sqlalchemy/orm/query.py, line 31, in __init__ self._extension = self.mapper.extension.copy() AttributeError: '_MapExt' object has no attribute 'copy' i have very simple one, just for sanity checks: class _MapExt( sqlalchemy.orm.MapperExtension): def before_insert( self, mapper, connection, instance): assert (instance.__class__ is not mapper.class_, 'load_only_object - no save: ' + str( instance.__class__) + ':'+str(mapper) ) before_update = before_delete = before_insert _mapext = _MapExt() and using that one on all mappers which should be readonly. svilen --~--~-~--~~~---~--~~ 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: rev 3449 breaks MapperExtension?
On Sep 7, 2007, at 3:06 PM, [EMAIL PROTECTED] wrote: On Friday 07 September 2007 20:25:50 Michael Bayer wrote: we've got plenty of MapperExtensions running. i dont see how you are getting mapper.extension to be your actual mapper, its supposed to point to a container called ExtensionCarrier ( unless, you are saying mapper.extension = _MapExt(). not supported, unless you want to provide a property-based patch for it). yes, i am setting it this way... okay, if thats not the proper way anymore, i'll have a look. Any other way to force a readonly mapper? a non-primary mapper is read only. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---