SQLite Forum

SELECT using variables
Login

SELECT using variables

(1) By anonymous on 2020-07-16 20:28:52 [source]

Hi...

I have a table with following columns (author, title, body)

I am trying to implement a tag system, clicking a tag should retrieve all posts that have this tag.

my python code is as follows:

def get_posts(name, tag):
    posts = get_db().execute(
        'SELECT * FROM post WHERE (? = ?) ORDER BY created DESC', (name, tag)
    ).fetchall()

name is a variable for a column
tag is the value assigned to name.

But it won't work

Some suggested:
def get_posts(name, tag):
    posts = get_db().execute(
        'SELECT * FROM post WHERE ('+ name +' = ?) ORDER BY created DESC', (tag,)
    ).fetchall()

but won't work too... 

What am I doing wrong???

(2) By Larry Brasfield (LarryBrasfield) on 2020-07-16 20:40:22 in reply to 1 [link] [source]

If you had stated the relevant schema, including that of whatever table(s) contain the tag values and/or association with the author_title_body table, somebody might volunteer a query. You might also peruse the SELECT documentation where it mentions, in section 1, the effects of the join clause. This would help you understand why a single-table query (of a table without any tag columns) does not reveal anything about tags.

(3.1) By David Raymond (dvdraymond) on 2020-07-17 14:07:02 edited from 3.0 in reply to 1 [link] [source]

Single quotes are used for a string literal. Double quotes are used for identifiers. So you're asking if the string literal 'author' is equal to the tag value, not if the column with the name "author" is equal to the tag.

Be sure that the input is coming from a trustworthy source, otherwise you could get a SQL injection attack: https://xkcd.com/327/

Some libraries will include a function that will return the appropriately quoted form of an indentifier for you, but I don't think Python's sqlite3 module has that. I could be wrong.

Edit: My bad, I misread the quotes and got mixed up which were for Python and which were for the statement.

(4.1) By Warren Young (wyoung) on 2020-07-17 18:01:34 edited from 4.0 in reply to 1 [link] [source]

In your first code sample, the problem is that parameters in queries cannot be schema names. SQLite needs all of the names up front to prepare the query for efficient execution. The query planner cannot know what to do with a statement if you don't provide the elements of the schema it will operate over, so if SQLite allowed this, it would have to re-prepare the query each time it ran.

As for your second code sample, I’m not clear on the problem. I'll need you to post more details; as it stands, I'm reduced to wild guesses. What is the content of name, what errors do you get, does the same query work in the sqlite3 shell, etc.

(5) By Warren Young (wyoung) on 2020-07-17 18:04:22 in reply to 4.1 [link] [source]

I'm reduced to wild guesses

Okay, here's one: (tag,) in Python evaluates to a 2-element list, not a 1-element list, but your query needs only one parameter. Does the error go away if you remove the unneeded comma?

(6) By David Raymond (dvdraymond) on 2020-07-17 18:37:24 in reply to 5 [link] [source]

(tag,) is a one element tuple, so it's the correct way of sending one parameter.

(7) By Larry Brasfield (LarryBrasfield) on 2020-07-17 19:33:27 in reply to 1 [link] [source]

With this suggestion, I do not gainsay the various guesses offered already.

If you intend to spend much time seeking help with technical issues, this article, "How to ask for (and receive) technical help on the internet", should help you to better help others to help you.

From what you've written, I have only the vaguest notion of what you are trying to accomplish. However, I could rewrite your python code this way: def get_posts(name, tag): if name == tag: posts = get_db().execute( 'SELECT * FROM post ORDER BY created DESC' ).fetchall() , with the same results as your code. I doubt that is what you intended.

I also note, for other forum participants, that the OP seems to have posted the single, poorly formulated request for help, then effectively abandoned it. It makes me wonder if similarly hasty pleas have appeared in many fora, and suggests to me that little effort to provide assistance is warranted.

(8) By TripeHound on 2020-07-17 21:14:52 in reply to 7 [link] [source]

It's also been (poorly) asked on StackOverflow, including the ubiquitous "but it didn't work" with no hint of how it didn't work nor any error messages that might have been produced.