python - 如何在创建表期间在 SQLite 中设置默认日期值

Question

寻求一些建议和帮助。我想将列值设置为默认为当前系统日期，不应该将日期值插入表中。我尝试了两种不同的方法但没有成功。第一个在下面。使用字符串格式并运行此代码，

current_date = datetime.now().strftime('%Y %m %d')
conn = sqlite3.connect("pipe_tally.db")

cur = conn.cursor()

def create_table():
    """This function creates the database table."""
    cur.execute("""CREATE TABLE tally_file (
        pipe_id TEXT PRIMARY KEY,
        kind TEXT,
        joint_num INTEGER,
        heat INTEGER,
        asset_length REAL CHECK(asset_length > 0),
        asset_wall_thickness REAL,
        asset_degree REAL,
        manufacturer TEXT,
        collected_date TEXT DEFAULT {}
        )""".format(current_date))
    conn.commit()

给我错误:

Traceback (most recent call last):
  File "main.py", line 18, in <module>
    main()
  File "main.py", line 11, in main
    db.create_table()
  File "/home/bigdaddy/PAM_Android/db_helper.py", line 25, in create_table
    )""".format(current_date))
sqlite3.OperationalError: near "09": syntax error

我猜 09 是月份，但我不理解这个错误。第二种方法是尝试使用如下元组:

import sqlite3
from datetime import datetime

current_date = datetime.now().strftime('%Y %m %d')
conn = sqlite3.connect("pipe_tally.db")

cur = conn.cursor()

def create_table():
    """This function creates the database table."""
    cur.execute("""CREATE TABLE tally_file (
        pipe_id TEXT PRIMARY KEY,
        kind TEXT,
        joint_num INTEGER,
        heat INTEGER,
        asset_length REAL CHECK(asset_length > 0),
        asset_wall_thickness REAL,
        asset_degree REAL,
        manufacturer TEXT,
        collected_date TEXT DEFAULT (?)
        )""", (current_date,))
    conn.commit()

运行返回:

Traceback (most recent call last):
  File "main.py", line 18, in <module>
    main()
  File "main.py", line 11, in main
    db.create_table()
  File "/home/bigdaddy/PAM_Android/db_helper.py", line 25, in create_table
    )""", (current_date,))
sqlite3.OperationalError: default value of column [collected_date] is not constant

是否可以使用 SQLite 完成此操作？我正在使用 Xubuntu Linux 16.04 LTS、Python3.6 和 SQLite3 版本 2.8.17在此先感谢您的帮助。

Answer 1

您可以使用特殊符号 CURRENT_DATE作为新插入行的默认值:

CREATE TABLE tally_file (
    [...],
    collected_date TEXT DEFAULT CURRENT_DATE
);