SqlAlchemy Insert error

[Mike]

Hi,

I'm a nerb with SQLAlchemy, so this is probably a silly question, but
here goes. I have a table in SQL Server V.8 where I am trying to
insert a row. Here's most of my code:

<code>

from sqlalchemy import *

db = dbConnect() # function to connect to my db
db.echo = True

metadata = MetaData(db)

entries = Table('tbl_TimeEntries', metadata,
Column('dateworked', String(10),
primary_key=True),
Column('empid', Integer, primary_key=True),
Column('reg', Float),
Column('ot', Float),
Column('ce', Float),
Column('hol', Float),
Column('sklv', Float),
Column('vac', Float),
Column('ct', Float),
Column('conv', Float),
Column('misc', Float),
Column('comments', Unicode(256))
)
i = entries.insert(values={'dateworked':'02/13/2008', 'empid':258,
'reg':8.00})

</code>

Unfortunately, this results in the following traceback:

Traceback (most recent call last):
File "//someServer/Scripts/PythonPackages/Development/Timesheet/
sqlalchemy-test2.py", line 48, in -toplevel-
main(tblName, key, val)
File "//someServer/Scripts/PythonPackages/Development/Timesheet/
sqlalchemy-test2.py", line 28, in main
i.execute()
File "c:\python24\lib\site-packages\SQLAlchemy-0.4.2b-py2.4.egg
\sqlalchemy\sql\expression.py", line 992, in execute
File "c:\python24\lib\site-packages\SQLAlchemy-0.4.2b-py2.4.egg
\sqlalchemy\engine\base.py", line 1211, in execute_clauseelement
File "c:\python24\lib\site-packages\SQLAlchemy-0.4.2b-py2.4.egg
\sqlalchemy\engine\base.py", line 895, in execute_clauseelement
File "c:\python24\lib\site-packages\SQLAlchemy-0.4.2b-py2.4.egg
\sqlalchemy\engine\base.py", line 906, in _execute_compiled
File "c:\python24\lib\site-packages\SQLAlchemy-0.4.2b-py2.4.egg
\sqlalchemy\engine\default.py", line 278, in pre_execution
File "c:\python24\lib\site-packages\SQLAlchemy-0.4.2b-py2.4.egg
\sqlalchemy\databases\mssql.py", line 322, in pre_exec
File "C:\Python24\lib\site-packages\pymssql.py", line 126, in
execute
self.executemany(operation, (params,))
File "C:\Python24\lib\site-packages\pymssql.py", line 152, in
executemany
raise DatabaseError, "internal error: %s" % self.__source.errmsg()
DatabaseError: internal error: SQL Server message 8106, severity 16,
state 1, line 1:
Table 'tbl_TimeEntries' does not have the identity property. Cannot
perform SET operation.
DB-Lib error message 10007, severity 5:
General SQL Server error: Check messages from the SQL Server.


Upon digging in Google and Microsoft's pages, it seems that SQLAlchemy
is trying to use the SET IDENTITY_INSERT setting for a column that
doesn't have this setting. Why is it doing that in the first place and
how to I stop that behavior?

Also, as you may notice, I have two columns set as the primary key. Is
that the correct way to create a composite primary key?

Any tips are appreciated. I apologize for the lengthy post. I am
programming with Python 2.4 and SQLAlchemy-0.4.2b on Windows XP.

Thanks,

Mike

[Rick Morrison]

SQLAlchemy assumes that an integer primary key is of the auto-incrementing type, and for MSSQL this means IDENTITY_INSERT.

To tell it to not make that assumption, use the autoincrement=False on the column:

     ...

     Column('dateworked', String(10), primary_key=True),

     Column('empid', Integer, primary_key=True, autoincrement=False),
     Column('reg', Float),
     ...

Your multi-column primary key def looks fine.

Rick

[Michael Bayer]

On Feb 11, 2008, at 2:51 PM, Mike wrote:

> DatabaseError: internal error: SQL Server message 8106, severity 16,
> state 1, line 1:
> Table 'tbl_TimeEntries' does not have the identity property. Cannot
> perform SET operation.
> DB-Lib error message 10007, severity 5:
> General SQL Server error: Check messages from the SQL Server.
>
>
> Upon digging in Google and Microsoft's pages, it seems that SQLAlchemy
> is trying to use the SET IDENTITY_INSERT setting for a column that
> doesn't have this setting. Why is it doing that in the first place and
> how to I stop that behavior?

im not the MS-SQL guy but it appears that normally, when inserting a
column which is an autoincrementing integer value, MS-SQL requires
that you call SET IDENTITY_INSERT for the table overall (i.e. not any
particular column) in order to allow the insert to proceed. The SQLA
driver does this when it sees that an integer value is being sent in,
and turns it off after the insert completes.

In this case, if the empid column does not have autoincrmenting
behavior on the MS-SQL side, configure the Column object with
"autoincrement=False". Normally autoincrement is assumed to be True
on a primary key integer-holding column. Looking at the source it
appears this should prevent the identity insert step from happening.

>
>
> Also, as you may notice, I have two columns set as the primary key. Is
> that the correct way to create a composite primary key?

yes this is fine. The other option would be to use the
PrimaryKeyConstraint construct explicitly but the twin "primary_key"
flags have the same effect.

[Mike]

Rick and Michael,

On Feb 11, 2:25 pm, "Rick Morrison" <rickmorri...@gmail.com> wrote:
> SQLAlchemy assumes that an integer primary key is of the auto-incrementing
> type, and for MSSQL this means IDENTITY_INSERT.
>
> To tell it to not make that assumption, use the autoincrement=False on the
> column:
>
> ...
> Column('dateworked', String(10), primary_key=True),

> Column('empid', Integer, primary_key=True, *autoincrement=False*),
> Column('reg', Float),

That worked perfectly. And thanks goes out to Micheal for giving such
a thorough explanation of why this needs to be set that way.

> ...
>
> Your multi-column primary key def looks fine.
>
> Rick

I'm glad I got half of it right anyway. Which reminds me, what does
one do if the table you need to work with doesn't have a primary key
at all? Thanks!

Mike