[sqlalchemy] Adding business logic to Session by inheritance?
Hi again! This questions can probably only be answered by one of the developers of SQLAlchemy. I need to store information that is valid for the whole session in a sane place, together with accessor methods. I ended up extending the orm.Session class and passing my class via class_=MySession to the sessionmaker. Originally I added only 2 fields and 4 methods to the Session but it seems like this will grow a bit. Is it okay to add a whole bunch of methods to my Session class? Any hints how to avoid name clashes for future SQLAlchemy versions? Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Implementing fine-grained access control
I'd like to find some way to implement access controls on mapped objects, with the following features: * Example: given a BlogPost object, only the owner, or a superuser, would be allowed to set fields such as title and body. * Example: reading the body field would check the privacy field as well as the current user, and only let the owner read a private field. * The owner should be determined based on a configurable column name, or as the result of a method call. * The current user should be explicitly specified rather than coming from some global state. The intention is not to make unwanted operations impossible, but to offer the programmer a degree of confidence that, so long as he uses the object in a particular way, the security constraints he specifies won't be violated, regardless of logic errors elsewhere (in a web layer, typically). It seems that one possible way to do this would be to use proxy objects to access the real instances. Returning proxies doesn't seem difficult (a mapper extension could do this if the mapped class specifies it desires it). Interaction with the session might be problematic, though, if all you have is proxy objects. Does this seem to be the correct path to follow, or is there a better approach? -- 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] many-to-many doc clarification suggestion
When I went looking for docs to remind myself how to build a many-to-many
relationship, the first doc I found at
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html
keywords = Table(
'keywords', Base.metadata,
Column('author_id', Integer, ForeignKey('authors.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id'))
)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
keywords = relationship(Keyword, secondary=keywords)
... confused me completely, because I couldn't tell whether ``keywords`` was
the association table? (Which seemed to be implied by
``secondary=keywords``). And ``keywords`` has a foreign key
(``keyword_id``) which points to ``keywords.id``, a column that... doesn't
exist? Trying to run it threw ``sqlalchemy.exc.NoReferencedColumnError:
Could not create ForeignKey 'keywords.id' on table 'keywords': table
'keywords' has no column named 'id'``
Anyway, I suggest that it would be much less confusing to just refer to the
ORM tutorial example, Building a Many To Many Relationship at
http://www.sqlalchemy.org/docs/orm/tutorial.html, which I think is much more
clear but which unfortunately only shows up at position #14 in the search
results of a many-to-many search.
Thanks!
--
- Catherine
http://catherinedevlin.blogspot.com
--
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] Re: Problems when querying the database
So what do you suggest to get the instance by title? I can query the database by title to get the id and then query again to get the instance by id. What do you think? On Nov 17, 1:10 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 16, 2010, at 3:56 PM, Alvaro Reinoso wrote: Hi all, I have a problem when querying the database: This channel class: class Channel(rdb.Model): Represents both complex channels and trivial ones (media) rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) items = relationship(MediaItem, secondary=channel_items, order_by=MediaItem.position, backref=channels) I get the proper channel object if I do: channel = session.query(Channel).filter(Channel.title == title) filter() always returns a Query object, not an instance. So there is no difference here between the call above and the one on MediaGroup, as far as the return result being a Query. And this is my mediaGroup class: class MediaGroup(rdb.Model): Represents MediaGroup class. Contains channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, primaryjoin=lambda: MediaGroup.id == media_group_groups.c.media_groupA_id, secondaryjoin=lambda: MediaGroup.id == media_group_groups.c.media_groupB_id, backref=media_groups) I get the Query object object if I do: mediaGroup = session.query(MediaGroup).filter(MediaGroup.title == title) I don't know if it's because of the relationships but I tried without mediaGroups relation, and I didn't work either. Any idea?? Thanks! -- 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 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 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] Advantage ov SQLAlchemy over plain SQL
Hi! at the company I work for I'd like to propose introducing Python to replace the hopelessly outdated SAS scripts. SQLAlchemy seems like a good option for SQL handling and I can imagine modules specifically tailored to our needs. However, the other guys are slightly conservative and might object they are fine using simple SQL and SAS. Actually they are not, because even simple tasks take hours of work. And our project work requires solving many ad hoc tasks which our system isn't really able to handle. Now I'd like to point out advantages of the SQLAlchemy approach, but I only vaguely know them. Could you pin point the advantages of using (python classes)/(the sqlalchemy system) for database toolboxes? Something simple, maybe with example ideas that I could present? [I know there is a small paragraph in the documentation, but it only says an ORM couldn't be written otherwise. Unfortunately I don't know well enough why to use an ORM. Actually I was thinking about the expression language.] Thanks! -- 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] many-to-many doc clarification suggestion
On Nov 22, 2010, at 9:57 AM, Catherine Devlin wrote:
When I went looking for docs to remind myself how to build a many-to-many
relationship, the first doc I found at
http://www.sqlalchemy.org/docs/orm/extensions/declarative.html
keywords = Table(
'keywords', Base.metadata,
Column('author_id', Integer, ForeignKey('authors.id')),
Column('keyword_id', Integer, ForeignKey('keywords.id'))
)
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
keywords = relationship(Keyword, secondary=keywords)
... confused me completely, because I couldn't tell whether ``keywords`` was
the association table? (Which seemed to be implied by
``secondary=keywords``). And ``keywords`` has a foreign key (``keyword_id``)
which points to ``keywords.id``, a column that... doesn't exist? Trying to
run it threw ``sqlalchemy.exc.NoReferencedColumnError: Could not create
ForeignKey 'keywords.id' on table 'keywords': table 'keywords' has no column
named 'id'``
well thats a bug, the table is named incorrectly in the docs, and the target
class should be in the docs too.
Anyway, I suggest that it would be much less confusing to just refer to the
ORM tutorial example, Building a Many To Many Relationship at
http://www.sqlalchemy.org/docs/orm/tutorial.html, which I think is much more
clear but which unfortunately only shows up at position #14 in the search
results of a many-to-many search.
The #1 example ideally would be
http://www.sqlalchemy.org/docs/orm/relationships.html#many-to-many . Linking
to the tutorial is less than ideal since most examples build cumulatively on
what was prior, so its not ideal as a reference document, even though the m2m
at the bottom is a little more self contained.
The section in declarative.html solved the issue of a pretty steady parade of
users who were switching to declarative, and got confused over how to construct
many to manys. It might be OK for declarative to link to the declarative
example in many-to-many above, I'll consider more closely leaving the paragraph
in declarative, but linking to the example in the main mapper docs, just to cut
down on redundancy.
Thanks!
--
- Catherine
http://catherinedevlin.blogspot.com
--
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.
Re: [sqlalchemy] Implementing fine-grained access control
On Nov 22, 2010, at 9:30 AM, Dan Ellis wrote: I'd like to find some way to implement access controls on mapped objects, with the following features: * Example: given a BlogPost object, only the owner, or a superuser, would be allowed to set fields such as title and body. * Example: reading the body field would check the privacy field as well as the current user, and only let the owner read a private field. * The owner should be determined based on a configurable column name, or as the result of a method call. * The current user should be explicitly specified rather than coming from some global state. The intention is not to make unwanted operations impossible, but to offer the programmer a degree of confidence that, so long as he uses the object in a particular way, the security constraints he specifies won't be violated, regardless of logic errors elsewhere (in a web layer, typically). It seems that one possible way to do this would be to use proxy objects to access the real instances. Returning proxies doesn't seem difficult (a mapper extension could do this if the mapped class specifies it desires it). Interaction with the session might be problematic, though, if all you have is proxy objects. Does this seem to be the correct path to follow, or is there a better approach? I'm assuming the reason for proxy objects is so that usage would continue to look like: blogpost.body = new body instead of blogpost.set_body(user, new body) ? So for that kind of thing, if you want certain operations to proceed under the umbrella of some context, like who the current user is, Python context managers are very neat for this. with security_manager.user(some_user): blogpost.body = new body You'd normally use @property on your BlogPost object to intercept read/set events, or the @validates decorator which catches only set events, to achieve this. BlogPost could find the local context manager usually via thread local. You could also use SessionExtension to disallowed changes to objects during flush() or commit(), again using some thread local context. That would be my version. If you want something a lot more elaborate and formalized, there's Zope security proxies: http://pypi.python.org/pypi/zope.security .SQLAlchemy mapped objects should be able to be used directly with security proxies, however it requires integration with the ORM, which we added a lot of hooks in order to help someone achieve this about two years ago. I'm not sure if Zope has published an SQLA integration layer with ZCP, however. -- 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.
Re: [sqlalchemy] Adding business logic to Session by inheritance?
On Nov 22, 2010, at 4:33 AM, Torsten Landschoff wrote: Hi again! This questions can probably only be answered by one of the developers of SQLAlchemy. I need to store information that is valid for the whole session in a sane place, together with accessor methods. I ended up extending the orm.Session class and passing my class via class_=MySession to the sessionmaker. Originally I added only 2 fields and 4 methods to the Session but it seems like this will grow a bit. Is it okay to add a whole bunch of methods to my Session class? Any hints how to avoid name clashes for future SQLAlchemy versions? yeah there's no magic bullet here, you can add as many methods as you like and each one raises the chance of a future collision by a tiny amount. We don't add too many methods to Session so its not terribly dangerous, but you could consider building a Session facade of your own which delegates to the real Session internally. If I were really adding a comprehensive business-centric persistence layer I'd almost certainly do that. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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.
Re: [sqlalchemy] Advantage ov SQLAlchemy over plain SQL
On Nov 22, 2010, at 5:38 AM, A. S. wrote: Hi! at the company I work for I'd like to propose introducing Python to replace the hopelessly outdated SAS scripts. SQLAlchemy seems like a good option for SQL handling and I can imagine modules specifically tailored to our needs. However, the other guys are slightly conservative and might object they are fine using simple SQL and SAS. Actually they are not, because even simple tasks take hours of work. And our project work requires solving many ad hoc tasks which our system isn't really able to handle. Now I'd like to point out advantages of the SQLAlchemy approach, but I only vaguely know them. Could you pin point the advantages of using (python classes)/(the sqlalchemy system) for database toolboxes? Something simple, maybe with example ideas that I could present? [I know there is a small paragraph in the documentation, but it only says an ORM couldn't be written otherwise. Unfortunately I don't know well enough why to use an ORM. Actually I was thinking about the expression language.] I don't have any example code, but I have written several Python applications (and one C++ app) using plain SQL and have started work on a new app using SQLAlchemy, so I'll share my experience. Let me add that I spent several years maintaining a moderately sized SQL database and wrote lots of pure SQL, though not necessarily using Python. As my programs accessed SQL, I found myself writing a lot of SQL code to access the data. A lot of this code while not identical, was very similar and seemed redundant. For example, take a simple single table lookup. If you want to do simple CRUD (create, update delete), you have to write at least three separate SQL statements for each table. While the skeleton of these SQL statements are similar, the specific column names and the table name are different. I ended up writing some Python routines that would build the SQL statements if I supplied a list of columns and the table name. But this is what SQLAlchemy does (and much more) so why reinvent the wheel? In the case of my C++ app (I hadn't found a suitable ORM), I ended up writing a Python script to generate SQL statements and C++ code to access the tables. Another advantage is the relative ease with which you can handle changes to the database structure. As I was developing my SA app, I realized that I needed a new column in (at least) one of my tables. I'm using SA's declarative approach and I only had to add the column to my declarative. I didn't have to change any SQL or Python code. One problem I have had with SQLAlchemy is unlearning the way I did things with pure SQL. At it's simplest level, SQLAlchemy can generate the SQL and Python code to access single tables and you might be tempted to write Python code to merge this kind of single table access into larger data graphs, but the true power (IMHO) of SQLAlchemy is that it can handle complex data graphs automatically. Mark -- 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] Re: Implementing fine-grained access control
On Nov 22, 11:15 am, Michael Bayer mike...@zzzcomputing.com wrote: I'm assuming the reason for proxy objects is so that usage would continue to look like: blogpost.body = new body Right, exactly. So for that kind of thing, if you want certain operations to proceed under the umbrella of some context, like who the current user is, Python context managers are very neat for this. with security_manager.user(some_user): blogpost.body = new body Using a context manager is a nice idea. Although thread-locals... a bit icky. You'd normally use @property on your BlogPost object to intercept read/set events, or the @validates decorator which catches only set events, to achieve this. BlogPost could find the local context manager usually via thread local. If I use properties with the same names as the columns, how can I avoid them clobbering the actual columns? I did try subclassing DeclarativeMeta to enforce column_prefix='_', but it I think I misunderstood what that does, because it made query(BlogPost).get(...) complain about the DB not having an _id field. Does column_prefix change what SA expects the DB columns look like, or just the names of the attributes? -- 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] MetaData, postgresql, and temporary tables
I have an app where I think I'm going to want to use a TEMPORARY TABLE like this: CREATE TEMPORARY TABLE results AS SELECT .; and then do two things: 1. I need a select count(1) from that table 2. I need to be able to iterate over the rows in that table The reason I am using a TEMPORARY table is that the result might be quite large - potentially much too large for the client. Right now I'm not using a temporary table - I am declaring a cursor and a window function (COUNT(*) OVER ()...) and then removing the resulting count row from the results before passing them on for further processing. Sadly, I need the total result size before I return any results. :-( Anyway, the questions I have are concerned with table reflection: 1. is table reflection thread safe? 2. the only way I've found for reflecting a temporary table is this sequence: a. create table b. acquire temporary table namespace with: select nspname from pg_namespace n where n.oid = pg_my_temp_schema(); c. reflect with schema=nspname from above 3. When I'm done, I always issue a ROLLBACK. What can I do to make sure the MetaData forgets about this temporary table? -- Jon -- 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] Re: Implementing fine-grained access control
On Nov 22, 2010, at 12:21 PM, Dan Ellis wrote: If I use properties with the same names as the columns, how can I avoid them clobbering the actual columns? I did try subclassing DeclarativeMeta to enforce column_prefix='_', but it I think I misunderstood what that does, because it made query(BlogPost).get(...) complain about the DB not having an _id field. Does column_prefix change what SA expects the DB columns look like, or just the names of the attributes? So the traditional approach is synonym(), which yeah involves naming the column with the underscore, and theres a helper in declarative for that. Synonym() is being superceded in 0.7 by hybrid attributes, which you can take a look at in 0.6 in the examples/derived_attributes/ example. The ad-hoc placement of synonym or a @hybrid doesn't scale up well for when you want every attribute to have the same thing happening. With declarative, you can augment the mapper() function used to create a mapping, if I wanted to do the underscore thing _ and automatically generate descriptors for all attributes I'd probably use that hook. That is __mapper_cls__ on the declarative class or the mapper argument to declarative_base(). I'd avoid the usage of a custom metaclass if possible, tends to be too brittle. There's also a hook you can get when the descriptors are being placed on the class. This hook is also being modernized in 0.7, but you can experiment with the 0.6 version using the example in examples/custom_attributes/listen_for_events.py, which illustrates the placement of AttributeExtensions on all classes (in 0.7 this will look like event.listen(myfunc, on_set, InstrumentedAttribute)). But using that same example, you could instead replace all the attributes being instrumented with a custom subclass of orm.attributes.InstrumentedAttribute that does what you want on __get__(), __set__(), __del__().Just saying attr.__class__ = MyCustomAttribute against the existing object would achieve that.That should be pretty easy and non-intrusive, and can still be directed at just certain target attributes. Another way to implement a system where one master function is receiving keys and values, you can use __getattribute__() and __setattr__(). If I really had a generic key/value/roles system in place, I might choose that. -- 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] Implementing fine-grained access control
On Nov 22, 2010, at 9:30 AM, Dan Ellis wrote: I'd like to find some way to implement access controls on mapped objects, with the following features: * Example: given a BlogPost object, only the owner, or a superuser, would be allowed to set fields such as title and body. * Example: reading the body field would check the privacy field as well as the current user, and only let the owner read a private field. * The owner should be determined based on a configurable column name, or as the result of a method call. * The current user should be explicitly specified rather than coming from some global state. The intention is not to make unwanted operations impossible, but to offer the programmer a degree of confidence that, so long as he uses the object in a particular way, the security constraints he specifies won't be violated, regardless of logic errors elsewhere (in a web layer, typically). It seems that one possible way to do this would be to use proxy objects to access the real instances. Returning proxies doesn't seem difficult (a mapper extension could do this if the mapped class specifies it desires it). Interaction with the session might be problematic, though, if all you have is proxy objects. Does this seem to be the correct path to follow, or is there a better approach? The approach I use is rather PostgreSQL-specific but works well for this scenario because security is controlled by the database instead of potentially-hairy python logic. When a user first makes a request, I generate the SQLAlchemy models by reflecting all views and tables which the user (also mapped as a PostgreSQL user) can see (based on the search_path). Optionally-available base model classes include details for unreflectable attributes such as foreign keys for views. The generated model classes are then cached as modelname_role name. By manipulating database ACLs and the search_path, it becomes trivial to push security and role changes. This setup allows me to control all models via view manipulation- column- and row-level security as well as modifying data for specific roles while using bog-standard SQLAlchemy models. 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] MetaData, postgresql, and temporary tables
On Nov 22, 2010, at 12:42 PM, Jon Nelson wrote: I have an app where I think I'm going to want to use a TEMPORARY TABLE like this: CREATE TEMPORARY TABLE results AS SELECT .; and then do two things: 1. I need a select count(1) from that table 2. I need to be able to iterate over the rows in that table The reason I am using a TEMPORARY table is that the result might be quite large - potentially much too large for the client. Right now I'm not using a temporary table - I am declaring a cursor and a window function (COUNT(*) OVER ()...) and then removing the resulting count row from the results before passing them on for further processing. Sadly, I need the total result size before I return any results. :-( Anyway, the questions I have are concerned with table reflection: 1. is table reflection thread safe? its threadsafe since its just metadata.tables[key] = new_table, though there could be a bit of a dogpile effect if multiple threads are all looking for the same table. 2. the only way I've found for reflecting a temporary table is this sequence: a. create table b. acquire temporary table namespace with: select nspname from pg_namespace n where n.oid = pg_my_temp_schema(); c. reflect with schema=nspname from above 3. When I'm done, I always issue a ROLLBACK. What can I do to make sure the MetaData forgets about this temporary table? You should use a different MetaData object that is local to your transaction. Its also likely a lot easier to use a Table object and emit the CREATE statement from that, I don't really use reflection for anything that isn't ad-hoc. If the Table has a ForeignKey to a table in your main MetaData, the ForeignKey gets a Column object instead of a string (i.e. ForeignKey(mytable.c.target)).To emit the CREATE, subclass sqlalchemy.schema.CreateTable and use @compiles, see http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs as well as http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views for an example. I would likely get the original CREATE TABLE string from the given compiler (compiler.visit_create_table(element)) and just regexp the phrase TEMP into it. -- 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] Adding business logic to Session by inheritance?
Hi Michael, Am Montag, den 22.11.2010, 11:18 -0500 schrieb Michael Bayer: Originally I added only 2 fields and 4 methods to the Session but it seems like this will grow a bit. Is it okay to add a whole bunch of methods to my Session class? Any hints how to avoid name clashes for future SQLAlchemy versions? yeah there's no magic bullet here, you can add as many methods as you like and each one raises the chance of a future collision by a tiny amount. We don't add too many methods to Session so its not terribly dangerous, but you could consider building a Session facade of your own which delegates to the real Session internally. If I were really adding a comprehensive business-centric persistence layer I'd almost certainly do that. I originally had such a facade, but I dumped it because I did not want to reimplement the scoped session adapter. Any hints how to get the best of both worlds? ;-) Thanks, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Adding business logic to Session by inheritance?
On Nov 22, 2010, at 1:11 PM, Torsten Landschoff wrote: Hi Michael, Am Montag, den 22.11.2010, 11:18 -0500 schrieb Michael Bayer: Originally I added only 2 fields and 4 methods to the Session but it seems like this will grow a bit. Is it okay to add a whole bunch of methods to my Session class? Any hints how to avoid name clashes for future SQLAlchemy versions? yeah there's no magic bullet here, you can add as many methods as you like and each one raises the chance of a future collision by a tiny amount. We don't add too many methods to Session so its not terribly dangerous, but you could consider building a Session facade of your own which delegates to the real Session internally. If I were really adding a comprehensive business-centric persistence layer I'd almost certainly do that. I originally had such a facade, but I dumped it because I did not want to reimplement the scoped session adapter. Any hints how to get the best of both worlds? ;-) you should be able to feed other kinds of session-like objects into scoped_session(). Look at its source, it doesn't do much. Ignore everything it says about extensions too, that's all going away. You could reuse and extend its approach using the instrument() function to add your own accessors to it. Thanks, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Re: Implementing fine-grained access control
Thanks for those tips. InstrumentationManager sounded like the best thing, so I've gone with that idea. Here's the basic structure of it for anyone who's interested: http://pastie.textmate.org/1318179 Thanks again, Michael. -- 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] Re: Problems when querying the database
Fixed it! On Nov 22, 10:14 am, Alvaro Reinoso alvrein...@gmail.com wrote: So what do you suggest to get the instance by title? I can query the database by title to get the id and then query again to get the instance by id. What do you think? On Nov 17, 1:10 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 16, 2010, at 3:56 PM, Alvaro Reinoso wrote: Hi all, I have a problem when querying the database: This channel class: class Channel(rdb.Model): Represents both complex channels and trivial ones (media) rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) items = relationship(MediaItem, secondary=channel_items, order_by=MediaItem.position, backref=channels) I get the proper channel object if I do: channel = session.query(Channel).filter(Channel.title == title) filter() always returns a Query object, not an instance. So there is no difference here between the call above and the one on MediaGroup, as far as the return result being a Query. And this is my mediaGroup class: class MediaGroup(rdb.Model): Represents MediaGroup class. Contains channels and other media groups rdb.metadata(metadata) rdb.tablename(media_groups) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) channels = relationship(Channel, secondary=media_group_channels, order_by=Channel.titleView, backref=media_groups) mediaGroups = relationship(MediaGroup, secondary=media_group_groups, order_by=MediaGroup.title, primaryjoin=lambda: MediaGroup.id == media_group_groups.c.media_groupA_id, secondaryjoin=lambda: MediaGroup.id == media_group_groups.c.media_groupB_id, backref=media_groups) I get the Query object object if I do: mediaGroup = session.query(MediaGroup).filter(MediaGroup.title == title) I don't know if it's because of the relationships but I tried without mediaGroups relation, and I didn't work either. Any idea?? Thanks! -- 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 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 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] Problem deleting an item
Hello all, I have these classes: class Screen(rdb.Model): Represents the screen rdb.metadata(metadata) rdb.tablename(screens) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) .. crm = relationship(CRM, uselist=False, backref=screens) class CRM(rdb.Model): Represents the CRM Tab rdb.metadata(metadata) rdb.tablename(crms) id = Column(id, Integer, ForeignKey(screens.id), primary_key=True) title = Column(title, String(50)) contactInformation = relationship(CRMContactInformation, uselist=False, backref=crms) endpointConfiguration = relationship(CRMEndpointConfiguration, uselist=False, backref=crms) location = relationship(CRMLocation, uselist=False, backref=crms) slaEvent = relationship(CRMSLAEvent, uselist=False, backref=crms) notes = relationship(CRMNote, uselist=True, backref=crms) log = relationship(CRMLog, uselist=True, backref=crms) I get this error when trying to delele a screen object: session.delete(screen) AssertionError: Dependency rule tried to blank-out primary key column 'crms.id' on instance 'CRM at 0xa3a7fac' Thanks! -- 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] Quick question on fetch batch size
When reading objects like so (notice no `.all()`): for obj in Session().query(User): ... what's the batch size with which sqlalchemy fetches rows from the DB? (If the engine matters: for Postgresql, MySQL, and sqlite?) Thanks. -- Yang Zhang http://yz.mit.edu/ -- 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] Problem deleting an item
On Nov 22, 2010, at 4:32 PM, Alvaro Reinoso wrote: Hello all, I have these classes: class Screen(rdb.Model): Represents the screen rdb.metadata(metadata) rdb.tablename(screens) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) .. crm = relationship(CRM, uselist=False, backref=screens) class CRM(rdb.Model): Represents the CRM Tab rdb.metadata(metadata) rdb.tablename(crms) id = Column(id, Integer, ForeignKey(screens.id), primary_key=True) title = Column(title, String(50)) contactInformation = relationship(CRMContactInformation, uselist=False, backref=crms) endpointConfiguration = relationship(CRMEndpointConfiguration, uselist=False, backref=crms) location = relationship(CRMLocation, uselist=False, backref=crms) slaEvent = relationship(CRMSLAEvent, uselist=False, backref=crms) notes = relationship(CRMNote, uselist=True, backref=crms) log = relationship(CRMLog, uselist=True, backref=crms) I get this error when trying to delele a screen object: session.delete(screen) AssertionError: Dependency rule tried to blank-out primary key column 'crms.id' on instance 'CRM at 0xa3a7fac' Its illegal for a primary key attribute to be NULL, so when deleting a Screen you need to delete the CRM as well. Configure 'cascade=all, delete-orphan' on your crm relationship. -- 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] Quick question on fetch batch size
On Nov 22, 2010, at 5:17 PM, Yang Zhang wrote: When reading objects like so (notice no `.all()`): for obj in Session().query(User): ... what's the batch size with which sqlalchemy fetches rows from the DB? (If the engine matters: for Postgresql, MySQL, and sqlite?) postgresql: all rows are fetched by the DBAPI, unless the server side cursors mode is used, typically via execution_options(stream_results=True) mysql: all rows are fetched by the MySQLdb DBAPI, none are buffered by OurSQL, MySQL-connector-python has an option to control the behavior though SQLA has no fine-grained hooks into it at the moment (you may be able to set it manually as a connect_args option to create_engine()) pysqlite: not sure if it buffers all rows ahead of time, check their docs/mailing list Then: for obj in query: all rows are loaded into a result list of objects which is fully buffered until results are yielded, unless the yield_per() modifier method is used.So particularly with PG, if you use query.execution_options(stream_results=True).yield_per(100), you'll get streaming rows in chunks of 100, but with the other DBs things are more questionable (consult the API docs for details on those methods). Joined eager loading is not safe to use with yield_per() and subquery eager loading will defeat the purpose of yield_per(). What you should be getting from this is that you are better off using LIMIT in conjunction with criteria that can retrieve windows of objects. Using OFFSET is the most blunt way to do it, but performs poorly for large tables. For a web page thats paginating, I'll ensure there's always a deterministic ordering, and save the last displayed value, then the subsequent page uses that previous value in the WHERE clause as a starting point. For a batch job, if possible I might use a window function with PG and MS-SQL, to return windows of identifiers across the table: select id, row_number() over (order by id) as rownum where criteria and rownum % 100=1 that gives you a list of id values that are 100 rows apart. You can then use that to select groups: for index, prev_id in enumerate(ids): if index len(ids) - 1: next_id = ids[index + 1] select * from table where id between prev_id and next_id else: select * from table where id prev_id -- 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] Avoiding mutable column types
Hi again!
Sorry for all the questions, here is another one.
What I really would like to do is to read a chunk of data for a mapped
instance and disseminate that into a number of attributes (not the usual
one column - one attribute mapping). This could easily be done using
an orm.reconstructor.
Writing to that data triggers an internal flag which I would like the
ORM to take into account to detect that the instance is dirty (even
better: signal the ORM at the same time that it is dirty). During flush,
I would like to have a orm.deconstructor ;-) method run that collects
the data from the instance attributes for flushing it to the database.
I am far from having this implemented. I tried starting with a simple
example using the MutableType mixin:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.types import TypeDecorator, MutableType
import Image
import ImageEnhance
import numpy
from cStringIO import StringIO
engine = create_engine(sqlite:tmp/test.db, echo=True)
class _ImageType(TypeDecorator):
impl = LargeBinary
def process_bind_param(self, value, dialect):
im = Image.fromarray(value)
fakefile = StringIO()
im.save(fakefile, 'png')
return fakefile.getvalue()
def process_result_value(self, value, dialect):
fakefile = StringIO(value)
return numpy.array(Image.open(fakefile))
class ImageType(MutableType, _ImageType):
def copy_value(self, value):
print creating a copy
return numpy.copy(value)
def compare_values(self, x, y):
print compare: {0} vs. {1}.format(id(x), id(y))
result = (x == y).all()
print result: {0}.format(result)
return result
metadata = MetaData()
image_table = Table(images, metadata,
Column(id, Integer, primary_key=True),
Column(image, ImageType))
metadata.create_all(engine)
class StoredImage(object):
def __init__(self, fname):
self.image = numpy.array(Image.open(fname))
mapper(StoredImage, image_table)
Session = sessionmaker(engine)
session = Session()
images = session.query(StoredImage).all()
if images:
for im in images:
data = im.image
for row in xrange(len(data)):
for col in xrange(len(data[0])):
r, g, b = data[row][col]
data[row][col] = b, g, r
# Image.fromarray(im.image).show()
print dirty, session.dirty
else:
im = StoredImage(/usr/share/backgrounds/Daisy.jpg)
session.add(im)
session.commit()
This lacks almost everything that I really want:
o data is still in a single attribute
o no dirty flag handling, but full content comparison
o the comparison is run each time the ORM looks up its dirty instances.
Just pondering...
Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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] Avoiding mutable column types
On Nov 22, 2010, at 6:17 PM, Torsten Landschoff wrote:
Hi again!
Sorry for all the questions, here is another one.
What I really would like to do is to read a chunk of data for a mapped
instance and disseminate that into a number of attributes (not the usual
one column - one attribute mapping). This could easily be done using
an orm.reconstructor.
Writing to that data triggers an internal flag which I would like the
ORM to take into account to detect that the instance is dirty (even
better: signal the ORM at the same time that it is dirty). During flush,
I would like to have a orm.deconstructor ;-) method run that collects
the data from the instance attributes for flushing it to the database.
The simplest way to approach not using mutable types is to assign a new value
to the attribute. So if you copied im.image = im.image.update(new data), that
results in just a single comparison at flush time. If you expire() the
attribute first, then the comparison is against NO_VALUE so would avoid a long
comparison.You could make StoredImage.image a proxy object of some kind
that replaces the contents of StoredImage._image, the real object, whenever an
event occurs. Or, for more efficiency, StoredImage.image just adds itself to
a set() somewhere when a change is detected, and stores the changes given in an
internal dictionary - then you do a SessionExtension.before_flush(), look in
the set() for all the StoredImages, then write their _image attribute with
their changes.
I am far from having this implemented. I tried starting with a simple
example using the MutableType mixin:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.types import TypeDecorator, MutableType
import Image
import ImageEnhance
import numpy
from cStringIO import StringIO
engine = create_engine(sqlite:tmp/test.db, echo=True)
class _ImageType(TypeDecorator):
impl = LargeBinary
def process_bind_param(self, value, dialect):
im = Image.fromarray(value)
fakefile = StringIO()
im.save(fakefile, 'png')
return fakefile.getvalue()
def process_result_value(self, value, dialect):
fakefile = StringIO(value)
return numpy.array(Image.open(fakefile))
class ImageType(MutableType, _ImageType):
def copy_value(self, value):
print creating a copy
return numpy.copy(value)
def compare_values(self, x, y):
print compare: {0} vs. {1}.format(id(x), id(y))
result = (x == y).all()
print result: {0}.format(result)
return result
metadata = MetaData()
image_table = Table(images, metadata,
Column(id, Integer, primary_key=True),
Column(image, ImageType))
metadata.create_all(engine)
class StoredImage(object):
def __init__(self, fname):
self.image = numpy.array(Image.open(fname))
mapper(StoredImage, image_table)
Session = sessionmaker(engine)
session = Session()
images = session.query(StoredImage).all()
if images:
for im in images:
data = im.image
for row in xrange(len(data)):
for col in xrange(len(data[0])):
r, g, b = data[row][col]
data[row][col] = b, g, r
# Image.fromarray(im.image).show()
print dirty, session.dirty
else:
im = StoredImage(/usr/share/backgrounds/Daisy.jpg)
session.add(im)
session.commit()
This lacks almost everything that I really want:
o data is still in a single attribute
o no dirty flag handling, but full content comparison
o the comparison is run each time the ORM looks up its dirty instances.
Just pondering...
Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
