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
Filename, size sqlalchemy-csv-normalise-0.1.2.tar.gz (11.2 kB) File type Source Python version None Upload date Hashes View

Supported by

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