0

in python my code to fetch data from the table is as follows:

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

name and value are variables depending on what the user clicks on the page.

The code won't work... how should it be written???

Addon:

Searching for a solution I bumped into the following:

def get_posts(name, tag):
    posts = get_db().execute(
        """SELECT * FROM post WHERE ({} = ?)""".format(name), (tag,)
    ).fetchall()

But didn't work for me too...

user90939
  • 7
  • 5
  • `'SELECT * FROM post WHERE '+ name + ' = ?) ORDER BY created DESC', (value)` – Chetan Jul 16 '20 at 17:30
  • thanks for ur quick reply... but it didn't work... you forgot to open the bracket before closing it... so I did: 'SELECT * FROM post WHERE ('+ name + ' = ?)... But the response is "Incorrect number of bindings" – user90939 Jul 16 '20 at 17:58
  • Did you remove `name` from the end? – Chetan Jul 16 '20 at 18:11
  • def get_posts(name, tag): posts = get_db().execute( 'SELECT * FROM post WHERE ('+ name + ' = ?) ORDER BY created DESC', (value)).fetchall() – user90939 Jul 16 '20 at 18:14
  • I have to mention... name is a variable, not a column. The columns are author, title, body. – user90939 Jul 16 '20 at 18:19
  • I know that. But the name will have column name assigned to it right? You want to query the table with both column name and value passed dynamically to the query. Try to add comma after `value` As `(value,)` https://stackoverflow.com/questions/16856647/sqlite3-programmingerror-incorrect-number-of-bindings-supplied-the-current-sta – Chetan Jul 16 '20 at 18:31
  • I did... but it won't work as well – user90939 Jul 16 '20 at 18:35
  • get_posts method has tag parameter but that's not used. Is the value variable declared somewhere else? Or you mistyped it in place of using`tag`? – Chetan Jul 16 '20 at 18:40
  • yes ... I mistyped... it's value not tag... mistyping happened here not in the application – user90939 Jul 16 '20 at 19:39
  • I am not sure what you found on Google about this error, but I found https://techoverflow.net/2019/10/14/how-to-fix-sqlite3-python-incorrect-number-of-bindings-supplied-the-current-statement-uses-1-supplied/. According to it you need to have pass value as an list element. So can you try `[value]`? – Chetan Jul 16 '20 at 21:07
  • Thanks a lot Chetan for your support and patience... I tried, but no... didn't work... I edited the post ... please have a look – user90939 Jul 17 '20 at 13:20
  • @user90939 I don't know Perl, but in your latest edit, I'm guessing `{}` is a place-holder to tell `.format()` where to put `name`. If so, you appear to have an extra closing-brace, at least in your edit. Another tip: at least while debugging, form the SELECT statement before `.execute` so you can print/see its value and make sure that part has worked as intended. – TripeHound Jul 17 '20 at 14:44
  • As TripeHound suggests, you have a bug in your (Python) code with the extra brace. More importantly, what doesn't work? How does it fail? What did you expect to happen? Without answers to these questions in the body of the question itself, our answers will be guesses. – Anon Coward Jul 17 '20 at 21:43
  • @Anon: Thanks for ur comment... the extra brace was a typo mistake. I am implementing this function in a flask project. It may be possible that flask doesn't allow this because it affects the security. – user90939 Jul 17 '20 at 22:44
  • That seems unlikely. Flask and SQLite are a not-uncommon combination of tools. What doesn't work? – Anon Coward Jul 17 '20 at 22:49
  • TypeError: The view function did not return a valid response. The function either returned None or ended without a return statement. This is the response... – user90939 Jul 17 '20 at 23:10
  • The function you showed us doesn't return anything at all under any condition. – Anon Coward Jul 17 '20 at 23:18
  • 1
    :) it ends with return posts Here is the complete function: def get_posts(name, tag): posts = get_db().execute( """SELECT * FROM post WHERE ({} = ?)""".format(name), (tag,) ).fetchall() if posts is None: abort(404, "Post id {0} doesn't exist.".format(id)) return posts – user90939 Jul 17 '20 at 23:22

0 Answers0