2021-12-10

Tech Programing

程式人小天地

SQLite3 in Python: How to use UNIX time as the “primary key”

2 min read


I’m trying to create a table in my database where the PRIMARY KEY is UNIX time (I know for a fact that this will be unique for each data entry). My issue is that I can’t enter the data I want to when I designate the UNIX_time column as the PRIMARY KEY.

Creating table without PRIMARY KEY

cursor.execute("CREATE TABLE trades (UNIX_time REAL, Date TEXT, Time TEXT, Position TEXT, Entry_Price REAL, Quantity REAL, Stop_Loss REAL, Take_Profit REAL, Projected_Loss REAL, Projected_Profit REAL, Realised_PnL REAL)")

Creating table with PRIMARY KEY

cursor.execute("CREATE TABLE trades (UNIX_time REAL PRIMARY KEY, Date TEXT, Time TEXT, Position TEXT, Entry_Price REAL, Quantity REAL, Stop_Loss REAL, Take_Profit REAL, Projected_Loss REAL, Projected_Profit REAL, Realised_PnL REAL)")

A typical data entry

cursor.execute(
        """INSERT INTO trades
        (UNIX_time, Date, Time, Position, Entry_Price, Quantity, Stop_Loss, Take_Profit, Projected_Loss, Projected_Profit)
        VALUES (?,?,?,?,?,?,?,?,?,?)""",
        (UNIX_time, date_time[0], date_time[1], position, entry_price, quantity, SL_price, TP_price, proj_loss, proj_profit)
        )

Again, when I DO NOT define UNIX_time as the PRIMARY KEY, I can enter my data with no issues, but when I DO define UNIX_time as the PRIMARY KEY, I get a sqlite3.IntegrityError: datatype mismatch error. I’ve found that you can’t define the PRIMARY KEY as INTEGER value as the auto-generated ID tags do that already, so it doesn’t work. My UNIX time is a float type value, so are those restrictions still in place? If not, what am I doing wrong?



Source link

資料來源:Stackoverflow

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *