Skip to main content
 首页 » 编程设计

python之遍历 fetchall 时在 sqlite 中获取行 ID

2024年11月01日11jillzhang

我试图在遍历它时获取该行的 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 不是自动递增的。