如何在Microsoft SQL Server插入数据时自动生成未使用的主键？

问题描述 #

我现在有一段Python代码用来向SQL Server数据库插入数据，但每次插入新数据都得手动修改Id字段的值（因为它是主键）。我用的是Microsoft SQL Server Studio，请问怎么才能让每次插入时自动使用未被占用的主键值？

以下是我的Python代码：

import urllib.request as urllib
import socket
import pyodbc
from datetime import datetime

#Timestamp for undersøgelse
timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
#Host info og IP
host = "www.rejseplanen.dk"
dest = socket.gethostbyname(host)
hdata = 'host',host,'IP:',dest
#Responseheader request
request = urllib.Request('http://rejseplanen.dk')
request.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36')
response = urllib.urlopen(request)
rdata = response.info()
#SQL Connection til local database
con = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                      'Server=DESKTOP-THV2IDL;'
                      'Database=host;'
                      'Trusted_Connection=yes;')
cursor = con.cursor()
cursor.execute('SELECT * FROM host.dbo.hosts')
for row in cursor:
    print(row)
con.execute('INSERT INTO host.dbo.hosts (Id, ip, host, HSTS, HPKP, XContentTypeOptions, XFrameOptions, ContentSecurityPolicy, Xssprotection, Server, Timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', (4123, host, dest, rdata['Strict-Transport-Security'], rdata['Public-Key-Pins'], rdata['X-Content-Type-Options'], rdata['X-Frame-Options'], rdata['Content-Security-Policy'], rdata['X-XSS-Protection'], rdata['Server'], timestamp))
con.commit()

解决方案 #

要解决手动指定主键的问题，核心是把SQL Server表中的Id字段设置为自增标识列（Identity Column），这样数据库会自动为每条新插入的记录生成唯一的主键值，无需手动指定。

步骤1：修改表结构，将Id设为自增主键

有两种方式可以实现：

方式一：通过SQL Server Studio可视化操作

1. 打开SQL Server Studio，找到你的host数据库，展开后定位到dbo.hosts表。
2. 右键点击表，选择「设计」进入表结构编辑界面。
3. 选中Id列，在界面下方的「列属性」面板中找到「标识规范」选项：
   • 将「是标识」设置为「是」
   • 「标识种子」（初始值）默认设为1，「标识增量」（每次递增的值）默认设为1，保持默认即可（特殊需求可自行调整）。
4. 保存表结构，完成设置。

方式二：通过SQL语句修改表结构

执行以下SQL命令来修改Id列的属性：

ALTER TABLE host.dbo.hosts
ALTER COLUMN Id INT IDENTITY(1,1);

注意：如果表中已有数据，需要确保现有Id列的所有值都是唯一的，否则执行该命令会报错。若存在重复值，需先清理或调整数据后再操作。

步骤2：修改Python代码，移除手动指定的Id参数

因为数据库会自动生成Id值，所以插入语句中不需要再包含Id字段和对应的参数。修改后的插入代码如下：

# 移除INSERT语句中的Id字段和对应的4123参数
con.execute('INSERT INTO host.dbo.hosts (ip, host, HSTS, HPKP, XContentTypeOptions, XFrameOptions, ContentSecurityPolicy, Xssprotection, Server, Timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', (host, dest, rdata['Strict-Transport-Security'], rdata['Public-Key-Pins'], rdata['X-Content-Type-Options'], rdata['X-Frame-Options'], rdata['Content-Security-Policy'], rdata['X-XSS-Protection'], rdata['Server'], timestamp))
con.commit()

可选：获取刚插入记录的自增Id值

如果需要在代码中获取刚插入记录的Id值，可以使用SCOPE_IDENTITY()函数，示例代码如下：

cursor = con.cursor()
# 执行插入操作
cursor.execute('INSERT INTO host.dbo.hosts (ip, host, HSTS, HPKP, XContentTypeOptions, XFrameOptions, ContentSecurityPolicy, Xssprotection, Server, Timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', (host, dest, rdata['Strict-Transport-Security'], rdata['Public-Key-Pins'], rdata['X-Content-Type-Options'], rdata['X-Frame-Options'], rdata['Content-Security-Policy'], rdata['X-XSS-Protection'], rdata['Server'], timestamp))
# 获取刚生成的自增Id
new_record_id = cursor.execute("SELECT SCOPE_IDENTITY()").fetchone()[0]
print(f"新插入记录的Id为：{new_record_id}")
con.commit()

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