humble-database 0.0.1

A simple interface for managing database connections and queries

humble-database

This file will become your README and also the index of your documentation.

Install

pip install humble_database

How to use

Database Example

import pandas as pd
import os
from sqlalchemy import text
from urllib.request import urlretrieve

urlretrieve(
    "http://2016.padjo.org/files/data/starterpack/census-acs-1year/acs-1-year-2015.sqlite",
    filename='acs.db'
)
db = Database(drivername='sqlite',database = 'acs.db')

db.query_to_df("select * from sqlite_schema").head(2)

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	type	name	tbl_name	rootpage	sql
0	table	states	states	2	CREATE TABLE states (\n year INTEGER , \n ...
1	table	congressional_districts	congressional_districts	3	CREATE TABLE congressional_districts (\n ye...

db.query_to_df("""select * from states limit 5""")

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	year	name	geo_id	total_population	white	black	hispanic	asian	american_indian	pacific_islander	other_race	median_age	total_households	owner_occupied_homes_median_value	per_capita_income	median_household_income	below_poverty_line	foreign_born_population	state
0	2015	Alabama	04000US01	4858979	3204076	1296681	192870	58918	19069	2566	5590	38.7	1846390	134100	44765	44765	876016	169972	01
1	2015	Alaska	04000US02	738432	452472	24739	51825	45753	98300	6341	2201	33.3	250185	259600	73355	73355	74532	58544	02
2	2015	Arizona	04000US04	6828065	3802263	282718	2098411	210922	276132	9963	6951	37.4	2463008	194300	51492	51492	1159043	914400	04
3	2015	Arkansas	04000US05	2978204	2174934	466486	207743	41932	18221	7551	3826	37.9	1144663	120700	41995	41995	550508	142841	05
4	2015	California	04000US06	39144818	14815122	2192844	15184545	5476958	135866	143408	87813	36.2	12896357	449100	64500	64500	5891678	10688336	06

db.query_to_records(
    "select * from states limit 2",
)[0]

{'year': 2015, 'name': 'Alabama', 'geo_id': '04000US01', 'total_population': 4858979, 'white': 3204076, 'black': 1296681, 'hispanic': 192870, 'asian': 58918, 'american_indian': 19069, 'pacific_islander': 2566, 'other_race': 5590, 'median_age': 38.7, 'total_households': 1846390, 'owner_occupied_homes_median_value': 134100, 'per_capita_income': 44765, 'median_household_income': 44765, 'below_poverty_line': 876016, 'foreign_born_population': 169972, 'state': '01'}

ORM Example

SQL Alchemy Models

from pydantic import BaseModel,computed_field,field_validator,ConfigDict,Field
from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase,Mapped, mapped_column,relationship
from typing import List

class Base(DeclarativeBase):
    year:  Mapped[int]
    name: Mapped[str]
    geo_id: Mapped[str]
    total_population: Mapped[int]
    white: Mapped[int]
    black: Mapped[int]
    hispanic: Mapped[int]
    asian: Mapped[int]
    american_indian: Mapped[int]
    pacific_islander: Mapped[int]
    other_race: Mapped[int]
    median_age: Mapped[int]
    total_households: Mapped[int]
    owner_occupied_homes_median_value: Mapped[int]
    per_capita_income: Mapped[int]
    median_household_income: Mapped[int]
    below_poverty_line: Mapped[int]
    foreign_born_population: Mapped[int]

class State(Base):
    __tablename__ = 'states'
    state: Mapped[str] = mapped_column(primary_key=True)
    total_population: Mapped[int]

    places: Mapped[List['Place']] = relationship(back_populates='state_')
    congressional_districts: Mapped[List['CongressionalDistrict']] = relationship(back_populates='state_')

class Place(Base):
    __tablename__ = 'places'
    place: Mapped[str] = mapped_column(primary_key=True)
    total_population: Mapped[int]
    state: Mapped[str] = mapped_column(ForeignKey("states.state"))
    
    state_: Mapped['State'] = relationship(back_populates='places')

class CongressionalDistrict(Base):
    __tablename__ = 'congressional_districts'
    
    congressional_district: Mapped[str] = mapped_column(primary_key=True)
    state: Mapped[str] = mapped_column(ForeignKey("states.state"))
    
    state_: Mapped['State'] = relationship(back_populates='congressional_districts')

with db.session_scope() as session:
    s = session.query(State).first()
    print(s,'\n')
    for place in s.places:
        print(place.name,'::',place.median_household_income)

<__main__.State object> 

Birmingham city, Alabama :: 32378
Dothan city, Alabama :: 44208
Hoover city, Alabama :: 77365
Huntsville city, Alabama :: 46769
Mobile city, Alabama :: 38678
Montgomery city, Alabama :: 41836
Tuscaloosa city, Alabama :: 44125

with db.session_scope() as session:
    result = session.query(State).limit(7).all()
    for state in result:
        print(
            state.name,
            len(state.places),
            len(state.congressional_districts)
        )

Alabama 7 7
Alaska 1 1
Arizona 16 9
Arkansas 6 4
California 137 53
Colorado 16 7
Connecticut 8 5

Pydantic Models

class ACSBase(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    
    year: int = Field()
    name: str = Field()
    geo_id: str = Field()
    total_population: Optional[int] = Field(None)
    white: Optional[int] = Field(None)
    black: Optional[int] = Field(None)
    hispanic: Optional[int] = Field(None)
    asian: Optional[int] = Field(None)
    american_indian: Optional[int] = Field(None)
    pacific_islander: Optional[int] = Field(None)
    other_race: Optional[int] = Field(None)
    median_age: float = Field()
    total_households: Optional[int] = Field(None)
    owner_occupied_homes_median_value: int = Field()
    per_capita_income: int = Field()
    median_household_income: int = Field()
    below_poverty_line: Optional[int] = Field(None)
    foreign_born_population: Optional[int] = Field(None)    
    state: int = Field()

class PlaceModel(ACSBase):
    """A Model for a record from the 'places' table"""
    place: str

class CDModel(ACSBase):
    """A Model for a record from the 'congressional_districts' table"""
    congressional_district: str 

class StateModel(ACSBase):
    """A Model for a record from the 'states' table"""

    places: List[PlaceModel]
    congressional_districts: List[CDModel]
    
    @computed_field(return_type=float,title='People per District',)
    def avg_people_per_cd(self) -> float:
        return sum([cd.total_population for cd in self.congressional_districts]) / len(self.congressional_districts)

from IPython.display import JSON

# mode = serialization includes computed fields
JSON(StateModel.model_json_schema(mode='serialization'))

<IPython.core.display.JSON object>

from humble_database.data_model import DataModel

ACSDataModel = DataModel[StateModel]
JSON(ACSDataModel.model_json_schema(mode='serialization'))

<IPython.core.display.JSON object>

with db.session_scope() as session:
    orm_result = session.query(State).all()
    result = ACSDataModel(data=orm_result)

result

title: DataModel[StateModel] description: None DataFrame:

<style scoped> .dataframe tbody tr th:only-of-type { vertical-align: middle; }

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	year	name	geo_id	total_population	white	black	hispanic	asian	american_indian	pacific_islander	...	total_households	owner_occupied_homes_median_value	per_capita_income	median_household_income	below_poverty_line	foreign_born_population	state	places	congressional_districts	avg_people_per_cd
0	2015	Alabama	04000US01	4858979	3204076	1296681	192870	58918	19069	2566	...	1846390	134100	44765	44765	876016.0	169972.0	1	[{'year': 2015, 'name': 'Birmingham city, Alab...	[{'year': 2015, 'name': 'Congressional Distric...	694139.857143
1	2015	Alaska	04000US02	738432	452472	24739	51825	45753	98300	6341	...	250185	259600	73355	73355	74532.0	58544.0	2	[{'year': 2015, 'name': 'Anchorage municipalit...	[{'year': 2015, 'name': 'Congressional Distric...	738432.000000
2	2015	Arizona	04000US04	6828065	3802263	282718	2098411	210922	276132	9963	...	2463008	194300	51492	51492	1159043.0	914400.0	4	[{'year': 2015, 'name': 'Avondale city, Arizon...	[{'year': 2015, 'name': 'Congressional Distric...	711564.777778
3	2015	Arkansas	04000US05	2978204	2174934	466486	207743	41932	18221	7551	...	1144663	120700	41995	41995	550508.0	142841.0	5	[{'year': 2015, 'name': 'Fayetteville city, Ar...	[{'year': 2015, 'name': 'Congressional Distric...	695398.750000
4	2015	California	04000US06	39144818	14815122	2192844	15184545	5476958	135866	143408	...	12896357	449100	64500	64500	5891678.0	10688336.0	6	[{'year': 2015, 'name': 'Alameda city, Califor...	[{'year': 2015, 'name': 'Congressional Distric...	735426.811321

5 rows × 22 columns