使用pymssql设置SQL表ObjectID为Identity列失败求助

It sounds like you're hitting a transaction management conflict between your embedded SQL statements and pymssql's default transaction behavior. Let's break down the issues and fix this step by step.

Key Problem Analysis #

SSMS generates SQL with manual BEGIN TRANSACTION/COMMIT blocks, but pymssql operates in manual commit mode by default. Mixing these two transaction control methods can lead to incomplete or uncommitted changes, even if you call conn.commit() at the end.

Fixes & Optimizations #

Here's a revised version of your code with critical adjustments, plus additional troubleshooting steps:

Revised Code #

import pymssql

newTableName = "A_Test_DashAutomation"

try:
    # Establish connection (ensure you fill in your server/db credentials)
    conn = pymssql.connect(
        server="your_server_name",
        user="your_username",
        password="your_password",
        database="your_database"
    )
    cursor = conn.cursor()

    # Set session options (removed redundant transaction blocks)
    cursor.execute("""
        SET QUOTED_IDENTIFIER ON
        SET ARITHABORT ON
        SET NUMERIC_ROUNDABORT OFF
        SET CONCAT_NULL_YIELDS_NULL ON
        SET ANSI_NULLS ON
        SET ANSI_PADDING ON
        SET ANSI_WARNINGS ON
    """)

    # Create temporary table with OBJECTID as Identity
    create_temp_sql = f"""
        CREATE TABLE dbo.Tmp_{newTableName} (
            SubProjectTempId bigint NULL,
            CIPNumber varchar(16) NOT NULL,
            Label nvarchar(50) NULL,
            Date_Started datetime2(7) NULL,
            Date_Completed datetime2(7) NULL,
            Status nvarchar(25) NULL,
            Shape geography NULL,
            Type varchar(2) NOT NULL,
            ProjectCode varchar(16) NULL,
            ActiveFlag int NULL,
            Category varchar(32) NULL,
            ProjectDescription varchar(64) NULL,
            UserDefined varchar(1024) NULL,
            InactiveReasonDate datetime NULL,
            FYTDBudget money NULL,
            LTDBudget money NULL,
            PeriodExpenses money NULL,
            FYTDExpenses money NULL,
            LTDExpenses money NULL,
            LTDEncumbrances money NULL,
            LTDBalance money NULL,
            FiscalYear int NULL,
            ToPeriod int NULL,
            _LastImported datetime NOT NULL,
            OBJECTID int NOT NULL IDENTITY (1, 1),
            GDB_GEOMATTR_DATA varbinary(MAX) NULL
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    """
    cursor.execute(create_temp_sql)

    cursor.execute(f"ALTER TABLE dbo.Tmp_{newTableName} SET (LOCK_ESCALATION = TABLE)")
    cursor.execute(f"SET IDENTITY_INSERT dbo.Tmp_{newTableName} ON")

    # Migrate data from original table
    insert_sql = f"""
        IF EXISTS(SELECT * FROM dbo.{newTableName})
        EXEC('INSERT INTO dbo.Tmp_{newTableName} 
            (SubProjectTempId, CIPNumber, Label, Date_Started, Date_Completed, Status, Shape, Type, ProjectCode, ActiveFlag, Category, ProjectDescription, UserDefined, InactiveReasonDate, FYTDBudget, LTDBudget, PeriodExpenses, FYTDExpenses, LTDExpenses, LTDEncumbrances, LTDBalance, FiscalYear, ToPeriod, _LastImported, OBJECTID, GDB_GEOMATTR_DATA)
            SELECT SubProjectTempId, CIPNumber, Label, Date_Started, Date_Completed, Status, Shape, Type, ProjectCode, ActiveFlag, Category, ProjectDescription, UserDefined, InactiveReasonDate, FYTDBudget, LTDBudget, PeriodExpenses, FYTDExpenses, LTDExpenses, LTDEncumbrances, LTDBalance, FiscalYear, ToPeriod, _LastImported, OBJECTID, GDB_GEOMATTR_DATA
            FROM dbo.{newTableName} WITH (HOLDLOCK TABLOCKX)')
    """
    cursor.execute(insert_sql)

    cursor.execute(f"SET IDENTITY_INSERT dbo.Tmp_{newTableName} OFF")
    cursor.execute(f"DROP TABLE dbo.{newTableName}")
    cursor.execute(f"EXECUTE sp_rename N'dbo.Tmp_{newTableName}', N'{newTableName}', 'OBJECT'")

    # Add primary key constraint
    alter_pk_sql = f"""
        ALTER TABLE dbo.{newTableName} 
        ADD CONSTRAINT R1143_pk PRIMARY KEY CLUSTERED (OBJECTID)
        WITH(
            PAD_INDEX = OFF, 
            FILLFACTOR = 75, 
            STATISTICS_NORECOMPUTE = OFF, 
            IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, 
            ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
    """
    cursor.execute(alter_pk_sql)

    # Create spatial index
    spatial_index_sql = f"""
        CREATE SPATIAL INDEX SIndx ON dbo.{newTableName}(Shape)
        USING GEOGRAPHY_AUTO_GRID
        WITH(
            CELLS_PER_OBJECT = 16, 
            STATISTICS_NORECOMPUTE = OFF, 
            ALLOW_ROW_LOCKS = ON, 
            ALLOW_PAGE_LOCKS = ON
        )
    """
    cursor.execute(spatial_index_sql)

    # Add SRID check constraint
    check_constraint_sql = f"""
        ALTER TABLE dbo.{newTableName} 
        ADD CONSTRAINT g1084_ck CHECK (([Shape].[STSrid]=(4326)))
    """
    cursor.execute(check_constraint_sql)

    # Commit the entire transaction once all steps are successful
    conn.commit()
    print(f"Successfully set OBJECTID as Identity column for table {newTableName}.")

except pymssql.DatabaseError as err:
    # Rollback on any error to avoid partial changes
    conn.rollback()
    print(f"Database error occurred: {str(err)}")

finally:
    # Clean up resources
    if cursor:
        cursor.close()
    if conn:
        conn.close()

Critical Changes Made #

1. Removed manual transaction blocks: Eliminated all BEGIN TRANSACTION and COMMIT statements from your SQL. pymssql manages transactions automatically, and mixing manual SQL transactions with client-side commits causes conflicts.
2. Added error rollback: If any step fails, conn.rollback() ensures no partial changes are left in the database.
3. Unified commit: All operations are wrapped in a single transaction, committed only after every step completes successfully.
4. Clean resource management: The finally block ensures cursors and connections are closed properly.

Additional Troubleshooting Steps #

• Validate generated SQL: Print the final SQL strings (e.g., print(create_temp_sql)) and run them directly in SSMS to confirm they work as expected. This rules out syntax issues from string concatenation.
• Upgrade pymssql: Old versions may have bugs with SQL Server identity columns. Run pip install --upgrade pymssql to get the latest release.
• Try pyodbc as an alternative: If pymssql still behaves unexpectedly, switch to pyodbc (which has more robust SQL Server support). The code structure will be nearly identical—just adjust the connection string:

  import pyodbc
  conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_pass')
  

内容的提问来源于stack exchange，提问作者EFKan