[sqlalchemy] Re: MSSQL and LIMIT/OFFSET
The hardest part I guess is when we encounter a _TextClause based order_by, assuming there is no builtin way to parse a _TextClause back down to column, table desc etc... On Jan 10, 6:47 pm, Esceo [EMAIL PROTECTED] wrote: Something more is probably needed. adding row_number will render a distinct clause useless... need to form an inner select with the original select, and at the same time, make sure the order_by clause of the row_number field gets rewritten, I had a patch for 0.3 (working in all the cases I've encountered), but I guess I should ask about how we may get the corresponding order_by clause in 0.4 Lei On Nov 27 2007, 7:18 am, Michael Bayer [EMAIL PROTECTED] wrote: On Nov 26, 2007, at 2:25 PM, Paul Johnston wrote: Hi, Can someone (Mike?) give me a hand with this, I've hit the limits of my understanding of the query compiler. The patch I've done on #638 (mostly copied from Oracle) creates a subquery with row_number if there is an OFFSET. It aliases the query, because MSSQL is funny about that, and it also attempts to move ORDER BY from the inner to outer query (again, MSSQL is funny). Only problem is that it doesn't quite pick up the correct labelled name. Uncomment this line in the patch to see the problem: #limitselect._order_by_clause = select._order_by_clause Any ideas would be a help (and a query compiler 101 document would be fab!) attached is a modified version of that patch which includes the unit test. If the SQL isnt right, change the unit tests first to reflect what results you'd like to see, then we can try tweaking it. the key feature used here is the proxies element on Column. this is also a new feature, it was previously some nasty methodology before 0.4.1. if i have a table: create table sometable (a int, b int) and then a select: select a, b from sometable and an aliased version of it: (select a, b from sometable) as anon_1 you've got six columns in play. you have table.a, table.b; you have select.a and select.b, and anon1.a and anon_1.b. The proxies collection allows you to navigate from anon_1.a to select.a back to table.a; its a collection that in almost all cases has just one element (it only has multiple elements when a column is proxying a UNION or similar). So that way you can navigate to the column which an aliased column represents. mssql.638.patch 4KDownload - Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ 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 LIMIT/OFFSET
Hi, adding row_number will render a distinct clause useless... Actually, this isn't a problem, as the distinct goes in an inner query, and the row_number is only in the outer query. I did have this mostly working, just need a final push to get it finished and committed. Paul --~--~-~--~~~---~--~~ 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 LIMIT/OFFSET
Hi Paul, not quite, at least the patch above doesnt do that... Lei On Jan 10, 8:02 pm, Paul Johnston [EMAIL PROTECTED] wrote: Hi, adding row_number will render a distinct clause useless... Actually, this isn't a problem, as the distinct goes in an inner query, and the row_number is only in the outer query. I did have this mostly working, just need a final push to get it finished and committed. Paul --~--~-~--~~~---~--~~ 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 LIMIT/OFFSET
soon to be attached is a slightly more involved patch (white space used is different from source), wraps the original query inside an alias _msr, and deals with TextClause based order_by as well... the order_by choosen for row_number over is oid in the case when there is no order_by specified, there might be a more faithful implementation that uses an aliased null column and order by that in row_number over... Lei On Jan 10, 8:33 pm, Esceo [EMAIL PROTECTED] wrote: Hi Paul, not quite, at least the patch above doesnt do that... Lei On Jan 10, 8:02 pm, Paul Johnston [EMAIL PROTECTED] wrote: Hi, adding row_number will render a distinct clause useless... Actually, this isn't a problem, as the distinct goes in an inner query, and the row_number is only in the outer query. I did have this mostly working, just need a final push to get it finished and committed. Paul- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ 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 LIMIT/OFFSET
patch is attached --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- Index: mssql.py === --- mssql.py(revision 4043) +++ mssql.py(working copy) @@ -20,7 +20,7 @@ Note that the start increment values for sequences are optional and will default to 1,1. -* Support for ``SET IDENTITY_INSERT ON`` mode (automagic on / off for +* Support for ``SET IDENTITY_INSERT ON`` mode (automagic on / off for ``INSERT`` s) * Support for auto-fetching of ``@@IDENTITY/@@SCOPE_IDENTITY()`` on ``INSERT`` @@ -34,7 +34,7 @@ * pymssql has problems with binary and unicode data that this module does **not** work around - + import datetime, random, warnings, re, sys, operator @@ -44,7 +44,7 @@ from sqlalchemy.engine import default, base from sqlalchemy import types as sqltypes from sqlalchemy.util import Decimal as _python_Decimal - + MSSQL_RESERVED_WORDS = util.Set(['function']) class MSNumeric(sqltypes.Numeric): @@ -67,9 +67,9 @@ # Not sure that this exception is needed return value else: -return str(value) +return str(value) return process - + def get_col_spec(self): if self.precision is None: return NUMERIC @@ -87,7 +87,7 @@ return str(value) return None return process - + class MSInteger(sqltypes.Integer): def get_col_spec(self): return INTEGER @@ -116,14 +116,14 @@ super(MSDate, self).__init__(False) def get_col_spec(self): -return SMALLDATETIME +return DATETIME class MSTime(sqltypes.Time): __zero_date = datetime.date(1900, 1, 1) def __init__(self, *a, **kw): super(MSTime, self).__init__(False) - + def get_col_spec(self): return DATETIME @@ -135,7 +135,7 @@ value = datetime.datetime.combine(self.__zero_date, value) return value return process - + def result_processor(self, dialect): def process(value): if type(value) is datetime.datetime: @@ -144,7 +144,7 @@ return datetime.time(0, 0, 0) return value return process - + class MSDateTime_adodbapi(MSDateTime): def result_processor(self, dialect): def process(value): @@ -154,7 +154,7 @@ return datetime.datetime(value.year, value.month, value.day) return value return process - + class MSDateTime_pyodbc(MSDateTime): def bind_processor(self, dialect): def process(value): @@ -162,7 +162,7 @@ return datetime.datetime(value.year, value.month, value.day) return value return process - + class MSDate_pyodbc(MSDate): def bind_processor(self, dialect): def process(value): @@ -170,7 +170,7 @@ return datetime.datetime(value.year, value.month, value.day) return value return process - + def result_processor(self, dialect): def process(value): # pyodbc returns SMALLDATETIME values as datetime.datetime(). truncate it back to datetime.date() @@ -178,7 +178,7 @@ return value.date() return value return process - + class MSDate_pymssql(MSDate): def result_processor(self, dialect): def process(value): @@ -187,11 +187,11 @@ return value.date() return value return process - + class MSText(sqltypes.Text): def get_col_spec(self): if self.dialect.text_as_varchar: -return VARCHAR(max) +return VARCHAR(max) else: return TEXT @@ -238,7 +238,7 @@ return None return value and True or False return process - + def bind_processor(self, dialect): def process(value): if value is True: @@ -250,27 +250,27 @@ else: return value and True or False return process - + class MSTimeStamp(sqltypes.TIMESTAMP): def get_col_spec(self): return TIMESTAMP - + class MSMoney(sqltypes.TypeEngine): def get_col_spec(self): return MONEY - + class MSSmallMoney(MSMoney): def get_col_spec(self): return SMALLMONEY - + class MSUniqueIdentifier(sqltypes.TypeEngine): def get_col_spec(self): return UNIQUEIDENTIFIER -
[sqlalchemy] Re: MSSQL and LIMIT/OFFSET
the other part of the patch is a rewrite of INSERT INTO (.+) \(\) VALUES \(\)$ (insertion of empty list of values) into INSERT INTO %s DEFAULT VALUES which is problematic when we insert into a table with only one field and it's identity On Jan 10, 9:43 pm, lei you [EMAIL PROTECTED] wrote: patch is attached mssql.diff 26KDownload --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
On Jan 9, 9:24 pm, Michael Bayer [EMAIL PROTECTED] wrote: So if I understand what youre looking to do here, youd like a keyword to be deleted when the last parent is removed ? That is exactly what I would like... if youd like to check for this condition after each flush you can do it with SessionExtension, using after_flush(). it could be done in a single DELETE statement, i.e. delete from keywords where not exists(select 1 from keyword_associations where keyword_id=keywords.id) ...But I would also like to track tag deletion in SessionExtension, and If I'm using directly a DELETE statement, it won't trigger anything in SessionExtension. --~--~-~--~~~---~--~~ 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] SQLautocode 0.5.1 relased!
Hi, I'm very pleased to announce a new version of sqlautocode. It features many bugfixes and some refactored code. More information about sqlautocode: http://code.google.com/p/sqlautocode/ Special Thanks to Jason Kirtland - most of the refactoring and bugfixing was done by him! Simon Pamies --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
On Jan 10, 4:00 pm, Michael Bayer [EMAIL PROTECTED] wrote: A statement issued in SessionExtension would fire unconditionally upon any flush(), so thats the trigger there. not sure what you mean by track here, if it means you want to know the keywords that were deleted, you'd just issue the above SELECT first, do something with the rows, then the DELETE. I want to know the keywords that were deleted, but it's more complicated... keywords are mapped to Keyword objects with another relation and cascading rules. I would like those cascading rules to apply, and know it in SessionExtension. --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
On Jan 10, 2008, at 6:51 AM, Laurent Houdard wrote: if youd like to check for this condition after each flush you can do it with SessionExtension, using after_flush(). it could be done in a single DELETE statement, i.e. delete from keywords where not exists(select 1 from keyword_associations where keyword_id=keywords.id) ...But I would also like to track tag deletion in SessionExtension, and If I'm using directly a DELETE statement, it won't trigger anything in SessionExtension. A statement issued in SessionExtension would fire unconditionally upon any flush(), so thats the trigger there. not sure what you mean by track here, if it means you want to know the keywords that were deleted, you'd just issue the above SELECT first, do something with the rows, then the DELETE. --~--~-~--~~~---~--~~ 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] Depreciation warning
Sorry previous message, I've pressed a magic combination keys here and message was sent. however The least problem with the warning is that it not inform what table and column are wrong the traceback returns: /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2p3dev_r4036-py2.5.egg/sqlalchemy/databases/sqlite.py:389: SADeprecationWarning: Using String type with no length for CREATE TABLE is deprecated; use the Text type explicitly colspec = self.preparer.format_column(column) + + column.type.dialect_impl(self.dialect, _for_ddl=True).get_col_spec() my model is divided along many files to modularization, each file have more than 10 tables, the model seems to have just one wrong column, is not so dificult to me find the wrong colum, but if model was greater it could be. maybe you can include this information at traceback. Att, -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ 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: Many to many and orphan deletion
how about, go into sessionextension.after_flush(), make a new session local to the operation, issue a Query with the Select for all orphan keywords, delete them all and flush that sub-session, then expunge() those keywords from the parent session sent to after_flush, like this: [...] Brilliant !! I even can extend local session with the session extension itself in case of an orphan deletion deletes another orphan... --~--~-~--~~~---~--~~ 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: Depreciation warning
sure ive reopened 912 again so we can add that On Jan 10, 2008, at 11:04 AM, Alexandre da Silva wrote: Sorry previous message, I've pressed a magic combination keys here and message was sent. however The least problem with the warning is that it not inform what table and column are wrong the traceback returns: /usr/lib/python2.5/site-packages/SQLAlchemy-0.4.2p3dev_r4036- py2.5.egg/sqlalchemy/databases/sqlite.py:389: SADeprecationWarning: Using String type with no length for CREATE TABLE is deprecated; use the Text type explicitly colspec = self.preparer.format_column(column) + + column.type.dialect_impl(self.dialect, _for_ddl=True).get_col_spec() my model is divided along many files to modularization, each file have more than 10 tables, the model seems to have just one wrong column, is not so dificult to me find the wrong colum, but if model was greater it could be. maybe you can include this information at traceback. Att, -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ 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] Doing a dynamic Update
Hi, I just started trying to figure out SQLAlchemy today as I was hoping to translate my current SQL queries into a database agnostic format. I admit that this OO way is kind of confusing to me. Anyway, my question is how do I translate the following SQL statement into SQLAlchemy format? sql = UPDATE tbl_TimeEntries SET %s = %s WHERE dateworked = '%s' % (fieldName[x], value, dbDate) I''ve been reading and re-reading the official docs and skimming the tutorials most of the day, but it seems that I somehow need to know what column name I am going to update ahead of time, which isn't really possible in my case as this is for a timesheet application I created using wxPython as the frontend and the user can update any column they want in any order. The SQL statement above works; I would just like to use SQLAlchemy instead. I am currently using Python 2.5 (and 2.4 occasionally) on Windows XP with SqlAlchemy 0.4.2b. Thanks! Mike --~--~-~--~~~---~--~~ 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: Doing a dynamic Update
For a stepwise migration from raw, SQL, it will probably be easier to get your mind around the SQL-expression side of the library, and then adopt ORM features as you feel comfortable with them. On the SQL-expression side of the library, you'll find that your Table() object has a collection called c (for Columns). It's a dict-like collection that supports retrieving the column by name: Table.update() takes a dictionary of updates, so the name-based access is already in there: tbl.update(tbl.c.dateworked == mydate).execute(dict(columnname = newvalue)) or using sessions: S.execute(tbl.update(tbl.c.dateworked == mydate), dict(columnname = newvalue)) HTH, Rick --~--~-~--~~~---~--~~ 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] Get default value
Hello all, is there a way that I can get the default value defined on Column(default=something) ? the default I get is a ColumDefault, and I don't know how to get it's value. any suggestion? -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ 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: Get default value
Isn't it just column.default ? --~--~-~--~~~---~--~~ 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: Get default value
Ah, I read too fast, you are getting back the ColumnDefault object try column.default.arg --~--~-~--~~~---~--~~ 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: Get default value
Alexandre da Silva wrote: Hello all, is there a way that I can get the default value defined on Column(default=something) ? the default I get is a ColumDefault, and I don't know how to get it's value. any suggestion? The value is in its .arg property: col.default.arg --~--~-~--~~~---~--~~ 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: Get default value
Em Qui, 2008-01-10 às 17:13 -0800, jason kirtland escreveu:
col.default.arg
Yes, exactly this.
another question
How many levels I can inherit classes/tables without get something
wrong?
let me show a simplest sample hierarchy:
resource
/ \
person material
/ \
employeecustomer
now, I am creating a type column on resource to map persons and
materials, them I am creating another type column on person, to get
mapped the various types of persons.
by this way, I could get a employee instance, just selecting a resource
but something is going wrong, because the column type on resource is not
filled with correct value, and I am getting NOT NULL CONSTRAINT by
insert an employee.
the tables
resource_table = Table(
Column('id',Integer, primary_key=True),
Column('poly', String(31), nullable=False)
)
person_table = Table(
Column('id',Integer, primary_key=True, ForeignKey('resource.id'),
primary_key=True)),
Column('poly', String(31), nullable=False)
)
employee_table = Table(
Column('id',Integer, primary_key=True, ForeignKey('person.id'),
primary_key=True)),
)
the classes
class Resource(object):
pass
class Person(Resource):
pass
class Employee(Person):
pass
mappers
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource'
)
mapper(Person, person_table,
polymorphic_on=person_table.c.poly,
inherits=Resource, polymorphic_identity='person'
)
mapper(Employee employee_table,
inherits=Person, polymorphic_identity='employee',
)
is all, now when I create an instance of Employee and try to save, I get
back an integrity error, that resource.poly cannot be null
any suggestion?
thank's for previous replies.
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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] Exclude Autogenerated Timestamp Column
Hello,
I am having a problem inserting an object into a MS SQL table that
contains a timestamp field (now) that is generated automatically -
sqlalchemy is defaulting this column to None and when it is generating
the SQL insert. Is there a way to configure the mapper so that it
ignores specific columns?
I looked at the related topic below, but that is resolved by using a
sqlalchemy construct specific to primary keys, and i have not seen one
that is designated for timestamps.
http://groups.google.com/group/sqlalchemy/browse_thread/thread/749c55a835b7458/51b38f4b08d31d6f?lnk=gstq=column+exclude#51b38f4b08d31d6f
I am new to sqlalchemy so I may be going about this the wrong way, but
my attempts at overriding with a reflected column were similarly
unsuccessful.
Column('now', MSTimeStamp, nullable=False)
Any thoughts on how to exclude columns from generated inserts?
cheers
dean
--~--~-~--~~~---~--~~
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: Get default value
You're mixing single-table inheritance (using the discriminator column),
with concrete inheritance (using multiple tables).
You have to pick one scheme or the other. Either use a single inheritance
chain, or separate the two class hierarchies into two separate chains that
don't inherit from each other. In the separate scheme, each chain can use
it's own discriminator column, but you cannot combine two class hierarchies
that each use a different discriminator column.
On Jan 10, 2008 8:46 PM, Alexandre da Silva [EMAIL PROTECTED] wrote:
Em Qui, 2008-01-10 às 17:13 -0800, jason kirtland escreveu:
col.default.arg
Yes, exactly this.
another question
How many levels I can inherit classes/tables without get something
wrong?
let me show a simplest sample hierarchy:
resource
/ \
person material
/ \
employeecustomer
now, I am creating a type column on resource to map persons and
materials, them I am creating another type column on person, to get
mapped the various types of persons.
by this way, I could get a employee instance, just selecting a resource
but something is going wrong, because the column type on resource is not
filled with correct value, and I am getting NOT NULL CONSTRAINT by
insert an employee.
the tables
resource_table = Table(
Column('id',Integer, primary_key=True),
Column('poly', String(31), nullable=False)
)
person_table = Table(
Column('id',Integer, primary_key=True, ForeignKey('resource.id'),
primary_key=True)),
Column('poly', String(31), nullable=False)
)
employee_table = Table(
Column('id',Integer, primary_key=True, ForeignKey('person.id'),
primary_key=True)),
)
the classes
class Resource(object):
pass
class Person(Resource):
pass
class Employee(Person):
pass
mappers
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource'
)
mapper(Person, person_table,
polymorphic_on=person_table.c.poly,
inherits=Resource, polymorphic_identity='person'
)
mapper(Employee employee_table,
inherits=Person, polymorphic_identity='employee',
)
is all, now when I create an instance of Employee and try to save, I get
back an integrity error, that resource.poly cannot be null
any suggestion?
thank's for previous replies.
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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: Exclude Autogenerated Timestamp Column
I'm not sure I understand what you're looking for...you want the column to
remain NULL after an insert?
Then take off the default from the column definition and make it a datetime
field instead of a timestamp.
On Jan 10, 2008 9:15 PM, deanH [EMAIL PROTECTED] wrote:
Hello,
I am having a problem inserting an object into a MS SQL table that
contains a timestamp field (now) that is generated automatically -
sqlalchemy is defaulting this column to None and when it is generating
the SQL insert. Is there a way to configure the mapper so that it
ignores specific columns?
I looked at the related topic below, but that is resolved by using a
sqlalchemy construct specific to primary keys, and i have not seen one
that is designated for timestamps.
http://groups.google.com/group/sqlalchemy/browse_thread/thread/749c55a835b7458/51b38f4b08d31d6f?lnk=gstq=column+exclude#51b38f4b08d31d6f
I am new to sqlalchemy so I may be going about this the wrong way, but
my attempts at overriding with a reflected column were similarly
unsuccessful.
Column('now', MSTimeStamp, nullable=False)
Any thoughts on how to exclude columns from generated inserts?
cheers
dean
--~--~-~--~~~---~--~~
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: Get default value
Em Qui, 2008-01-10 às 21:20 -0500, Rick Morrison escreveu:
You're mixing single-table inheritance (using the discriminator
column), with concrete inheritance (using multiple tables).
You have to pick one scheme or the other. Either use a single
inheritance chain, or separate the two class hierarchies into two
separate chains that don't inherit from each other. In the separate
scheme, each chain can use it's own discriminator column, but you
cannot combine two class hierarchies that each use a different
discriminator column.
hum, so something is wrong here, I recreate a simple test case and all
is working properly, using the exactly model suggested on my previous
message. I will debug my application to find where is the error, but for
now, I don't know if this should work, but is working.
here is the test case and results:
code-8--code--
from sqlalchemy import create_engine, MetaData, Table, Column, types,
ForeignKey
from sqlalchemy.orm import mapper, relation, backref, create_session
from sqlalchemy import String, Unicode, Integer, DateTime, Numeric,
Boolean, UnicodeText
db = create_engine('sqlite:///:memory:')
metadata = MetaData()
metadata = MetaData(db)
metadata.bind = db
session = create_session(bind=db)
resource_table = Table('resource', metadata,
Column('id',Integer, primary_key=True),
Column('name', String(30)),
Column('poly', String(31), nullable=True)
)
person_table = Table('person', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
Column('poly', String(31), nullable=False)
)
material_table = Table('material', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
employee_table = Table('employee', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
technical_table = Table('technical', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
class Resource(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return Resource id=%d ,name=%s % (self.id,self.name)
class Person(Resource):
def __repr__(self):
return Person id=%d ,name=%s % (self.id,self.name)
class Material(Resource):
def __repr__(self):
return Material id=%d ,name=%s % (self.id,self.name)
class Employee(Person):
def __repr__(self):
return Employee id=%d ,name=%s % (self.id,self.name)
class Technical(Person):
def __repr__(self):
return Technical id=%d ,name=%s % (self.id,self.name)
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource'
)
mapper(Person, person_table,
polymorphic_on=person_table.c.poly,
inherits=Resource, polymorphic_identity='person'
)
mapper(Material, material_table,
inherits=Resource, polymorphic_identity='material'
)
mapper(Employee, employee_table,
inherits=Person, polymorphic_identity='employee',
)
mapper(Technical, technical_table,
inherits=Person, polymorphic_identity='technical',
)
metadata.create_all(bind=db)
r = Resource('resource name')
p = Person('person name')
m = Material('material name')
e = Employee('employee name')
t = Technical('technical name')
session.save(r)
session.save(p)
session.save(m)
session.save(e)
session.save(t)
session.flush()
print LIST FROM RESOURCES #
for o in session.query(Resource).all():
print o
print LIST FROM PERSONS #
for o in session.query(Person).all():
print o
code-8--code--
The results:
LIST FROM RESOURCES #
Resource id=1 ,name=resource name
Person id=2 ,name=person name
Material id=3 ,name=material name
Employee id=4 ,name=employee name
Technical id=5 ,name=technical name
LIST FROM PERSONS #
Person id=2 ,name=person name
Employee id=4 ,name=employee name
Technical id=5 ,name=technical name
I think it is working properly.
but I will try to remove the type columns
Thank's for help
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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: Get default value
Em Qui, 2008-01-10 às 21:20 -0500, Rick Morrison escreveu: You're mixing single-table inheritance (using the discriminator column), with concrete inheritance (using multiple tables). You have to pick one scheme or the other. Either use a single inheritance chain, or separate the two class hierarchies into two separate chains that don't inherit from each other. In the separate scheme, each chain can use it's own discriminator column, but you cannot combine two class hierarchies that each use a different discriminator column. In fact I am doing what is sugested here: http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_inheritance_joined It works fine with one level inheritance class-subclass but the third subclassing don't working, it was because objects was in cache... savint to db and tryin to load again the identity is lost. how do you suggest to I do this? or I cannot? -- Alexandre da Silva Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007) --~--~-~--~~~---~--~~ 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 LIMIT/OFFSET
There is some part in the patch which modifies the aliased_select method global (global for the visit_select method) that didn't quite work out, the locals()['aliased_select'] part the solution was to put aliased_select inside an array... The need to modify aliased_select comes in because we needed to export additional columns inside the inner select when the order_by clause relies on a column that is not exported. On Jan 10, 9:46 pm, Esceo [EMAIL PROTECTED] wrote: the other part of the patch is a rewrite of INSERT INTO (.+) \(\) VALUES \(\)$ (insertion of empty list of values) into INSERT INTO %s DEFAULT VALUES which is problematic when we insert into a table with only one field and it's identity On Jan 10, 9:43 pm, lei you [EMAIL PROTECTED] wrote: patch is attached mssql.diff 26KDownload- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ 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] polymorphic inheritance
Hello all,
sa developers, is too dificult try to implement inheritance works by
that form?
I don't know the SA core, so I think is no so difficult to make this
working.
for now, I accept this code changed to work properly, as it works
removing the session.clear()
thank's a lot
code---8--code--
from sqlalchemy import create_engine, MetaData, Table, Column, types,
ForeignKey
from sqlalchemy.orm import mapper, relation, backref, create_session
from sqlalchemy import String, Unicode, Integer, DateTime, Numeric,
Boolean, UnicodeText
db = create_engine('sqlite:///:memory:')
metadata = MetaData()
metadata = MetaData(db)
metadata.bind = db
session = create_session(bind=db)
resource_table = Table('resource', metadata,
Column('id',Integer, primary_key=True),
Column('name', String(30)),
Column('poly', String(31), nullable=True)
)
person_table = Table('person', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
Column('poly', String(31), nullable=False)
)
material_table = Table('material', metadata,
Column('id',Integer, ForeignKey('resource.id'), primary_key=True,),
)
employee_table = Table('employee', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
technical_table = Table('technical', metadata,
Column('id',Integer, ForeignKey('person.id'), primary_key=True),
)
class Resource(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return Resource id=%d ,name=%s % (self.id,self.name)
class Person(Resource):
def __repr__(self):
return Person id=%d ,name=%s % (self.id,self.name)
class Material(Resource):
def __repr__(self):
return Material id=%d ,name=%s % (self.id,self.name)
class Employee(Person):
def __repr__(self):
return Employee id=%d ,name=%s % (self.id,self.name)
class Technical(Person):
def __repr__(self):
return Technical id=%d ,name=%s % (self.id,self.name)
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource'
)
mapper(Person, person_table,
polymorphic_on=person_table.c.poly,
inherits=Resource, polymorphic_identity='person'
)
mapper(Material, material_table,
inherits=Resource, polymorphic_identity='material'
)
mapper(Employee, employee_table,
inherits=Person, polymorphic_identity='employee',
)
mapper(Technical, technical_table,
inherits=Person, polymorphic_identity='technical',
)
metadata.create_all(bind=db)
r = Resource('resource name')
p = Person('person name')
m = Material('material name')
e = Employee('employee name')
t = Technical('technical name')
session.save(r)
session.save(p)
session.save(m)
session.save(e)
session.save(t)
session.flush()
session.clear()
print LIST FROM RESOURCES #
for o in session.query(Resource).all():
print o, o.poly
print LIST FROM PERSONS #
for o in session.query(Person).all():
print o
code---8--code--
Att,
--
Alexandre da Silva
Analista de Sistemas - Bacharel em Sistemas de Informação (2003-2007)
--~--~-~--~~~---~--~~
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: Get default value
How many levels I can inherit classes/tables without get something
wrong?
my tests go to 4, all works. And as all corner cases are already there,
i guess any level above will work too.
mixed inheritance (joined+concrete) also can be made to work, as long as
polymoprhic_union() is fixed slightly - AND no real polymoprhism over
concrete tables, that does not work on SA level / conceptualy.
may i suggest, that u get dbcook.sf.net, model your whole hierarchy there
(it is SIMPLE), then run it in generator mode (see usage/example/*) and see
what equivalent SA source/calls it generates.
maybe you are missing something (some mapper/relation parameters are tricky
to guess). be ware, only joined_inheritance is of real use (single_table is
not implemented).
let me show a simplest sample hierarchy:
resource
/ \
person material
/ \
employeecustomer
now, I am creating a type column on resource to map persons and
materials, them I am creating another type column on person, to get
mapped the various types of persons.
no u dont do it this way. u musthave only one discriminator column per
hierarchy-island.
either use the root one, and put all types there, or separate the material
from resource in its own subhierarchy. If there is explosion of types, to
avoid the huuuge union/outerjoin, u can make the resource a virtual base,
that is, not a table at all - so u'll have two separate db-hierarchies, each
one with its own root/discriminator. (dbcook: just declare
DBCOOK_no_mapping=True there.)
resource_table = Table(
Column('id',Integer, primary_key=True),
Column('poly', String(31), nullable=False)
)
person_table = Table(
Column('id',Integer, primary_key=True, ForeignKey('resource.id'),
primary_key=True)),
Column('poly', String(31), nullable=False)
)
u should not have poly here. its already in the root=resource.
employee_table = Table(
Column('id',Integer, primary_key=True, ForeignKey('person.id'),
primary_key=True)),
)
class Resource(object):
pass
class Person(Resource):
pass
class Employee(Person):
pass
mapper(Resource, resource_table,
polymorphic_on=resource_table.c.poly,
polymorphic_identity='resource'
)
mapper(Person, person_table,
polymorphic_on=person_table.c.poly,
inherits=Resource, polymorphic_identity='person'
)
either put the correct polymorphic_on=resource.c.poly, or remove it
alltogether,
it comes from the inherited base-mapper.
mapper(Employee employee_table,
inherits=Person, polymorphic_identity='employee',
)
svilen
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
