Python SQlite returning an Object rather than a string

Question

I'm trying to create a support ticket system using flask. I am using sqlite3 for my database of tickets. I am trying to get all of the 'ids' of tickets with a status of 'pending' however when I run the said function it returns <sqlite3.Cursor object at 0x03746B20> is their anyway I can fetch all of the ids as an array of strings?

Function:

def GetPendingTickets():
  opendb()
  qry = "SELECT 'id' FROM 'tickets' WHERE 'Status'='Pending'"
  connection.row_factory = lambda cursor, row: row[0]
  openTickets = cursor.execute(qry)
  closedb()
  return openTickets

Thanks in advance!

Hi, yes I tried fetchall however it only returned "[]", I know that there is at least one ticket with the status "pending" because I can see it in the database (im using a database viewer) — William Smith
– William Smith, Commented Feb 14, 2016 at 17:12

flaschbier · Accepted Answer · 2016-10-29 06:30:24Z

1

Just removing the quotes leaves open the question, why to do that.

Well, quoting is a bit clumsy in SQL. String literals are quoted with single quotes like in 'pending' (always mandatory), while field and table names may be quoted in double quotes like in "tickets", but this is only mandatory, when the field names contain strange characters or are case-sensitive. You were heavily working with string literals, but this is not your intention. you can write

qry = """SELECT "id" FROM "tickets" WHERE "Status" = 'Pending'"""

or, minimizing quotes, write

qry = """SELECT id FROM tickets WHERE "Status" = 'Pending'"""

when the field name of the status field is really "Status" and not "STATUS" (you would have created the table with quotes in that case).

edited Oct 29, 2016 at 6:30

answered Feb 14, 2016 at 18:30

flaschbier

4,2452 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Padraic Cunningham · Accepted Answer · 2016-02-14 17:26:43Z

1

You can use fetchall, fetchone or fetchmany after the execute:

iIn [1]: import sqlite3

In [2]: con = sqlite3.connect(":memory:")

In [3]: cur = con.cursor()

In [4]: cur.executescript("""
   ...:     create table person(
   ...:         firstname,
   ...:         lastname,
   ...:         age
   ...:     );
   ...: 
   ...:     create table book(
   ...:         title,
   ...:         author,
   ...:         published
   ...:     );
   ...: 
   ...:     insert into book(title, author, published)
   ...:     values (
   ...:         'Dirk Gently''s Holistic Detective Agency',
   ...:         'Douglas Adams',
   ...:         1987
   ...:     );
   ...:     """)
Out[4]: <sqlite3.Cursor at 0x7f1eac026ce0>

In [5]: cur.execute("SELECT * FROM `book`")
Out[5]: <sqlite3.Cursor at 0x7f1eac026ce0>

In [6]: cur.fetchall()
[(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)]

Also change your query:

qry = "SELECT id FROM tickets WHERE Status = 'Pending'"

edited Feb 14, 2016 at 17:26

answered Feb 14, 2016 at 17:09

Padraic Cunningham

181k30 gold badges264 silver badges327 bronze badges

4 Comments

William Smith Over a year ago

Hi, I tried .fetchal(), and it returned [], I know there is at least one row, where status = pending (I am using an Database Viewer)

Padraic Cunningham Over a year ago

Why are you wrapping the query strings in quotes?

William Smith Over a year ago

Because that is what I naturally did, I have now removed them, and it worked, thanks

Padraic Cunningham Over a year ago

The column names should not be wrapped in strings, just the row value if that is stored as a string. You're welcome.

Collectives™ on Stack Overflow

Python SQlite returning an Object rather than a string

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related