python - Python-MSSQL (pymssql) 设置字段Identity Column
问题描述
我一直在尝试在 SQL 上使用 python 将 ObjectID 设置为 Identity 列。下面的 SQL 语句适用于 Management Studio (SSMS) 并将 ObjectID 设置为 Identity 列。该代码有效,但是在检查 SSMS 上的表时,我没有将 ObjectID 视为 Identity 列。
以下方法适用于 python,但不会更改 SSMS 上的标识列。
conn.commit()
每次执行后添加。使用 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))
任何帮助,将不胜感激。
谢谢
解决方案
问题解决了。每次创建新表时 STSrid 都会发生变化,因此将这部分从 SQL 代码中删除会有所帮助。但是,我不确定空间索引是否保持不变。
干杯
cursor.execute("""
ALTER TABLE dbo.""" + newTableName + """ ADD CONSTRAINT
g1084_ck CHECK (([Shape].[STSrid]=(4326))) """)
推荐阅读
- excel - 将字符串从 Excel 单元格粘贴到 Outlook 邮件时保留回车符
- linux - Objdump 中的 -s(或 --full-contents)标志有什么作用?
- angular - 如何在页面重新加载时关闭垫子扩展面板
- dataweave - 将 json 映射到平面文件的 dataweave 表达式
- vb.net - 无法为 VB 导入 Quartz.NET 库
- php - 如何显示来自 Codeigniter 和 mongodb 的所有索引数据?
- svn - 在 svn 中设置存储库权限
- php - 使用 API 在 akeneo api 中添加产品的问题
- php - 无法使用 simplexml_load_string 解析 XML
- c# - 从多个线程更新 GUI