英文:
Is it possible in SQlite3 python that if a row has been previously updated, it cannot be updated for like 5 seconds
问题
在数据库表中,如果特定行之前已被修改或更新,我希望在一段时间内(比如说我设置的5秒内)不允许修改该行,即使程序尝试修改该行,然后在此之后可以再次修改或更新该行。这是否可能?
英文:
In a database table, if a specific row has been previously modified or updated, I want that row to not be changed or cannot be modified for let's say I set in 5 seconds even if the program is trying to modify that row, then after that the row can be modified or update again. Is that possible?
I didn't try anything yet because I dont have an idea if it is possible or not
答案1
得分: 0
以下是翻译好的部分:
是的,这是可能的。要实现这种行为,您可以向您的表添加一个新列,我们称之为 last_modified
。它将获得当前时间戳的默认值。
CREATE TABLE your_table (
...
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
或者
ALTER TABLE your_table ADD COLUMN last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
现在,每次您想要更新表时,您都可以检查受影响的行是否在最近的5秒内被更新,如果没有,就更新该行;如果是,则抛出错误。
import sqlite3
import time
# 连接到SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# 如果表不存在,则创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS your_table
(id INTEGER PRIMARY KEY AUTOINCREMENT,
column1 TEXT,
column2 TEXT,
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
# 检查是否可以修改行的函数
def can_modify_row(row_id, interval):
cursor.execute("SELECT ROUND((JULIANDAY('now') - JULIANDAY(last_modified)) * 86400) FROM your_table WHERE id = ?", (row_id,))
elapsed_time = cursor.fetchone()[0]
return elapsed_time > interval
# 更新行,将last_modified时间戳设置为当前时间
def update_row(row_id, column1_value, column2_value):
cursor.execute("UPDATE your_table SET column1 = ?, column2 = ?, last_modified = CURRENT_TIMESTAMP WHERE id = ?", (column1_value, column2_value, row_id))
conn.commit()
# 示例用法
row_id = 1
interval = 5 # 秒
if can_modify_row(row_id, interval):
print("修改行...")
update_row(row_id, "new_value1", "new_value2")
else:
print("暂时无法修改行。请稍后再试。")
# 关闭数据库连接
conn.close()
这段代码目前尚未经过测试,但我希望您能理解其思想。
英文:
Yes this is possible. To implement this kind of behaviour you can add a new column to your table let's call it last_modified
. Which get's a default value of the current timestamp
CREATE TABLE your_table (
...
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
or
ALTER TABLE your_table ADD COLUMN last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
Now each time you want to update your table you check if the affected row has been updated in the last 5 seconds if not update the row if yes throw an error.
import sqlite3
import time
# Connect to the SQLite database
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()
# Create the table if it doesn't exist
cursor.execute('''CREATE TABLE IF NOT EXISTS your_table
(id INTEGER PRIMARY KEY AUTOINCREMENT,
column1 TEXT,
column2 TEXT,
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP)''')
# Function to check if a row can be modified
def can_modify_row(row_id, interval):
cursor.execute("SELECT ROUND((JULIANDAY('now') - JULIANDAY(last_modified)) * 86400) FROM your_table WHERE id = ?", (row_id,))
elapsed_time = cursor.fetchone()[0]
return elapsed_time > interval
# Update a row, setting the last_modified timestamp to the current time
def update_row(row_id, column1_value, column2_value):
cursor.execute("UPDATE your_table SET column1 = ?, column2 = ?, last_modified = CURRENT_TIMESTAMP WHERE id = ?", (column1_value, column2_value, row_id))
conn.commit()
# Example usage
row_id = 1
interval = 5 # seconds
if can_modify_row(row_id, interval):
print("Modifying the row...")
update_row(row_id, "new_value1", "new_value2")
else:
print("Cannot modify the row yet. Please try again later.")
# Close the database connection
conn.close()
The code is currently untested, but I hope you get the idea
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论