[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 -~--~~~~--~~--~--~---
