[sqlalchemy] Re: Multiprocess issues
neative built a bunch of those job engines in other languages, is it something you could post as a recipe / example ? sure, I'll put up something in the next few days - it will let me see if it works with sqlite as well --~--~-~--~~~---~--~~ 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] Creating column comments on the database
Does SQLAlchemy support comment on column or comments on other database objects? I expected Column to have a comment keyword, but found nothing of that kind. Maybe I am missing something? I find column comments often useful, and though they may not be SQL standard, they are supported by all major databases. -- 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] Re: Creating column comments on the database
at the very least you could use per-table DDL() with an ALTER to apply it after the fact... On Jun 4, 2008, at 8:41 AM, Christoph Zwerschke wrote: Does SQLAlchemy support comment on column or comments on other database objects? I expected Column to have a comment keyword, but found nothing of that kind. Maybe I am missing something? I find column comments often useful, and though they may not be SQL standard, they are supported by all major databases. -- 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] Inheritacne, reflecting tables and CircularDependencyError
I have a PostgreSQL database with 3 tables: Object, Role and User:
CREATE TABLE Object
(
ObjectID BIGINT NOT NULL DEFAULT NEXTVAL('SEQ_ObjectID'),
ObjectTypeID INT NOT NULL,
OwnerUserID BIGINT NOT NULL,
CreatedTimestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
NOW(),
LastModifiedUserID BIGINT DEFAULT NULL,
CONSTRAINT PK_Object PRIMARY KEY (ObjectID),
CONSTRAINT FK_Object_ObjectType FOREIGN KEY (ObjectTypeID)
REFERENCES ObjectType(ObjectTypeID)
);
CREATE TABLE Role
(
RoleID BIGINT NOT NULL DEFAULT NEXTVAL('SEQ_ObjectID'),
CONSTRAINT PK_Role PRIMARY KEY (RoleID),
CONSTRAINT FK_Role_Object FOREIGN KEY (RoleID) REFERENCES
Object(ObjectID)
ON DELETE CASCADE
);
CREATE TABLE User
(
UserID BIGINT NOT NULL DEFAULT NEXTVAL('SEQ_ObjectID'),
CONSTRAINT PK_User PRIMARY KEY (UserID),
CONSTRAINT FK_User_Role FOREIGN KEY (UserID) REFERENCES
Role(RoleID)
ON DELETE RESTRICT
);
ALTER TABLE Object
ADD CONSTRAINT FK_Object_owner FOREIGN KEY (OwnerUserID)
REFERENCES User(UserID)
ON DELETE RESTRICT;
ALTER TABLE Object
ADD CONSTRAINT FK_Object_last_modified FOREIGN KEY
(LastModifiedUserID) REFERENCES User(UserID)
ON DELETE RESTRICT;
Then I reflect the tables:
object_table = sa.Table('Object', SA_Metadata, autoload = True,
autoload_with=engine)
role_table = sa.Table('Role', SA_Metadata, autoload = True,
autoload_with=engine)
user_table = sa.Table('User', SA_Metadata, autoload = True,
autoload_with=engine)
And map them:
orm.mapper(db.Object, object_table,
polymorphic_on=object_table.c.ObjectTypeID, polymorphic_identity=1)
orm.mapper(db.Role, role_table, inherits=db.Object,
polymorphic_identity=2)
orm.mapper(db.User, user_table, inherits=db.Role,
polymorphic_identity=3)
And when I try to insert a new User:
user = db.User()
user.OwnerUserID = 1
db_session.save(user)
db_session.flush()
I get this error:
sqlalchemy.exceptions.CircularDependencyError: Circular dependency
detected [(Role, User), (Object, Role), (User, Object)][]
The tables are circular, but the UserID and OwnerUserID are never
identical for any User that SQLAlchemy should insert. I spent this
whole day looking for a solution, but there were none.
--~--~-~--~~~---~--~~
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] mssql and offset support
Hello, From Turbogears website I found out that mssql doesn't support offset query. read below. On Wed, Jun 4, 2008 at 4:20 PM, Lukasz Szybalski [EMAIL PROTECTED] wrote: Paginate uses the same output from the same method, but slices the result differently, that's why it is important to not use .all() (if you are not on MSSQL server, because MSSQL server does not support the use of offsets in queries). I took off the .all() just to see what happens and here comes the error. So I guess I have to use .all()?!? File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQLAlchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mssql.py, line 498, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self.identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception Error location in template file '/home/unique/turbogears///templates/active.kid' on line 25 between columns 3 and 72: ... span py:content=activegrid(active)some content/span ... File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQLAlchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mssql.py, line 498, in do_execute What is interesting is the end of the traceback not the beginning, but I suspect the error you get is because you are using MSSQL and it does not support offsets. I know some people are working on implementing an offest support for MSSQL to be able to use proxy results instead of using all() on MSSQL. I guess my question is, if wanted to use offset. How would I do it? Postgresql, Mysql Oracle all support offsets. AFAIK Oracle doesn't. You can fake it using an embedded SQL and the implicit rownum-column. but that will cost performance. If you can, keep a referernce to the cursor around. Lucas --~--~-~--~~~---~--~~ 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 Wed, Jun 4, 2008 at 7:41 AM, Christoph Zwerschke [EMAIL PROTECTED] wrote: Does SQLAlchemy support comment on column or comments on other database objects? I expected Column to have a comment keyword, but found nothing of that kind. Maybe I am missing something? I find column comments often useful, and though they may not be SQL standard, they are supported by all major databases. I started using info dict on a column names in sa file definitions. It would be nice to save it to db if once could. What db supports comments and what table name is it? Lucas --~--~-~--~~~---~--~~ 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: mssql and offset support
There's offset support in the current sqla mssql driver. It's implemented using the ansi ROW_NUMBER() OVER construct, which is supported only in mssql 2005 and higher. To turn it on, add the has_window_funcs keyword in the dburi, or as an engine constructor keyword. the broken traceback is some as-of-yet unknown issue with pyodbc that reports the wrong stack trace. --~--~-~--~~~---~--~~ 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: mssql and offset support
er, that's, has_window_funcs=1 --~--~-~--~~~---~--~~ 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: Inheritacne, reflecting tables and CircularDependencyError
Just like you needed to use an ALTER statement to add the keys from
Users-Objects, SQLA needs the same courtesy so it knows that no
dependency should be stated when it inserts a row into all three
tables for your User object. The INSERT of the user knows it must
insert a row into three tables, so it wants to insert in their order
of dependency. This is done for the full set of rows to be inserted
for the Object hierarchy so it doesn't necessarily know that the
OwnerUserID column is NULL in all cases. The list of tables needs a
clue as to what their natural dependency order is since its ambiguous
here (you could make the argument that their dependency order *is*
unambiguous due to the mapping applied to them, so perhaps this would
make a nice improvement at some point. in general this is a really
rare occurence).
So autoload Object as follows:
object_table = Table('Object', metadata,
Column(OwnerUserID, Integer, ForeignKey(User.UserID,
name='x', use_alter=True)),
Column(LastModifiedUserID, Integer, ForeignKey(User.UserID,
name='x', use_alter=True)),
autoload = True, autoload_with=engine)
the datatypes and foreign key names are not crucial here since you
aren't issuing a CREATE TABLE.
On Jun 4, 2008, at 10:54 AM, Marin wrote:
I have a PostgreSQL database with 3 tables: Object, Role and User:
CREATE TABLE Object
(
ObjectID BIGINT NOT NULL DEFAULT NEXTVAL('SEQ_ObjectID'),
ObjectTypeID INT NOT NULL,
OwnerUserID BIGINT NOT NULL,
CreatedTimestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
NOW(),
LastModifiedUserID BIGINT DEFAULT NULL,
CONSTRAINT PK_Object PRIMARY KEY (ObjectID),
CONSTRAINT FK_Object_ObjectType FOREIGN KEY (ObjectTypeID)
REFERENCES ObjectType(ObjectTypeID)
);
CREATE TABLE Role
(
RoleID BIGINT NOT NULL DEFAULT NEXTVAL('SEQ_ObjectID'),
CONSTRAINT PK_Role PRIMARY KEY (RoleID),
CONSTRAINT FK_Role_Object FOREIGN KEY (RoleID) REFERENCES
Object(ObjectID)
ON DELETE CASCADE
);
CREATE TABLE User
(
UserID BIGINT NOT NULL DEFAULT NEXTVAL('SEQ_ObjectID'),
CONSTRAINT PK_User PRIMARY KEY (UserID),
CONSTRAINT FK_User_Role FOREIGN KEY (UserID) REFERENCES
Role(RoleID)
ON DELETE RESTRICT
);
ALTER TABLE Object
ADD CONSTRAINT FK_Object_owner FOREIGN KEY (OwnerUserID)
REFERENCES User(UserID)
ON DELETE RESTRICT;
ALTER TABLE Object
ADD CONSTRAINT FK_Object_last_modified FOREIGN KEY
(LastModifiedUserID) REFERENCES User(UserID)
ON DELETE RESTRICT;
Then I reflect the tables:
object_table = sa.Table('Object', SA_Metadata, autoload = True,
autoload_with=engine)
role_table = sa.Table('Role', SA_Metadata, autoload = True,
autoload_with=engine)
user_table = sa.Table('User', SA_Metadata, autoload = True,
autoload_with=engine)
And map them:
orm.mapper(db.Object, object_table,
polymorphic_on=object_table.c.ObjectTypeID, polymorphic_identity=1)
orm.mapper(db.Role, role_table, inherits=db.Object,
polymorphic_identity=2)
orm.mapper(db.User, user_table, inherits=db.Role,
polymorphic_identity=3)
And when I try to insert a new User:
user = db.User()
user.OwnerUserID = 1
db_session.save(user)
db_session.flush()
I get this error:
sqlalchemy.exceptions.CircularDependencyError: Circular dependency
detected [(Role, User), (Object, Role), (User, Object)][]
The tables are circular, but the UserID and OwnerUserID are never
identical for any User that SQLAlchemy should insert. I spent this
whole day looking for a solution, but there were none.
--~--~-~--~~~---~--~~
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: mssql and offset support
On Wed, Jun 4, 2008 at 10:22 AM, Rick Morrison [EMAIL PROTECTED] wrote: There's offset support in the current sqla mssql driver. It's implemented using the ansi ROW_NUMBER() OVER construct, which is supported only in mssql 2005 and higher. Since I am using 2000 I don't think its going to work for me do?!? To turn it on, add the has_window_funcs keyword in the dburi, or as an engine constructor keyword. the broken traceback is some as-of-yet unknown issue with pyodbc that reports the wrong stack trace. -- 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] Re: mssql and offset support
Since I am using 2000 I don't think its going to work for me do?!? nope --~--~-~--~~~---~--~~ 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
Lukasz Szybalski schrieb:
I started using info dict on a column names in sa file definitions. It
would be nice to save it to db if once could. What db supports
comments and what table name is it?
Oracle and PostgreSQL have the comment on SQL statement. MySQL has a
comment clause for column definitions. SA could abstract away these
differences, the syntax would be something like this:
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), comment=The Unix user name),
Column('fullname', String(100), comment=Full name with title))
This way, the table definition in Python itself would be commented, and
at the same time, comments on the database would be created that help
you if you operate directly on the database with some admin tool.
-- 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] Re: Creating column comments on the database
On Wed, Jun 4, 2008 at 12:09 PM, Christoph Zwerschke [EMAIL PROTECTED] wrote:
Lukasz Szybalski schrieb:
I started using info dict on a column names in sa file definitions. It
would be nice to save it to db if once could. What db supports
comments and what table name is it?
Oracle and PostgreSQL have the comment on SQL statement. MySQL has a
comment clause for column definitions. SA could abstract away these
differences, the syntax would be something like this:
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), comment=The Unix user name),
Column('fullname', String(100), comment=Full name with title))
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}),
Would be nice if the whole dictionary was written to a database. I
could use info[description], info[FI], info[TB] etc... when I display
data. That would be really useful definition!!!
Lucas
--~--~-~--~~~---~--~~
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] Unknown inheritance type question
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
-~--~~~~--~~--~--~---
