{ "cells": [ { "cell_type": "markdown", "id": "28ef4f99-bfb7-44a5-b9f3-83e9d8b71bf2", "metadata": {}, "source": [ "# Test connection to PostGRES Database" ] }, { "cell_type": "code", "execution_count": 1, "id": "a6d4b7d0-033b-4ce8-92a6-8dd95c4717ec", "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: pandas in /opt/conda/lib/python3.11/site-packages (2.2.3)\n", "Requirement already satisfied: psycopg2-binary in /opt/conda/lib/python3.11/site-packages (2.9.9)\n", "Requirement already satisfied: SQLAlchemy in /opt/conda/lib/python3.11/site-packages (2.0.32)\n", "Requirement already satisfied: numpy>=1.23.2 in /opt/conda/lib/python3.11/site-packages (from pandas) (2.1.2)\n", "Requirement already satisfied: python-dateutil>=2.8.2 in /opt/conda/lib/python3.11/site-packages (from pandas) (2.9.0)\n", "Requirement already satisfied: pytz>=2020.1 in /opt/conda/lib/python3.11/site-packages (from pandas) (2024.1)\n", "Requirement already satisfied: tzdata>=2022.7 in /opt/conda/lib/python3.11/site-packages (from pandas) (2024.2)\n", "Requirement already satisfied: typing-extensions>=4.6.0 in /opt/conda/lib/python3.11/site-packages (from SQLAlchemy) (4.12.2)\n", "Requirement already satisfied: greenlet!=0.4.17 in /opt/conda/lib/python3.11/site-packages (from SQLAlchemy) (3.0.3)\n", "Requirement already satisfied: six>=1.5 in /opt/conda/lib/python3.11/site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0)\n" ] } ], "source": [ "!pip install pandas psycopg2-binary SQLAlchemy" ] }, { "cell_type": "markdown", "id": "65a67b38-d44c-45cb-b87a-c08b763e3520", "metadata": {}, "source": [ "### Connect to the Database and display tables" ] }, { "cell_type": "code", "execution_count": 2, "id": "94bc21dd-fce0-4e7b-a6e0-ca2de56dec5c", "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "from sqlalchemy import create_engine" ] }, { "cell_type": "code", "execution_count": 3, "id": "5c45e206-38b1-4f79-8539-36d7e72e9b90", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
table_name
0ca_nonprofits
1ca_business
2ca_nonprofits_NTEE_Codes
3arcgis_geoenrichment
4city_neighborhoods
5community_neighborhoods
6bgs_sd_imp
7spatial_ref_sys
8usda_2022_branded_food_nutrients
9usda_2022_branded_food_product
10usda_2022_food_branded_experimental
11usda_2022_food_calorie_conversion_factor
12usda_2022_food_portions
13usda_2022_food_protein_conversion_factor
14usda_2022_nutrient_master
15geography_columns
16geometry_columns
17raster_columns
18raster_overviews
\n", "
" ], "text/plain": [ " table_name \n", "0 ca_nonprofits\n", "1 ca_business\n", "2 ca_nonprofits_NTEE_Codes\n", "3 arcgis_geoenrichment\n", "4 city_neighborhoods\n", "5 community_neighborhoods\n", "6 bgs_sd_imp\n", "7 spatial_ref_sys\n", "8 usda_2022_branded_food_nutrients\n", "9 usda_2022_branded_food_product\n", "10 usda_2022_food_branded_experimental\n", "11 usda_2022_food_calorie_conversion_factor\n", "12 usda_2022_food_portions\n", "13 usda_2022_food_protein_conversion_factor\n", "14 usda_2022_nutrient_master\n", "15 geography_columns\n", "16 geometry_columns\n", "17 raster_columns\n", "18 raster_overviews" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "pd.set_option('display.max_columns', None) # Show all columns\n", "pd.set_option('display.width', 1000) # Adjust the display width\n", "pd.set_option('display.colheader_justify', 'center') # Center-align column headers\n", "\n", "connection_string = \"postgresql://ndp-public:XKcBHaU1@awesome-hw.sdsc.edu:5432/nourish\"\n", "engine = create_engine(connection_string)\n", "tables_df = pd.read_sql_query(\"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\", engine)\n", "display(tables_df)" ] }, { "cell_type": "markdown", "id": "ef952414-cf2d-47de-9d96-387f57fb3e7b", "metadata": {}, "source": [ "### Display Tables Contents" ] }, { "cell_type": "code", "execution_count": 4, "id": "2cbeb2bf-c54a-4884-9de3-4d6d8d1e461a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
einnameicostreetcitystatezipGROUPsubsectionaffiliationclassificationrulingdeductibilityfoundationactivityorganizationstatustax_periodasset_cdincome_cdfiling_req_cdpf_filing_req_cdacct_pdasset_amtincome_amtrevenue_amtntee_cdsort_name
0320575664WOMEN WITH VOICESNone7950 ETIWANDA AVE APT 9202RANCHO CUCAMONGACA91739-00000333700201810115051202212.00020120.00.00.0X99None
1320575826NARAYANAN FAMILY FOUNDATIONNone300 N CATALINA STBURBANKCA91505-36150331000201812116011202212.00020120.00.00.0T50None
2320576717MOD MOVEMENT% MYOUTDESK LLC3001 E STSACRAMENTOCA95816-38070331200201811115011202212.0551012584337.0501081.0501081.0S80None
3320577173OLDE TOWNE QUILTERS OF NIPOMO INC% KAREN JONESPO BOX 1291NIPOMOCA93444-12910331200201812116011202212.00020120.00.00.0B60None
4320577432PRINCIPLES OF FAITH CHRISTIAN FELLOWSHIP INCNonePO BOX 9402SN BERNRDNOCA92427-04020331700201908110011NaN006012NaNNaNNaNNoneNone
.......................................................................................
190638320570956UNITED STATE OLYMPIC AND PARALYMPIC PROPERTIES...% LA 2004 EXPLRTRY CMMTTEE SOLE MBR10900 WILSHIRE BLVDSTE 700LOS ANGELESCA90024-00000331000202308115051202212.086101248056767.01492701.01492701.0N71None
190639320571178COME CENTERNone1517 E 17TH STOAKLANDCA94606-39340331000201807116011202312.00020120.00.00.0A62None
190640320571224NO LIMITS CREATIVE ARTS% CARLA C HUTCHISON644 HIGHLANDER AVEPLACENTIACA92870-32290331200201809116051202212.023201212585.043651.043651.0A60None
190641320572148LIGHTHOUSE OF HOPE AND RESTORATION A CALIFORNI...% RACHNA SINGH20210 SORRENTO LN APT 203PORTER RANCHCA91326-44670331000202202116011202212.00020120.00.00.0Z99None
190642320573955CENTRO CRISTIANO SEMILLAS DE FE% SANTOS MARTINEZ PEREZ2355 LONG BEACH BLVD APT 102LONG BEACHCA90806-32910337000201904110011NaN006012NaNNaNNaNX20None
\n", "

190643 rows × 28 columns

\n", "
" ], "text/plain": [ " ein name ico street city state zip GROUP subsection affiliation classification ruling deductibility foundation activity organization status tax_period asset_cd income_cd filing_req_cd pf_filing_req_cd acct_pd asset_amt income_amt revenue_amt ntee_cd sort_name\n", "0 320575664 WOMEN WITH VOICES None 7950 ETIWANDA AVE APT 9202 RANCHO CUCAMONGA CA 91739-0000 0 3 3 3700 201810 1 15 0 5 1 202212.0 0 0 2 0 12 0.0 0.0 0.0 X99 None \n", "1 320575826 NARAYANAN FAMILY FOUNDATION None 300 N CATALINA ST BURBANK CA 91505-3615 0 3 3 1000 201812 1 16 0 1 1 202212.0 0 0 2 0 12 0.0 0.0 0.0 T50 None \n", "2 320576717 MOD MOVEMENT % MYOUTDESK LLC 3001 E ST SACRAMENTO CA 95816-3807 0 3 3 1200 201811 1 15 0 1 1 202212.0 5 5 1 0 12 584337.0 501081.0 501081.0 S80 None \n", "3 320577173 OLDE TOWNE QUILTERS OF NIPOMO INC % KAREN JONES PO BOX 1291 NIPOMO CA 93444-1291 0 3 3 1200 201812 1 16 0 1 1 202212.0 0 0 2 0 12 0.0 0.0 0.0 B60 None \n", "4 320577432 PRINCIPLES OF FAITH CHRISTIAN FELLOWSHIP INC None PO BOX 9402 SN BERNRDNO CA 92427-0402 0 3 3 1700 201908 1 10 0 1 1 NaN 0 0 6 0 12 NaN NaN NaN None None \n", "... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...\n", "190638 320570956 UNITED STATE OLYMPIC AND PARALYMPIC PROPERTIES... % LA 2004 EXPLRTRY CMMTTEE SOLE MBR 10900 WILSHIRE BLVDSTE 700 LOS ANGELES CA 90024-0000 0 3 3 1000 202308 1 15 0 5 1 202212.0 8 6 1 0 12 48056767.0 1492701.0 1492701.0 N71 None \n", "190639 320571178 COME CENTER None 1517 E 17TH ST OAKLAND CA 94606-3934 0 3 3 1000 201807 1 16 0 1 1 202312.0 0 0 2 0 12 0.0 0.0 0.0 A62 None \n", "190640 320571224 NO LIMITS CREATIVE ARTS % CARLA C HUTCHISON 644 HIGHLANDER AVE PLACENTIA CA 92870-3229 0 3 3 1200 201809 1 16 0 5 1 202212.0 2 3 2 0 12 12585.0 43651.0 43651.0 A60 None \n", "190641 320572148 LIGHTHOUSE OF HOPE AND RESTORATION A CALIFORNI... % RACHNA SINGH 20210 SORRENTO LN APT 203 PORTER RANCH CA 91326-4467 0 3 3 1000 202202 1 16 0 1 1 202212.0 0 0 2 0 12 0.0 0.0 0.0 Z99 None \n", "190642 320573955 CENTRO CRISTIANO SEMILLAS DE FE % SANTOS MARTINEZ PEREZ 2355 LONG BEACH BLVD APT 102 LONG BEACH CA 90806-3291 0 3 3 7000 201904 1 10 0 1 1 NaN 0 0 6 0 12 NaN NaN NaN X20 None \n", "\n", "[190643 rows x 28 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "table_name = 'ca_nonprofits'\n", "df = pd.read_sql_table(table_name, engine)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 5, "id": "9e2c097f-76f7-49a8-b50c-e0a12006b80a", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
NTEECodeDescriptionDefinition
0AArts, Culture & HumanitiesPrivate nonprofit organizations whose primary ...
1A01Alliances & AdvocacyOrganizations whose activities focus on influe...
2A02Management & Technical AssistanceConsultation, training, and other forms of man...
3A03Professional Societies & AssociationsLearned societies, professional councils, and ...
4A05Research Institutes & Public Policy AnalysisOrganizations whose primary purpose is to cond...
............
676Y44Voluntary Employees Beneficiary Associations G...Governmental organizations that provide for th...
677Y50CemeteriesOrganizations that maintain cemeteries, or pro...
678Y99Mutual & Membership Benefit N.E.C.Organizations that provide benefits or assista...
679ZUnknownNA
680Z99UnknownThis is a temporary code for organizations unt...
\n", "

681 rows × 3 columns

\n", "
" ], "text/plain": [ " NTEECode Description Definition \n", "0 A Arts, Culture & Humanities Private nonprofit organizations whose primary ...\n", "1 A01 Alliances & Advocacy Organizations whose activities focus on influe...\n", "2 A02 Management & Technical Assistance Consultation, training, and other forms of man...\n", "3 A03 Professional Societies & Associations Learned societies, professional councils, and ...\n", "4 A05 Research Institutes & Public Policy Analysis Organizations whose primary purpose is to cond...\n", ".. ... ... ...\n", "676 Y44 Voluntary Employees Beneficiary Associations G... Governmental organizations that provide for th...\n", "677 Y50 Cemeteries Organizations that maintain cemeteries, or pro...\n", "678 Y99 Mutual & Membership Benefit N.E.C. Organizations that provide benefits or assista...\n", "679 Z Unknown NA\n", "680 Z99 Unknown This is a temporary code for organizations unt...\n", "\n", "[681 rows x 3 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "table_name = 'ca_nonprofits_NTEE_Codes'\n", "df = pd.read_sql_table(table_name, engine)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": null, "id": "f2a75f01-51e3-402c-94aa-ae23302dc38f", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.11.9" } }, "nbformat": 4, "nbformat_minor": 5 }