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?
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]