It's likely this question has been answered already (in fact, it has for other languages), but I've searched for hours and I didn't find anything that helped with this particular problem, so here goes.
I'm trying to create a basic journal app, that uses SQLite to store the entries. However, I've spent days (on and off) trying to figure out how to make an escaped SQLite query, so I can get/write data that has otherwise-error-causing characters. I'm using SQLAlchemy.
According to How to properly escape strings when manually building SQL queries in SQLAlchemy?, to escape a variable while passing it to a query, you can use ":varname" in the query string, and then pass an argument named varname to execute().
However, when I tried that with my code, it didn't work. Here's an example.
from sqlalchemy import engine
from sqlalchemy.orm import Session
connection = engine.create_engine("sqlite:////home/user/journal.db").connect()
session = Session(connection)
var = 'Merry'
print(connection.execute("SELECT * FROM entries WHERE entry LIKE \"%:varname%\"", varname=var).fetchall())
This outputs an empty list ([]). However, when I replace :varname in the query with Merry, it returns the correct data.
For those who are interested, here is exactly what my database looks like. If you want code that can create it, I'd be happy to provide it.
| date | entry | preview | number |
|---|---|---|---|
2021-02-03 |
This is from 2021. |
This is from 2021. |
1 |
1908-12-25 |
This is from 1908.\nMerry Christmas!\n (repeated four times) |
This is from 1908.\nMerry Chris... |
2 |
Other answers (like some of these) don't help because they don't escape the characters. Why does the plain string query work, when the variable query doesn't?
