Code block unreachable when in if/else statement

Question

When using an if/else statement to verify data was found to return the proper status code, my code within the loop to parse the response becomes completely unreachable.

The following works as intended.

class Circuit(Resource):
    def get(self, store):
        print('USAGE: Received a request at CIRCUIT for Store ' + store )
        conn = sqlite3.connect('store-db.db')
        cur = conn.cursor()
        res = cur.execute('SELECT * FROM Circuit WHERE StoreNumber like ' + store)     

        for r in res:
            column_names = ["StoreNumber", "MainLEC", "MainCircuitID","SprintNUA","LastMileCircuitID", "AnalogCarrier", "SignalingCluster"]
            data = [r[0], r[1], r[2], r[3], r[4], r[5], r[6]]            
            datadict = {column_names[itemindex]:item for itemindex, item in enumerate(data)}
            return(datadict, 200)

200 Result:

HTTP/1.0 200 OK
Content-Type: application/json
Content-Length: 239
Access-Control-Allow-Origin: *
Server: Werkzeug/0.14.1 Python/3.7.0
Date: Thu, 15 Nov 2018 16:30:01 GMT

{
  "StoreNumber": "42",
  "MainLEC": "XO",
  "MainCircuitID": "xx/xxx/xxxxx/ /TQW /",
  "SprintNUA": "",
  "LastMileCircuitID": "xx/xxxx/xxxx//PA",
  "AnalogCarrier": "XO/BE",
  "SignalingCluster": "ipv4:xx.2.xx.x0x"
}

404 Result (no data found but still returns 200)

HTTP/1.0 200 OK
Content-Type: application/json
Content-Length: 5
Access-Control-Allow-Origin: *
Server: Werkzeug/0.14.1 Python/3.7.0
Date: Thu, 15 Nov 2018 16:31:14 GMT

null

So that works, but I want to check for no data found so I wrote a conditional that gets the row count. Here are examples of its use.

Code Sample

class Circuit(Resource):
    def get(self, store):
        print('USAGE: Received a request at CIRCUIT for Store ' + store )
        conn = sqlite3.connect('store-db.db')
        cur = conn.cursor()
        res = cur.execute('SELECT * FROM Circuit WHERE StoreNumber like ' + store)     

        if len(list(cur)) == 0:
            return('No data', 404)
        else:
            for r in res:
                column_names = ["StoreNumber", "MainLEC", "MainCircuitID","SprintNUA","LastMileCircuitID", "AnalogCarrier", "SignalingCluster"]
                data = [r[0], r[1], r[2], r[3], r[4], r[5], r[6]]            
                datadict = {column_names[itemindex]:item for itemindex, item in enumerate(data)}
                return(datadict, 200)

200 Result:

HTTP/1.0 200 OK
Content-Type: application/json
Content-Length: 5
Access-Control-Allow-Origin: *
Server: Werkzeug/0.14.1 Python/3.7.0
Date: Thu, 15 Nov 2018 16:35:53 GMT

null

The data just comes back as null and after testing with prints I found that at for r in res: my code is becoming unreachable. I've verified all indents.

404 Result:

HTTP/1.0 404 NOT FOUND
Content-Type: application/json
Content-Length: 10
Access-Control-Allow-Origin: *
Server: Werkzeug/0.14.1 Python/3.7.0
Date: Thu, 15 Nov 2018 16:37:17 GMT

"No data"

The count is 0 so we get our 404 return, so I know the count and conditional are working.

If I put a print() right after else: it will run, but the loop will not.

Answer 1

By calling list() on the cursor, you exhaust the iterator, leaving nothing else for your else branch to loop through. The toy code below demonstrates this, and I have made some alterations for better practice:

1. I used the with context manager which will handle closing the database for us even in the event of an error.
2. I've used parameterized queries to retrieve values; these will help protect against SQL Injection.
3. I've demonstrated using .fetchall() to retrieve results. Although iterating directly over the cursor is more efficient than generating the whole result list upfront, it allows you to iterate multiple times, in addition to assigning the results to a meaningful name.

Example:

import sqlite3

with sqlite3.connect(":memory:") as conn: # Using a context manager
    c = conn.cursor()

    c.execute("""
              CREATE TABLE IF NOT EXISTS testing(
                  some_code INTEGER,
                  data TEXT)
              """)

    c.executemany("""
                  INSERT INTO testing VALUES (?, ?)
                  """, [[1, 'hi'], [2, 'bye'], [1, 'something']])

    # Query the new database using a parameterized query
    c.execute("select * from testing where some_code = ?", (1,))

    if len(list(c)) > 0: # Exhausts the iterator and then throws the result away 
        print("Printing result set 1")
        for row in c:
            print(row)
        print("End of result set 1")
        print()

    # Repeat the query
    c.execute("select * from testing where some_code = ?", (1,))
    print("Printing result set 2")
    for row in c: # iterate the cursor 
        print(row)
    print("End of result set 2")
    print()

    # And one more time but using fetchall()
    c.execute("select * from testing where some_code = ?", (1,))
    data = c.fetchall() # Exhaust the iterator but assign a list to a name
    print("Printing result set 3")
    for row in data:
        print(row)
    print("End of result set 3")
    print()

    # And we can keep on printing without re-querying
    print("Printing result set 4")
    for row in data: 
        print(row)
    print("End of result set 4")
    print()

Answer 2

It looks like cur is an iterator, and that res is a reference that iterator. When you call list(cur) it exhausts the iterator, and then you throw that data away. Then you try to iterate through res, there's nothing left and so your for loop does nothing.

The obvious answer would be to do:

    res = list(cur) 
    if len(res) == 0:
        return('No data', 404)
    else:
        for r in res:
            column_names = ["StoreNumber", "MainLEC", "MainCircuitID","SprintNUA","LastMileCircuitID", "AnalogCarrier", "SignalingCluster"]
            data = [r[0], r[1], r[2], r[3], r[4], r[5], r[6]]            
            datadict = {column_names[itemindex]:item for itemindex, item in enumerate(data)}
            return(datadict, 200)