Skip to content

Latest commit

 

History

History
194 lines (145 loc) · 6.26 KB

DATABASE.adoc

File metadata and controls

194 lines (145 loc) · 6.26 KB
Raw

Setting up the FlatGov db for Django

Table of Contents

We are storing the data for this application in a SQL (Postgres) database to make use of the Django ORM.

Added Bill, Sponsor model to bills app.

At the moment, we have 2 models - Bill & Sponsor. We will need to add more models and fields in the future.

Update environment variables

  1. Add 2 env vars. In ~/.bash_profile or ~/.bashrc DJANGO_SETTINGS_MODULE=flatgov.dev SECRET_KEY=setAKeyInPrductioSECRET_KEY=setAKeyInPrductionn

Or, temporarily, from the command line:

$export DJANGO_SETTINGS_MODULE=flatgov.dev
$export SECRET_KEY=setAKeyInPrductioSECRET_KEY=setAKeyInPrductionn

Download tmp files

  1. Download tmp files in fetch_bill app. ~Flatgov/server_py/flatgov/fetch_bill$ ./download_bills.sh

  2. You can find congress directory in here - ~FlatGov/server_py/flatgov/congress

Install Postgres and set up flatgov database

Install PostgreSQL

On a Mac, this can be done with brew install postgresql or manually via https://www.postgresql.org/download/macosx/ If downloading manually, postgres command line should be enabled. See https://postgresapp.com/documentation/cli-tools.html

On Linux sudo apt-get install python-pip python-dev libpq-dev postgresql postgresql-contrib

Set up the FlatGov database

  • Start psql command line.

On a Mac, with the postgres user, enter the psql command line this way:

psql -h localhost -d postgres

On Ubuntu, set a password for the postgres user: sudo -u postgres psql postgres

Then \password postgres

Set the password and then close psql:

\q

  • Create the flatgov db and vmm user

postgres=# CREATE DATABASE flatgov;
CREATE DATABASE
postgres=# CREATE USER vmm WITH PASSWORD 'vmm';
CREATE ROLE
postgres=# ALTER ROLE vmm SET client_encoding TO 'utf8';
ALTER ROLE
postgres=# ALTER ROLE vmm SET default_transaction_isolation TO 'read committed';
ALTER ROLE
postgres=# ALTER ROLE vmm SET timezone TO 'UTC';
ALTER ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE flatgov TO vmm;
GRANT
postgres=# \q
flatgov{flatgov}$

Restart postgresql (may not be necessary): brew services restart postgresql

Create db migration

From Flatgov/server_py/flatgov$:

flatgov{flatgov}$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, bills, contenttypes, sessions
Running migrations:
Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying bills.0001_initial... OK
  Applying bills.0002_auto_20201113_1449... OK
  Applying bills.0003_auto_20201113_1604... OK
  Applying bills.0004_auto_20201116_1336... OK
  Applying bills.0005_auto_20201116_1341... OK
  Applying bills.0006_auto_20201116_1421... OK
  Applying bills.0007_auto_20201116_1432... OK
  Applying bills.0008_bill_cosponsors_dict... OK
  Applying sessions.0001_initial... OK

If modules error, make sure (1) you are in the python virtual environment (2) environment variables are set (3) requirements.txt has been run (if haven’t yet)

cd /path/to/FlatGov/server_py
pip install -r requirements.txt

Load fixtures

The About page is created as a fixture. To load it, run

cd server_py/flatgov
python manage.py loaddata about_us.json

Create billsMeta.json file via Django command.

  • Add data to database

Given that we are using django orm for database, we can store bill, sponsor data via django commands.

$ python manage.py bill_data
$ python manage.py process_bill_meta
$ python manage.py related_bills
Note
 ~FlatGov/server_py/flatgov/common directory contains all the django commands. Also, the --congress flag tells the scripts to use data from the congress directory in ` …​server_py/flatgov/congress`, created by the unitedstates/congress scraper, described in SCRAPER.adoc. This will have directories for each congress: ` …​server_py/flatgov/congress/data/117`, ` …​server_py/flatgov/congress/data/116`, ` …​server_py/flatgov/congress/data/115`, etc.

The billdata.py file creates bill, sponsor objects and adds proper relations.

After the metadata is loaded, index the bills to Elasticsearch and process bill similarity:

$ python manage.py elastic_load --uscongress
$ python manage.py bill_similarity --uscongress

Review Models with Django Admin

To review the data models, create a Django superuser and view the Django Admin panel.

  • Create a Django Superadmin

$python manage.py createsuperuser

Locally, I’ve used cool1dude as the pw

Review data from the Django shell

It may be useful to review data from the Django shell. For example, to find a sample of a Committee Document from the 117th Congress, enter the shell and run queries on the model:

(flatgov) ubuntu:/opt/flatgov/FlatGov/server_py/flatgov$ python manage.py shell
Python 3.8.3 (default, Sep 24 2020, 22:52:34)
[GCC 7.5.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from bills.models import CommitteeDocument
>>> queryset = CommitteeDocument.objects.filter(congress=117)
>>> queryset.count()
14
>>> record.queryset.first()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
NameError: name 'record' is not defined
>>> record = queryset.first()
>>> record.original_pdf_link
'//www.govinfo.gov/content/pkg/CRPT-117hrpt3/pdf/CRPT-117hrpt3.pdf'
>>> record.associated_legislation
'H. Res.85'