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.

Source Distribution

sqlalchemy-csv-normalise-0.1.2.tar.gz (11.2 kB view hashes)

Uploaded source

Built Distribution

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Huawei Huawei PSF Sponsor Microsoft Microsoft PSF Sponsor NVIDIA NVIDIA PSF Sponsor Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page