This is a pre-production deployment of Warehouse. Changes made here affect the production instance of PyPI (
Help us improve Python packaging - Donate today!

Push semi-structured data (e.g. JSON documents) into a database with a minimum of fuss. Includes validation and schema migration.

Project Description

sqlnosql gets you from nosql to, well… sql. It takes a JSON schema and it turns it into any of:

  • a SQL schema
  • a sqlalchemy Table object
  • migration files for Alembic/SQLAlchemy

It can also take JSON data and insert it into your database – either from the commandline or from Python.

Type in sqlnosql --help on the command line for more information.

sqlnosql will translate JSON types into SQL types and it will flatten down nested data structures so that e.g. {"supplies": {"medical": "string"}} becomes a supplies_medical column with type TEXT.

sqlnosql is useful in ETL processes where you might have a bunch of data in flat files or a nosql database that you want to get into a proper SQL database for analysis purposes.

If your database supports it, sqlnosql will keep arrays intact. In particular, Postgres has an ARRAY type. However, keep in mind that native arrays can only contain a single, simple type, like strings or numbers. Complex types will automatically be reduced to simple types by sqlnosql. By default it does this by simply serializing them to JSON.

Similarly, if your JSON schema specifies objects that don’t have any specifically defined properties, these too will be serialized into strings.

Keep in mind that your JSON schema must be exhaustive. Fields not in your schema will not become columns and will be ignored.

Because you probably ain’t gonna need it, there’s no support for data normalization, that is, no support for splitting out your data into separate tables that are connected through foreign keys.

Migrations with Alembic

Alembic might seem overwhelming at first, especially for those who are not from a Python background but would simply like to use the migration feature of sqlnosql. Don’t worry, it’s really rather easy to get started.

Figure out where you’d like to keep your migration code, probably a subdirectory of your general code repo, and do alembic init <revisions_dir>. Now open alembic.ini and look for sqlalchemy.url – that’s where you should enter the authentication for and location to your database.

(Alternatively, it is also possible to keep this information in environment variables, which we will explain later.)

Now open <revisions_dir>/, look for the line that says target_metadata = None and change it to something like

import json
import sqlnosql
from sqlalchemy import MetaData

schema = json.load(open('path/to/schema.json'))
target_metadata = MetaData()
table = sqlnosql.schema.create_table(schema, target_metadata, pk='my_primary_key_column')

And that’s all there is to it in terms of setup.

Now, to create a new revision (a.k.a. migration), run alembic revision --autogenerate. Alembic will generate a Python file containing upgrade and download functions. It will tell you the path to this file, and before applying the migration you will want to check that file to make sure the database operations it suggests are correct, and if not, make the necessary adjustments.

Finally, to run the migration, do alembic upgrade head.

For more details on Alembic, take a look at the Alembic tutorial.

Release History

Release History

This version
History Node


History Node


Download Files

Download Files

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

File Name & Checksum SHA256 Checksum Help Version File Type Upload Date
sqlnosql-0.2.0.tar.gz (6.6 kB) Copy SHA256 Checksum SHA256 Source Jul 28, 2014

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting