[sqlalchemy] Make a copy of a select
Hello *, (Using SQLAlchemy 0.6.8, but planning to move to 0.7 soon) I am trying to build queries dynamically and I have to add joins on the primary table to be able to create my where-clauses. To add the join clause on my instance of Select, I found this discussion: http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/5910/focus=5917 Now I need to retain the original Select untouched (as it can be used to derive other Select instances). I found out that all the interesting methods on Select deliver a new copy using the @_generative decorator. For append_from, there does not seem to be a generative equivalent. Therefore I want to roll it manually, but I don't really want to call the private _generate method. Therefore I am wondering if there is a canonical way to copy an instance of Select. It seems that pickling is supported (__getstate__ and __setstate__ are provided). However, the class 'sqlalchemy.sql.expression._SelectBaseMixin overrides _generate to to also clear out exported collections. However, the __getstate__ implementation comes from ClauseElement. My question: Is it supported to do from copy import copy new_select = copy(old_select) where old_select is an instance of Select? Are there any plans to add a generative way to extend the froms of a Select? ;-) Thanks and 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 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] Best practice for changing record structure
Hi! As a big fan of SQLA I am looking for a way to implement something which would in fact be something like phpmysqladmin but based on SQLA and not as big. I know about migrate but that is not the way to go for me. Basically I would like to generate: databases tables Python objects (reflecting those tables) In my own (web) framework I have the ability to load python objects dynamically from a database so that is not the problem. I could use the generated SQLAlchemy objects as a base class for the programmer to extend (which can be done from the web-interface). Something like class Customer(Customer_SQLABase)…. The problem I have is in updating the record structure in the database. To keep things in sync with SQLA development I'm puzzled which approach to take. Should I customize DDL? as described in /docs/core/schema.html? Any thoughts, hint or tips would be very nice… Martijn -- 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.
Re: [sqlalchemy] Make a copy of a select
the generative method on Select is select_from(): http://www.sqlalchemy.org/docs/core/expression_api.html?highlight=select_from#sqlalchemy.sql.expression.Select.select_from Copying a select() is best performed via myselect._generate(). its a shallow copy and the select() tries to make sure it never mutates the state of internals - but also select's _generate() clears out a series of cached values that are derived from the internal state and that is definitely needed. I also spent 8 hours on an obscure bug involving all of this yesterday but that's unlikely to affect you. On Sep 6, 2011, at 4:47 AM, Torsten Landschoff wrote: Hello *, (Using SQLAlchemy 0.6.8, but planning to move to 0.7 soon) I am trying to build queries dynamically and I have to add joins on the primary table to be able to create my where-clauses. To add the join clause on my instance of Select, I found this discussion: http://thread.gmane.org/gmane.comp.python.sqlalchemy.user/5910/focus=5917 Now I need to retain the original Select untouched (as it can be used to derive other Select instances). I found out that all the interesting methods on Select deliver a new copy using the @_generative decorator. For append_from, there does not seem to be a generative equivalent. Therefore I want to roll it manually, but I don't really want to call the private _generate method. Therefore I am wondering if there is a canonical way to copy an instance of Select. It seems that pickling is supported (__getstate__ and __setstate__ are provided). However, the class 'sqlalchemy.sql.expression._SelectBaseMixin overrides _generate to to also clear out exported collections. However, the __getstate__ implementation comes from ClauseElement. My question: Is it supported to do from copy import copy new_select = copy(old_select) where old_select is an instance of Select? Are there any plans to add a generative way to extend the froms of a Select? ;-) Thanks and 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 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. -- 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.
Re: [sqlalchemy] Best practice for changing record structure
On Sep 6, 2011, at 9:38 AM, Martijn Moeling wrote: Hi! As a big fan of SQLA I am looking for a way to implement something which would in fact be something like phpmysqladmin but based on SQLA and not as big. I know about migrate but that is not the way to go for me. Basically I would like to generate: databases tables Python objects (reflecting those tables) In my own (web) framework I have the ability to load python objects dynamically from a database so that is not the problem. I could use the generated SQLAlchemy objects as a base class for the programmer to extend (which can be done from the web-interface). Something like class Customer(Customer_SQLABase)…. The problem I have is in updating the record structure in the database. To keep things in sync with SQLA development I'm puzzled which approach to take. Should I customize DDL? as described in /docs/core/schema.html? Any thoughts, hint or tips would be very nice… I have a library that serves as the base for a bunch of new ALTER constructs and such called Alembic: https://bitbucket.org/zzzeek/alembic/overview . It uses the @compiled system to achieve this. I wrote most of it over a year ago and its basically a project needing some more work to write basic documentation and flesh it out some more (though I have used it on the job to a minimal extent). It's a migration tool, but also can be used just for the DDL constructs which you can see in alembic.ddl (i.e. same philosophy as SQLAlchemy - provide more rudimental tools as well as functionality on top). You can check it out to see some of the ALTERs, and also any code/documentation/beta tester contributions are entirely welcome as well (it just would require some source code reading since there's no docs yet). -- 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] Question on session.expunge.all()
I have a products database which is daily syncronized with an external source via a csv file. There are several thousand rows in question. The synchronization does two things: 1. Update only price if changed for existing products 2. Insert new products if they don't exist with all fields from csv But basically, for each row in the csv, after the row is processed (one of the above two things is done), I don't need the object in session anymore. Memory and performance are of course an issue, and I can't find a way to test memory consumption with or without expunge_all() so my questions are: 1. Do I need to session.expunge_all() after each csv row is processed, or are they automatically garbage collected? 2. Is there any significant overhead inherent in expunge_all() that I'm not seeing right now? Performance-wise, it seems the task is complete in more or less same time with or without expunge_all() While I'm at it, I also need to delete rows in the database that do not have corresponding row in the csv file (say linked by csv_key field), the first solution that comes to mind is building a list of keys in the csv file (few thousand keys) and then doing: session.query(Product).filter(not_(Product.product_id.in_(csv_keys))).delete() I believe there is less overhead in sending such a large (but single!) query to the database and leaving it to determine what to delete by itself, than selecting each row in the database and checking if its csv_key exists in the csv_keys list on the application side and then issuing delete statements for rows that matched the criteria. Am I wrong? (I can't truncate table and reinsert products for other reasons) The database is Postgresql. Thanks! -- .oO V Oo. -- 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.
Re: [sqlalchemy] Question on session.expunge.all()
On Sep 6, 2011, at 10:40 AM, Vlad K. wrote: I have a products database which is daily syncronized with an external source via a csv file. There are several thousand rows in question. The synchronization does two things: 1. Update only price if changed for existing products 2. Insert new products if they don't exist with all fields from csv But basically, for each row in the csv, after the row is processed (one of the above two things is done), I don't need the object in session anymore. Memory and performance are of course an issue, and I can't find a way to test memory consumption with or without expunge_all() so my questions are: 1. Do I need to session.expunge_all() after each csv row is processed, or are they automatically garbage collected? 2. Is there any significant overhead inherent in expunge_all() that I'm not seeing right now? Performance-wise, it seems the task is complete in more or less same time with or without expunge_all() In modern SQLAlchemy, the Session maintains only weak references to objects that are clean, that is, are persistent in the database and have no pending changes to be flushed.As all references to them are lost, they are garbage collected by the Python interpreter.Note that objects are strongly referenced when they are present in the collection or attribute of a parent object, until that parent is also garbage collected.There is an overhead to process which occurs when the object is dereferenced and removed from the session (weakref callbacks handle the accounting). But calling expunge_all() probably isn't doing much here as the objects are likely being cleaned out in the same way regardless. While I'm at it, I also need to delete rows in the database that do not have corresponding row in the csv file (say linked by csv_key field), the first solution that comes to mind is building a list of keys in the csv file (few thousand keys) and then doing: session.query(Product).filter(not_(Product.product_id.in_(csv_keys))).delete() I believe there is less overhead in sending such a large (but single!) query to the database and leaving it to determine what to delete by itself, than selecting each row in the database and checking if its csv_key exists in the csv_keys list on the application side and then issuing delete statements for rows that matched the criteria. Am I wrong? That's definitely a dramatically faster way to do things, rather than to load each record individually and mark as deleted - it's the primary reason delete() and update() are there. You'll probably want to send False as the value of synchronize_session to the delete() call so that it doesn't go through the effort of locating local records that were affected (unless you need that feature). -- 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.
Re: [sqlalchemy] Best practice for changing record structure
Michael, Looks promising but I need some time to get clues about the workings. I lost my password for bitbucket, I have a project there too, mp2mwsgi to run mod_python code on top of mod_wsgi (or any other wsgi but not tested) I might very well extend alembic and put an frond-end on it using extjs which I use for my project. I'll be in touch! Martijn On Sep 6, 2011, at 16:09 , Michael Bayer wrote: On Sep 6, 2011, at 9:38 AM, Martijn Moeling wrote: Hi! As a big fan of SQLA I am looking for a way to implement something which would in fact be something like phpmysqladmin but based on SQLA and not as big. I know about migrate but that is not the way to go for me. Basically I would like to generate: databases tables Python objects (reflecting those tables) In my own (web) framework I have the ability to load python objects dynamically from a database so that is not the problem. I could use the generated SQLAlchemy objects as a base class for the programmer to extend (which can be done from the web-interface). Something like class Customer(Customer_SQLABase)…. The problem I have is in updating the record structure in the database. To keep things in sync with SQLA development I'm puzzled which approach to take. Should I customize DDL? as described in /docs/core/schema.html? Any thoughts, hint or tips would be very nice… I have a library that serves as the base for a bunch of new ALTER constructs and such called Alembic: https://bitbucket.org/zzzeek/alembic/overview . It uses the @compiled system to achieve this. I wrote most of it over a year ago and its basically a project needing some more work to write basic documentation and flesh it out some more (though I have used it on the job to a minimal extent). It's a migration tool, but also can be used just for the DDL constructs which you can see in alembic.ddl (i.e. same philosophy as SQLAlchemy - provide more rudimental tools as well as functionality on top). You can check it out to see some of the ALTERs, and also any code/documentation/beta tester contributions are entirely welcome as well (it just would require some source code reading since there's no docs yet). -- 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. -- 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] Get value from field class and instance
Let's say there is a mapped (declaratively, but that shouldn't matter) class, Data, that has fields Data.value1, ... Data.value10. There is also an instance of this class, data that is populated from the data table. Obviously, you can get the values using data.value1, ... But is there a simple way to get a data value using the instance object (data) and a class field (Data.value1). What's the easiest way given data and Data.value1 to get / set that value of data.value1? So far I've come up with: Data.__getattribute__(data, Data.value1.property.columns[0].name) but is there a more direct way? == If you're curious, here's what I'm trying to do. I have an calculation that sums a calculation on all of a particular type of field. If I add a new field of this type to the table, it would be nice if it were automatically included in the calculation. I've created a custom descendent of Column for this type of column. When the constructor of this custom class is called during the table construction, it adds the created field to a list. The calculation should then step through the columns in this list when calculating the value. Thanks, Mark -- 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.
Re: [sqlalchemy] Get value from field class and instance
On Sep 6, 2011, at 2:38 PM, Mark Erbaugh wrote: Let's say there is a mapped (declaratively, but that shouldn't matter) class, Data, that has fields Data.value1, ... Data.value10. There is also an instance of this class, data that is populated from the data table. Obviously, you can get the values using data.value1, ... But is there a simple way to get a data value using the instance object (data) and a class field (Data.value1). What's the easiest way given data and Data.value1 to get / set that value of data.value1? Data.value1 is a Python descriptor, so Data.value1.__get__(data, Data) would do it.Or getattr(data, Data.value1.key) as key is present on the SQLA instrumented attribute. So far I've come up with: Data.__getattribute__(data, Data.value1.property.columns[0].name) but is there a more direct way? == If you're curious, here's what I'm trying to do. I have an calculation that sums a calculation on all of a particular type of field. If I add a new field of this type to the table, it would be nice if it were automatically included in the calculation. I've created a custom descendent of Column for this type of column. When the constructor of this custom class is called during the table construction, it adds the created field to a list. The calculation should then step through the columns in this list when calculating the value. Thanks, Mark -- 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. -- 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.
Re: [sqlalchemy] Get value from field class and instance
On Sep 6, 2011, at 2:48 PM, Michael Bayer wrote: On Sep 6, 2011, at 2:38 PM, Mark Erbaugh wrote: Let's say there is a mapped (declaratively, but that shouldn't matter) class, Data, that has fields Data.value1, ... Data.value10. There is also an instance of this class, data that is populated from the data table. Obviously, you can get the values using data.value1, ... But is there a simple way to get a data value using the instance object (data) and a class field (Data.value1). What's the easiest way given data and Data.value1 to get / set that value of data.value1? Data.value1 is a Python descriptor, so Data.value1.__get__(data, Data) would do it.Or getattr(data, Data.value1.key) as key is present on the SQLA instrumented attribute. Thanks. -- 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] subqueryload for a ColumnProperty?
I have a collection of deferred `ColumnProperty`s that I'd like to start loading in subsequent queries. I know you can get this to happen on the first access of a `ColumnProperty` that is deferred, but I wonder if there's any way to specify this in the query. For relationships, `joinedload[_all()]` has a counterpart `subqueryload[_all()]`. Is there nothing similar we can do for `ColumnProperty`? -- 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.
