William Smith William Smith - 1 month ago 13
Python Question

Python SQlite returning an Object rather than a string

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!

Answer

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).

Comments