{ "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 Table" ] }, { "cell_type": "code", "execution_count": 4, "id": "0953ccb2-a411-4783-929b-39b7b961ac64", "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", "
idstate_namecountycitymetro_areais_unincorporated_placezipcodesneighboring_citiesneighboring_unincorporated_placesnearby_unincorporated_placesneighborhoodsnearby_cities
068CaliforniaSan DiegoSan Diego Country EstatesSan Diego AreaTrue[92065]None[Ramona][Alpine, Crest, Descanso, Eucalyptus Hills, Ha...None[Poway, Santee]
114CaliforniaSan DiegoEscondidoSan Diego AreaFalse[92025, 92026, 92027, 92029, 92069, 92078, 92082][San Diego, San Marcos][Hidden Meadows][Bonsall, Fairbanks Ranch, Lake San Marcos, Ra...[Hickory, Los Arboles, Mission Grove, Old Esco...[Carlsbad, Encinitas, Poway, Vista]
216CaliforniaSan DiegoLa MesaSan Diego AreaFalse[91941, 91942, 92020, 92120][El Cajon, Lemon Grove, San Diego][Casa de Oro-Mount Helix, Spring Valley][Bonita, Bostonia, Eucalyptus Hills, Granite H...[Lake Murray, College East, Lake Murray, San C...[National City, Santee]
321CaliforniaSan DiegoSan DiegoSan Diego AreaFalse[91911, 91914, 91915, 91932, 91942, 91945, 919...[Chula Vista, Coronado, Del Mar, El Cajon, Esc...[Bonita, Fairbanks Ranch, La Presa, Ramona][Bostonia, Casa de Oro-Mount Helix, Crest, Euc...[Amphitheater and Water Park, Egger Highlands,...None
424CaliforniaSan DiegoSolana BeachSan Diego AreaFalse[92014, 92075][Del Mar, Encinitas, San Diego]None[Camp Pendleton South, Fairbanks Ranch, Lake S...None[Carlsbad, Escondido, Oceanside, Poway, San Ma...
.......................................
66118CaliforniaImperialWestmorlandEl Centro AreaFalse[92281]NoneNone[Bombay Beach, Heber, Niland, Seeley]None[Brawley, Calexico, Calipatria, El Centro, Hol...
67119CaliforniaImperialHoltvilleEl Centro AreaFalse[92250]NoneNone[Heber, Niland, Ocotillo, Seeley]None[Brawley, Calexico, Calipatria, El Centro, Imp...
68120CaliforniaImperialNilandEl Centro AreaTrue[92257]NoneNone[Bombay Beach, North Shore, Salton City, Salto...None[Brawley, Calipatria, El Centro, Holtville, Im...
69121CaliforniaImperialHeberEl Centro AreaTrue[92249]NoneNone[Niland, Ocotillo, Seeley]None[Brawley, Calexico, Calipatria, El Centro, Hol...
70122CaliforniaImperialEl CentroEl Centro AreaFalse[92243][Imperial]None[Heber, Jacumba, Niland, Ocotillo, Seeley]None[Brawley, Calexico, Calipatria, Holtville, Wes...
\n", "

71 rows × 12 columns

\n", "
" ], "text/plain": [ " id state_name county city metro_area is_unincorporated_place zipcodes neighboring_cities neighboring_unincorporated_places nearby_unincorporated_places neighborhoods nearby_cities \n", "0 68 California San Diego San Diego Country Estates San Diego Area True [92065] None [Ramona] [Alpine, Crest, Descanso, Eucalyptus Hills, Ha... None [Poway, Santee]\n", "1 14 California San Diego Escondido San Diego Area False [92025, 92026, 92027, 92029, 92069, 92078, 92082] [San Diego, San Marcos] [Hidden Meadows] [Bonsall, Fairbanks Ranch, Lake San Marcos, Ra... [Hickory, Los Arboles, Mission Grove, Old Esco... [Carlsbad, Encinitas, Poway, Vista]\n", "2 16 California San Diego La Mesa San Diego Area False [91941, 91942, 92020, 92120] [El Cajon, Lemon Grove, San Diego] [Casa de Oro-Mount Helix, Spring Valley] [Bonita, Bostonia, Eucalyptus Hills, Granite H... [Lake Murray, College East, Lake Murray, San C... [National City, Santee]\n", "3 21 California San Diego San Diego San Diego Area False [91911, 91914, 91915, 91932, 91942, 91945, 919... [Chula Vista, Coronado, Del Mar, El Cajon, Esc... [Bonita, Fairbanks Ranch, La Presa, Ramona] [Bostonia, Casa de Oro-Mount Helix, Crest, Euc... [Amphitheater and Water Park, Egger Highlands,... None\n", "4 24 California San Diego Solana Beach San Diego Area False [92014, 92075] [Del Mar, Encinitas, San Diego] None [Camp Pendleton South, Fairbanks Ranch, Lake S... None [Carlsbad, Escondido, Oceanside, Poway, San Ma...\n", ".. ... ... ... ... ... ... ... ... ... ... ... ...\n", "66 118 California Imperial Westmorland El Centro Area False [92281] None None [Bombay Beach, Heber, Niland, Seeley] None [Brawley, Calexico, Calipatria, El Centro, Hol...\n", "67 119 California Imperial Holtville El Centro Area False [92250] None None [Heber, Niland, Ocotillo, Seeley] None [Brawley, Calexico, Calipatria, El Centro, Imp...\n", "68 120 California Imperial Niland El Centro Area True [92257] None None [Bombay Beach, North Shore, Salton City, Salto... None [Brawley, Calipatria, El Centro, Holtville, Im...\n", "69 121 California Imperial Heber El Centro Area True [92249] None None [Niland, Ocotillo, Seeley] None [Brawley, Calexico, Calipatria, El Centro, Hol...\n", "70 122 California Imperial El Centro El Centro Area False [92243] [Imperial] None [Heber, Jacumba, Niland, Ocotillo, Seeley] None [Brawley, Calexico, Calipatria, Holtville, Wes...\n", "\n", "[71 rows x 12 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "table_name = 'city_neighborhoods'\n", "df = pd.read_sql_table(table_name, engine)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": 5, "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", "
idstate_namecountycitymetro_areacommunityzipcodesneighboring_citiesneighboring_unincorporated_placesnearby_unincorporated_placesneighboring_communitiesnearby_communitiesnearby_cities
0283CaliforniaSan DiegoChula VistaSan Diego AreaEastlake Land Swap[91915]NoneNoneNone[Eastlake Greens, Otay Ranch, Otay Ranch Villa...[East Lake I, Eastlake Trails, Eastlake Vistas...None
1284CaliforniaSan DiegoChula VistaSan Diego AreaEastlake Trails[91915]NoneNoneNone[Eastlake Greens, Eastlake Vistas, Eastlake Wo...[Bella Lago, East Lake I, Eastlake Land Swap, ...None
2285CaliforniaSan DiegoChula VistaSan Diego AreaEastlake Vistas[91915]NoneNoneNone[Eastlake Trails, Olympic Training Center, Ota...[Bella Lago, East Lake I, Eastlake Greens, Eas...None
3286CaliforniaSan DiegoChula VistaSan Diego AreaEastlake Woods[91914]NoneNoneNone[Eastlake Greens, Eastlake Trails, Eastlake Wo...[Bella Lago, Bonita Long Canyon, East Lake I, ...None
4287CaliforniaSan DiegoChula VistaSan Diego AreaEastlake Woods West[91914]NoneNoneNone[Eastlake Woods, Fenton St, Rolling Hills Ranch][Bella Lago, East Lake I, Eastlake Greens, Eas...None
..........................................
224124CaliforniaSan DiegoSan DiegoSan Diego AreaValencia Park[92102, 92113, 92114]NoneNoneNone[Alta Vista, Chollas View, Eastside, Emerald H...[Bay Terraces, Central, Granger, Mountain View...None
225125CaliforniaSan DiegoSan DiegoSan Diego AreaWooded Area[92106, 92107]NoneNoneNone[La Playa, Roseville - Fleet Ridge, Sunset Cli...[Gaslamp Quarter, Harborview, Horton Plaza, Li...None
226441CaliforniaSan DiegoSan MarcosSan Diego AreaLa Costa Oaks[92009, 92024, 92078]NoneNoneNone[La Costa Ridge, Rancho La Costa][Aviara, Bressi Ranch, Carlsbad Ranch, Hediond...None
227442CaliforniaSan DiegoSan MarcosSan Diego AreaLa Costa Ridge[92009, 92078]NoneNoneNone[La Costa Oaks, Rancho La Costa][Aviara, Bressi Ranch, Carlsbad Ranch, Hediond...None
228443CaliforniaSan DiegoSan MarcosSan Diego AreaRancho La Costa[92009, 92011, 92078]NoneNoneNone[Aviara, La Costa Greens, La Costa Oaks, La Co...[Bressi Ranch, Calavera Hills, Carlsbad Ranch,...None
\n", "

229 rows × 13 columns

\n", "
" ], "text/plain": [ " id state_name county city metro_area community zipcodes neighboring_cities neighboring_unincorporated_places nearby_unincorporated_places neighboring_communities nearby_communities nearby_cities\n", "0 283 California San Diego Chula Vista San Diego Area Eastlake Land Swap [91915] None None None [Eastlake Greens, Otay Ranch, Otay Ranch Villa... [East Lake I, Eastlake Trails, Eastlake Vistas... None \n", "1 284 California San Diego Chula Vista San Diego Area Eastlake Trails [91915] None None None [Eastlake Greens, Eastlake Vistas, Eastlake Wo... [Bella Lago, East Lake I, Eastlake Land Swap, ... None \n", "2 285 California San Diego Chula Vista San Diego Area Eastlake Vistas [91915] None None None [Eastlake Trails, Olympic Training Center, Ota... [Bella Lago, East Lake I, Eastlake Greens, Eas... None \n", "3 286 California San Diego Chula Vista San Diego Area Eastlake Woods [91914] None None None [Eastlake Greens, Eastlake Trails, Eastlake Wo... [Bella Lago, Bonita Long Canyon, East Lake I, ... None \n", "4 287 California San Diego Chula Vista San Diego Area Eastlake Woods West [91914] None None None [Eastlake Woods, Fenton St, Rolling Hills Ranch] [Bella Lago, East Lake I, Eastlake Greens, Eas... None \n", ".. ... ... ... ... ... ... ... ... ... ... ... ... ...\n", "224 124 California San Diego San Diego San Diego Area Valencia Park [92102, 92113, 92114] None None None [Alta Vista, Chollas View, Eastside, Emerald H... [Bay Terraces, Central, Granger, Mountain View... None \n", "225 125 California San Diego San Diego San Diego Area Wooded Area [92106, 92107] None None None [La Playa, Roseville - Fleet Ridge, Sunset Cli... [Gaslamp Quarter, Harborview, Horton Plaza, Li... None \n", "226 441 California San Diego San Marcos San Diego Area La Costa Oaks [92009, 92024, 92078] None None None [La Costa Ridge, Rancho La Costa] [Aviara, Bressi Ranch, Carlsbad Ranch, Hediond... None \n", "227 442 California San Diego San Marcos San Diego Area La Costa Ridge [92009, 92078] None None None [La Costa Oaks, Rancho La Costa] [Aviara, Bressi Ranch, Carlsbad Ranch, Hediond... None \n", "228 443 California San Diego San Marcos San Diego Area Rancho La Costa [92009, 92011, 92078] None None None [Aviara, La Costa Greens, La Costa Oaks, La Co... [Bressi Ranch, Calavera Hills, Carlsbad Ranch,... None \n", "\n", "[229 rows x 13 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "table_name = 'community_neighborhoods'\n", "df = pd.read_sql_table(table_name, engine)\n", "display(df)" ] }, { "cell_type": "code", "execution_count": null, "id": "c960bcb0-a2d6-475d-9c2e-97d924e791ff", "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 }