python - 每次添加一行时更新时间戳？

Question

我有循环代码，向每一行添加一行信息。但是，我发现每一行都没有新的时间戳，而是与第一行有相同的时间戳，这让我相信 current_timestamp 的值并不是每次都更新。因此，什么解决了这个问题？这是我的代码:

if __name__ == "__main__":
    main()
    deleteAll()   # Clears current table


    ID = 0
    while ID < 100:          
        insert(ID, 'current_date', 'current_timestamp')
        ID += 1
    conn.commit()        

我的插入函数:

def insert(ID, date, timestamp): # Assumes table name is test1
    cur.execute(
    """INSERT INTO test1 (ID, date,timestamp) VALUES (%s, %s, %s);""", (ID, AsIs(date), AsIs(timestamp))) 

这段代码是用 python 编写的，顺便说一句，它使用 postgresql 来处理数据库。

Answer 1

立即修复是在每次插入后提交，否则所有插入都将在单个事务中完成

while ID < 100:          
    insert(ID, 'current_date', 'current_timestamp')
    ID += 1
    conn.commit()        

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.

那些函数不应作为参数传递，而应包含在 SQL 语句中

def insert(ID): # Assumes table name is test1
    cur.execute("""
        INSERT INTO test1 (ID, date, timestamp)
        VALUES (%s, current_date, current_timestamp);
    """, (ID,)
    ) 

最佳实践是将提交保持在循环之外以进行单个事务

while ID < 100:          
    insert(ID)
    ID += 1
conn.commit()        

并使用 statement_timestamp 函数，顾名思义，它返回语句时间戳而不是交易开始时间戳

INSERT INTO test1 (ID, date, timestamp)
values (%s, statement_timestamp()::date, statement_timestamp())