import JSON   import pandas as PD   import sqlalchemy   import pyodbc   #file = open('rhapitest.json')   import requests   url = "https://n142.meraki.com/########”   querystring = {"fields":["phoneNumber","location","lastConnected"]}   headers = {       'x-cisco-meraki-api-key': "#####",     'content-type': "application/json",     'accept': "application/json",     'cache-control': "no-cache",     'postman-token': "#######"     }   response = requests.request("GET", url, headers=headers, params=querystring) data = json.loads(response.text) #print(data)   id,name,strTags,osName,systemModel,serialNumber,lastConnected,location = [],[],[],[],[],[],[],[] #x = 0 for device in data['devices']:     #  x = x+1    # print (x)     id.append(device[u'id'])       name.append(device[u'name'])      # tags.append(device[u'tags'])       #print (device[u'name'])         #print (device[u'tags'])     temp1=(device[u'tags'])     if temp1:      temp2 = ','.join(temp1)      strTags.append(temp2)       #strTags.append=(device[u"tags"])       if not temp1:      strTags.append("")         #  (device[u'tags'])       #  tags = (device[u'tags'])     #print(tags[x])     #    print (2)     #print (temp1)     #    tags.append(device[u'tags'])     # print (tags)        #  try:    #    temp1=device[u'tags']     #except:      #   tags.append("No tag available")     #else:       #  tags.append(device[u'tags'])        osName.append(device[u'osName'])       systemModel.append(device[u'systemModel'])       serialNumber.append(device[u'serialNumber'])       lastConnected.append(device[u'lastConnected'])       #location.append(device['location'])     try:         temp=device[u'location']     except:         location.append("No location")     else:         location.append(device[u'location'])       #print(temp)     #if (device[u'location']) in data:      #   print(device[u'location'])   df = pd.DataFrame([id,name,strTags,osName,systemModel,serialNumber,lastConnected,location]).T   #df = df.apply(lamba location: location.str.replace(',',' ',)) #df = df.replace(',',' ', regex=True) #df['location'] = df['location'].replace('[',' ', regex=True) #df['location'] = df['location'].replace(']',' ', regex=True)   df.columns = ['id','name','tags','osName','systemModel','serialNumber','lastConnected','location']   #print(data)   #df.to_csv('output.csv', #sep=',', encoding = #'utf-8', index=False)   connStr = pyodbc.connect(    "DRIVER={SQL Server};"    "SERVER=####;"    "Database=#####;"    #"Trusted_Connection=yes;"    "UID=#####;"    "PWD={#######}"    ) cursor = connStr.cursor()   for index,row in df.iterrows():   cursor.execute("INSERT INTO dbo.Meraki_Staging_API([MID],[name],[tags],[osName],[systemModel],[serialNumber],[lastConnected],[location]) values (?,?,?,?,?,?,?,?)", row['id'],row['name'],str(strTags),row['osName'],row['systemModel'],row['serialNumber'],row['lastConnected'],row['location'])   connStr.commit() cursor.close() connStr.close()   Everything works apart from the fact that the tags column data for each column shows as     "tags": [     "NUC",     "Sydney"     ],     "tags": [     "FileZilla",     "London",     "NUC"     ],     However, I will like it to show     MID   name     tags       ssid        wifiMac osName               systemModel      uuid       serialNumber               hasChromeMdm                   642    B050949              ['NUC,Sydney']   NULL     NULL     Windows 8.1 Enterprise     644    NYCDC01             ['FileZilla,London,NUC']  NULL     NULL     Windows Server 2012 R2 (64-      I want the program to work in a way whereby all the tags for each MID are not in one column but tags     each MID should be shown at each column instead.  Your help will be really helpful as I am new to writing python and API.  The issues am having now is that all the tags are showing on each column instead of showing the tags linked to each MID in the SQL table   I hope this more clear.   Thank you so much for the help ... View more

Thank you and will try this. ... View more

@BrechtSchamp Thanks for your response, really appreciate   import json import pandas as pd import sqlalchemy import pyodbc #file = open('rhapitest.json') import requests url = "###########/" querystring = {"fields":["phoneNumber","location","lastConnected"]} headers = { 'x-cisco-meraki-api-key': "##########", 'content-type': "application/json", 'accept': "application/json", 'cache-control': "no-cache", 'postman-token': "###########" } response = requests.request("GET", url, headers=headers, params=querystring) data = json.loads(response.text) #print(data) id,name,tags,osName,systemModel,serialNumber,lastConnected,location = [],[],[],[],[],[],[],[] #x = 0 for device in data['devices']: # x = x+1 # print (x) id.append(device[u'id']) name.append(device[u'name']) temp1=(device[u'tags']) if not temp1: tags.append("No tags available") else: ', '.join(temp1) print(temp1) # try: # mylist = (device[u'tags']) # except: # tags.append("No tag available") # else: # str(mylist).strip('[]') osName.append(device[u'osName']) systemModel.append(device[u'systemModel']) serialNumber.append(device[u'serialNumber']) lastConnected.append(device[u'lastConnected']) #location.append(device['location']) try: temp=device[u'location'] except: location.append("No location") else: location.append(device[u'location']) #print(temp) #if (device[u'location']) in data: # print(device[u'location']) df = pd.DataFrame([id,name,tags,osName,systemModel,serialNumber,lastConnected,location]).T #df = df.apply(lamba location: location.str.replace(',',' ',)) #df = df.replace(',',' ', regex=True) #df['location'] = df['location'].replace('[',' ', regex=True) #df['location'] = df['location'].replace(']',' ', regex=True) df.columns = ['id','name','tags','osName','systemModel','serialNumber','lastConnected','location'] #print(data) #df.to_csv('output.csv', #sep=',', encoding = #'utf-8', index=False) connStr = pyodbc.connect( "DRIVER={SQL Server};" "SERVER=#######;" "Database=#####;" #"Trusted_Connection=yes;" "UID=ciphrconnector;" "PWD=#######" ) cursor = connStr.cursor() for index,row in df.iterrows(): cursor.execute("INSERT INTO dbo.Meraki_Staging_API([MID],[name],[tags],[osName],[systemModel],[serialNumber],[lastConnected],[location]) values (?,?,?,?,?,?,?,?)", row['id'],row['name'],row['tags'],row['osName'],row['systemModel'],row['serialNumber'],row['lastConnected'],row['location']) connStr.commit() cursor.close() connStr.close()     That is an example of my code. the column with the issue is tags, at the moment the data in the tags columns shows like this   "tags": [                 "Corporate_Apps",                 "Jabber",                 "London",                 "Okta",                 "VBDevice",                 "exchage2",                 "iPhone",                 "mobile"             ],   "tags": [                 "Laptop",                 "OwnCloud",                 "Shanghai"             ],   So am guessing is not going into the database because of the [] and "" around the list of words in it. At the moment the if statement works but only pulls in the No tags available into the DB tables but not other tags.     I hope this helps   Thanks ... View more