This is a migration tool, responsible for uploading massive CSVs to a centralised database. The database can be accessed remotely by teams with the right set of credentials. This repository contains the database physical schema creation, and uploading passed CSV files to appropriate tables.
For more details on the schema see here.
$ git clone https://github.com/s-bose7/csv-uploader.git
Navigate to the project directory:
$ cd csv-uploader
Create and activate a virtual environment using your preferred tool (e.g., venv).
$ python3 -m venv gp-db-uploader
$ source gp-db-uploader/bin/activate # unix
Install dependencies listed in requirements.txt:
$ pip install -r requirements.txt
Create a data directory to store your CSV files for upload.
$ mkdir data # UNIX
Create the migration environment:
$ alembic init migrations # This will create a dir migrations in project root with an alembic.ini file
Create a .env file in the project root for environment variables:
$ touch .env # UNIX
Populate the .env file with the following variables, replacing the placeholders with your actual credentials:
DEV_DB_USER=postgres
DEV_DB_PASSWORD=your_development_password
DEV_DB_HOST=localhost
DEV_DB_PORT=5432
DEV_DB_NAME=your_development_database_name
PROD_DB_USER=your_production_username
PROD_DB_PASSWORD=your_production_password
PROD_DB_HOST=your_production_database_host
PROD_DB_PORT=5432
PROD_DB_NAME=your_production_database_name
Obtain production credentials from your heroku-postgres
settings. Never store production credentials in version control (e.g., Git).
Refer to this separate guide for instructions on starting the database container in a development environment.
run_migrations.py
handles database schema changes through Alembic migrations. Execute this first:
python3 run_migrations.py -production -new # Use '-development' for development
-new
flag is to indicate that there's been some changes in the original database schema, so alembic needs to create new migrations accordingly. So only pass this flag if you changed the original schema otherwise leave blank.
main.py
uploads data from your CSV files. Run it only after migrations:
python3 main.py -production # Use '-development' for development
The -production flag is used for production environment. Use -development for local development.
The project uses Alembic for migrations. Refer to the official documentation for advanced usage.
Data integrity checks are important to verify the core database insertion logic. Refer here to see the kinds of checks being performed after inserting data.
- Create database schema
- Add configurations
- Create the migrations
- Read and validate data
- Make insertions
- Add indexes to tables
- Add logging
- Deploy to heroku
- Batch insertions for better performance.