pymssql INSERT won't auto increment ID

Question

I am using pymssql in my django project to connect to a database. I'm trying to use execute() method to do an insert.
However I get this error:

Cannot insert the value NULL into column 'ID', table ITEMS

the ID column is primary key and so it should be filled by the sql itself as I understand. here is my insert command:

    bill_id = execute("""
       INSERT INTO ITEMS(COlUMN1,COlUMN2, COlUMN3,COlUMN4,COlUMN5)
       VALUES (%d, %d, %d, %d, %d);
        """, (1713, 6, 929, 1184, 25))

and none of these COLUMNs are ID. can anyone tell me what am doing wrong?

Why don't you use Django models to interact with the database? — mhawke, Oct 12 '17 at 09:46
@mhawke I can't do that. I'm working on a rather big project and it's not my call. — Bahman Rouhani, Oct 12 '17 at 09:47
If the table was not created by Django, then the ID field may not have `AUTO_INCREMENT` set. You'll need to either specify the id, or alter the column so that it auto increments. — Alasdair, Oct 12 '17 at 09:48
@Alasdair thanks. that might be the case. how would I alter the column? — Bahman Rouhani, Oct 12 '17 at 09:49
You'll need to run the appropriate `ALTER TABLE` statement. I can't help with the exact syntax for MySQL. — Alasdair, Oct 12 '17 at 09:51

michaeldbjava · Accepted Answer · 2017-10-12T09:54:33.300

3

If id column is not an auto increment key column, it will not work. If you define a column in SQL within a create table statement it is not automatically an autoincrement column. You must define it. Perhaps you should make at first a query to get the max value of id column. At second you can increment the value und add the primary column to your insert statement.

I found this link to use django meta data api.

https://docs.djangoproject.com/en/1.9/ref/models/meta/#migrating-from-the-old-api

edited Oct 12 '17 at 09:54

answered Oct 12 '17 at 09:48

michaeldbjava

114
10

thanks, that's what I did eventually, but I still can't figure out why the id is not being set by sql. the column is auto increment so it should increment by itself. – Bahman Rouhani Oct 12 '17 at 09:54
Ok, it is an MySQL Database. – michaeldbjava Oct 12 '17 at 09:59
Try the following to show the structure of table. https://dev.mysql.com/doc/refman/5.7/en/show-columns.html – michaeldbjava Oct 12 '17 at 10:02
Ok, you use MS-SQL Server. The following shows you an possibility to show meta Data of table. At first analyse the structure. If you are 100% sure that it is an autoincrement column. I can not say which reason it has. https://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query. – michaeldbjava Oct 12 '17 at 10:19
thanks for your help. I appreciate it. I will try this as soon as possible – Bahman Rouhani Oct 12 '17 at 11:07

pymssql INSERT won't auto increment ID

1 Answers1