lenalytics/main.py

## main.py
import csv
import psycopg2
from ast import literal_eval
from re import sub

def connect():
    connection = psycopg2.connect(user="xxx",
                                  password="xxx",
                                  host="xxx",
                                  port="5432",
                                  database="dbname")
    return connection


def upload_to_table(filename, table):
    """This method reads files from ./data/filename, applies transformations from table, and inserts the result into the
    database """
    conn = connect()
    c = conn.cursor()
    file = open(filename, "r")
    reader = csv.reader(file, delimiter=',')
    next(reader, None)  # skip the headers
    for row in reader:
        table(row).insert(cursor=c)
    conn.commit()
    c.close()

upload_to_table("data/au/listings.csv", Listing)
upload_to_table("data/au/reviews.csv", Review)
upload_to_table("data/au/calendar.csv", Calendar)


## CLASS DEFINITIONS BELOW


class Listing:
    def __init__(self, csv_row):
        self.raw_csv_row = csv_row
        self.id = toInt(csv_row[0])
        self.listing_url = csv_row[1]
        self.scrape_id = toInt(csv_row[2])
        self.last_searched = toString(csv_row[3], '-infinity')
        self.last_scraped = toString(csv_row[4], '-infinity')

        self.name = csv_row[5]
        self.description = csv_row[6]
        self.neighborhood_overview = csv_row[7]
        self.picture_url = csv_row[8]

        self.host_id = toInt(csv_row[9])
        self.host_url = csv_row[10]
        self.host_name = csv_row[11]
        self.host_since = toString(csv_row[12], 'infinity')

        self.host_location = csv_row[13]
        self.host_about = csv_row[14]
        self.host_response_time = csv_row[15]
        self.host_response_rate = toInt(csv_row[16])
        self.host_acceptance_rate = toInt(csv_row[17])
        self.host_is_superhost = csv_row[18] == 't'
        self.host_thumbnail_url = csv_row[19]
        self.host_picture_url = csv_row[20]
        self.host_neighbourhood = csv_row[21]
        self.host_listings_count = toInt(csv_row[22])
        self.host_total_listings_count = toInt(csv_row[23])
        self.host_verifications = literal_eval(csv_row[24])  # array
        self.host_has_profile_pic = csv_row[25] == 't'
        self.host_identity_verified = csv_row[26] == 't'
        self.neighbourhood = csv_row[27]

        # self.location = csv_row[]
        self.latitude = float(csv_row[28])
        self.longitude = float(csv_row[29])

        self.property_type = csv_row[30]
        self.room_type = csv_row[31]
        self.accommodates = toInt(csv_row[32])
        self.bathrooms = toInt(csv_row[33])
        self.bathrooms_text = csv_row[34]
        self.bedrooms = toInt(csv_row[35])
        self.beds = toInt(csv_row[36])
        self.amenities = literal_eval(csv_row[37])  # array
        self.price = toDecimal(csv_row[38])
        self.minimum_nights = toInt(csv_row[39])
        self.maximum_nights = toInt(csv_row[40])
        self.minimum_minimum_nights = toInt(csv_row[41])
        self.maximum_minimum_nights = toInt(csv_row[42])
        self.minimum_maximum_nights = toInt(csv_row[43])
        self.maximum_maximum_nights = toInt(csv_row[44])
        self.minimum_nights_avg_ntm = toInt(csv_row[45])
        self.maximum_nights_avg_ntm = toInt(csv_row[46])
        self.calendar_updated = csv_row[47]

        self.has_availability = csv_row[48] == 't'
        self.availability_30 = toInt(csv_row[49])
        self.availability_60 = toInt(csv_row[50])
        self.availability_90 = toInt(csv_row[51])
        self.availability_365 = toInt(csv_row[52])
        self.calendar_last_scraped = toString(csv_row[53], '-infinity')
        self.number_of_reviews = toInt(csv_row[54])
        self.number_of_reviews_ltm = toInt(csv_row[55])
        self.number_of_reviews_l30d = toInt(csv_row[56])
        self.first_review = toString(csv_row[57], '-infinity')
        self.last_review = toString(csv_row[58], '-infinity')
        self.review_scores_rating = toInt(csv_row[59])
        self.review_scores_accuracy = toInt(csv_row[60])
        self.review_scores_cleanliness = toInt(csv_row[61])
        self.review_scores_checkin = toInt(csv_row[62])
        self.review_scores_communication = toInt(csv_row[63])
        self.review_scores_location = toInt(csv_row[64])
        self.review_scores_value = toInt(csv_row[65])

        self.requires_license = csv_row[66] == 't'
        self.license = csv_row[67]
        self.instant_bookable = csv_row[68] == 't'
        self.calculated_host_listings_count = toInt(csv_row[69])
        self.calculated_host_listings_count_entire_homes = toInt(csv_row[70])
        self.calculated_host_listings_count_private_rooms = toInt(csv_row[71])
        self.calculated_host_listings_count_shared_rooms = toInt(csv_row[72])
        self.region_id = toInt(csv_row[73])
        self.region_name = csv_row[74]
        self.region_parent_id = toInt(csv_row[75])
        self.region_parent_name = csv_row[76]
        self.region_parent_parent_id = toInt(csv_row[77])
        self.region_parent_parent_name = csv_row[78]
        self.reviews_per_month = toDecimal(csv_row[79])

    def insert(self, cursor=None):
        sql = """INSERT INTO "listing"(id,listing_url,scrape_id,last_searched,last_scraped,name,description,neighborhood_overview,picture_url,host_id,host_url,host_name,host_since,host_location,host_about,host_response_time,host_response_rate,host_acceptance_rate,host_is_superhost,host_thumbnail_url,host_picture_url,host_neighbourhood,
        host_listings_count,host_total_listings_count,host_verifications,host_has_profile_pic,host_identity_verified,
        neighbourhood, location, latitude, longitude,
        property_type,room_type,accommodates,bathrooms,bathrooms_text,bedrooms,beds,amenities,price,
        minimum_nights,maximum_nights,minimum_minimum_nights,maximum_minimum_nights,minimum_maximum_nights,maximum_maximum_nights,
        minimum_nights_avg_ntm,maximum_nights_avg_ntm,calendar_updated,
        has_availability,availability_30,availability_60,availability_90,availability_365,calendar_last_scraped,number_of_reviews,number_of_reviews_ltm,number_of_reviews_l30d,first_review,last_review,review_scores_rating,review_scores_accuracy,review_scores_cleanliness,review_scores_checkin,review_scores_communication,review_scores_location,review_scores_value,
        requires_license,license,instant_bookable,calculated_host_listings_count,calculated_host_listings_count_entire_homes,calculated_host_listings_count_private_rooms,calculated_host_listings_count_shared_rooms,region_id,region_name,region_parent_id,region_parent_name,region_parent_parent_id,region_parent_parent_name,reviews_per_month)
        VALUES(%s, %s, %s, %s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s, %s, %s, %s, %s,
        POINT(%s, %s), %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s);"""
        try:
            cursor.execute(sql, (
                self.id, self.listing_url, self.scrape_id, self.last_searched, self.last_scraped, self.name,
                self.description,
                self.neighborhood_overview, self.picture_url, self.host_id, self.host_url, self.host_name,
                self.host_since,
                self.host_location, self.host_about, self.host_response_time, self.host_response_rate,
                self.host_acceptance_rate, self.host_is_superhost, self.host_thumbnail_url, self.host_picture_url,
                self.host_neighbourhood, self.host_listings_count, self.host_total_listings_count,
                self.host_verifications,
                self.host_has_profile_pic, self.host_identity_verified, self.neighbourhood,
                self.latitude, self.longitude, self.latitude, self.longitude,
                self.property_type, self.room_type, self.accommodates, self.bathrooms, self.bathrooms_text,
                self.bedrooms,
                self.beds, self.amenities, self.price,
                self.minimum_nights, self.maximum_nights, self.minimum_minimum_nights, self.maximum_minimum_nights,
                self.minimum_maximum_nights,
                self.maximum_maximum_nights, self.minimum_nights_avg_ntm, self.maximum_nights_avg_ntm,
                self.calendar_updated,
                self.has_availability, self.availability_30, self.availability_60, self.availability_90,
                self.availability_365, self.calendar_last_scraped, self.number_of_reviews, self.number_of_reviews_ltm,
                self.number_of_reviews_l30d, self.first_review, self.last_review, self.review_scores_rating,
                self.review_scores_accuracy, self.review_scores_cleanliness, self.review_scores_checkin,
                self.review_scores_communication, self.review_scores_location, self.review_scores_value,
                self.requires_license, self.license, self.instant_bookable, self.calculated_host_listings_count,
                self.calculated_host_listings_count_entire_homes, self.calculated_host_listings_count_private_rooms,
                self.calculated_host_listings_count_shared_rooms, self.region_id, self.region_name,
                self.region_parent_id,
                self.region_parent_name, self.region_parent_parent_id, self.region_parent_parent_name,
                self.reviews_per_month
            ))
        except Exception as error:
            print(self.raw_csv_row)
            print("Exception:", error)

class Review:
    def __init__(self, csv_row):
        self.raw_csv_row = csv_row
        self.listing_id = toInt(csv_row[0])
        self.id = toInt(csv_row[1])
        self.date = toString(csv_row[2], '+infinity')
        self.reviewer_id = toInt(csv_row[3])
        self.reviewer_name = csv_row[4]
        self.comments = csv_row[5]

    def insert(self, cursor=None):
        sql = """INSERT INTO "review"(listing_id,id,date,reviewer_id,reviewer_name,comments)
        VALUES(%s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING;"""
        try:
            cursor.execute(sql, (
                self.listing_id, self.id, self.date, self.reviewer_id, self.reviewer_name, self.comments
            ))
        except Exception as error:
            print(self.raw_csv_row)
            print("Exception:", error)


class Calendar:
    def __init__(self, csv_row):
        self.raw_csv_row = csv_row
        self.listing_id = toInt(csv_row[0])
        self.date = toString(csv_row[1], '+infinity')
        self.available = csv_row[2] == 't'
        self.price = toDecimal(csv_row[3])
        self.adjusted_price = toDecimal(csv_row[4])
        self.minimum_nights = toInt(csv_row[5])
        self.maximum_nights = toInt(csv_row[6])

    def insert(self, cursor=None):
        sql = """INSERT INTO "airdata"."calendar"(listing_id,date,available,price,adjusted_price,minimum_nights,maximum_nights)
        VALUES(%s, %s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING;"""
        try:
            cursor.execute(sql, (
                self.listing_id, self.date, self.available, self.price, self.adjusted_price, self.minimum_nights,
                self.maximum_nights
            ))
        except Exception as error:
            print(self.raw_csv_row)
            print("Exception:", error)


