I'm trying to create a tagging app using python that functions much in the same way that popular *booru websites do. However, I'm having trouble setting up a search function that would effectively query my database and return the correct data.
What I expect to happen is I'll be able to put any number of tags in the search bar and have it return posts that are associated with at least those tags. Additionally, eventually I'd like to be able to add something like "!tag" to a search query to exclude posts with a certain tag. What's happening currently, is I've been able to query two tags at a time, but they are baked into the query and I can't find out how to change them.
I've used the answer posted here as a template: https://stackoverflow.com/a/62731660
I have three tables:
posts posttags tags
---- ---- ----
id postid | tagid id | tagname
Here's what my code looks like currently:
res = dbCursor.execute("SELECT postid FROM posttags JOIN tags on tagid=id WHERE tags.tagname in (?) GROUP BY postid HAVING count(DISTINCT tagname) = ?", (query, len(query)))
print(res.fetchall())
where query is the search terms and len(query) is the number of terms in the query.
What happens currently is the tags will be passed to the execute function, but print(res.fetchall()) only returns an empty list. If I replace the "(?)" in the query with ('tag1','tag2') it works fine and returns the appropriate postids. Formatting query to have backets and the like either throws a syntax error or returns nothing as before.
Right now I can only query the specific number of tags I hard code into the sql query, but I'd like to query any amount of tags that the user submits. Is there a way I can query an arbitrary amount of tags using sql?
I've checked multiple answers here on stack overflow but all of them seem to require baking the search parameters into the query. Could I just chop up the query manually and insert the terms, or is there a better way to go about this? Any information is appreciated!
I ended up manipulating the query string directly. I understand this is bad practice but it's the only way I know how to do this. The search terms are also unescaped which makes this prone to sql injection.
Here's how I did it. The code comments should help explain what's going on:
import sqlite3 as sql
def find():
# Get the value of a tkinter search bar with some formatting
# Converting it to a set and then a list removes duplicates
query = list(set(str(modify.get()).lower().split()))
# Create an empty list to store our found posts in
postsbytag = []
# Connect to our sqlite database
dbConnection = sql.connect('database.db')
dbCursor = dbConnection.cursor()
# Further format the results of our search bar
mystring = str(tuple(query))
# Get the number of tags searched
querylength = len(tuple(query))
# Some extra formatting to get the query to work if we only stitch in one tag
if querylength == 1:
mystring = mystring.replace(',','')
# Generate our sql query based on our search
myquery = "SELECT postid FROM posttags JOIN tags on tagid=id WHERE tags.tagname in " + mystring + " GROUP BY postid HAVING count(DISTINCT tagname) = " + str(querylength)
# Execute the query against the database
res = dbCursor.execute(myquery)
# Set the result of our query to variable
tagiddata = res.fetchall()
# From here we can process the information in tagiddata and do what we want with it.
# Close the database connection and exit the function
dbConnection.close()