CODEWITHSUNDEEP
pythonpyodbc
lem
lem

Reputation: 119

python class using pyodbc. Error on multiple rows

This piece of code prints out one line of data which is expected. It prints out "None" in the second row which I would like an explanation on as well but it is not the main question

rows = cursor.fetchone()
for row in rows:
    cur = SHIPMENT_BO(row.SHIPMENT_NUM, row.SHIPMENT_ID, row.ORIGIN_BRANCH_CODE, row.DEST_BRANCH_CODE)
    print(cur.PrintShipment())

This code only returns the first print statement in the function but none of the data.

rows = cursor.fetchall()
for row in rows:
    cur = SHIPMENT_BO(row.SHIPMENT_NUM, row.SHIPMENT_ID, row.ORIGIN_BRANCH_CODE, row.DEST_BRANCH_CODE)
    print(cur.PrintShipment())

Here is the class I call above

class SHIPMENT_BO:

    def __init__(self, shipNo, shipId, origBranch, destBranch):
        self.__shipmentNo = shipNo
        self.__shipmentID = shipId
        self.__originBranch = origBranch
        self.__destinationBranch = destBranch


    def PrintShipment(self):
        print("Shipment No, Shipment ID, Origin Branch, Destination Branch")
        print(self.__shipmentNo + " " + str(self.__shipmentID) + " " + self.__originBranch + " " + self.__destinationBranch)

If I print the shipment number directly it prints millions of rows as expected

rows = cursor.fetchall()
for row in rows:
    print(row.SHIPMENT_NUM)
    #cur = SHIPMENT_BO(row.SHIPMENT_NUM, row.SHIPMENT_ID, row.ORIGIN_BRANCH_CODE, row.DEST_BRANCH_CODE)
    #print(cur.PrintShipment())

My first question is why does the code and class work for one row but not for multiple rows? My second question is why do I get a "None" print after the code with one row? Lastly this code does not work at all (none of the print statements execute), surely you would get at least the one row you have selected in the cursor.

rows = cursor.fetchone()
for row in rows:
    cur = SHIPMENT_BO(row.SHIPMENT_NUM, row.SHIPMENT_ID, row.ORIGIN_BRANCH_CODE, row.DEST_BRANCH_CODE)
    print(cur.PrintShipment())

Upvotes: 1

Views: 1063

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

re: fetchone() vs. fetchall()

By using ...

rows = cursor.fetchone()
for row in rows:

... it looks like you expect pyodbc's fetchone() method to return a list containing a single tuple for the one row fetched, but it doesn't. It returns a pyodbc.Row object. When you iterate over that object you are actually iterating over the column values for that row:

sql = """\
SELECT 'foo' AS col1, 42 AS col2
"""
rows = cursor.execute(sql).fetchone()
for row in rows:
    print(row)

will print

foo
42

On the other hand, fetchall() does return a list (of pyodbc.Row objects), so it behaves more like you would expect

sql = """\
SELECT 'foo' AS col1, 42 AS col2
UNION ALL
SELECT 'bar' AS col1, 123 AS col2
"""
rows = cursor.execute(sql).fetchall()
for row in rows:
    print(row)

will print

('foo', 42)
('bar', 123)

re: printing None after each row

When you do

print(cur.PrintShipment())

You invoke your PrintShipment method, which includes its own print() function calls to display the column headings and column data. Then after the PrintShipment method returns you are print-ing the return value of the PrintShipment method, which is None because it doesn't include a return statement. To avoid printing the spurious None values just call the method itself:

cur.PrintShipment()  # not wrapped in a print() function

Upvotes: 1

Related Questions