[sqlalchemy] Database Views
Hi all, This is a topic that has been discussed before, but I haven't been able to successfully implement any of the proposed solutions in my own code. I've created a few Views in my postgres database, and I'm looking for a way to simply query them from sqlalchemy. I tried just treating them as tables, but this failed when the mapper wasn't able to find a primary key (makes sense). The code I tried was just a naive attempt: class SpectrumView(object): pass spectrum_view = Table('spectrum_view', metadata, autoload=True) mapper(SpectrumView, spectrum_view) So I read in some message that it might be possible to specify a primary key to the mapper, like mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem is I'm not defining my tables in python - they are already created, so I don't have any column objects to pass to primary_key. Anyway, I'm just curious to see if anyone has had success with an implementation of database views in sqlalchemy, and possibly examples of those cases. Thanks, Adrian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Database Views
On Nov 8, 2010, at 1:16 PM, Adrian wrote: Hi all, This is a topic that has been discussed before, but I haven't been able to successfully implement any of the proposed solutions in my own code. I've created a few Views in my postgres database, and I'm looking for a way to simply query them from sqlalchemy. I tried just treating them as tables, but this failed when the mapper wasn't able to find a primary key (makes sense). The code I tried was just a naive attempt: class SpectrumView(object): pass spectrum_view = Table('spectrum_view', metadata, autoload=True) mapper(SpectrumView, spectrum_view) So I read in some message that it might be possible to specify a primary key to the mapper, like mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem is I'm not defining my tables in python - they are already created, so I don't have any column objects to pass to primary_key. Anyway, I'm just curious to see if anyone has had success with an implementation of database views in sqlalchemy, and possibly examples of those cases. Views are reflectable, so you can reflect the view, then add the primary key to the proper column in the metadata. Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.
Re: [sqlalchemy] Database Views
On Nov 8, 2010, at 1:16 PM, Adrian wrote: Hi all, This is a topic that has been discussed before, but I haven't been able to successfully implement any of the proposed solutions in my own code. I've created a few Views in my postgres database, and I'm looking for a way to simply query them from sqlalchemy. I tried just treating them as tables, but this failed when the mapper wasn't able to find a primary key (makes sense). The code I tried was just a naive attempt: class SpectrumView(object): pass spectrum_view = Table('spectrum_view', metadata, autoload=True) mapper(SpectrumView, spectrum_view) So I read in some message that it might be possible to specify a primary key to the mapper, like mapper(SpectrumView, spectrum_view, primary_key=[..]), but the problem is I'm not defining my tables in python - they are already created, so I don't have any column objects to pass to primary_key. The column objects are always available from table.c regardless of whether autoload was used or not: mapper(SpectrumView, spectrum_view, primary_key=[spectrum_view.c.somecolumn, spectrum_view.c.someothercolumn]) Anyway, I'm just curious to see if anyone has had success with an implementation of database views in sqlalchemy, and possibly examples of those cases. Thanks, Adrian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Database views
In my existing Firebird database I have some views defined. In the model I thought I could just go ahead and define them basically like tables but I get an exception: Traceback (most recent call last): File saTest.py, line 4, in module import modelTest as model File C:\Dev\BoaTest04\sqlalchemy\modelTest.py, line 237, in module vcbook = sao.mapper(Vcbook, vcbook_table) File c:\downloaded software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\__init__.py, line 516, in mapper return Mapper(class_, local_table, *args, **params) File c:\downloaded software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\mapper.py, line 152, in __init__ self._compile_tables() File c:\downloaded software\python\sqlalchemy-0.4.0beta6\lib\sqlalchemy\orm\mapper.py, line 414, in _compile_tables raise exceptions.ArgumentError(Could not assemble any primary key columns for mapped table '%s' % (self.mapped_table.name)) sqlalchemy.exceptions.ArgumentError: Could not assemble any primary key columns for mapped table 'vcbook' In my case the views are always used in read-only mode, changes are made to the actual table. |Is there someway I can define these views. I was able to work around the above exception by defining a primary key column or columns (compound key) but I have one or two views which don't really have a primary key. Appreciate any hints/tips. Werner | --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---