def save_items(self, items): if self.settings['SOCRATA_USERNAME'] and self.settings[ 'SOCRATA_PASSWORD']: self.logger.info("Updating socrata with %s rows" % len(items)) client = Socrata("data.code4sa.org", None, self.settings['SOCRATA_USERNAME'], self.settings['SOCRATA_PASSWORD']) client.replace("n6i8-b8c5", items)
def CreateETL(view_data, title, description, category, tags): #preparing data cols = list(view_data) columns = [{ "fieldName": cols[0].lower(), "name": cols[0], "dataTypeName": "text" }] for i in range(1, len(cols)): x = { "fieldName": cols[i].lower(), "name": cols[i], "dataTypeName": "text" } columns.append(x) tags = tags.split(",") #Uploadin data try: client = Socrata(cfg["web"], cfg["token"], username=cfg["email"], password=cfg["password"]) print(tags) print(category) print(description) print(columns) print(cols) NewDataSet = client.create(title, description=description, columns=columns, tags=tags, category=category) client.publish(NewDataSet.get('id')) client.set_permission(NewDataSet.get('id'), "private") # Convertion to JSON datajson = view_data.to_json(None, orient='records') # JSON to list datajson = json.loads(datajson) client.replace(NewDataSet.get('id'), datajson) print('Socrata done') error = 'OK' dataset_id = NewDataSet.get('id') client.close() except BaseException as e: #if there is an error, reload login with error message error = str(e) print('Error description:') print(error) dataset_id = 'NoData' return error, dataset_id
def CreateMetadata(filename, sheetname, FirstCol, LastCol, tags, title): #Create Socrata client client = Socrata(cfg["web"], cfg["token"], username=cfg["email"], password=cfg["password"]) #preparing Excel file parse_cols = '%s:%s' % (FirstCol, LastCol) #getting path of the excel file path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'documents/%s' % filename.replace(" ", "_")) #Reading excel with Panda ExcelFile = pd.read_excel(path, sheetname=sheetname, parse_cols=parse_cols, index=False) #Defining columns automatically cols = list(ExcelFile) columns = [{ "fieldName": cols[0].lower(), "name": cols[0], "dataTypeName": "text" }] for i in range(1, len(cols)): x = { "fieldName": cols[i].lower(), "name": cols[i], "dataTypeName": "text" } columns.append(x) #create dataset NewDataSet = client.create( title, description="Lista de antenas Wi-fi en el atlántico", columns=columns, tags=tags, category="Ciencia, Tecnología e Innovación") ## Publish dataset NewDataSet.get('id') get the dataset ID client.publish(NewDataSet.get('id')) client.set_permission(NewDataSet.get('id'), "public") # Reemplazar datos # Conversion a JSON datajson = ExcelFile.to_json(None, orient='records') # Conversion a list datajson = json.loads(datajson) client.replace(NewDataSet.get('id'), datajson) print('Socrata done') client.close()
def test_replace(): 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 = "replace_songs.txt" data = [ { "theme": "Surfing", "artist": "Wavves", "title": "King of the Beach", "year": "2010" }, { "theme": "History", "artist": "Best Friends Forever", "title": "Abe Lincoln", "year": "2008" }, ] setup_mock(adapter, "PUT", response_data, 200) response = client.replace(DATASET_IDENTIFIER, data) assert isinstance(response, dict) assert response.get("Rows Created") == 2 client.close()
def upload_to_socrata(**kwargs): from sodapy import Socrata # make a sodapy Socrata client s = Socrata("data.detroitmi.gov", Variable.get("SOCRATA_TOKEN"), Variable.get("SOCRATA_USER"), Variable.get("SOCRATA_PASS")) # get the SQLAlchemy engine hook = PostgresHook('etl_postgres') eng = hook.get_sqlalchemy_engine() # get the payload result = eng.execute(f"select * from {kwargs['table']}") payload = [dict(row) for row in result] if kwargs['method'] == 'replace': job = s.replace(kwargs['id'], payload) else: chunk_size = 10000 for i in range(0, len(payload), chunk_size): try: r = s.upsert(kwargs['id'], payload[i:i + chunk_size]) except: print(f"Error on record {i}") r = s.upsert(kwargs['id'], payload[i:i + chunk_size])
def UpdateDataset(view_data, dataset_id): try: client = Socrata(cfg["web"], cfg["token"], username=cfg["email"], password=cfg["password"]) # Convertion to JSON datajson = view_data.to_json(None, orient='records') # JSON to list datajson = json.loads(datajson) client.replace(dataset_id, datajson) #print('Socrata done') error = 'OK' client.close() print('Socrata Done') except BaseException as e: #if there is an error, reload login with error message error = str(e) print('Error description:') print(error) return error
def test_replace(): 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 = "replace_songs.txt" data = [ {"theme": "Surfing", "artist": "Wavves", "title": "King of the Beach", "year": "2010"}, {"theme": "History", "artist": "Best Friends Forever", "title": "Abe Lincoln", "year": "2008"}, ] setup_mock(adapter, "PUT", response_data, 200) response = client.replace(DATASET_IDENTIFIER, data) assert isinstance(response, dict) assert response.get("Rows Created") == 2 client.close()
"person_id": "P", "primaryperson_id": "PP", }, }, "dataset_uid": ATD_ETL_CONFIG["SOCRATA_DATASET_PERSONS"] }, ] # Start timer start = time.time() # For each config, get records from Hasura and upsert to Socrata until res is [] for config in query_configs: print(f'Starting {config["table"]} table...') print(f'Truncating {config["table"]} table...') client.replace(config["dataset_uid"], []) records = None offset = 0 limit = 1000 total_records = 0 # Query records from Hasura and upsert to Socrata while records != []: # Create query, increment offset, and query DB query = config["template"].substitute( limit=limit, offset=offset, date_limit=get_date_limit(), initial_date_limit=get_initial_date_limit()) offset += limit data = run_hasura_query(query)
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)