Python sodapy.Socrata类代码示例

def test_set_permission():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, username=USERNAME, password=PASSWORD,
                     session_adapter=mock_adapter)

    response_data = "empty.txt"
    setup_old_api_mock(adapter, "PUT", response_data, 200)

    # Test response
    response = client.set_permission(DATASET_IDENTIFIER, "public")
    assert response.status_code == 200

    # Test request
    request = adapter.request_history[0]
    query_string = request.url.split("?")[-1]
    params = query_string.split("&")

    assert len(params) == 2
    assert "method=setPermission" in params
    assert "value=public.read" in params

    client.close()

def test_create():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, username=USERNAME, password=PASSWORD,
                     session_adapter=mock_adapter)

    response_data = "create_foobar.txt"
    setup_mock(adapter, "POST", response_data, 200, dataset_identifier=None)

    columns = [
        {"fieldName": "foo", "name": "Foo", "dataTypeName": "text"},
        {"fieldName": "bar", "name": "Bar", "dataTypeName": "number"}
    ]
    tags = ["foo", "bar"]
    response = client.create("Foo Bar", description="test dataset",
        columns=columns, tags=tags, row_identifier="bar")

    request = adapter.request_history[0]
    request_payload = json.loads(request.text) # can't figure out how to use .json

    # Test request payload
    for dataset_key in ["name", "description", "columns", "tags"]:
        assert dataset_key in request_payload

    for column_key in ["fieldName", "name", "dataTypeName"]:
        assert column_key in request_payload["columns"][0]

    # Test response
    assert isinstance(response, dict)
    assert len(response.get("id")) == 9
    client.close()

    def getData(self):
        # Get crime records via API
        if self.storage == "remote":
            print("Getting crime data from remote source.\nThis takes a while (approx. 5 mins)! Please be patient.")

            # API request information
            client_crime = Socrata('data.lacity.org','7pTgt6f2oTY53aDI1jXNJoNZD')
            offset_temp = 0
            conn = sq.connect("ReferralCrimeMap.db")
            cur = conn.cursor()
            cur.execute("DROP TABLE IF EXISTS 'Crime2016'")

            # Getting data in dataframe then manipulate before storing in ReferralCrimeMap.db
            while True:
                results = client_crime.get("7fvc-faax", limit=10000, offset=offset_temp)
                crime_df_temp = pd.DataFrame.from_records(results)
                # This loop stops when the next block of dataframe is empty
                if crime_df_temp.empty == True:
                    break

                # Split location_1 into lat and long
                # Create 'year_rptd' to filter cases reported in 2016
                # Create 'count' for later data analysis
                crime_df_temp['location_1'] = crime_df_temp['location_1'].astype('str')
                crime_df_temp['long'] = crime_df_temp['location_1'].map(lambda x: x.split(']')[0].split('[')[-1].split(',')[0])
                crime_df_temp['lat'] = crime_df_temp['location_1'].map(lambda x: x.split(']')[0].split('[')[-1].split(',')[-1])
                crime_df_temp['year_rptd'] = crime_df_temp['date_rptd'].map(lambda x: x.split('-')[0])
                crime_df_temp['month_rptd'] = crime_df_temp['date_rptd'].map(lambda x: x.split('-')[1])
                crime_df_temp['count'] = 1
                crime_df_temp = crime_df_temp[crime_df_temp['year_rptd']=='2016']

                # Insert dataframe into ReferralCrimeMap.db
                pd_sql.to_sql(crime_df_temp, 'Crime2016', conn, if_exists='append', index=False)
                offset_temp+=10000

                # Shows the percentage of data 
                if offset_temp % 100000 == 0:
                    print(offset_temp/2000000*100,"%")
                else:
                    continue
            cur.execute("SELECT * FROM Crime2016")
            print(cur.fetchone())
            conn.close()

        # Load local data if -source is set to local
        else:
            print("Getting crime data from local source.")
            conn = sq.connect("ReferralCrimeMap.db")
            cur = conn.cursor()
            query = "SELECT * FROM Crime"
            try:
                crime = pd.read_sql(query, conn)
                conn.close()
                print(crime.head())
            except Exception as e:
                print("There is an error:", e)
                print("Please set data course as remote.")
                exit()

def gen_data(filepath, api_key, username=None, password=None, output='json'):
        api = Socrata('data.seattle.gov', api_key, username=username, password=password)

        with open(filepath) as fp:
            uid = set([i.strip() for i in fp.readlines()]) | set([i.strip() for i in open('completed.json')])

        for dataset in uid:
            print(dataset, file=open('completed.json', 'a'))
            yield {dataset: api.get('/resource/' + dataset + '.' + output)}

