def save_data(elements):
prepared_stmt = """
INSERT INTO fact_exchange (
id_dim_date,
id_dim_exchange,
volume,
symbol
)
VALUES (%s, %s, %s, %s);
"""
con = Database().connect()
cursor = con.cursor()
for e in elements:
# Builds the INSERT statement parameters
parameters = (
e.id_dim_date,
e.id_dim_exchange,
e.volume,
e.symbol
)
logging.debug('Saving transaction = ' + str(parameters))
# Execute the INSERT statement within the transaction
cursor.execute(prepared_stmt, parameters)
# Final transaction COMMIT
con.commit()
def save_data(elements):
prepared_stmt = """
INSERT INTO dim_coin (
id_dim_coin,
name,
symbol,
coin_name,
full_name
)
VALUES (%s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
name = %s,
symbol = %s,
coin_name = %s,
full_name = %s
"""
con = Database().connect()
cursor = con.cursor()
for e in elements:
# Builds the INSERT statement parameters
parameters = (e.coin_id, e.name, e.symbol, e.coin_name, e.full_name,
e.name, e.symbol, e.coin_name, e.full_name)
logging.debug('Saving coin = ' + str(parameters))
# Execute the INSERT statement within the transaction
cursor.execute(prepared_stmt, parameters)
# Final transaction COMMIT
con.commit()
def save_data(elements):
prepared_stmt = """
INSERT IGNORE INTO fact_coin (
id_dim_date,
id_dim_time,
id_dim_coin,
volume_from_usd,
volume_to_usd,
close,
open,
high,
low,
revenue
)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
"""
con = Database().connect()
cursor = con.cursor()
for e in elements:
# Builds the INSERT statement parameters
parameters = (e.id_dim_date, e.id_dim_coin, e.id_dim_time,
e.volume_from_usd, e.volume_to_usd, e.open, e.close,
e.low, e.high, e.revenue)
logging.debug('Saving transaction = ' + str(parameters))
# Execute the INSERT statement within the transaction
cursor.execute(prepared_stmt, parameters)
# Final transaction COMMIT
con.commit()
def save_data(elements):
prepared_stmt = """
INSERT INTO dim_exchange (
id_dim_exchange,
name,
url,
country,
centralization_type,
internal_name
)
VALUES (%s, %s, %s, %s, %s, %s)
ON DUPLICATE KEY UPDATE
name = %s,
url = %s,
country = %s,
centralization_type = %s,
internal_name = %s
"""
con = Database().connect()
cursor = con.cursor()
for e in elements:
# Builds the INSERT statement parameters
parameters = (e.id_dim_exchange, e.name, e.url, e.country,
e.centralization_type, e.internal_name, e.name, e.url,
e.country, e.centralization_type, e.internal_name)
logging.debug('Saving exchange = ' + str(parameters))
# Execute the INSERT statement within the transaction
cursor.execute(prepared_stmt, parameters)
# Final transaction COMMIT
con.commit()
def get_id_dim_date(date):
prepared_stmt = 'SELECT id_dim_date FROM dim_date WHERE date = DATE(%s)'
parameter = [date]
con = Database().connect()
cursor = con.cursor()
cursor.execute(prepared_stmt, parameter)
data = cursor.fetchone()
return data[0] if len(data) else None
def get_id_dim_coin(coin):
prepared_stmt = 'SELECT id_dim_coin FROM dim_coin WHERE symbol = %s'
parameter = [coin]
con = Database().connect()
cursor = con.cursor()
cursor.execute(prepared_stmt, parameter)
data = cursor.fetchone()
return data[0] if len(data) else None
def get_id_dim_time(time):
prepared_stmt = 'SELECT id_dim_time FROM dim_time WHERE time = %s'
parameter = [time]
con = Database().connect()
cursor = con.cursor()
cursor.execute(prepared_stmt, parameter)
data = cursor.fetchone()
return data[0] if len(data) else None
class Service:
def __init__(self):
self.__db = Database("./data/database.db")
self.current_customer_id = ""
def create_customer(self, customer_id, name, phone_no):
customer = Customer(self.__db, customer_id, name, phone_no)
# Returns false if the customer already exists to inform the client.
# Returns true for a successful creation.
return not customer.in_db
def set_customer(self, customer_id):
customer = Customer(self.__db, customer_id)
if customer.in_db:
self.current_customer_id = customer.customer_id
def make_bookmark(self, location_id):
Bookmark(self.__db, self.current_customer_id, location_id)
def make_reservation(self, location_id, start, end):
reservation = Reservation(db=self.__db, customer_id=self.current_customer_id,
location_id=location_id, start=start, end=end)
if reservation.inserted:
return self.__db.get_last_insert_id()
else:
return False
def update_reservation_end(self, reservation_id, new_end):
reservation = Reservation(db=self.__db, reservation_id=reservation_id)
reservation.update_end(new_end)
def get_location_details(self, location_id):
location = Location(self.__db, location_id)
if location.exists:
return [location.name, location.address, location.email]
else:
return False
def checkout(self):
customer = Customer(self.__db, self.current_customer_id)
if customer.get_no_of_reservations() > 0:
return True
else:
return False
def get_all_location_ids(self):
query_string = "SELECT id FROM Location"
return self.__db.read(query_string, ())
def reset_db(self):
self.__db.reset_db()
def main():
rPath = "training/yelp_training_set_review.json"
parser = RichReviewJSONParser()
print 'Read all reviews from json....'
reviews = parser.getReviews(rPath)
db = Database(reviews)
print "Finished"
print db.statistcInfo()
print "Get subset of reviews..."
db.filter(20,10)
print "Finished"
print "Write to files..."
db.richReviewToFile("training.txt","test.txt",10)
print "Finished"
db.ratingReport("user.txt","business.txt")
def get_exchanges():
con = Database().connect()
cursor = con.cursor()
cursor.execute('SELECT id_dim_exchange, internal_name FROM dim_exchange')
data = cursor.fetchall()
exchanges = []
for value in data:
# Builds the "Exchange" object from the response, and appends to the exchange list
exchanges.append(
Exchange(
value[0], # id_dim_exchange
None,
None,
None,
None,
value[1] # internal_name
)
)
return exchanges
from util.Database import Database
import sys
# This script is for filtering the original dataset
# It will preserve those users that have more than 20 reviews
# and businesses that have more than 10 reviews
if __name__ == '__main__':
# File that saves all filtered reviews
rout = sys.argv[1]
# File that saves all users in reviews
uout = sys.argv[2]
# File that saves all business in reviews
bout = sys.argv[3]
rPath = "training/yelp_training_set_review.json"
print 'Start loading original dataset'
parser = ReviewJSONParser()
reviews = parser.getReviews(rPath)
db = Database(reviews)
print 'Finished'
print db.statistcInfo()
print 'Start filter data'
db.filter(20, 10)
print 'Finished'
db.reviewToFile(rout)
db.ratingReport(uout, bout)
import sys # This script is for filtering the original dataset # It will preserve those users that have more than 20 reviews # and businesses that have more than 10 reviews if __name__ == '__main__': # File that saves all filtered reviews rout = sys.argv[1] # File that saves all users in reviews uout = sys.argv[2] # File that saves all business in reviews bout = sys.argv[3] rPath = "training/yelp_training_set_review.json" print 'Start loading original dataset' parser = ReviewJSONParser() reviews = parser.getReviews(rPath) db = Database(reviews) print 'Finished' print db.statistcInfo() print 'Start filter data' db.filter(20,10) print 'Finished' db.reviewToFile(rout) db.ratingReport(uout,bout)
def __init__(self):
self.__db = Database("./data/database.db")
self.current_customer_id = ""