Skip to main content

SQL loader for Socrata data sets

Project description

🏛️ socrata2sql CircleCI (all branches) LICENSE

Plenty of state and local governments use Socrata to run their open data portals. This tool allows you to grab a dataset from one of these portals and copy it into a SQL database of your choice. It uses the Socrata API to understand the columns in the dataset and attempts to create correctly-typed columns in the SQL database to match, including PostGIS geometries if the database and source dataset support them.

Requirements

  • Python 3.x

Installation

$ pipenv install socrata2sql

or

$ pip install socrata2sql

Usage

Socrata to SQL database loader

Load a dataset from a Socrata-powered open data portal into a SQL database.
Uses the Socrata API to inspect the dataset, then sets up a table with matching
SQL types and loads all rows. The loader supports any database supported by
SQLalchemy.

Usage:
  socrata2sql insert <site> <dataset_id> [-d=<database_url>] [-a=<app_token>] [-t=<table_name>]
  socrata2sql ls <site> [-a=<app_token>]
  socrata2sql (-h | --help)
  socrata2sql (-v | --version)

Options:
  <site>             The domain for the open data site. Ex: www.dallasopendata.com
  <dataset_id>       The ID of the dataset on the open data site. This is usually
                     a few characters, separated by a hyphen, at the end of the
                     URL. Ex: 64pp-jeba
  -d=<database_url>  Database connection string for destination database as
                     dialect+driver://username:password@host:port/database.
                     Default: sqlite:///<dataset name>.sqlite
  -t=<table_name>    Destiation table in the database. Defaults to a sanitized
                     version of the dataset's name on Socrata.
  -a=<app_token>     App token for the site. Only necessary for high-volume
                     requests. Default: None
  -h --help          Show this screen.
  -v --version       Show version.

Examples:
  List all datasets on the Dallas open data portal:
  $ socrata2sql ls www.dallasopendata.com

  Load the Dallas check register into a local SQLite file (file name chosen
  from the dataset name):
  $ socrata2sql insert www.dallasopendata.com 64pp-jeba

  Load it into a PostgreSQL database called mydb:
  $ socrata2sql insert www.dallasopendata.com 64pp-jeba -d=postgresql:///mydb

Local development

There are (for now) very limited tests. They can be run with:

$ python setup.py tests

Copyright

© 2019 The Dallas Morning News

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

socrata2sql-0.1.4.tar.gz (6.0 kB view hashes)

Uploaded source

Built Distribution

socrata2sql-0.1.4-py3-none-any.whl (8.4 kB view hashes)

Uploaded py3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page