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. > > > ------------------------------------------- > 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','[email protected]','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. > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > 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. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. 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.
