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)
