# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # SQL and Pandas

# Now that we know how to manipulate data in Pandas, the next segment will cover how to interface with databases from your code.

# +
# Let's get back to the data frames we had before
import geopandas as gpd

df = gpd.pd.read_csv('data/Places_Full.csv')
dfD = gpd.pd.read_csv('data/Dist_Out.csv')
# -





# # SQL Databases - What and why
#
# We've been using geopandas to manipulate the data and have started to do some interesting analysis. Now we want to store the results. What are our options?
#
# One of them is the to_csv method. This will let us export or data frame in the same format we were importing earlier
#

# +
# dfD.to_csv?
# -

# What are the pros and cons of storing our files that way?
#
# **Pros**
# - (from audience)
# - 
#
#
# **Cons**
#
# - (from audience)
#





# # What is a database? 
#
# * a database is a software system for _capturing_, _storing_ and _analyzing_ data. 
# * nearly all databases use the _relational_ data model in which information is structured in row and column format: 
#
# <img src="terminology.png" width = "600">
#
# <br>
#
# ---
# ## Motivation for using a database
#
# * fast searching
# * powerful methods for performing analysis on groups of data
# * capability of joining information between datasets
# * data types have unique functionality (e.g. dates are not just integers but have methods related to year, month, day)
# * centralized repository, minimizes duplication, controlled access across multiple users
# * optional: geospatial encoding  
#
# ## The relational data model:
#
# * there are many different flavors of databases but the most well developed is the _relational_ data model
#    * each record has a unique identifier (primary key)
#    * data are manipulated using _Structured Query Language_ (SQL):   
#
# ## Structured Query Language (SQL):
# * standard language for relational databases
# * across different databases the core syntax is similar but there are small differences in some function names
#
# <br>
#
# ---
#
#
# ### Data Types
#
# * all data in a column must be of the same data type
# * this is required by SQL so that the database knows how to operate on the data in a consistent way
# * here are a few common [data types](https://www.postgresql.org/docs/9.4/static/datatype.html):
#
# | Name | Aliases | Description |
# | --- | --- | --- |
# |  boolean | bool | logical Boolean (true/false) |
# | character | varchar(n) | fixed-length character string |
# | date |  | calendar date (year, month, day) |
# | double precision | float | double precision floating-point number |
# | integer | int| signed four-byte integer |
# | timestamp |  | date and time (no time zone) |
# | text | text | arbitrary length text |
#
#
#





# # Talking to a database from Pandas/Geopandas
#
# The pandas library supports importing and exporting data from an outside database, using the SQL language.

# We'll need the pandas library as Geopandas doesn't support all the 
# SQL functions that we might want.
import pandas as pd

# We also need some way to connect to our database. In this case we
# are using SQLite to talk to a local database.
import sqlite3

# +
# To talk to a database, pandas needs a connection object passed into its
# methods. We'll call this conn.
conn = sqlite3.connect("geo.db")

# Now conn is connected to a database file called geo.db

# +
# Say we want to store our data frame
# If we were in the previous notebook, this would produce and error:
gfD.to_sql('distances', conn)

# Basic sqlite doesn't support the "Point" type that we created earlier.
# Other databases will support geometric data types, but for now let's
# If needed, you can use iloc to take just the first 15 columns of the dataframe.
gfD.iloc[:,0:15].to_sql('distances', conn)

# +
# If you need to drop a table, can use this command, but be careful!
# pd.read_sql_query("drop table distances;", conn)
# pd.read_sql_query("drop table places;", conn)

# You can also use the if_exists='replace' parameter, but it's safer to explicitly delete your tables
# -

# Places table
df.to_sql('places', conn)

# Distances table
dfD.to_sql('distances', conn)





# # SQLite commands
#
# These are some useful SQL commands.
#
# ``` mysql
# -- If you aren't importing a table directly, you'll need to create one
# CREATE TABLE example (
#     name text,
#     department text,
#     email text,
# );
#
# -- When working directly in SQLite, you'll need these commands
# .headers on
# .mode csv
# .nullvalue NULL
#
# .import <file> <table>
#
# -- Template query, e
# SELECT
# FROM distances as d
# ;
#
# ```

# # Importing from a database
#
# Pandas makes importing data from a database easy. We can run a query to get the whole table, but we can also use the LIMIT command to get a smaller area

dfDtop5 = pd.read_sql_query("SELECT * FROM distances LIMIT 5;", conn)

dfDtop5





# # Other databases than SQLite
#
# SQLite is great for storing files locally, but more commonly your data might be on a web server and different database system. Fortunately the `sqlalchemy` is also supported by Pandas.

from sqlalchemy import create_engine
import sqlalchemy.types as types

engine = create_engine('postgresql://maasr:UWCSE414@dssg18.cofuftxjqsbc.us-east-1.rds.amazonaws.com/dssg18')

conn_aws = engine.connect()

# +
# Our same drop tables commands from before if needed
#pd.read_sql_query("drop table places;", conn_aws)
#pd.read_sql_query("drop table distances;", conn_aws)
# -

df.to_sql('places', conn_aws)

dfD.to_sql('distances', conn_aws)

dfDsmall = pd.read_sql_query("select * from distances limit 5;", conn_aws)

dfDsmall





# That should get you started with using pandas and databases! There is a lot more to learn, but it will depend on your specfic use cases. Good luck!

StarcoderdataJupyterScriptsDedupFiltered