def toInt(s):
    cs = sub("[^0-9-.]", "", s)
    try:
        conv = int(cs)
    except:
        conv = 0
    return conv


def toDecimal(s):
    cs = sub("[^0-9.-]", "", s)
    try:
        conv = float(cs)
    except:
        conv = 0
    return conv


def badString(s):
    if not s: return True
    return s.isspace() or s == ''


def toString(s, default):
    return default if badString(s) else s
	import csv
	import psycopg2
	from ast import literal_eval
	from re import sub

	def connect():
	connection = psycopg2.connect(user="xxx",
	password="xxx",
	host="xxx",
	port="5432",
	database="dbname")
	return connection


	def upload_to_table(filename, table):
	"""This method reads files from ./data/filename, applies transformations from table, and inserts the result into the
	database """
	conn = connect()
	c = conn.cursor()
	file = open(filename, "r")
	reader = csv.reader(file, delimiter=',')
	next(reader, None) # skip the headers
	for row in reader:
	table(row).insert(cursor=c)
	conn.commit()
	c.close()

	upload_to_table("data/au/listings.csv", Listing)
	upload_to_table("data/au/reviews.csv", Review)
	upload_to_table("data/au/calendar.csv", Calendar)


	## CLASS DEFINITIONS BELOW


	class Listing:
	def __init__(self, csv_row):
	self.raw_csv_row = csv_row
	self.id = toInt(csv_row[0])
	self.listing_url = csv_row[1]
	self.scrape_id = toInt(csv_row[2])
	self.last_searched = toString(csv_row[3], '-infinity')
	self.last_scraped = toString(csv_row[4], '-infinity')

	self.name = csv_row[5]
	self.description = csv_row[6]
	self.neighborhood_overview = csv_row[7]
	self.picture_url = csv_row[8]

	self.host_id = toInt(csv_row[9])
	self.host_url = csv_row[10]
	self.host_name = csv_row[11]
	self.host_since = toString(csv_row[12], 'infinity')

	self.host_location = csv_row[13]
	self.host_about = csv_row[14]
	self.host_response_time = csv_row[15]
	self.host_response_rate = toInt(csv_row[16])
	self.host_acceptance_rate = toInt(csv_row[17])
	self.host_is_superhost = csv_row[18] == 't'
	self.host_thumbnail_url = csv_row[19]
	self.host_picture_url = csv_row[20]
	self.host_neighbourhood = csv_row[21]
	self.host_listings_count = toInt(csv_row[22])
	self.host_total_listings_count = toInt(csv_row[23])
	self.host_verifications = literal_eval(csv_row[24]) # array
	self.host_has_profile_pic = csv_row[25] == 't'
	self.host_identity_verified = csv_row[26] == 't'
	self.neighbourhood = csv_row[27]

	# self.location = csv_row[]
	self.latitude = float(csv_row[28])
	self.longitude = float(csv_row[29])

	self.property_type = csv_row[30]
	self.room_type = csv_row[31]
	self.accommodates = toInt(csv_row[32])
	self.bathrooms = toInt(csv_row[33])
	self.bathrooms_text = csv_row[34]
	self.bedrooms = toInt(csv_row[35])
	self.beds = toInt(csv_row[36])
	self.amenities = literal_eval(csv_row[37]) # array
	self.price = toDecimal(csv_row[38])
	self.minimum_nights = toInt(csv_row[39])
	self.maximum_nights = toInt(csv_row[40])
	self.minimum_minimum_nights = toInt(csv_row[41])
	self.maximum_minimum_nights = toInt(csv_row[42])
	self.minimum_maximum_nights = toInt(csv_row[43])
	self.maximum_maximum_nights = toInt(csv_row[44])
	self.minimum_nights_avg_ntm = toInt(csv_row[45])
	self.maximum_nights_avg_ntm = toInt(csv_row[46])
	self.calendar_updated = csv_row[47]

	self.has_availability = csv_row[48] == 't'
	self.availability_30 = toInt(csv_row[49])
	self.availability_60 = toInt(csv_row[50])
	self.availability_90 = toInt(csv_row[51])
	self.availability_365 = toInt(csv_row[52])
	self.calendar_last_scraped = toString(csv_row[53], '-infinity')
	self.number_of_reviews = toInt(csv_row[54])
	self.number_of_reviews_ltm = toInt(csv_row[55])
	self.number_of_reviews_l30d = toInt(csv_row[56])
	self.first_review = toString(csv_row[57], '-infinity')
	self.last_review = toString(csv_row[58], '-infinity')
	self.review_scores_rating = toInt(csv_row[59])
	self.review_scores_accuracy = toInt(csv_row[60])
	self.review_scores_cleanliness = toInt(csv_row[61])
	self.review_scores_checkin = toInt(csv_row[62])
	self.review_scores_communication = toInt(csv_row[63])
	self.review_scores_location = toInt(csv_row[64])
	self.review_scores_value = toInt(csv_row[65])

	self.requires_license = csv_row[66] == 't'
	self.license = csv_row[67]
	self.instant_bookable = csv_row[68] == 't'
	self.calculated_host_listings_count = toInt(csv_row[69])
	self.calculated_host_listings_count_entire_homes = toInt(csv_row[70])
	self.calculated_host_listings_count_private_rooms = toInt(csv_row[71])
	self.calculated_host_listings_count_shared_rooms = toInt(csv_row[72])
	self.region_id = toInt(csv_row[73])
	self.region_name = csv_row[74]
	self.region_parent_id = toInt(csv_row[75])
	self.region_parent_name = csv_row[76]
	self.region_parent_parent_id = toInt(csv_row[77])
	self.region_parent_parent_name = csv_row[78]
	self.reviews_per_month = toDecimal(csv_row[79])

	def insert(self, cursor=None):
	sql = """INSERT INTO "listing"(id,listing_url,scrape_id,last_searched,last_scraped,name,description,neighborhood_overview,picture_url,host_id,host_url,host_name,host_since,host_location,host_about,host_response_time,host_response_rate,host_acceptance_rate,host_is_superhost,host_thumbnail_url,host_picture_url,host_neighbourhood,
	host_listings_count,host_total_listings_count,host_verifications,host_has_profile_pic,host_identity_verified,
	neighbourhood, location, latitude, longitude,
	property_type,room_type,accommodates,bathrooms,bathrooms_text,bedrooms,beds,amenities,price,
	minimum_nights,maximum_nights,minimum_minimum_nights,maximum_minimum_nights,minimum_maximum_nights,maximum_maximum_nights,
	minimum_nights_avg_ntm,maximum_nights_avg_ntm,calendar_updated,
	has_availability,availability_30,availability_60,availability_90,availability_365,calendar_last_scraped,number_of_reviews,number_of_reviews_ltm,number_of_reviews_l30d,first_review,last_review,review_scores_rating,review_scores_accuracy,review_scores_cleanliness,review_scores_checkin,review_scores_communication,review_scores_location,review_scores_value,
	requires_license,license,instant_bookable,calculated_host_listings_count,calculated_host_listings_count_entire_homes,calculated_host_listings_count_private_rooms,calculated_host_listings_count_shared_rooms,region_id,region_name,region_parent_id,region_parent_name,region_parent_parent_id,region_parent_parent_name,reviews_per_month)
	VALUES(%s, %s, %s, %s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s, %s, %s, %s, %s,
	POINT(%s, %s), %s, %s,
	%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
	%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
	%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s);"""
	try:
	cursor.execute(sql, (
	self.id, self.listing_url, self.scrape_id, self.last_searched, self.last_scraped, self.name,
	self.description,
	self.neighborhood_overview, self.picture_url, self.host_id, self.host_url, self.host_name,
	self.host_since,
	self.host_location, self.host_about, self.host_response_time, self.host_response_rate,
	self.host_acceptance_rate, self.host_is_superhost, self.host_thumbnail_url, self.host_picture_url,
	self.host_neighbourhood, self.host_listings_count, self.host_total_listings_count,
	self.host_verifications,
	self.host_has_profile_pic, self.host_identity_verified, self.neighbourhood,
	self.latitude, self.longitude, self.latitude, self.longitude,
	self.property_type, self.room_type, self.accommodates, self.bathrooms, self.bathrooms_text,
	self.bedrooms,
	self.beds, self.amenities, self.price,
	self.minimum_nights, self.maximum_nights, self.minimum_minimum_nights, self.maximum_minimum_nights,
	self.minimum_maximum_nights,
	self.maximum_maximum_nights, self.minimum_nights_avg_ntm, self.maximum_nights_avg_ntm,
	self.calendar_updated,
	self.has_availability, self.availability_30, self.availability_60, self.availability_90,
	self.availability_365, self.calendar_last_scraped, self.number_of_reviews, self.number_of_reviews_ltm,
	self.number_of_reviews_l30d, self.first_review, self.last_review, self.review_scores_rating,
	self.review_scores_accuracy, self.review_scores_cleanliness, self.review_scores_checkin,
	self.review_scores_communication, self.review_scores_location, self.review_scores_value,
	self.requires_license, self.license, self.instant_bookable, self.calculated_host_listings_count,
	self.calculated_host_listings_count_entire_homes, self.calculated_host_listings_count_private_rooms,
	self.calculated_host_listings_count_shared_rooms, self.region_id, self.region_name,
	self.region_parent_id,
	self.region_parent_name, self.region_parent_parent_id, self.region_parent_parent_name,
	self.reviews_per_month
	))
	except Exception as error:
	print(self.raw_csv_row)
	print("Exception:", error)

	class Review:
	def __init__(self, csv_row):
	self.raw_csv_row = csv_row
	self.listing_id = toInt(csv_row[0])
	self.id = toInt(csv_row[1])
	self.date = toString(csv_row[2], '+infinity')
	self.reviewer_id = toInt(csv_row[3])
	self.reviewer_name = csv_row[4]
	self.comments = csv_row[5]

	def insert(self, cursor=None):
	sql = """INSERT INTO "review"(listing_id,id,date,reviewer_id,reviewer_name,comments)
	VALUES(%s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING;"""
	try:
	cursor.execute(sql, (
	self.listing_id, self.id, self.date, self.reviewer_id, self.reviewer_name, self.comments
	))
	except Exception as error:
	print(self.raw_csv_row)
	print("Exception:", error)


	class Calendar:
	def __init__(self, csv_row):
	self.raw_csv_row = csv_row
	self.listing_id = toInt(csv_row[0])
	self.date = toString(csv_row[1], '+infinity')
	self.available = csv_row[2] == 't'
	self.price = toDecimal(csv_row[3])
	self.adjusted_price = toDecimal(csv_row[4])
	self.minimum_nights = toInt(csv_row[5])
	self.maximum_nights = toInt(csv_row[6])

	def insert(self, cursor=None):
	sql = """INSERT INTO "airdata"."calendar"(listing_id,date,available,price,adjusted_price,minimum_nights,maximum_nights)
	VALUES(%s, %s, %s, %s, %s, %s, %s) ON CONFLICT DO NOTHING;"""
	try:
	cursor.execute(sql, (
	self.listing_id, self.date, self.available, self.price, self.adjusted_price, self.minimum_nights,
	self.maximum_nights
	))
	except Exception as error:
	print(self.raw_csv_row)
	print("Exception:", error)


	def toInt(s):
	cs = sub("[^0-9-.]", "", s)
	try:
	conv = int(cs)
	except:
	conv = 0
	return conv


	def toDecimal(s):
	cs = sub("[^0-9.-]", "", s)
	try:
	conv = float(cs)
	except:
	conv = 0
	return conv


	def badString(s):
	if not s: return True
	return s.isspace() or s == ''


	def toString(s, default):
	return default if badString(s) else s