Python每次插入新数据时如何自动生成不存在的主键

Question

我有这个 SQL 查询，但每次插入新数据时都必须更改 id，因为它是主键。如何让它在每次插入时添加一个新的未使用的主键值？我正在使用 Microsoft SQL Server Studioimport urllib.request as urllibimport socketimport pyodbcfrom datetime import datetime#Timestamp for undersøgelsetimestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')#Host info og IPhost = "www.rejseplanen.dk"dest = socket.gethostbyname(host)hdata = 'host',host,'IP:',dest#Responseheader requestrequest = 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 databasecon = 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()

Answer 1

你没有。你让数据库来做。因此，主机表应定义为：

create table host (

    id int identity(1, 1) primary key,

    ip . . . 

);

然后，您将值排除在外insert：

INSERT INTO host.dbo.hosts (ip, host, HSTS, HPKP, XContentTypeOptions, XFrameOptions, ContentSecurityPolicy, Xssprotection, Server, Timestamp)

    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

（请注意，id不在列列表中。）