我试图在遍历它时获取该行的 ID,但似乎无法弄清楚如何。这是我的。
cursor = conn.cursor()
cursor.execute("SELECT * FROM Logs {} ORDER BY created {} LIMIT 0, {}".format(created_filter, settings.order, settings.limit))
logs = list()
for row in cursor.fetchall():
# I want to get the row id in here.
这是我创建和写入数据库的地方。一切正常,显然我的行 ID 除外。
file = settings.logs_path
log_table_name = 'Logs'
pin_table_name = 'Pins'
conn = sqlite3.connect(file, check_same_thread=False)
c = conn.cursor()
log_schema = dict(rowid = 'INTEGER PRIMARY KEY AUTOINCREMENT',
asctime = 'TEXT',
created = 'REAL',
exc_info = 'TEXT',
exc_text = 'TEXT',
filename = 'TEXT',
funcName = 'TEXT',
levelname = 'TEXT',
levelno = 'INTEGER',
lineno = 'INTEGER',
module = 'TEXT',
msecs = 'REAL',
message = 'TEXT',
msg = 'TEXT',
name = 'TEXT',
pathname = 'TEXT',
process = 'INTEGER',
processName = 'TEXT',
relativeCreated = 'REAL',
thread = 'INTEGER',
threadName = 'TEXT')
pin_schema = dict(rowid = 'INTEGER PRIMARY KEY AUTOINCREMENT',
pinnedId = 'INTEGER')
sql = 'CREATE TABLE IF NOT EXISTS ' + log_table_name + ' ({})'.format(','.join(log_schema))
c.execute(sql)
sql = 'CREATE TABLE IF NOT EXISTS ' + pin_table_name + ' ({})'.format(','.join(pin_schema))
c.execute(sql)
conn.commit()
def write_log(log):
keys = [key for key in log_schema.keys() if key != 'rowid']
values = []
for key in keys:
try:
values.append(str(getattr(log, key)))
except Exception:
values.append('')
parameters = ['?' for key in keys]
c.execute("INSERT INTO Logs ({}) VALUES ({})".format(','.join(keys),','.join(parameters)), values)
conn.commit()
请您参考如下方法:
您的 CREATE TABLE 命令缺少字段类型。注意这里发生了什么:
In [27]: pin_schema = dict(rowid = 'INTEGER PRIMARY KEY AUTOINCREMENT',
pinnedId = 'INTEGER')
In [30]: pin_table_name = 'Pins'
In [31]: sql = 'CREATE TABLE IF NOT EXISTS ' + pin_table_name + ' ({})'.format(','.join(pin_schema))
In [32]: sql
Out[32]: 'CREATE TABLE IF NOT EXISTS Pins (pinnedId,rowid)'
您得到的只是没有字段类型的字段名称。相反,要么将 pin_schema 和 log_schema 定义为包含字段和字段类型的字符串列表,要么遍历 pin_schema.items() :
sql = 'CREATE TABLE IF NOT EXISTS ' + pin_table_name + ' ({})'.format(
', '.join('{f} {t}'.format(f=field, t=field_type)
for field, field_type in pin_schema.items()))
In [35]: sql
Out[35]: 'CREATE TABLE IF NOT EXISTS Pins (pinnedId INTEGER, rowid INTEGER PRIMARY KEY AUTOINCREMENT)'
缺少字段类型解释了为什么 rowid 不是自动递增的。
