pymssql (pymssql)设置字段标识列

Question

我一直试图使用SQL上的python将ObjectID设置为Identity列。下面的SQL语句在Management (SSMS)上工作，并将ObjectID设置为Identity列。代码可以工作，但是当在SSMS上检查表时，我不认为ObjectID是Identity列。

以下方法在python上工作，但不更改SSMS上的identity列。

1. 每次执行后添加conn.commit()。
2. 使用python文件读取器运行.sql文件。

如图所示，在python中执行代码后，Identity列仍然是空的。

​

​

代码由SSMS生成，我的目的是设置ObjectID字段标识列。也许还有更好的方法。

以下是代码：

newTableName = "A_Test_DashAutomation"
try:
    cursor.execute("""
    BEGIN TRANSACTION
    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
    COMMIT
    BEGIN TRANSACTION """)

    cursor.execute("""
    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("ALTER TABLE dbo.Tmp_" + newTableName + " SET (LOCK_ESCALATION = TABLE)")

    cursor.execute("SET IDENTITY_INSERT dbo.Tmp_" + newTableName + " ON")

    cursor.execute("""
    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("SET IDENTITY_INSERT dbo.Tmp_" + newTableName + " OFF")

    cursor.execute("DROP TABLE dbo." + newTableName)

    cursor.execute("EXECUTE sp_rename N'dbo.Tmp_" + newTableName + "', N'" + newTableName + "', 'OBJECT'")

    cursor.execute("""
    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("""
    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("""
    ALTER TABLE dbo.""" + newTableName + """ ADD CONSTRAINT
        g1084_ck CHECK (([Shape].[STSrid]=(4326)))
    """)

    cursor.execute("COMMIT")

    conn.commit()
    print("Object ID is set to Identify column.")

except pymssql.DatabaseError, err:
    print(str(err))

任何帮助都将不胜感激。

谢谢

Answer 1

问题解决了。每次创建新表时，STSrid都会更改，因此将这一部分从SQL代码中删除会有所帮助。然而，我不确定空间指数是否保持不变。

干杯

cursor.execute("""
ALTER TABLE dbo.""" + newTableName + """ ADD CONSTRAINT
    g1084_ck CHECK (([Shape].[STSrid]=(4326))) """)