0

I am currently using python and Flask to query a database. I have the following function:`

def get_details(username):

  conn = database_connect()
  if(conn is None):
      return ERROR_CODE
  cur = conn.cursor()
  val = None

  try:
     cur.execute("""SELECT username, name, age, address
                    FROM users
                    WHERE username = %s;""",(username))

     val = cur.fetchone()
  except:
     print("failed to retrieve")

  cur.close()
  conn.close()
  return val

username in the "users" table is a primary key and hence only a tuple is returned by this query (confirmed this by manually querying in postgress). However "val" is always a "none". I have also tried to use fetchall() but this doesn't work either.

Is there something wrong with the query itself? How should I code this?

Flow-MH
  • 61
  • 1
  • 9
  • You should have seen that something was wrong when it printed out "failed to retrieve". The next step would be to not catch that exception at all because the traceback has useful info. – Alex Hall May 21 '16 at 12:18

1 Answers1

3

There is a problem in the way you pass the query parameters into the query. Pass them in a tuple:

cur.execute("""SELECT username, name, age, address
               FROM users
               WHERE username = %s""", (username, ))
                       # comma is important HERE^

As a side note, consider using an ORM instead of going to the database directly and manually constructing SQL queries. There is a relevant package for you to explore: flask-sqlalchemy.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Thank you, this worked! Do you mind explaining the meaning behind the additional comma in the parameter? – Flow-MH May 21 '16 at 12:09
  • `cursor.execute()` takes a string and a tuple parameter. One-tuples are special: https://wiki.python.org/moin/TupleSyntax – thebjorn May 21 '16 at 12:14