# ---
# 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
# ---

import numpy as np
import pandas as pd
import pandas_profiling
import pymysql

tripData1 = pd.read_csv('/dltraining/datasets/Trip Data/trip_data_1.csv', nrows = 10000)

tripData1.head()

# +
tripFare1 = pd.read_csv('/dltraining/datasets/Trip Fare/trip_fare_1.csv', nrows = 10000) 

#remove the leading spaces
tripFare1.columns = ['medallion', 'hack_license', 'vendor_id', 'pickup_datetime',
       'payment_type', 'fare_amount', 'surcharge', 'mta_tax',
       'tip_amount', 'tolls_amount', 'total_amount']
# -

tripFare1.head()



# # Database Connection 

# +
host="nyctaxi.cq3rwooo9ghy.ap-southeast-2.rds.amazonaws.com"
port=3306
dbname="NYCTaxiDB"
user="Administrator"
password="<PASSWORD>"

conn = pymysql.connect(host, user=user,port=port,
                           passwd=password, db=dbname)
# create object to talk to the db
cursorObject = conn.cursor()
# -

# check to see if there are any tables
sqlQuery = "show tables"
cursorObject.execute(sqlQuery)
for x in cursorObject:
  print(x)

sqlQuery = "CREATE TABLE tripData(id INT AUTO_INCREMENT PRIMARY KEY, medallion char(32), hack_license char(32), vendor_id varchar(32), rate_code int, store_and_fwd_flag char(5), pickup_datetime datetime, dropoff_datetime datetime , passenger_count int, trip_time_in_secs int, trip_distance float(10,5), pickup_longitude float(12, 7), pickup_latitude float(12, 7), dropoff_longitude float(12, 7), dropoff_latitude float(12, 7))"
cursorObject.execute(sqlQuery)

sqlQuery = "CREATE TABLE fareData(id INT AUTO_INCREMENT PRIMARY KEY, medallion char(32), hack_license char(32), vendor_id varchar(32), pickup_datetime datetime, payment_type varchar(32), fare_amount float(10,3), surcharge float(10,3), mta_tax float(10,3), tip_amount float(10,3), tolls_amount float(10,3), total_amount float(10,3))"
cursorObject.execute(sqlQuery)

sqlQuery = "show tables"
cursorObject.execute(sqlQuery)
for x in cursorObject:
  print(x)

tripFare1.to_sql(con=conn, name='fareData_1', if_exists='append', index=False)

sqlQuery = "select count(*) from fareData"
cursorObject.execute(sqlQuery)
for x in cursorObject:
  print(x)

sqlQuery="ALTER TABLE NYCTaxiDB.fareData AUTO_INCREMENT =1"
cursorObject.execute(sqlQuery)
for x in cursorObject:
  print(x)

sqlQuery = "SELECT name FROM sqlite_master WHERE type='table' AND name='fareData_1'"
cursorObject.execute(sqlQuery)
for x in cursorObject:
  print(x)

pandas_profiling.ProfileReport(tripFare1)





# close the connection
conn.close()

StarcoderdataJupyterScriptsDedupFiltered