Python pyodbc.row to list

Question

I am trying to create a python script to convert from SQLServer database to Mongodb. In the query from SQLServer, I am returning the data as JSON using the FOR JSON AUTO method. When I use the following code, I cannot get the right type for my var for pymongo to insert the data correctly.

client = MongoClient('localhost', 27017)
db = client.npnrd_test
collection = db.certs

sql_file = open('./sql/F1043_Tract.sql')
sql_d = sql_file.read()
sql_file.close()

cursor.execute(sql_d)
cert_data = cursor.fetchone()
cert_list = cert_data[0]
print(cert_list)
print(type(cert_list))

When I get the "type" of cert_list it is a class 'str' not class list as it should be for pymongo. If I print the data and copy and paste it directly into a new variable, it is a type class list and completes in pymongo. What am I missing?

Answer 1

I believe you need to use cert_data directly and not cert_list. Try below. Usually cursor.fetchone() retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available. Are you using pyodbc?

cert_data = cursor.fetchone()
print type(cert_data) 

Instead of using cursor.fetchone(), you could also use the cursor as an iterator

for row in cursor:
   print row
   # use row data to insert to mongodb

Updating answer as per comments. To convert pyodbc.row to list you could use simple list comprehension.

for row in cursor:
   row_to_list = [elem for elem in row]