Get words in a [ ] in separate words

[Tbells]

I am trying to get words in a [ ] to be generated in separate words.  I have a list of tags in a [] which the python sees as an array which is no.

Examples are shown below

"tags": [

                "FileZilla",

                "London",

                "NUC"

            ],

"tags": [

                "Laptop",

                "Stockholm"

            ],

"tags": [

                "Corporate_Apps",

                "Jabber",

                "London",

                "Okta",

                "VBDevice",

                "exchage2",

                "iPhone",

                "mobile"

            ],

Above are the tags for each device in the organisation. I will the tags to come as 

Corporate_Apps,

                Jabber,

                London,

                Okta,

                VBDevice,

                exchage2,

                iPhone,

                mobile

for each tag as am trying to export it into a SQL table other columns work perfectly but just tags seem not to work while generating it from the API using python into a database. I have tried using split but that doesn't seem to work.

However am really need to Python, please be nice.

Thank you all.

[BrechtSchamp]

How are you calling the API? I ask because generally when I've seen tags be returned, they've been a single string with tags separated by a space.

In that case to turn them into an array you can simply do:

array_of_tags = your_string_containing_the_tags.split(' ')

It may help if you share some of your code (take out any sensitive information). 

[Tbells]

@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

[BrechtSchamp]

Okay, without debugging the code, does this help you:

import json
import requests

url = "h t t p s ://api.meraki.com/api/v0/networks/XXXXXXXXXXXXXXX/sm/devices"

headers = {
    'Accept': "*/*",
    'X-Cisco-Meraki-API-Key': "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx",
    'Cache-Control': "no-cache",
    'Accept-Encoding': "gzip, deflate",
    'Connection': "keep-alive",
    'cache-control': "no-cache"
    }

response = requests.request("GET", url, headers=headers)

print(response.text)

result = json.loads(response.text)
for device in result["devices"]:
    print("- DEVICE NAME:", device["name"])

    #device["tags"] is an array containing the tags of a device

    #if you just want a comma separated string of tags
    print(" * comma-separated string of tags:")
    print(','.join(device["tags"]))

    #if you want to go over all tags of a device
    print(" * iterating over the tags")
    for tag in device["tags"]:
        print(tag)
    print("---------------------")

[Tbells]

Thank you and will try this.

[Tbells]

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