def run(self, argv): if len(argv) < 3: self.usage(argv) config_uri = argv[1] fromDate = argv[2] options = parse_vars(argv[3:]) setup_logging(config_uri) settings = get_appsettings(config_uri, options=options) engine = engine_from_config(settings, 'sqlalchemy.') DBSession.configure(bind=engine) Base.metadata.create_all(engine) with transaction.manager: url = 'https://servicesenligne2.ville.montreal.qc.ca/api/infoneige/InfoneigeWebService?wsdl' client = Client(url) planification_request = client.factory.create('getPlanificationsForDate') planification_request.fromDate = fromDate planification_request.tokenString = 'ug33-b81ab488-c335-4021-9c52-26d6b8523301-e7aa002b-0d9d-4b5c-81ef-b012979cdafb-dab06588-1962-4b16-9942-a18054094f60-a4186179-d555-4fed-b35f-ec0c74da97a3-aa3b3766-4d26-42f0-888a-a6569a1dd745' response = client.service.GetPlanificationsForDate(planification_request) if response['responseStatus'] == 0: log.info('%s plannings returned', response['planifications']['count']) cartodb_client = CartoDBOAuth(settings['cartodb.key'], settings['cartodb.secret'], settings['cartodb.user'], settings['cartodb.password'], settings['cartodb.domain']) for result in response['planifications']['planification']: ''' street_side_status = StreetSideHistory( municipality_id = result['munid'], street_side_id = result['coteRueId'], state = result['etatDeneig'], observed_on = result['dateMaj'], ) DBSession.merge(street_side_status) ''' if any(val in result for val in ['dateDebutPlanif', 'dateFinPlanif', 'dateDebutReplanif', 'dateFinReplanif']): try: result['dateDebutReplanif'] except AttributeError: result['dateDebutReplanif'] = None try: result['dateFinReplanif'] except AttributeError: result['dateFinReplanif'] = None ''' print result planning = Planning( municipality_id = result['munid'], street_side_id = result['coteRueId'], planned_start_date = result['dateDebutPlanif'], planned_end_date = result['dateFinPlanif'], replanned_start_date = result['dateDebutReplanif'], replanned_end_date = result['dateFinReplanif'], modified_on = result['dateMaj'], ) DBSession.merge(planning) ''' #transaction.manager.commit() cartodb_client.sql('UPDATE cote SET etat = %(etat)s WHERE cote_rue_id = %(cote_rue_id)d' % {"etat": result['etatDeneig'], "cote_rue_id": result['coteRueId']}) else: log.info('Status %s: %s', response['responseStatus'], response['responseDesc'])
from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) with open('BasicInfo_2014_05_30_combinedaddress_geocoded_combinedgeo.csv', 'rb') as input_file: info = csv.reader(input_file) header = info.next() for row in info: try: rownew = [] for cell in row: cell = re.sub(r'\'', '"', str(cell)) rownew.append(cell) writerow = '(' + str(rownew).strip('[]') + ')' print cl.sql('INSERT INTO nycdaycaremap values' + writerow) except CartoDBException as e: print("some error ocurred", e)
import scraperwiki from cartodb import CartoDBOAuth, CartoDBException #import json user = '******' password = '******' CONSUMER_KEY = 'aCUzZCTPpB6JChlkOvObHVZSgE1JcK3vcWAsGHXU' CONSUMER_SECRET = 'yXfiMNeUlhJEaKnvTnF5z5GVVVmZ9NNLWFkM9FEv' cartodb_domain = 'newsbeastlabs' cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) import scraperwiki from cartodb import CartoDBOAuth, CartoDBException #import json user = '******' password = '******' CONSUMER_KEY = 'aCUzZCTPpB6JChlkOvObHVZSgE1JcK3vcWAsGHXU' CONSUMER_SECRET = 'yXfiMNeUlhJEaKnvTnF5z5GVVVmZ9NNLWFkM9FEv' cartodb_domain = 'newsbeastlabs' cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain)
from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) with open('BasicInfo_2014_05_30_combinedaddress_geocoded_combinedgeo.csv', 'rb') as input_file: info = csv.reader(input_file) header = info.next() for row in info: try: rownew = [] for cell in row: cell = re.sub(r'\'','"',str(cell)) rownew.append(cell) writerow = '('+ str(rownew).strip('[]') + ')' print cl.sql('INSERT INTO nycdaycaremap values' + writerow) except CartoDBException as e: print ("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re import time cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) with open('BasicInfo_2014_06_18_combinedaddress_geocoded_combinedgeo.csv', 'rb') as input_file: info = csv.reader(input_file) header = info.next() headernew = [] for cell in header: cell = re.sub(' ','_',str(cell)) headernew.append(cell) column_names = '('+ str(headernew).strip('[]') + ')' column = re.sub('\'','',column_names) info_server = cl.sql('select ' + column + ' from basicinfo') output_file = open('Serverdata.csv', "wb") writer = csv.writer(output_file) writer.writerow(header) for row in info_server["rows"]: rowwrite = [] rowvalues = row.values() rowwrite = str(rowvalues[0]).strip('()').rsplit(',') writer.writerow(rowwrite) output_file.close()
from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: print cl.sql("select * from basicinfo where site_id='DC20687'") except CartoDBException as e: print ("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: print cl.sql("delete from basicinfo_test") except CartoDBException as e: print ("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: print cl.sql("select * from basicinfo where site_id='DC20687'") except CartoDBException as e: print("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * import json cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: a = cl.sql("SELECT * FROM basicinfo WHERE site_id='DC20687'") # WHERE site_id='DC20687' print(a) #for key, value in a.items(): #print(key, value) except CartoDBException as e: print ("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: print cl.sql('select * from nycdaycaremap') except CartoDBException as e: print ("some error ocurred", e)
#------------------------------------------------------------------------------- # Created: 01/07/2014 # Copyright: (c) Pedro 2014 # Licence: <your licence> #------------------------------------------------------------------------------- #from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) fname = 'BasicInfo_2014_06_09.csv' meas = "site_id, center_name, permit_holder, address, borough, zip_code, phone, permit_number, permit_expiration_date, permit_status, age_range, maximum_capacity, certified_to_administer_medication, site_type" with open(fname) as csvfile: bdat = csv.reader(csvfile) to_db = [(rw[0], rw[1], rw[2], rw[3], rw[4], rw[5], rw[6], rw[7], rw[8], rw[9], rw[10], rw[11], rw[12], rw[13]) for rw in bdat] del(to_db[0]) # remove first line of headers to_db = to_db[0:15] # Format as string for insert statement data = ','.join(str(en) for en in to_db) data = re.sub(r'\'','"',str(data)) data = re.sub(r'""','"', str(data)) data = re.sub(r'" ','', str(data))
from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: print cl.sql("delete from basicinfo WHERE site_id='DC20687'") except CartoDBException as e: print ("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) try: print cl.sql("delete from basicinfo WHERE site_id='DC20687'") except CartoDBException as e: print("some error ocurred", e)
from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re import time cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) with open('BasicInfo_2014_05_30_combinedaddress_geocoded_combinedgeo.csv', 'rb') as input_file: info = csv.reader(input_file) header = info.next() headernew = [] for cell in header: cell = re.sub(' ', '_', str(cell)) headernew.append(cell) column_names = '(' + str(headernew).strip('[]') + ')' column = re.sub('\'', '', column_names) info_server = cl.sql('select ' + column + ' from basicinfo') output_file = open('Serverdata.csv', "wb") writer = csv.writer(output_file) writer.writerow(header) for row in info_server["rows"]: rowwrite = [] rowvalues = row.values() rowwrite = str(rowvalues[0]).strip('()').rsplit(',') writer.writerow(rowwrite) output_file.close()
from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re import time cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) with open("BasicInfo_2014_05_30_combinedaddress_geocoded_combinedgeo.csv", "rb") as input_file: info = csv.reader(input_file) header = info.next() headernew = [] for cell in header: cell = re.sub(" ", "_", str(cell)) headernew.append(cell) column_names = "(" + str(headernew).strip("[]") + ")" column = re.sub("'", "", column_names) info_server = cl.sql("select " + column + " from basicinfo") output_file = open("Serverdata.csv", "wb") writer = csv.writer(output_file) writer.writerow(header) for row in info_server["rows"]: rowwrite = [] rowvalues = row.values() rowwrite = str(rowvalues[0]).strip("()").rsplit(",") writer.writerow(rowwrite) output_file.close() for i, row in enumerate(info): try:
from cartodb import CartoDBOAuth, CartoDBException from secret import * import csv import re import time cl = CartoDBOAuth(CONSUMER_KEY, CONSUMER_SECRET, user, password, cartodb_domain) with open('BasicInfo_2014_06_18_combinedaddress_geocoded_combinedgeo.csv', 'rb') as input_file: info = csv.reader(input_file) header = info.next() headernew = [] for cell in header: cell = re.sub(' ', '_', str(cell)) headernew.append(cell) column_names = '(' + str(headernew).strip('[]') + ')' column = re.sub('\'', '', column_names) info_server = cl.sql('select ' + column + ' from basicinfo') output_file = open('Serverdata.csv', "wb") writer = csv.writer(output_file) writer.writerow(header) for row in info_server["rows"]: rowwrite = [] rowvalues = row.values() rowwrite = str(rowvalues[0]).strip('()').rsplit(',') writer.writerow(rowwrite) output_file.close()