Inserting Data into MS SQL DB Using Pymssql: Troubleshooting and Solutions for Error Insertion

Error Inserting Data into MS SQL DB Using Pymssql

In this article, we will delve into the issue of inserting data into a Microsoft SQL database using the pymssql library in Python. We will explore the problem with the provided code, identify the root cause, and provide a solution to fix it.

Introduction

The problem arises when trying to insert data into a table named products_tb in the kaercher database using the pymssql library. The issue is that the INSERT INTO statement fails to generate an unique ID for the product, which is required to group all the data at any given time and create graphs.

Problem Description

The problem occurs when trying to insert a new item into the products_tb table. The code attempts to use the OUTPUT (Inserted.productgroupid) clause to retrieve the newly generated ID. However, this approach fails due to a syntax error in the SQL statement.

Provided Code

The provided code is as follows:

import pymssql

class KrcPipeline(object):

    def __init__(self):
        self.conn = pymssql.connect(host='DESKTOP-P1TF28R', user='sa', password='123', database='kaercher')
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):

        sql_statement = f'''
                    BEGIN
                        IF NOT EXISTS (SELECT * FROM [kaercher].[dbo].[products_tb]
                            WHERE productid = {item['productid']})
                        BEGIN
                        INSERT INTO [kaercher].[dbo].[products_tb] (productid, category, name, description)
                        OUTPUT (Inserted.productgroupid)
                        VALUES ({item['productid']}, {item['category']}, {item['name']}, {item['description']})
                        END
                        ELSE
                        BEGIN
                            SELECT productgroupid FROM [kaercher].[dbo].[products_tb]
                            WHERE productid = {item['productid']}
                        END
                    END
        '''

        self.cursor.execute(sql_statement)

        self.conn.commit()

        return item

Solution

To fix the issue, we need to correct two syntax errors in the SQL statement:

The IF NOT EXIST clause should be IF NOT EXISTS.
The values for productid, category, name, and description should be enclosed in single quotes.

Here is the corrected code:

import pymssql

class KrcPipeline(object):

    def __init__(self):
        self.conn = pymssql.connect(host='DESKTOP-P1TF28R', user='sa', password='123', database='kaercher')
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):

        sql_statement = f'''
                    BEGIN
                        IF NOT EXISTS (SELECT * FROM [kaercher].[dbo].[products_tb]
                            WHERE productid = {item['productid']})
                        BEGIN
                        INSERT INTO [kaercher].[dbo].[products_tb] (productid, category, name, description)
                        VALUES (@productid, @category, @name, @description)
                        SELECT CONVERT(int, SCOPE_IDENTITY()) AS productgroupid
                        END
                        ELSE
                        BEGIN
                            SELECT productgroupid FROM [kaercher].[dbo].[products_tb]
                            WHERE productid = {item['productid']}
                        END
                    END
        '''

        self.cursor.execute(sql_statement)

        self.conn.commit()

        return item

In the corrected code, we have added @ symbols to the column names in the INSERT INTO statement. This is because SQL Server requires column names to be prefixed with an @ symbol when using named parameters.

Additionally, we have removed the OUTPUT (Inserted.productgroupid) clause and instead used a separate SELECT statement to retrieve the newly generated ID. The SCOPE_IDENTITY() function returns the identity value for the last inserted record.

Conclusion

In this article, we discussed an issue with inserting data into a Microsoft SQL database using the pymssql library in Python. We identified two syntax errors in the provided code and corrected them to fix the problem. By making these corrections, you should now be able to insert data into the products_tb table successfully.

Last modified on 2025-05-07