Skip to main content
Join the official Python Developers Survey 2018 and win valuable prizes: Start the survey!

Generates SQL DDL that will accept Python data

Project description

Infers SQL DDL (Data Definition Language) from table data.

Use at command line:

$ ddlgenerator -i postgresql '{"Name": "Alfred", "species": "wart hog", "kg": 22}'

DROP TABLE generated_table;
CREATE TABLE generated_table (
        name VARCHAR(6) NOT NULL,
        kg INTEGER NOT NULL,
        species VARCHAR(8) NOT NULL
)
;
INSERT INTO generated_table (kg, Name, species) VALUES (22, 'Alfred', 'wart hog');

Reads data from files:

$ ddlgenerator postgresql mydata.yaml > mytable.sql

Enables one-line creation of tables with their data

$ ddlgenerator –inserts postgresql mydata.json | psql

To use in Python:

>>> from ddlgenerator.ddlgenerator import Table
>>> table = Table({"Name": "Alfred", "species": "wart hog", "kg": 22})
>>> sql = table.sql('postgresql', inserts=True)

Options

  • Free software: MIT license

Supported data formats

  • Pure Python
  • YAML
  • JSON
  • CSV
  • Pickle

Features

  • Supports all SQL dialects supported by SQLAlchemy
  • Coerces data into most specific data type valid on all column’s values
  • Takes table name from file name
  • Guesses format of input data if unspecified by file extension
  • with -i/--inserts flag, adds INSERT statements
  • with -u/--uniques flag, surmises UNIQUE constraints from data
  • Handles nested data, creating child tables as needed
  • Accepts wildcards in filenames (remember quote marks to avoid shell expansion)

Options

-h, --help            show this help message and exit
-k KEY, --key KEY     Field to use as primary key
-r, --reorder         Reorder fields alphabetically, ``key`` first
-u, --uniques         Include UNIQUE constraints where data is unique
-t, --text            Use variable-length TEXT columns instead of VARCHAR
-d, --drops           Include DROP TABLE statements
-i, --inserts         Include INSERT statements
--no-creates          Do not include CREATE TABLE statements
--save-metadata-to FILENAME
                      Save table definition in FILENAME for later --use-
                      saved-metadata run
--use-metadata-from FILENAME
                      Use metadata saved in FROM for table definition, do
                      not re-analyze table structure
-l LOG, --log LOG     log level (CRITICAL, FATAL, ERROR, DEBUG, INFO, WARN)

Large tables

As of now, ddlgenerator is not well-designed for table sizes approaching your system’s available memory. It does accept a –limit keyword that should help when creating DDL from very large tables.

Another (messier) approach is to break your input data into multiple files, then run ddlgenerator with --save-metadata against a small but representative sample. Then run with --no-creates and -use-saved-metadata to generate INSERTs from the remaining files without needing to re-determine the column types each time.

Installing

git clone git clone https://github.com/catherinedevlin/ddl-generator.git
cd ddl-generator
python setup.py install

Credits

  • Mike Bayer for sqlalchemy
  • coldfix and Mark Ransom for their StackOverflow answers
  • Audrey Roy for cookiecutter

History

0.1.1 (2014-05-23)

  • First release on PyPI.

Project details


Download files

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

Filename, size & hash SHA256 hash help File type Python version Upload date
ddlgenerator-0.1.1-py3.4.egg (37.7 kB) Copy SHA256 hash SHA256 Egg 3.4 May 23, 2014
ddlgenerator-0.1.1.tar.gz (18.0 kB) Copy SHA256 hash SHA256 Source None May 23, 2014

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page