db autogenerated pks?
Smoke
I'm new to sqlalchemy and was checking if i can introduce it into some
of my projects... I have some database ( sql server ) tables with
MSUniqueidenfier columns set as PK and with newid() as default
values...
So, if i try to map this table into a class,save a new record and
flush, then i have errors because it says that column doesn't support
NULL values..
Is there any option i'm missing that can make me exclude this PK from
the INSERT query or somehow tell sqlalchemy that this pk column value
is autogenerated by the database?
thanks,
FP
P.S.:
My code is something very simple... like this:
t = sa.Table("Machines", metadata, autoload=True)
Session = sessionmaker(bind=db, autoflush=False, transactional=False)
class Machine(object):
pass
session = Session()
sa.orm.mapper(Machine, t)#, exclude_properties=['uId'])
m = Machine()
nm = Machine()
nm.name, nm.node = "Mac1", "P"
session.save(nm)
session.flush()
KyleJ
http://www.sqlalchemy.org/docs/04/metadata.html#metadata_tables_reflecting_overriding
Specify the type with MSUniqueIdentifier from
sqlalchemy.databases.mssql
Smoke
if i change:
t = sa.Table("Machines", metadata, autoload=True)
to:
t = sa.Table("Rpm_MacchineConfig", metadata,
sa.Column('uId', sa.databases.mssql.MSUniqueIdentifier,
primary_key=True),
autoload=False)
i have:
sqlalchemy.exceptions.DBAPIError: (ProgrammingError) ('42000',
"[42000] [Microso
ft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
')'. (102)") u'INSERT INTO [Rpm_MacchineConfig] () VALUES ()' []
and if i put autoload= True, like this:
t = sa.Table("Rpm_MacchineConfig", metadata,
sa.Column('uId', sa.databases.mssql.MSUniqueIdentifier,
primary_key=True),
autoload=True)
i have this:
sqlalchemy.exceptions.DBAPIError: (IntegrityError) ('23000', "[23000]
[Microsoft
][ODBC SQL Server Driver][SQL Server]Impossible to insert NULL value
into column 'uId' on table 'dbtests.dbo.Rpm_MachineConfig'.The column
does not support NULL values.... (515); [01000] [Microsoft][O
DBC SQL Server Driver][SQL Server]Instruction interrupted. (3621)") u
'INSERT INTO [Rpm_MacchineConfig] (nome, nodo, descrizione) VALUES
(?, ?, ?)' ['MARIO', 'FI', None ]
Any hint before i start changing my table design? I'm a newbie on
sqlalchemy so i'm probably missing something...
thanks
FP
On 12 Set, 02:18, KyleJ <osma...@gmail.com> wrote:
> You probably need to override the autoloaded primary key column:http://www.sqlalchemy.org/docs/04/metadata.html#metadata_tables_refle...
Rick Morrison
SA provides a mechanism called "passive default" to handle these kinds of things. What it does under the covers, or what you can do explicitly without it is:
a) first call newid() to get the new GUID
b) then do the insert using the GUID value as a normal attribute
My personal opinion is that GUID keys are over-utilized, and there are usually better alternatives that will perform better overall, and will not make you swim upstream with SA. Here's a link to an article about an alternate scheme to get rid of GUID keys that talks about performance implications, I'm sure you can find more if you look:
http://www.sql-server-performance.com/articles/per/guid_performance_p1.aspx
Rick
Still have problems...
if i change:
t = sa.Table("Machines", metadata, autoload=True)
to:
t = sa.Table("Rpm_MacchineConfig", metadata,
sa.Column('uId', sa.databases.mssql.MSUniqueIdentifier,
primary_key=True),
autoload=False)
i have:
sqlalchemy.exceptions.DBAPIError: (ProgrammingError) ('42000',
"[42000] [Microso
ft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
')'. (102)") u'INSERT INTO [Rpm_MacchineConfig] () VALUES ()' []
and if i put autoload= True, like this:
t = sa.Table("Rpm_MacchineConfig", metadata,
sa.Column ('uId', sa.databases.mssql.MSUniqueIdentifier,
> > t = sa.Table ("Machines", metadata, autoload=True)
Smoke
On 12 Set, 19:31, "Rick Morrison" <rickmorri...@gmail.com> wrote:
> SQL Server provides no facilities for retrieving a GUID key after an insert
> -- it's not a true autoincrementing key. The MSSQL driver for SA uses either
> @@IDENTITY or SCOPE_IDENTITY() to retreive the most-recently inserted
> autoincrement value, but there is no such facility for getting GUID keys.
>
> SA provides a mechanism called "passive default" to handle these kinds of
> things. What it does under the covers, or what you can do explicitly without
> it is:
>
> a) first call newid() to get the new GUID
> b) then do the insert using the GUID value as a normal attribute
>
> My personal opinion is that GUID keys are over-utilized, and there are
> usually better alternatives that will perform better overall, and will not
> make you swim upstream with SA.
Ok... In the meanwhile i've tryied to play a little bit and found that
if i just change the pk from the guid to another(s) column(s)
everything works great. Probably this is the right occasion for me to
sanitize clean up e refactor this application database design... :)
Here's a link to an article about an
> alternate scheme to get rid of GUID keys that talks about performance
> implications, I'm sure you can find more if you look:
>
> http://www.sql-server-performance.com/articles/per/guid_performance_p...
Thans much! :)
FP
>
> Rick
>
> On 9/12/07, Smoke <fabio.pli...@gmail.com> wrote:
>
>
>
> > Still have problems...
>
> > if i change:
>
> > t = sa.Table("Machines", metadata, autoload=True)
>
> > to:
>
> > t = sa.Table("Rpm_MacchineConfig", metadata,
> > sa.Column('uId', sa.databases.mssql.MSUniqueIdentifier,
> > primary_key=True),
> > autoload=False)
>
> > i have:
>
> > sqlalchemy.exceptions.DBAPIError: (ProgrammingError) ('42000',
> > "[42000] [Microso
> > ft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
> > ')'. (102)") u'INSERT INTO [Rpm_MacchineConfig] () VALUES ()' []
>
> > and if i put autoload= True, like this:
>
> > t = sa.Table("Rpm_MacchineConfig", metadata,
> > sa.Column('uId', sa.databases.mssql.MSUniqueIdentifier,
> > primary_key=True),
> > > > t = sa.Table("Machines", metadata, autoload=True)