Creating Databases¶
To create a database to hold HMDA data, run the script hmda_create_schemas
with a database URL as your argument. The database URL should be specified as
it would be in SQLAlchemy.
Examples:
hmda_create_schemas sqlite://hmda.db
hmda_create_schemas mysql://root@localhost/hmda
hmda_create_schemas postgresql://peter:rabbit@10.0.0.34/hmda
You will need to install the Python libraries for your database separately.
hmda_tools
does not require any DB libraries, as it tries to stay agnostic.
You can also create these schemas programatically using
hmda_tools.data.create_schemas()
.
The created schema will look like the following (dependent on database):
CREATE TABLE "action_taken" (
"id" int(11) NOT NULL,
"action_taken" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "agency" (
"id" int(11) NOT NULL,
"agency_abbr" varchar(10) NOT NULL,
"agency" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "cbsa" (
"cbsa_code" int(11) NOT NULL,
"parent_code" int(11) DEFAULT NULL,
"name" varchar(255) NOT NULL,
PRIMARY KEY ("cbsa_code")
);
CREATE TABLE "county" (
"county_fips_code" int(11) NOT NULL,
"state_fips_code" int(11) NOT NULL,
"ansi_code" varchar(8) NOT NULL,
"cbsa_code" int(11) DEFAULT NULL,
"name" varchar(255) NOT NULL,
"population" int(11) DEFAULT NULL,
"housing_units" int(11) DEFAULT NULL,
"land_area" bigint(20) DEFAULT NULL,
"water_area" bigint(20) DEFAULT NULL,
"latitude" varchar(20) DEFAULT NULL,
"longitude" varchar(20) DEFAULT NULL,
PRIMARY KEY ("county_fips_code","state_fips_code")
);
CREATE TABLE "denial_reason" (
"id" int(11) NOT NULL,
"denial_reason" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "edit_status" (
"id" int(11) NOT NULL,
"edit_status" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "ethnicity" (
"id" int(11) NOT NULL,
"ethnicity" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "hmda" (
"year" int(11) NOT NULL,
"respondent" varchar(10) DEFAULT NULL,
"agency" int(11) DEFAULT NULL,
"loan_type" int(11) DEFAULT NULL,
"property_type" int(11) DEFAULT NULL,
"loan_purpose" int(11) DEFAULT NULL,
"occupancy" int(11) DEFAULT NULL,
"loan_amount" int(11) DEFAULT NULL,
"preapproval" int(11) DEFAULT NULL,
"action_type" int(11) DEFAULT NULL,
"msa_md" int(11) DEFAULT NULL,
"state_code" int(11) DEFAULT NULL,
"county_code" int(11) DEFAULT NULL,
"census_tract_number" varchar(8) DEFAULT NULL,
"applicant_ethnicity" int(11) DEFAULT NULL,
"co_applicant_ethnicity" int(11) DEFAULT NULL,
"applicant_race_1" int(11) DEFAULT NULL,
"applicant_race_2" int(11) DEFAULT NULL,
"applicant_race_3" int(11) DEFAULT NULL,
"applicant_race_4" int(11) DEFAULT NULL,
"applicant_race_5" int(11) DEFAULT NULL,
"co_applicant_race_1" int(11) DEFAULT NULL,
"co_applicant_race_2" int(11) DEFAULT NULL,
"co_applicant_race_3" int(11) DEFAULT NULL,
"co_applicant_race_4" int(11) DEFAULT NULL,
"co_applicant_race_5" int(11) DEFAULT NULL,
"applicant_sex" int(11) DEFAULT NULL,
"co_applicant_sex" int(11) DEFAULT NULL,
"applicant_income" int(11) DEFAULT NULL,
"purchaser_type" int(11) DEFAULT NULL,
"denial_reason_1" int(11) DEFAULT NULL,
"denial_reason_2" int(11) DEFAULT NULL,
"denial_reason_3" int(11) DEFAULT NULL,
"rate_spread" varchar(10) DEFAULT NULL,
"hoepa_status" int(11) DEFAULT NULL,
"lien_status" int(11) DEFAULT NULL,
"edit_status" int(11) DEFAULT NULL,
"sequence_number" int(11) DEFAULT NULL,
"population" int(11) DEFAULT NULL,
"minority_population" float DEFAULT NULL,
"hud_median_family_income" int(11) DEFAULT NULL,
"tract_to_msa" float DEFAULT NULL,
"number_of_owner_occupied_units" int(11) DEFAULT NULL,
"number_of_family_units" int(11) DEFAULT NULL,
"application_date_indicator" int(11) DEFAULT NULL,
KEY "state_code" ("county_code"),
KEY "ix_hmda_occupancy" ("occupancy"),
KEY "ix_hmda_state_code" ("state_code"),
KEY "ix_hmda_year" ("year"),
KEY "ix_hmda_msa_md" ("msa_md"),
KEY "ix_hmda_applicant_ethnicity" ("applicant_ethnicity"),
KEY "ix_hmda_loan_amount" ("loan_amount"),
KEY "ix_hmda_census_tract_number" ("census_tract_number")
);
CREATE TABLE "hoepa" (
"id" int(11) NOT NULL,
"hoepa" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "lien_status" (
"id" int(11) NOT NULL,
"lien_status" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "loan_purpose" (
"id" int(11) NOT NULL,
"loan_purpose" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "loan_type" (
"id" int(11) NOT NULL,
"loan_type" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "owner_occupancy" (
"id" int(11) NOT NULL,
"owner_occupancy" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "preapproval" (
"id" int(11) NOT NULL,
"preapproval" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "property_type" (
"id" int(11) NOT NULL,
"property_type" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "purchaser_type" (
"id" int(11) NOT NULL,
"purchaser_type" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "race" (
"id" int(11) NOT NULL,
"race" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "sex" (
"id" int(11) NOT NULL,
"sex" varchar(255) NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "state" (
"fips_code" int(11) NOT NULL,
"abbr" varchar(2) NOT NULL,
PRIMARY KEY ("fips_code")
);