Iterating rows with Pyodbc

Question

I am using Pyodbc to return a number of rows which are dumped into a JSON and sent to a server. I would like to iterate my SQL table and return all records. I am using cursor.fetchall() now, and the program returns one record. As shown below. When I use fetchone an error is returned AttributeError: 'unicode' object has no attribute 'SRNUMBER' and fetchmany returns one record as well. How do I successfully return all records? I am using Python 2.6.7

Code:

import pyodbc
import json
import collections
import requests



    connstr = 'DRIVER={SQL Server};SERVER=server;DATABASE=ServiceRequest; UID=SA;PWD=pwd'
    conn = pyodbc.connect(connstr)
    cursor = conn.cursor()

    cursor.execute("""
                SELECT SRNUMBER, FirstName, LastName, ParentNumber
     FROM MYLA311 """)

    rows = cursor.fetchone()



    objects_list = []
    for row in rows:
         d = collections.OrderedDict()
         d['SRNUMBER']= row.SRNUMBER
         d['FirstName']= row.FirstName
         d['LastName']= row.LastName
         d['ParentNumber']= row.ParentNumber



    objects_list.append(d)

    output = {"MetaData": {},
    "SRData": d}

    print output

    j = json.dumps(output)
    print json.dumps(output, sort_keys=True, indent=4)`

Output for fetchall and fetchmany:

{
    "MetaData": {}, 
    "SRData": {
        "FirstName": "MyLAG", 
        "LastName": "ThreeEleven", 
        "ParentNumber": "021720151654176723", 
        "SRNUMBER": "1-3580171"
    }
}

score 8 · Accepted Answer · edited May 23 '17 at 12:21

Use code from my answer here to build a list of dictionaries for the value of output['SRData'], then JSON encode the output dict as normal.

import pyodbc
import json

connstr = 'DRIVER={SQL Server};SERVER=server;DATABASE=ServiceRequest; UID=SA;PWD=pwd'
conn = pyodbc.connect(connstr)
cursor = conn.cursor()

cursor.execute("""SELECT SRNUMBER, FirstName, LastName, ParentNumber FROM MYLA311""")

# build list of column names to use as dictionary keys from sql results
columns = [column[0] for column in cursor.description]

results = []
for row in cursor.fetchall():
    results.append(dict(zip(columns, row)))

output = {"MetaData": {}, "SRData": results}

print(json.dumps(output, sort_keys=True, indent=4))

for what it's worth, fetchall() will not work if there's just too many rows that can be fit in memory. — ewokx, Jul 13 '20 at 01:51

score 1 · Answer 2 · answered Jun 02 '15 at 01:29

1

For starters, the line

objects_list.append(d)

needs to be inside the for loop, not outside.

answered Jun 02 '15 at 01:29

maxymoo

35,286
11
92
119

Iterating rows with Pyodbc

2 Answers2