How to export Django data faster than ever before
Use our open-source software to take advantage of PostgreSQL’s powerful COPY TO command
Today the California Civic Data Coalition released a new open-source tool that enables the Django web framework to more quickly export comma-delimited data.
Version 2.0 of django-postgres-copy, now available on the Python Package Index, extends Django’s database tools to support PostgreSQL’s powerful COPY TO command.
Found only in PostgreSQL, COPY TO can write out tables with millions of rows in a matter of seconds.
Our code, crafted by the Coalition’s Lead Developer James Gordon, makes using it this easy:
The custom to_csv method does it all. To start using it yourself, all you need to do is install our library and add a custom manager to your model.
Once that’s done you’re ready to roll. You can even export database queries that include filters, groups or other Django database tricks. For instance, this will work:
And so will something like this:
In cases where your model is connected to other tables with a foreign key, you can increase the number of fields exported by listing them out and calling in related tables using Django’s double underscore notation.
Why do you need this?
The Coalition invented this tool as part of its open-source quest to master CAL-ACCESS, the jumbled, dirty and difficult government database tracking money in California politics.
We are nearing the completion of a pipeline of Python code that downloads, extracts, cleans, loads, transforms and republishes the state’s raw data as easy-to-understand spreadsheets. This new wrapper for COPY TO allows our pipeline to quickly and clearly export a set of simplified flat files for end users.
What else can it do?
The library has long supported swiftly importing data files with PostgreSQL’s COPY command. Starting today, that old tool is easier to access with a new from_csv method on our custom manager. Code like the following can load millions of records in your database in a matter of seconds.
If you’d like to try our tool out for yourself, you can install it with Python’s
pip like so:
To learn more about how it works, visit the technical documentation. There you’ll find a more complete explanation and information about some fancier tricks not covered here, like the capability to transform and clean data on-the-fly as it’s loaded into the database.
Since it was first released in 2015, django-posgres-copy has drawn contributions from coders around the world, including some major improvements from users in other fields. If there are improvements you’d like to see, go get involved on our GitHub repository.