CODEWITHSUNDEEP
pythonarrayssqlite
William Smith
William Smith

Reputation: 127

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!

Upvotes: 0

Views: 2405

Answers (2)

flaschbier
flaschbier

Reputation: 4175

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

Upvotes: 1

Padraic Cunningham
Padraic Cunningham

Reputation: 180522

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'"

Upvotes: 1

Related Questions