Skip to main content

SQLAlchemy utilities for normalising / denormalising table data, useful for CSV

Project description

sqlalchemy-csv-normalise

https://img.shields.io/pypi/v/sqlalchemy-csv-normalise.svg https://travis-ci.com/mohawk2/sqlalchemy-csv-normalise.svg?branch=master Documentation Status Updates

SQLAlchemy utilities for normalising / denormalising table data, useful for CSV

Where a table is normalised to have “lookup tables” of values referred to by e.g. a numeric foreign-key ID, these functions will enable extraction of the data (or conversely, loading from such) with the looked-up values substituted in. Among other things, this allows more human-friendly data editing in e.g. a spreadsheet.

Example:

from sqlalchemy_csv_normalise import denormalise_prepare
q, col_names = denormalise_prepare(db.session, table)
filename = table_to_filename(table)
with open(filename, 'w', newline='') as csv_file:
    csv_file_writer = csv.writer(csv_file)
    csv_file_writer.writerow(col_names)
    csv_file_writer.writerows(q.all())

from sqlalchemy_csv_normalise import renormalise_prepare, empty_deleter,\
    type_coercer
row_maker = renormalise_prepare(db.session, table)
row_cleaner = empty_deleter(table)
row_coercer = type_coercer(table)
filename = table_to_filename(table)
with open(filename, newline='') as csv_file:
    for d in csv.DictReader(csv_file):
        row = row_coercer(row_cleaner(row_maker(d)))
        db.session.add(table(**row))
db.session.commit()

Features

  • denormalise_prepare(session, table, colname_tidier)

Returns SQLAlchemy query, and the column-names it will return. The query will denormalise any foreign keys (FKs) if they refer to a table with a unique column that is not its primary key.

The names of any FK columns will have _id taken off the end as a simple heuristic. Override this by providing a colname_tidier.

  • empty_deleter(table)

Returns function that returns given dict minus empty strings for nullable columns. Useful because CSV has no way to record NULL.

  • type_coercer(table)

Returns function that given a row dict will coerce values. Works on dates and booleans. Will only operate on strings, so if you have pass in a row that has already got non-string values, they will not be affected.

  • renormalise_prepare(session, table, colname_tidier)

Returns function that will renormalise given dictionary Does the inverse of denormalise_prepare.

Credits

This package was created with Cookiecutter and the audreyr/cookiecutter-pypackage project template.

History

0.1.1 (2020-02-01)

  • First release on PyPI.

0.1.2 (2020-02-01)

  • make type_coercer only affect string values

Project details


Download files

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

Files for sqlalchemy-csv-normalise, version 0.1.2
Filename, size File type Python version Upload date Hashes
Filename, size sqlalchemy_csv_normalise-0.1.2-py2.py3-none-any.whl (6.2 kB) File type Wheel Python version py2.py3 Upload date Hashes View hashes
Filename, size sqlalchemy-csv-normalise-0.1.2.tar.gz (11.2 kB) File type Source Python version None Upload date Hashes View hashes

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 DigiCert DigiCert EV certificate StatusPage StatusPage Status page