def createBarChart(charttype):
    with open("database_charts/chartconfig.json") as config:
        jsonData = json.load(config)
    config.close()
    chart = jsonData[charttype]

    chartTitle = chart['chart-title']
    dataTitle = chart['data-title']
    yFormat = chart['y-axis-format']

    xaxis = chart['x-axis']
    items = chart['y-axis']
    colors = chart['color']

    with open("database_charts/url_info.json") as urlConfig:
        urlInfo = json.load(urlConfig)
    client = Socrata(urlInfo['url'], None, username=urlInfo['username'], password=urlInfo['password'])

    colorIndex = 0
    data = []

    for item in items:
        dict = {
            "type": chart["chart-type"],
            "legendText": item.replace("_", " "),
            "cursor": "pointer" if len(items) > 1 else "default",
            "showInLegend": True,
            'legendMarkerColor': colors[colorIndex],
            "toolTipContent": item.replace("_", " ") + " in year " + "{label}: {y}"
        }

        dataPoints = []
        request = client.get(dataTitle, select=xaxis + ", " + item)
        for r in request:
            if len(r) > 1:
                d = {
                    "label": int(r[xaxis]),
                    "y": int(r[item]),
                    "color": colors[colorIndex],
                }
            if d['label'] is not None:
                dataPoints.append(d)
        colorIndex += 1

        dict["dataPoints"] = dataPoints
        data.append(dict)

        # Create Chart Information
        chartInfo = {}
        chartInfo["chartTitle"] = chartTitle
        chartInfo["data"] = data
        chartInfo["json"] = json.dumps(data)
        chartInfo["valueFormat"] = yFormat
        chartInfo['addClick'] = False if len(items) == 1 else True
    return chartInfo

    def createPieChart(self):
        with open("database_charts/chartconfig.json") as config:
            data = json.load(config)

        chartconfig = {}
        for key, value in data.items():
            if key == self.chartType:
                chartconfig = value

        # The position index from the excel file
        xaxis = chartconfig['x-axis']
        # An array of dictionary with Name of the category and index in excel file
        yaxis = chartconfig['y-axis']

        charttitle = chartconfig['chart-title']
        datatitle = chartconfig['data-title']
        colors = chartconfig['color']
        colorIndex = 0

        with open("database_charts/url_info.json") as urlConfig:
            urlInfo = json.load(urlConfig)
        client = Socrata(urlInfo['url'], None, username=urlInfo['username'], password=urlInfo['password'])

        request = client.get(datatitle, select=",".join(yaxis), where=xaxis + "=" + str(self.year))[0]
        newData = []
        dic = {
            "type": "pie",
            "showInLegend": True,
            "toolTipContent": "{y} - #percent %",
            "yValueFormatString": "#0.#,,. Million",
            "legendText": "{indexLabel}",
        }
        dataPoints = {}
        for cat in yaxis:
            if cat in request.keys() and int(request[cat]) > 0:
                if cat not in dataPoints.keys():
                    dataPoints[cat] = {'y': int(request[cat]), 'indexLabel': cat.replace("_", " "),
                                       'legendMarkerColor': colors[colorIndex], 'color': colors[colorIndex]}
                else:
                    dataPoints[cat]['y'] += int(request[cat])
            colorIndex += 1

        for item in dataPoints:
            newData.append(dataPoints[item])

        dic['dataPoints'] = newData
        pieChartInfo = {}
        pieChartInfo['title'] = charttitle + str(self.year)
        pieChartInfo['data'] = dic
        pieChartInfo['json'] = json.dumps(dic)
        return pieChartInfo

def test_publish():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, username=USERNAME, password=PASSWORD,
                     session_adapter=mock_adapter)

    response_data = "create_foobar.txt"
    setup_publish_mock(adapter, "POST", response_data, 200)

    response = client.publish(DATASET_IDENTIFIER)
    assert isinstance(response, dict)
    assert len(response.get("id")) == 9
    client.close()

def test_get():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, session_adapter=mock_adapter)

    response_data = "get_songs.txt"
    setup_mock(adapter, "GET", response_data, 200)
    response = client.get(DATASET_IDENTIFIER)

    assert isinstance(response, list)
    assert len(response) == 10

    client.close()

