我正在嘗試對天氣報告資料庫進行修補。我需要像這樣手動更改日期列的每一行：
通過將日期格式更改為這種新格式：
.
請記住，此表及其屬性是在沒有任何條件的情況下制作的。因此， int/str/bools/any 也可以在這些行中的任何一行中，它應該可以作業。
這種新格式是由這個腳本執行的，這是一個 for 回圈，它簡單地提取舊資料庫值并回傳包含格式化字串的變數。
connection = sqlite3.connect('\\.db\\')
cursor = connection.cursor()
ROWID = cursor.execute('SELECT ROWID FROM update_test').fetchall()
Date = cursor.execute('SELECT Date FROM update_test').fetchall()
for row, dates in zip(ROWID, Date): # tuple
for i, x in zip(row, dates): # strings
try:
weekdays = r'^...'
regex = r'...-[\d\d]{1,}-Jan'
new_year = re.findall(regex, x)
for match in new_year:
updated_dates = f'{"2022"}{re.sub(weekdays, "", match)}'
date_object = datetime.datetime.strptime(updated_dates, '%Y-%d-%b').strftime('%Y-%m-%d')
print(i, date_object)
# update('test.db', 'update_test', 'date', date_object, i) # I want this bit to work
except TypeError:
pass
現在，我通常只是將這些變數傳遞給一個 INSERT 函式，例如：
def update(url, table, setVar, setVal, setID):
try:
connection = sqlite3.connect(url)
cursor = connection.cursor()
try:
cursor.execute(f'UPDATE {table} SET {setVar} = {setVal} WHERE ROWID = {setID}')
connection.commit()
except sqlite3.Error as error:
print(f'Error: \n {error}')
...
cursor.execute("SELECT name "
"FROM sqlite_master "
"WHERE type='table'")
... logging
... logging
... logging
... logging
... logging
connection.close()
... logging
except pandas.io.sql.DatabaseError:
...logging
但是發生了一件非常奇怪的事情，它只會像這樣更新格式化字串的年份：
Additionally, often, when used in a for loop, this year would increment -= 1 year. So: 2019, 2018, 2017 ... for each row specified in the update function.
My ideal output would be that dates would change into the new format I had initializing in that for loop (first script preview) and only those rows which specified (which already works anyway).
update('test.db', 'update_test', 'date', date_object, i) # I want this bit to work
uj5u.com熱心網友回復：
問題是您正在對 SQL 進行自己的替換。你最終會得到：
UPDATE table SET setVar = 2022-03-01 WHERE ROWID = xxx
Sqlite 將其視為算術運算式。2022 減 3 減 1 就是 2018。
短期解決方法是參考該值：
cursor.execute(f'UPDATE {table} SET {setVar} = "{setVal}" WHERE ROWID = {setID}')
更好的解決方法是讓連接器進行替換：
cursor.execute(f'UPDATE {table} SET {setVar} = ? WHERE ROWID = ?', (setVal, setID))
跟進
作為旁注，您的正則運算式在這里完全沒有必要。
connection = sqlite3.connect('\\.db\\')
cursor = connection.cursor()
rowset = cursor.execute('SELECT ROWID,Date FROM update_test')
for rowid,date in rowset:
parts = date.split('-')
if parts[2] == 'Jan':
parts[0] = '2022'
updated_dates = '-'.join(parts)
date_object = datetime.datetime.strptime(updated_dates, '%Y-%d-%b').strftime('%Y-%m-%d')
print(rowid, date_object)
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/405700.html
標籤：
上一篇：無法從AndroidStudio中的資料庫Sqlite獲取資料
下一篇：Python-'sqlite3.InterfaceError：錯誤系結引數0-可能是不受支持的型別。'使用sqlite3python將資料插入表時