[sqlalchemy] Re: Creating column comments on the database
Lukasz Szybalski schrieb: I think I prefer info dictionary rather then a string. Dict info which I use already have something like this: sqlalchemy.Column('DRIVE_TRAIN', sqlalchemy.Unicode(4) ,info={description:DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]}), sqlalchemy.Column('FUEL_SYS', sqlalchemy.Unicode(4) ,info={description:FUEL SYSTEM CODE, FI:FUEL INJECTION, TB:TURBO}), I think you're mixing different things here. What I suggested was support of the database comment statement/clause supported by all major databases (except maybe SQL Server). What you're looking for seems to be something similar to the Domain feature of the old Oracle Designer, http://marceloverdijk.blogspot.com/2008/05/ref-code-plugin.html http://its.unm.edu/ais/docs/oradomains.htm This would be an interesting extension as well. Should be possible with a custom TypeDecorator or TypeEngine. -- Christoph --~--~-~--~~~---~--~~ 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] data warehouse
Hi all, A very good day to all of you. Currently, i am working on data warehouse. I am using MS SQL. I have done quite a big of research online recentlhy. However, all i could find are defination of data warehouse and designing of data warehouse. I had both fact and dimension tables ready, but how should i create a store procedure to populate data from the different databases into my warehouse? Thanks and Regards Poy Ling --~--~-~--~~~---~--~~ 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: Creating column comments on the database
On Thu, Jun 5, 2008 at 2:37 AM, Christoph Zwerschke [EMAIL PROTECTED] wrote: Lukasz Szybalski schrieb: I think I prefer info dictionary rather then a string. Dict info which I use already have something like this: sqlalchemy.Column('DRIVE_TRAIN', sqlalchemy.Unicode(4) ,info={description:DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]}), sqlalchemy.Column('FUEL_SYS', sqlalchemy.Unicode(4) ,info={description:FUEL SYSTEM CODE, FI:FUEL INJECTION, TB:TURBO}), I think you're mixing different things here. What I suggested was support of the database comment statement/clause supported by all major databases (except maybe SQL Server). I see. What you're looking for seems to be something similar to the Domain feature of the old Oracle Designer, I wonder if it was possible to save the info field {} I showed above via the comment statement/clause you are mentioning if it gets implemented? This would really help in describing business rules and descriptions of each column. Lucas http://marceloverdijk.blogspot.com/2008/05/ref-code-plugin.html http://its.unm.edu/ais/docs/oradomains.htm This would be an interesting extension as well. Should be possible with a custom TypeDecorator or TypeEngine. -- Christoph -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ TurboGears Manual-Howto http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf --~--~-~--~~~---~--~~ 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] 0.4.6 tar balls on SF and pypi are different?
I just noticed the tarballs on SF and pypi are different. The one on SF: MD5 (SQLAlchemy-0.4.6.tar.gz) = 3043efb59000887ebe13fdcd6b6efadb SIZE (SQLAlchemy-0.4.6.tar.gz) = 1311544 The one on PYPI: MD5 (SQLAlchemy-0.4.6.tar.gz) = 3d1e737bb408de25b2fadb19a736b40e SIZE (SQLAlchemy-0.4.6.tar.gz) = 1311536 However it looks the content are identical. Could anyone please have a check on this? Thanks, -- Dryice @ http://dryice.name Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/sylvester-response.html --~--~-~--~~~---~--~~ 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: Unknown inheritance type question
this mapping: transaction_sale_join = join(transactions, sales) mapper(Sale, transaction_sale_join, inherits=transactions_mapper, polymorphic_identity=3, properties={ 'line_items': relation(LineItem, backref='sale', cascade='all, delete-orphan') }) is incorrect. The inherits configuration will create the join from transactions-sales for you - Sale should be mapped directly to sales. On Jun 4, 2008, at 6:29 PM, Brad Wells wrote: The complexity of the following setup is that of the transaction type Sales also have their own table. I am unsure of how properly establish this relationship between Transactions, Sales and TransactionTypes. This setup so far allows me to create Sale objects and save them. However Sale.query.all() (for example) results in: OperationalError: (OperationalError) (1066, Not unique table/alias: 'transactions') any advice is appreciated. Tables: transactions = Table('transactions', meta, Column('id', Integer, primary_key=True), Column('transaction_type_id', Integer), ForeignKeyConstraint(['transaction_type_id'], ['transaction_types.id']), ) transaction_types = Table('transaction_types', meta, Column('id', Integer, primary_key=True), Column('name', String(15)), Column('has_line_items', Boolean), ) sales = Table('sales', meta, Column('id', Integer, primary_key=True), Column('address', Text), Column('shipping', Float), Column('handling', Float), Column('purchase_order', String(35)), Column('transaction_id', Integer), ForeignKeyConstraint(['transaction_id'], ['transactions.id']), ) line_items = Table('line_items', meta, Column('id', Integer, primary_key=True), Column('position', Integer), Column('description', Text), Column('quantity', Float), Column('units', String(15)), Column('unit_rate', Float), Column('tax', Float), Column('transaction_id', Integer), ForeignKeyConstraint(['transaction_id'], ['transactions.id']), ) Classes: class Transaction(Entity): pass class TransactionType(Entity): pass class Payment(Transaction): pass class Adjustment(Transaction): pass class Receipt(Transaction): pass # abstract class class LineItemTransaction(Transaction): pass class Cost(LineItemTransaction): pass class Sale(LineItemTransaction): pass class LineItem(Entity): pass Mappers: mapper(TransactionType, transaction_types) transactions_mapper = mapper(Transaction, transactions, polymorphic_on=transactions.c.transaction_type_id, polymorphic_identity=0, properties={ 'transaction_type': relation(TransactionType, backref='transactions'), }) mapper(Cost, inherits=transactions_mapper, polymorphic_identity=1, properties={ 'line_items': relation(LineItem, backref='cost', cascade='all, delete-orphan') }) mapper(Payment, inherits=transactions_mapper, polymorphic_identity=2) mapper(Receipt, inherits=transactions_mapper, polymorphic_identity=4) mapper(Adjustment, inherits=transactions_mapper, polymorphic_identity=5) transaction_sale_join = join(transactions, sales) mapper(Sale, transaction_sale_join, inherits=transactions_mapper, polymorphic_identity=3, properties={ 'line_items': relation(LineItem, backref='sale', cascade='all, delete-orphan') }) mapper(LineItem, line_items) -brad --~--~-~--~~~---~--~~ 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: 0.4.6 tar balls on SF and pypi are different?
On Jun 5, 2008, at 10:13 AM, Dryice Liu wrote: I just noticed the tarballs on SF and pypi are different. The one on SF: MD5 (SQLAlchemy-0.4.6.tar.gz) = 3043efb59000887ebe13fdcd6b6efadb SIZE (SQLAlchemy-0.4.6.tar.gz) = 1311544 The one on PYPI: MD5 (SQLAlchemy-0.4.6.tar.gz) = 3d1e737bb408de25b2fadb19a736b40e SIZE (SQLAlchemy-0.4.6.tar.gz) = 1311536 However it looks the content are identical. Could anyone please have a check on this? I've noticed this before, and this seems to be a common side effect of the difference between: python setup.py sdist and python setup.py sdist upload issue for the setuptools list perhaps ? --~--~-~--~~~---~--~~ 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] Inheritance and self-relation in child
Hi, I have two tables: Image and PersistentImage, where the latter inherits the former. In addition, PersistentImage has a foreign key to itself (ie, all PersistentImage objects form a hierarchy among themselves). I haven't been able to get this to work - first it complained about wanting a primaryjoin, then it seemed to get confused between the parent/children relation among PersistentImage objects and the inheritance between PersistentImage and Image. Here are my mappers: mapper( Image, imageTable, polymorphic_on=imageTable.c.type, polymorphic_identity='Image' ) mapper( PersistentImage, persistentImageTable, inherits=Image, polymorphic_identity='PersistentImage', properties={ 'children': relation(PersistentImage, backref='parent') }) And here are the actual table definitions: imageTable = Table('Images', metadata, Column('id', Integer, primary_key=True), Column('name', String(256), nullable=False), Column('type', String(30), nullable=False) ) persistentImageTable = Table('PersistentImages', metadata, Column('id', Integer, ForeignKey('Images.id'), primary_key=True), Column('parentId', Integer, ForeignKey('PersistentImages.id')), Column('userId', Integer, ForeignKey('Users.id'), nullable=False) ) 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unknown inheritance type question
Thank you very much. again. -brad On Jun 5, 10:57 am, Michael Bayer [EMAIL PROTECTED] wrote: this mapping: transaction_sale_join = join(transactions, sales) mapper(Sale, transaction_sale_join, inherits=transactions_mapper, polymorphic_identity=3, properties={ 'line_items': relation(LineItem, backref='sale', cascade='all, delete-orphan') }) is incorrect. The inherits configuration will create the join from transactions-sales for you - Sale should be mapped directly to sales. On Jun 4, 2008, at 6:29 PM, Brad Wells wrote: The complexity of the following setup is that of the transaction type Sales also have their own table. I am unsure of how properly establish this relationship between Transactions, Sales and TransactionTypes. This setup so far allows me to create Sale objects and save them. However Sale.query.all() (for example) results in: OperationalError: (OperationalError) (1066, Not unique table/alias: 'transactions') any advice is appreciated. Tables: transactions = Table('transactions', meta, Column('id', Integer, primary_key=True), Column('transaction_type_id', Integer), ForeignKeyConstraint(['transaction_type_id'], ['transaction_types.id']), ) transaction_types = Table('transaction_types', meta, Column('id', Integer, primary_key=True), Column('name', String(15)), Column('has_line_items', Boolean), ) sales = Table('sales', meta, Column('id', Integer, primary_key=True), Column('address', Text), Column('shipping', Float), Column('handling', Float), Column('purchase_order', String(35)), Column('transaction_id', Integer), ForeignKeyConstraint(['transaction_id'], ['transactions.id']), ) line_items = Table('line_items', meta, Column('id', Integer, primary_key=True), Column('position', Integer), Column('description', Text), Column('quantity', Float), Column('units', String(15)), Column('unit_rate', Float), Column('tax', Float), Column('transaction_id', Integer), ForeignKeyConstraint(['transaction_id'], ['transactions.id']), ) Classes: class Transaction(Entity): pass class TransactionType(Entity): pass class Payment(Transaction): pass class Adjustment(Transaction): pass class Receipt(Transaction): pass # abstract class class LineItemTransaction(Transaction): pass class Cost(LineItemTransaction): pass class Sale(LineItemTransaction): pass class LineItem(Entity): pass Mappers: mapper(TransactionType, transaction_types) transactions_mapper = mapper(Transaction, transactions, polymorphic_on=transactions.c.transaction_type_id, polymorphic_identity=0, properties={ 'transaction_type': relation(TransactionType, backref='transactions'), }) mapper(Cost, inherits=transactions_mapper, polymorphic_identity=1, properties={ 'line_items': relation(LineItem, backref='cost', cascade='all, delete-orphan') }) mapper(Payment, inherits=transactions_mapper, polymorphic_identity=2) mapper(Receipt, inherits=transactions_mapper, polymorphic_identity=4) mapper(Adjustment, inherits=transactions_mapper, polymorphic_identity=5) transaction_sale_join = join(transactions, sales) mapper(Sale, transaction_sale_join, inherits=transactions_mapper, polymorphic_identity=3, properties={ 'line_items': relation(LineItem, backref='sale', cascade='all, delete-orphan') }) mapper(LineItem, line_items) -brad --~--~-~--~~~---~--~~ 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: Inheritance and self-relation in child
On Jun 5, 2008, at 11:18 AM, Tomer wrote: Hi, I have two tables: Image and PersistentImage, where the latter inherits the former. In addition, PersistentImage has a foreign key to itself (ie, all PersistentImage objects form a hierarchy among themselves). I haven't been able to get this to work - first it complained about wanting a primaryjoin, then it seemed to get confused between the parent/children relation among PersistentImage objects and the inheritance between PersistentImage and Image. Here are my mappers: mapper( Image, imageTable, polymorphic_on=imageTable.c.type, polymorphic_identity='Image' ) mapper( PersistentImage, persistentImageTable, inherits=Image, polymorphic_identity='PersistentImage', properties={ 'children': relation(PersistentImage, backref='parent') }) And here are the actual table definitions: imageTable = Table('Images', metadata, Column('id', Integer, primary_key=True), Column('name', String(256), nullable=False), Column('type', String(30), nullable=False) ) persistentImageTable = Table('PersistentImages', metadata, Column('id', Integer, ForeignKey('Images.id'), primary_key=True), Column('parentId', Integer, ForeignKey('PersistentImages.id')), Column('userId', Integer, ForeignKey('Users.id'), nullable=False) ) here it is: mapper(PersistentImage, persistentImageTable, inherits=Image, inherit_condition=persistentImageTable.c.id==imageTable.c.id, polymorphic_identity='PersistentImage', properties={ 'children':relation(PersistentImage, primaryjoin=persistentImageTable.c.parentId==persistentImageTable.c.id, backref=backref('parent', primaryjoin =persistentImageTable.c.parentId==persistentImageTable.c.id, remote_side=[persistentImageTable.c.id]) ) }) SQLA would probably slightly happier if you had the parentId foreign key referencing Images.id instead of PersistentImages.id but this should not be required. --~--~-~--~~~---~--~~ 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: Inheritance and self-relation in child
Worked like magic!! Thanks! BTW, why wasn't SQLA able to determine this automatically like it usually does? On Jun 5, 11:30 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 5, 2008, at 11:18 AM, Tomer wrote: Hi, I have two tables: Image and PersistentImage, where the latter inherits the former. In addition, PersistentImage has a foreign key to itself (ie, all PersistentImage objects form a hierarchy among themselves). I haven't been able to get this to work - first it complained about wanting a primaryjoin, then it seemed to get confused between the parent/children relation among PersistentImage objects and the inheritance between PersistentImage and Image. Here are my mappers: mapper( Image, imageTable, polymorphic_on=imageTable.c.type, polymorphic_identity='Image' ) mapper( PersistentImage, persistentImageTable, inherits=Image, polymorphic_identity='PersistentImage', properties={ 'children': relation(PersistentImage, backref='parent') }) And here are the actual table definitions: imageTable = Table('Images', metadata, Column('id', Integer, primary_key=True), Column('name', String(256), nullable=False), Column('type', String(30), nullable=False) ) persistentImageTable = Table('PersistentImages', metadata, Column('id', Integer, ForeignKey('Images.id'), primary_key=True), Column('parentId', Integer, ForeignKey('PersistentImages.id')), Column('userId', Integer, ForeignKey('Users.id'), nullable=False) ) here it is: mapper(PersistentImage, persistentImageTable, inherits=Image, inherit_condition=persistentImageTable.c.id==imageTable.c.id, polymorphic_identity='PersistentImage', properties={ 'children':relation(PersistentImage, primaryjoin=persistentImageTable.c.parentId==persistentImageTable.c.id, backref=backref('parent', primaryjoin =persistentImageTable.c.parentId==persistentImageTable.c.id, remote_side=[persistentImageTable.c.id]) ) }) SQLA would probably slightly happier if you had the parentId foreign key referencing Images.id instead of PersistentImages.id but this should not be required. --~--~-~--~~~---~--~~ 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: Inheritance and self-relation in child
On Jun 5, 2008, at 11:42 AM, Tomer wrote: Worked like magic!! Thanks! BTW, why wasn't SQLA able to determine this automatically like it usually does? when you join PersistentImage-PersistentImage, theres two ways to join on foreign keys between those (remember that a PersistentImage is also an Image). So SQLA reports that this is ambiguous. On Jun 5, 11:30 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 5, 2008, at 11:18 AM, Tomer wrote: Hi, I have two tables: Image and PersistentImage, where the latter inherits the former. In addition, PersistentImage has a foreign key to itself (ie, all PersistentImage objects form a hierarchy among themselves). I haven't been able to get this to work - first it complained about wanting a primaryjoin, then it seemed to get confused between the parent/children relation among PersistentImage objects and the inheritance between PersistentImage and Image. Here are my mappers: mapper( Image, imageTable, polymorphic_on=imageTable.c.type, polymorphic_identity='Image' ) mapper( PersistentImage, persistentImageTable, inherits=Image, polymorphic_identity='PersistentImage', properties={ 'children': relation(PersistentImage, backref='parent') }) And here are the actual table definitions: imageTable = Table('Images', metadata, Column('id', Integer, primary_key=True), Column('name', String(256), nullable=False), Column('type', String(30), nullable=False) ) persistentImageTable = Table('PersistentImages', metadata, Column('id', Integer, ForeignKey('Images.id'), primary_key=True), Column('parentId', Integer, ForeignKey('PersistentImages.id')), Column('userId', Integer, ForeignKey('Users.id'), nullable=False) ) here it is: mapper(PersistentImage, persistentImageTable, inherits=Image, inherit_condition=persistentImageTable.c.id==imageTable.c.id, polymorphic_identity='PersistentImage', properties={ 'children':relation(PersistentImage, primaryjoin =persistentImageTable.c.parentId==persistentImageTable.c.id, backref=backref('parent', primaryjoin =persistentImageTable.c.parentId==persistentImageTable.c.id, remote_side=[persistentImageTable.c.id]) ) }) SQLA would probably slightly happier if you had the parentId foreign key referencing Images.id instead of PersistentImages.id but this should not be required. --~--~-~--~~~---~--~~ 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: Inheritance and self-relation in child
Right, I missed that part. Thanks for the explanation... On Jun 5, 12:02 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 5, 2008, at 11:42 AM, Tomer wrote: Worked like magic!! Thanks! BTW, why wasn't SQLA able to determine this automatically like it usually does? when you join PersistentImage-PersistentImage, theres two ways to join on foreign keys between those (remember that a PersistentImage is also an Image). So SQLA reports that this is ambiguous. On Jun 5, 11:30 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 5, 2008, at 11:18 AM, Tomer wrote: Hi, I have two tables: Image and PersistentImage, where the latter inherits the former. In addition, PersistentImage has a foreign key to itself (ie, all PersistentImage objects form a hierarchy among themselves). I haven't been able to get this to work - first it complained about wanting a primaryjoin, then it seemed to get confused between the parent/children relation among PersistentImage objects and the inheritance between PersistentImage and Image. Here are my mappers: mapper( Image, imageTable, polymorphic_on=imageTable.c.type, polymorphic_identity='Image' ) mapper( PersistentImage, persistentImageTable, inherits=Image, polymorphic_identity='PersistentImage', properties={ 'children': relation(PersistentImage, backref='parent') }) And here are the actual table definitions: imageTable = Table('Images', metadata, Column('id', Integer, primary_key=True), Column('name', String(256), nullable=False), Column('type', String(30), nullable=False) ) persistentImageTable = Table('PersistentImages', metadata, Column('id', Integer, ForeignKey('Images.id'), primary_key=True), Column('parentId', Integer, ForeignKey('PersistentImages.id')), Column('userId', Integer, ForeignKey('Users.id'), nullable=False) ) here it is: mapper(PersistentImage, persistentImageTable, inherits=Image, inherit_condition=persistentImageTable.c.id==imageTable.c.id, polymorphic_identity='PersistentImage', properties={ 'children':relation(PersistentImage, primaryjoin =persistentImageTable.c.parentId==persistentImageTable.c.id, backref=backref('parent', primaryjoin =persistentImageTable.c.parentId==persistentImageTable.c.id, remote_side=[persistentImageTable.c.id]) ) }) SQLA would probably slightly happier if you had the parentId foreign key referencing Images.id instead of PersistentImages.id but this should not be required. --~--~-~--~~~---~--~~ 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] Optimizing a slow query
Hi, we're writing a bulleting board using sqlalchemy at the moment, but we have the problem, that the database query for viewing a topic is quite slow for big topics. These are the relevant table definitions and mappings: http://paste.pocoo.org/show/62703/ This is the query that is slow: http://paste.pocoo.org/show/62706/ This is what EXPLAIN says: http://paste.pocoo.org/show/62708/ Executing this query needs up to 25 seconds on our test server that's just idling. On our productive servers (which aren't idling, of course) phpbb is able to execute an adequate query much faster. May you can help us speeding up this query? I don't know what to improve, since all parts of the query already use a key (except the derived one), but unfortunately I have a quite small knowledge of improving database queries. Thank you very much, Benjamin Wiegand --~--~-~--~~~---~--~~ 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: Optimizing a slow query
for starters I'd combine post_table and post_text_table into onenot much is accomplished there by having two tables. Also make sure forum_post.topic_id is indexed. On Jun 5, 2008, at 1:37 PM, beewee wrote: Hi, we're writing a bulleting board using sqlalchemy at the moment, but we have the problem, that the database query for viewing a topic is quite slow for big topics. These are the relevant table definitions and mappings: http://paste.pocoo.org/show/62703/ This is the query that is slow: http://paste.pocoo.org/show/62706/ This is what EXPLAIN says: http://paste.pocoo.org/show/62708/ Executing this query needs up to 25 seconds on our test server that's just idling. On our productive servers (which aren't idling, of course) phpbb is able to execute an adequate query much faster. May you can help us speeding up this query? I don't know what to improve, since all parts of the query already use a key (except the derived one), but unfortunately I have a quite small knowledge of improving database queries. Thank you very much, Benjamin Wiegand --~--~-~--~~~---~--~~ 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] out of range / locate column / lost connection
I am having some database problems (attached below), any recommendations to either recover from or fix these from happening? I am not sure why it would work OK at first, and then at some point run bad... Any recommendations on a technique to debug this would be much appreciated. Full source code of the project can be browsed here: http://git.braydon.com/gitweb.cgi?p=sparrow;a=tree;h=refs/heads/master;hb=refs/heads/master For an idea of the project (screencast... although slightly old): http://interfce.com/videos/sparrow.html The site this is coming from (live): http://mochilla.com/ [05/Jun/2008:22:16:15] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/local/mochilla/sparrow/http.py, line 38, in default return render(args, kwargs, location_st) File /var/local/mochilla/sparrow/templates.py, line 363, in render return render_skeleton(location, location_st, kwargs) File /var/local/mochilla/sparrow/templates.py, line 266, in render_skeleton body, cmds_array = compile_templates(location, kwargs) File /var/local/mochilla/sparrow/templates.py, line 192, in compile_templates doc = get_uri(location) File /var/local/mochilla/sparrow/uri.py, line 31, in get_uri selected = db.session.query(TemplateRelation).filter_by(location=uri)[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 844, in __getitem__ return list(self[item:item+1])[0] IndexError: list index out of range 127.0.0.1 - - [05/Jun/2008:22:15:44] GET / HTTP/1.0 500 3293 [05/Jun/2008:22:15:59] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/local/mochilla/sparrow/http.py, line 38, in default return render(args, kwargs, location_st) File /var/local/mochilla/sparrow/templates.py, line 363, in render return render_skeleton(location, location_st, kwargs) File /var/local/mochilla/sparrow/templates.py, line 266, in render_skeleton body, cmds_array = compile_templates(location, kwargs) File /var/local/mochilla/sparrow/templates.py, line 252, in compile_templates data = search_list(doc.oid, doc.objects, location=location, locations=locations, cmds_array=doc.cmds_array, kwargs=kwargs) File /var/local/mochilla/sparrow/templates.py, line 172, in search_list objects = object_children(object_oid) File /var/local/mochilla/sparrow/objects.py, line 39, in object_children parent = get_object(oid) File /var/local/mochilla/sparrow/objects.py, line 31, in get_object selected = db.session.query(Objects).filter_by(oid=oid).one() File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 927, in one raise exceptions.InvalidRequestError('No rows returned for one()') InvalidRequestError: No rows returned for one() 127.0.0.1 - - [05/Jun/2008:18:15:08] GET / HTTP/1.0 200 24400 [05/Jun/2008:18:15:09] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/lib/python-support/python2.5/cherrypy/_cptools.py, line 140, in handle_func handled = self.callable(*args, **self._merged_args(kwargs)) TypeError: staticfile() got multiple values for keyword argument 'filename' class 'sqlalchemy.exceptions.NoSuchColumnError' [05/Jun/2008:21:42:22] HTTP Traceback (most recent call last): File /var/lib/python-support/python2.5/cherrypy/_cprequest.py, line 550, in respond cherrypy.response.body = self.handler() File /var/lib/python-support/python2.5/cherrypy/_cpdispatch.py, line 24, in __call__ return self.callable(*self.args, **self.kwargs) File /var/local/mochilla/sparrow/http.py, line 38, in default return render(args, kwargs, location_st) File /var/local/mochilla/sparrow/templates.py, line 346, in render get_uri(location_id) File /var/local/mochilla/sparrow/uri.py, line 31, in get_uri selected = db.session.query(TemplateRelation).filter_by(location=uri)[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 844, in __getitem__ return list(self[item:item+1])[0] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 986, in iterate_instances rows = [process[0](context, row) for row in fetch] File /var/lib/python-support/python2.5/sqlalchemy/orm/query.py, line 1551, in main
[sqlalchemy] Re: Schema and database migration: how to diff?
[EMAIL PROTECTED] writes: see dbcook.misc.metadata.diff.py as an attempt to do this over 2 metadata's. svn co https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/misc/metadata It works pretty well. How about a small cleanup to make it truly general an a promotion to a package of its own? With the `changeset` parts of sqlalchemy_migration, we could generate most of the upgrade script from the computed diff. Unless you already do that and I missed that part somehow. -- Yannick Gingras --~--~-~--~~~---~--~~ 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: 0.4.6 tar balls on SF and pypi are different?
Michael Bayer [EMAIL PROTECTED] wrote: On Jun 5, 2008, at 10:13 AM, Dryice Liu wrote: I just noticed the tarballs on SF and pypi are different. The one on SF: MD5 (SQLAlchemy-0.4.6.tar.gz) = 3043efb59000887ebe13fdcd6b6efadb SIZE (SQLAlchemy-0.4.6.tar.gz) = 1311544 The one on PYPI: MD5 (SQLAlchemy-0.4.6.tar.gz) = 3d1e737bb408de25b2fadb19a736b40e SIZE (SQLAlchemy-0.4.6.tar.gz) = 1311536 However it looks the content are identical. Could anyone please have a check on this? I've noticed this before, and this seems to be a common side effect of the difference between: python setup.py sdist and python setup.py sdist upload issue for the setuptools list perhaps ? Thanks for the clearance, Michael. Cheers, -- Dryice @ http://dryice.name Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/sylvester-response.html --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---