sqlalchemy trying to pass NULL to primary_key column on commit()
Jonathan Cox
I'm just a newbie trying to work his way through the tutorials and
documentation. I have a sql server 2008 set up with a table that I
defined within SQL Server Management Studio. Following the ORM
tutorial (mostly), I tried to use this table by using reflect=True in
my MetaData call. I create a User class, then create an instance of
that class. According to the tutorial, I'm not supposed to have
anything for the primary key column in the User class definition. I
then add the instance to the session. However, when I try to commit(),
it raises an exception complaining that I'm not allowed to pass a null
to my primary key column (named 'Identity'). I thought it was supposed
to automatically generate a primary key to go in that column. All the
code works except that commit line (tested by commenting that line
out). What am I doing wrong?
-------------------------------------------
My Code:
import pyodbc
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.sql import *
engine=create_engine('mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BSQL
+Server+Native+Client+10.0%7D%3BSERVER%3Dlocalhost%3BDATABASE
%3D8080data%3BUID%3D8080user%3BPWD%3Dpw')
Session=sessionmaker(bind=engine)
session=Session()
metadata=MetaData(bind=engine,reflect=True)
peopletable=Table('PeopleTable',metadata)
class User(object):
def __init__(self, First_Name, Last_Name, Street_Address,
State_or_Province,
ZIP_or_Postal_Code, Country, Phone, Email, Login,
Password):
self.First_Name=First_Name
self.Last_Name=Last_Name
self.Street_Address=Street_Address
self.State_or_Province=State_or_Province
self.ZIP_or_Postal_Code=ZIP_or_Postal_Code
self.Country=Country
self.Phone=Phone
self.Email=Email
self.Login=Login
self.Password=Password
mapper(User,peopletable)
noobuser=User('bobby','McGee','123 first
street','TX','55555','USA','123-123-1234','as...@asdf.com','bmcgee','pass')
session.add(noobuser)
session.commit()
---------------------------------------------------------
************************************************
The error (abridged):
Original exception was: (IntegrityError) ('23000', "[23000] [Microsoft]
[SQL Server Native Client 10.0][SQL Server]Cannot insert the value
NULL into column 'Identity', table '8080data.dbo.PeopleTable'; column
does not allow nulls. INSERT fails.
*****************************************************
Many Thanks.
Michael Bayer
On Feb 5, 2011, at 11:39 PM, Jonathan Cox wrote:
> Howdy,
> I'm just a newbie trying to work his way through the tutorials and
> documentation. I have a sql server 2008 set up with a table that I
> defined within SQL Server Management Studio. Following the ORM
> tutorial (mostly), I tried to use this table by using reflect=True in
> my MetaData call. I create a User class, then create an instance of
> that class. According to the tutorial, I'm not supposed to have
> anything for the primary key column in the User class definition. I
> then add the instance to the session. However, when I try to commit(),
> it raises an exception complaining that I'm not allowed to pass a null
> to my primary key column (named 'Identity'). I thought it was supposed
> to automatically generate a primary key to go in that column. All the
> code works except that commit line (tested by commenting that line
> out). What am I doing wrong?
SQL Server uses a keyword IDENTITY auto generate so-called "identity" values, which are typically primary keys. Either that's not the case on this particular table's primary key column, or SQLAlchemy is not detecting that the primary key column has IDENTITY configured on it. I'm leaning towards the former here, so make sure that 'identity' is primary key / IDENTITY.
an example of IDENTITY is at: http://msdn.microsoft.com/en-us/library/ms186775.aspx but you should also make sure it has a PRIMARY KEY constraint.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Jonathan Cox
not via code. However, I was able to go in and modify the properties
of the Identity column. I simply set {Is Identity} to 'yes' and my
problem went away. Thanks for pointing me in the right direction.