def createTables(instance='config/dev.json', verbose=True): ''' Creates tables in a PostgreSQL instance. ''' # # Loading database information # from config file. # database = loadJSONFile(instance)['database'] # # TODO: add environment variables # to these default values. # conn = psycopg2.connect( host=HOST_DATABASE, dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD ) cur = conn.cursor() # # Build each table. # for table in database: # # Construct SQL statement. # table_sql = "" for column in table['columns']: s = '%s %s, ' % (column['field_name'], column['type']) table_sql += s statement = 'CREATE TABLE IF NOT EXISTS "%s" (%sPRIMARY KEY (%s))' % (table['name'], table_sql, ", ".join(table['primary_key'])) # # Make statements to the database. # try: cur.execute(statement) conn.commit() print("%s table `%s` created." % (item('bullet'), str(table['name']))) except Exception as e: print('%s Table `%s` could not be created.' % (item('error'), table['name'])) if verbose: print(e) return False # # Close communication. # cur.close() conn.close()
def test_type(self): ''' utilities.item: Test for the type of configuration files. ''' for t in self.cases: result = Item.item(t) self.assertIs(type(result), type(''))
def main(): ''' Wrapper function for configuration scripts. ''' createTables() print('%s Created database table successfully.\n' % item('success'))
def createTables(instance='config/config.json'): ''' Creates tables a database based on the tables specified in the configuration file. ''' # # Loading database information # from config file. # database = loadJSONFile(instance)['database'] # # Build each table. # for table in database: # # Construct SQL statement. # table_sql = "" for column in table['columns']: s = '%s %s, ' % (column['field_name'], column['type']) table_sql += s statement = 'CREATE TABLE IF NOT EXISTS "%s" (%sPRIMARY KEY (%s))' % (table['name'], table_sql, ", ".join(table['primary_key'])) # # Send statements to the database. # try: scraperwiki.sql.execute(statement) print("%s table `%s` created." % (item('bullet'), str(table['name']))) except Exception as e: print('%s Table `%s` could not be created.' % (item('error'), table['name'])) print(e) return False
def storeData(data, table): ''' Store records in a SQLite database. ''' # # Check no NULL values are passed. # # pops = [] # for key in data.keys(): # if data.get(key) is None: # pops.append(key) # for key in pops: # data.pop(key) print('{bullet} Storing {n} records in database.'.format(bullet=item('bullet'), n=len(data))) for record in data: scraperwiki.sqlite.save(record.keys(), record, table_name=table)
def collectData(url): ''' Collects data from a specific endpoint from OCHA CERF. ''' print('{bullet} Collecting data from: {url}'.format(bullet=item('bullet'), url=url)) r = requests.get(url) if _extension(url) == 'json': result = [] for record in r.json(): result.append(makeFlat(record)) else: result = [] tree = ElementTree.fromstring(r.content) for element in tree: record = XML.XmlDictConfig(element) result.append(makeFlat(record)) return result
import app.utilities.load as Load from app.utilities.item import item from app.collect.collect import collectData from app.utilities.store_data import storeData def main(): ''' Application wrapper. ''' dir_name = os.path.dirname(os.path.realpath(__file__)) file = os.path.join(dir_name, 'config', 'config.json') config = Load.loadJSONFile(file) for endpoint in config['endpoints']: data = collectData(endpoint['url']) storeData(data, endpoint['name']) if __name__ == '__main__': try: main() print('{success} Successully collected OCHA CERF data.'.format(success=item('success'))) scraperwiki.status('ok') except Exception as e: print('{failure} Failed to collected OCHA CERF data.'.format(failure=item('error'))) scraperwiki.status('error', 'Failed to collect data.')