def test_get_metadata():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, session_adapter=mock_adapter)

    response_data = "get_song_metadata.txt"
    setup_old_api_mock(adapter, "GET", response_data, 200)
    response = client.get_metadata(DATASET_IDENTIFIER)

    assert isinstance(response, dict)
    assert "newBackend" in response
    assert "attachments" in response["metadata"]

    client.close()

def test_publish():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, username=USERNAME, password=PASSWORD,
                     session_adapter=mock_adapter)
    
    response_data = "create_foobar.txt"
    resource = "/api/views/songs/publication.json" # publish() removes .json
    set_up_mock(adapter, "POST", response_data, 200, resource=resource)
    
    response = client.publish("/resource/songs.json") # hard-coded so request uri is matched
    assert isinstance(response, dict)
    assert len(response.get("id")) == 9
    client.close()

    def getData(self):
        if self.storage == "remote":
            print("Getting DCFS referral data from remote source.")

            # API request
            client_dcfs = Socrata('data.lacounty.gov','7pTgt6f2oTY53aDI1jXNJoNZD')
            offset_temp = 0
            dcfs_df=pd.DataFrame()

            conn = sq.connect("ReferralCrimeMap.db")
            cur = conn.cursor()
            cur.execute('DROP TABLE IF EXISTS dcfs')

            print('Inserting DCFS referral dataframe into ReferralCrimeMap.db.')
            while True:
                results = client_dcfs.get("8vmx-hhtu", limit=5000, offset=offset_temp)
                dcfs_df = pd.DataFrame.from_records(results)
                # Break the loop and stop requesting if the block is empty 
                if dcfs_df.empty == True:
                    break
                # Insert dataframe into ReferralCrimeMap.db
                dcfs_df['location'] = dcfs_df['location'].astype('str')
                pd_sql.to_sql(dcfs_df, 'dcfs', conn, if_exists='append', index=False)
                
                offset_temp+=5000
                # I didn't use time.sleep as this API is unlimited
                # time.sleep(1)
            conn.close()

        else:
            print ("Getting DCFS referral data from local source.")
            conn = sq.connect("ReferralCrimeMap.db")
            cur = conn.cursor()
            query = '''
                    SELECT *
                    FROM dcfs
                    '''
            try:
                dcfs = pd.read_sql(query, conn)
                conn.close()
                print(dcfs.head())
            # If the table does not exist it will throw an error.
            except Exception as e:
                print('There is an error:', e)
                print('Please enter remote source.')
                exit()

def test_upsert_exception():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, session_adapter=mock_adapter)

    path = "/songs.json"
    response_data = "403_response_json.txt"
    set_up_mock(adapter, "POST", response_data, 403, reason="Forbidden")

    data = [{"theme": "Surfing", "artist": "Wavves",
             "title": "King of the Beach", "year": "2010"}]
    try:
        response = client.upsert(PATH, data)
    except Exception, e:
        assert isinstance(e, requests.exceptions.HTTPError)

def test_delete():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, username=USERNAME, password=PASSWORD,
                     session_adapter=mock_adapter)

    uri = "{0}{1}{2}".format(PREFIX, DOMAIN, PATH)
    adapter.register_uri("DELETE", uri, status_code=200)
    response = client.delete(PATH)
    assert response.status_code == 200

    try:
        client.delete("/foobar.json")
    except Exception, e:
        assert isinstance(e, requests_mock.exceptions.NoMockAddress)

def test_upsert():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, username=USERNAME, password=PASSWORD,
                     session_adapter=mock_adapter)

    response_data = "upsert_songs.txt"
    data = [{"theme": "Surfing", "artist": "Wavves",
             "title": "King of the Beach", "year": "2010"}]
    setup_mock(adapter, "POST", response_data, 200)
    response = client.upsert(DATASET_IDENTIFIER, data)

    assert isinstance(response, dict)
    assert response.get("Rows Created") == 1
    client.close()

def test_upsert_exception():
    mock_adapter = {}
    mock_adapter["prefix"] = PREFIX
    adapter = requests_mock.Adapter()
    mock_adapter["adapter"] = adapter
    client = Socrata(DOMAIN, APPTOKEN, session_adapter=mock_adapter)

    response_data = "403_response_json.txt"
    setup_mock(adapter, "POST", response_data, 403, reason="Forbidden")

    data = [{"theme": "Surfing", "artist": "Wavves",
             "title": "King of the Beach", "year": "2010"}]
    try:
        client.upsert(DATASET_IDENTIFIER, data)
    except Exception as e:
        assert isinstance(e, requests.exceptions.HTTPError)
    else:
        raise AssertionError("No exception raised for bad request.")