How to update rows of an attribute in SQLite3 when dealing with date-time string-CodePudding

I am trying to do a patch on a weather report database. I need to manually change each rows of a date column like so: by changing the date format to this new format: .

Bear in mind that this table and its attributes was made without any conditions. So, int/str/bools/any may as well be in any of these rows and it should work.

This new format was carried out by this script, a for loop which simply extracts the old database values and returns variables containing the formatted string.

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

Now, I would normally just pass these variables into an INSERT function such as this:

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

But a really weird thing happens where it would only update the year of the formatted string like so:

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

CodePudding user response：

The problem is that you are doing your own substitutions into the SQL. You will end up with:

UPDATE table SET setVar = 2022-03-01 WHERE ROWID = xxx

Sqlite sees that as an arithmetic expression. 2022 minus 3 minus 1 is 2018.

The short-term fix is to quote the value:

cursor.execute(f'UPDATE {table} SET {setVar} = "{setVal}" WHERE ROWID = {setID}')

A better fix is to let the connector do the substitution:

cursor.execute(f'UPDATE {table} SET {setVar} = ? WHERE ROWID = ?', (setVal, setID))

FOLLOWUP

As a side note, your regular expressions are totally unnecessary here.

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)