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

Question

我有这个 SQL 查询，但每次插入新数据时都必须更改 id，因为它是主键。如何让它在每次插入时添加一个新的未使用的主键值？

我正在使用 Microsoft SQL Server Studio

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()

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不在列列表中。）