LIKE not working in WHERE statement when also including variable condition

December 25, 2021

Using Python, I would like to delete all rows in an SQLite db, that fulfill two conditions – that the text in the gameID column does not contain ID, and that the date in the SaleEnd column is before the date data_relevant_period.

Apparently due to there being 2 different uses of % (that each on their own works), I get the error message ValueError: unsupported format character 'I' (0x49) at index 46

I am a newbie to Python (and haven’t programmed for years in any language), so I apologize if this is something basic, I did try to search for a solution for quite some time before asking.

from datetime import date, datetime
from dateutil.relativedelta import relativedelta
import sqlite3

data_relevant_period = date.today() - relativedelta(days = 14)

try:
    sqliteConnection = sqlite3.connect('games.db')
    cursor = sqliteConnection.cursor()

    # before filling db with new data, delete any lines that do not have an internal game ID and the sale was over 14 days ago    
    deleteRows = "DELETE FROM GamesData WHERE GameID NOT LIKE '%ID%' AND SaleEnd < '%s'" %(data_relevant_period,)      

    cursor.execute(deleteRows)    
    sqliteConnection.commit()            

    cursor.close()        
except sqlite3.Error as error:
    print("Failed to delete", error)
finally:
    if sqliteConnection:
         sqliteConnection.close()

On their own, both

deleteRows = "DELETE FROM GamesData WHERE GameID NOT LIKE '%ID%'"

and

deleteRows = "DELETE FROM GamesData WHERE SaleEnd < '%s'" %(data_relevant_period,)

work fine.

>Solution :

You could use f-strings :

deleteRows = f"DELETE FROM GamesData WHERE GameID NOT LIKE '%ID%' AND SaleEnd < '{data_relevant_period}'"

https://docs.python.org/3/tutorial/inputoutput.html

Or escape the % with another one :

deleteRows = "DELETE FROM GamesData WHERE GameID NOT LIKE '%%ID%%' AND SaleEnd < '%s'" %(data_relevant_period,)

As a side note, it’s a good practice to use either camelCase or snake_case. Python’s devs are asked to use snake_case, see PEP-8 for more information, (camelCase is just for classes).