Hey there! Let's break down how to solve both your problems with your Qt + SQLite setup. I'll use your existing MainDialog class as a starting point.

1. Display Time Delta as the Third Column in QTableView #

You have two straightforward options here—either calculate the time delta directly in your SQL query (most efficient) or compute it in a custom Qt model. Let's start with the SQL approach since it's simpler:

Option 1: Calculate Time Delta in SQL Query #

Modify your SQLite SELECT statement to compute the time difference between datetime2 and datetime1 right when fetching data. SQLite's strftime() function converts datetime strings to Unix timestamps, making subtraction easy:

from PyQt5.QtSql import QSqlQueryModel, QSqlDatabase
from PyQt5.QtCore import Qt

class MainDialog(QDialog, TabView.Ui_Dialog):
    def __init__(self, parent=None):
        super(MainDialog, self).__init__(parent)
        self.setupUi(self)
        
        # Connect to your SQLite database
        self.db = QSqlDatabase.addDatabase("QSQLITE")
        self.db.setDatabaseName("LaBase.db")
        if not self.db.open():
            print("Database connection failed!")
            return
        
        # Create a query model with built-in time delta calculation
        self.model = QSqlQueryModel()
        query_str = """
            SELECT datetime1, datetime2, 
                   strftime('%s', datetime2) - strftime('%s', datetime1) AS time_delta
            FROM your_table_name
        """
        self.model.setQuery(query_str, self.db)
        
        # Set human-readable headers for the table
        self.model.setHeaderData(0, Qt.Horizontal, "Datetime 1")
        self.model.setHeaderData(1, Qt.Horizontal, "Datetime 2")
        self.model.setHeaderData(2, Qt.Horizontal, "Time Delta (Seconds)")
        
        # Attach the model to your QTableView
        self.tableView.setModel(self.model)

Option 2: Custom Model for Dynamic Calculation #

If you need to compute the time delta on the fly (e.g., if datetime values might update in the model), create a custom model that inherits from QSqlQueryModel and overrides the data() method:

from PyQt5.QtCore import Qt, QVariant
from PyQt5.QtSql import QSqlQueryModel
from datetime import datetime

class CustomQueryModel(QSqlQueryModel):
    def data(self, index, role=Qt.DisplayRole):
        if role == Qt.DisplayRole and index.column() == 2:
            # Pull datetime values from the first two columns
            datetime1 = self.index(index.row(), 0).data()
            datetime2 = self.index(index.row(), 1).data()
            
            # Convert to datetime objects and calculate delta
            dt1 = datetime.fromisoformat(datetime1)
            dt2 = datetime.fromisoformat(datetime2)
            delta = dt2 - dt1
            # Return a user-friendly string (e.g., "0 days 01:23:45")
            return str(delta)
        return super().data(index, role)

# Use this custom model in your MainDialog:
class MainDialog(QDialog, TabView.Ui_Dialog):
    def __init__(self, parent=None):
        super(MainDialog, self).__init__(parent)
        self.setupUi(self)
        
        # ... database connection code ...
        
        self.model = CustomQueryModel()
        self.model.setQuery("SELECT datetime1, datetime2 FROM your_table_name", self.db)
        self.model.setHeaderData(2, Qt.Horizontal, "Time Delta")
        self.tableView.setModel(self.model)

2. Save Time Delta Results to SQLite Database #

To store the time delta permanently, you first need a column in your table to hold it. Then you can either insert the delta when adding new rows or update existing rows with the computed value.

Step 1: Add a Time Delta Column to Your Table (if missing) #

Run this SQL command once (either in your code or via a SQLite browser) to create the column:

# Execute this once to add the column to your table
query = QSqlQuery(self.db)
# Use INTEGER for seconds, or TEXT if you want to store formatted delta strings
query.exec_("ALTER TABLE your_table_name ADD COLUMN time_delta INTEGER;")

Step 2: Update Existing Rows with Time Delta #

Compute and save the delta for all existing rows with a single UPDATE statement:

query = QSqlQuery(self.db)
query.exec_("""
    UPDATE your_table_name
    SET time_delta = strftime('%s', datetime2) - strftime('%s', datetime1)
""")

Step 3: Insert New Rows with Precomputed Time Delta #

When adding new data to the table, calculate the delta first and include it in your INSERT statement:

def insert_new_row(datetime1_str, datetime2_str):
    # Calculate delta in total seconds
    dt1 = datetime.fromisoformat(datetime1_str)
    dt2 = datetime.fromisoformat(datetime2_str)
    delta_seconds = int((dt2 - dt1).total_seconds())
    
    query = QSqlQuery(self.db)
    query.prepare("""
        INSERT INTO your_table_name (datetime1, datetime2, time_delta)
        VALUES (:dt1, :dt2, :delta)
    """)
    query.bindValue(":dt1", datetime1_str)
    query.bindValue(":dt2", datetime2_str)
    query.bindValue(":delta", delta_seconds)
    query.exec_()

Just replace your_table_name with the actual name of your table in SQLite, and adjust the datetime parsing if you're using a format other than ISO 8601. Let me know if you need help tweaking this to your exact setup!

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