Python Socrata.get方法代碼示例

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
    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()

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
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)}

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
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

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
    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

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
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()

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
    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()

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
def fetch_data():
	# Make a connection	
	conn = r.connect(host="localhost", port=28015, db="test")
	# You need to register the App Token (manually?)
	client = Socrata("data.sunshinecoast.qld.gov.au", "6MbT9NoWolynKM1ooRzrvm7Fs")
	# API Endpoint
	endpoint = "/resource/mn3m-fqri.json"
	off = 0
	while True:
		data = client.get(endpoint, limit=50000,offset=off)
		if len(data) == 0:
			break
		for elem in data:
			try:
				# Only store if we have a date
				cur = elem['d_date_rec']
				r.table("planning").insert(elem).run(conn)
			except KeyError:
				pass
		off = off+50000

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
def get_resource_data(socrata_resource, since=None, limit=1000):
    client = Socrata(socrata_resource.domain, socrata_resource.token)
    kwargs = {
        'limit': limit,
        'where': socrata_resource.conditions,
    }
    if socrata_resource.unique_key:
        kwargs['order'] = socrata_resource.unique_key

    while True:
        if since:
            kwargs['where'] = "{} and {} > '{}'".format(
                socrata_resource.conditions,
                socrata_resource.unique_key,
                since
            )
        batch = client.get(socrata_resource.endpoint, **kwargs)
        if len(batch) > 0:
            since = batch[-1][socrata_resource.unique_key]
            yield batch
        else:
            return

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
def run_script():
    client = Socrata("data.sfgov.org", "wvRAyq5wvCnf9YKGmiuZ7T9y3")
    fetched_data = client.get("/resource/rqzj-sfat.json", select="objectid,latitude,longitude,fooditems,expirationdate")
    data = []

    for i in fetched_data:
        a = Node(
            i.get("objectid", None),
            i.get("latitude", None),
            i.get("longitude", None),
            i.get("fooditems", None),
            i.get("expirationdate", None),
        )
        print repr(a)
        data.append(a._asdict())

    urlparse.uses_netloc.append("postgres")
    try:
        url = urlparse.urlparse(os.environ["DATABASE_URL"])
        conn = psycopg2.connect(
            database=url.path[1:], user=url.username, password=url.password, host=url.hostname, port=url.port
        )
    except:
        url = urlparse.urlparse("postgresql://localhost/mydb")
        conn = psycopg2.connect(
            database=url.path[1:], user=url.username, password=url.password, host=url.hostname, port=url.port
        )
        print "I am unable to connect to the database"
    cur = conn.cursor()
    try:
        cur.executemany(
            "insert into foodtrucks(objectid,latitude, longitude,fooditems,expirationdate) select %(objectid)s,%(latitude)s,%(longitude)s,%(fooditems)s,%(expirationdate)s where not exists (select 1 from foodtrucks where objectid=%(objectid)s)",
            data,
        )
    except Exception as e:
        print "unable to query postgres =>", e
    conn.commit()
    return render_template("successScript.html")

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
def run_script():
    client = Socrata("data.sfgov.org","wvRAyq5wvCnf9YKGmiuZ7T9y3")
    fetched_data = client.get("/resource/rqzj-sfat.json",select="objectid,latitude,longitude,fooditems,expirationdate")
    for row in fetched_data:
        print str(row)
    print "fetched_data", str(fetched_data)
    data = []

    for i in fetched_data:
        a = Node(i.get('objectid',None),i.get('latitude',None),i.get('longitude',None),i.get('fooditems',None),i.get('expirationdate',None))
        print repr(a)
        data.append(a._asdict())

    print data
    try:
        conn = psycopg2.connect("dbname='mydb'")
    except:
        print "I am unable to connect to the database"
    cur = conn.cursor()
    try:
        cur.executemany("insert into foodtrucks(objectid,latitude, longitude,fooditems,expirationdate) select %(objectid)s,%(latitude)s,%(longitude)s,%(fooditems)s,%(expirationdate)s where not exists (select 1 from foodtrucks where objectid=%(objectid)s)",data)
    except Exception as e:
        print "unable to query postgres =>", e
    conn.commit()

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
from sodapy import Socrata

# client = Socrata("sandbox.demo.socrata.com", None)
# print client.get("nimj-3ivp", limit=10)

# client = Socrata("data.cms.gov/", None)
# print client.get("97k6-zzx3", limit=10)
# https://resource/97k6-zzx3.json?$limit=5

client = Socrata("data.sfgov.org", "8gffbg1meMZ1e2Z0yOz2OpwZq")
#client.get("cuks-n6tp", limit=1)
assault = client.get("cuks-n6tp", select ="category,time,location", where ="category='ASSAULT'",limit=10)
print "assault data"
print assault
theft = client.get("cuks-n6tp", select ="category,time,location", where ="category='VEHICLE THEFT'",limit=10)
print "vehicle theft data"
print theft
vandalism = client.get("cuks-n6tp", select ="category,time,location", where ="category='VANDALISM'",limit=10)
print "VANDALISM data"
print vandalism
kidnapping = client.get("cuks-n6tp", select ="category,time,location", where ="category='KIDNAPPING'",limit=10)
print "KIDNAPPING data"
print kidnapping
sex = client.get("cuks-n6tp", select ="category,time,location", where ="category='SEX OFFENSES, FORCIBLE'",limit=10)
print "SEX OFFENSES, FORCIBLE data"
print sex
dui = client.get("cuks-n6tp", select ="category,time,location", where ="category='DRIVING UNDER THE INFLUENCE'",limit=10)
print "DUI data"
print dui
client.close()

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
time_start = time.time() #Timer start for script
#Setup APIs call and initial variables
gmaps = googlemaps.Client(key=g_api_key)
api_setup = Socrata(socrata_api, socrata_app_token, socrata_id, socrata_pw)
max_retry = 3

