A bug in pymssql accessing sqlserver

Perform multi-Table operations on the database. If there is a dependency between the table and the table, you can use transactions explicitly to maintain the atomicity of database operations. Use python to access the sqlserver database. I use the pymssql library. When I used this library today, I found a problem.

The problem is probably like this:

I have two tables, one primary table (classinfo) and one slave table (student). The student table is associated with the classinfo table through the foreign key classid. The primary keys of both tables are auto-incrementing int fields.

 

The SQL statement for creating a table is as follows:

Create Table classinfo (<br/> classid int identity (1, 1) primary key, <br/> classname varchar (20) not null <br/>) <br/> go <br/> Create Table student (<br/> studentid int identity (1, 1) primary key, <br/> classid int not null, <br/> studentname varchar (20) not null, <br/> foreign key (classid) References classinfo (classid) <br/> go

In the code, create a class and insert student data into the class. If these two operations are completed in a transaction, we will write the following statement according to the documentation provided by pymssql:

Import pymssql <br/> conn = pymssql. connect (host = 'jgood // sqlexpress ', database = "study", <br/> User = "sa", password = "sa ") <br/> cur = Conn. cursor () <br/> # Add a record to the master table <br/> cur.exe cute ("insert into [classinfo] ([classname]) values (% s )", 'jk0403') <br/> # obtain the ID. <Br/> cur.exe cute ("select @ identity as ID") <br/> classid = cur. fetchone () [0] <br/> # Add a record to the slave table <br/> cur.exe cute ("insert into [Student] (classid, studentname) values (% d, % s) ", <br/> (classid, 'jgood ') <br/> Conn. commit () <br/> cur. close () <br/> Conn. close ()

However, it is a pity that an error occurred while running the Code:

If I divide the above operations into two independent transactions for implementation, it is found that there is no problem.

 

After an afternoon's exploration, we finally found a solution, that is, to add the auto-increment ID to the SQL statement in the form of a spelling string, rather than the form of SQL parameters.

 

# Add a record to the table <br/> cur.exe cute ("insert into [Student] (classid, studentname) values (" + STR (classid) + ", % s )", ('jgood ',))

I don't know if this is a bug in pymssql: in a transaction, the automatically generated foreign key values cannot be inserted into the database as SQL parameters.