#Setup Dictionary of found locations and coordinates
location_dict = {}
coord_pets = Pet.objects.filter(loc_lat__isnull=False).order_by('-intake_at')
for x in coord_pets: 
    if not location_dict.has_key(x.found_location):
        location_dict[x.found_location] = {"lat": x.loc_lat, "lon": x.loc_lon}
print coord_pets.count()

#Get record count from AAC
record_count = api_setup.get(intake_endpoint, select = "count('')")
record_count_out = api_setup.get(outcome_endpoint, select = "count('')")
count = record_count[0]['count'].encode('ascii','ignore')
count_out = record_count_out[0]['count'].encode('ascii','ignore')

#Functions to Update Database
def pull_in_intakes(intakes):
    for x in intakes:
        intime = datetime.strptime(x["intake_date"], '%m/%d/%y')
        if len(Pet.objects.filter(animal_id = x["animal_id"])) == 0: #Create a new pet
            new_pet = Pet()
            if x.has_key("name"):
                new_pet.name = x["name"]
            else:
                new_pet.name = "No Name"
            new_pet.animal_id = x["animal_id"]

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
from sodapy import Socrata
import csv

client = Socrata(site, app_token, username=user, password=passw)


dset = "/resource/xb7i-cvg2.json"
filepath = "grants-trunc.csv"

client.get(dset)
rowlist = []
with open(filepath, "rb") as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        rowlist.append(row)

client.replace(dset, rowlist)

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
def call_api():
    """Request data from socrata api and get back JSON."""
    client = Socrata("data.seattle.gov", 'SOCRATA_TOKEN')
    data = client.get("ih58-ykqj", content_type="json", limit=49998)
    return data

# 需要導入模塊: from sodapy import Socrata [as 別名]
# 或者: from sodapy.Socrata import get [as 別名]
 def get(self, request, *args, **kwargs):
     ''' Get criminal records from the dataset and save them the database as CriminalRecord objects'''
     ids = CriminalRecord.objects.values_list('case_id', flat=True)
     domain = settings.SOCRATA_DOMAIN
     token = settings.SOCRATA_APP_TOKEN
     endpoint = settings.SOCRATA_DATASET_ENDPOINT
     client = Socrata(domain, token)
     order = "date"
     where = 'latitude IS NOT NULL'
     offset = 0
     limit = 1000
     status = 200
     try:
         data = client.get(
             endpoint, order=order, where=where,
             offset=offset, limit=limit)
         while data:
             for record in data:
                 if self.to_int(record.get('id')) not in ids:
                     attrs = {
                         'case_id': self.to_int(
                             self.get_from_dict(record, 'id')),
                         'case_number': self.get_from_dict(
                             record, 'case_number'),
                         'date': datetime.strptime(
                             self.get_from_dict(record, 'date'),
                             '%Y-%m-%dT%H:%M:%S'),
                         'block': self.get_from_dict(record, 'block'),
                         'iucr': self.get_from_dict(record, 'iucr'),
                         'primary_type': self.get_from_dict(
                             record, 'primary_type'),
                         'crime_description': self.get_from_dict(
                             record, 'description'),
                         'location_description': self.get_from_dict(
                             record, 'location_description'),
                         'has_arrested': self.get_from_dict(
                             record, 'arrest'),
                         'is_domestic': self.get_from_dict(
                             record, 'domestic'),
                         'beat': self.get_from_dict(record, 'beat'),
                         'district': self.get_from_dict(
                             record, 'district'),
                         'ward': self.to_int(
                             self.get_from_dict(record, 'ward')),
                         'community_area': self.get_from_dict(
                             record, 'community_area'),
                         'fbi_code': self.get_from_dict(
                             record, 'fbi_code'),
                         'x_coordinate': self.to_int(
                             self.get_from_dict(
                                 record, 'x_coordinate')),
                         'y_coordinate': self.to_int(
                             self.get_from_dict(
                                 record, 'y_coordinate')),
                         'year': self.to_int(
                             self.get_from_dict(record, 'year')),
                         'updated_on': datetime.strptime(
                             self.get_from_dict(record, 'updated_on'),
                             '%Y-%m-%dT%H:%M:%S'),
                         'latitude': float(self.get_from_dict(
                             record, 'latitude')),
                         'longitude': float(self.get_from_dict(
                             record, 'longitude')),
                         'location': Point(
                             float(self.get_from_dict(
                                 record, 'longitude')),
                             float(self.get_from_dict(
                                 record, 'latitude')))
                     }
                     CriminalRecord.objects.create(**attrs)
             offset += limit
             data = client.get(
                 endpoint, order=order, where=where,
                 offset=offset, limit=limit)
         client.close()
     except Exception, e:
         print e
         status = 400