def insert(reviews):
if not isinstance(reviews, collections.Iterable):
reviews = [reviews]
wines = []
tasters = {}
# define the query
for w in reviews:
wines.append("(" + parse_str(w.points) + ",'" + parse_str(w.title) +
"','" + parse_str(w.variety) + "','" +
parse_str(w.description) + "','" + parse_str(w.country) +
"','" + parse_str(w.province) + "','" +
parse_str(w.region1) + "','" + parse_str(w.region2) +
"','" + parse_str(w.winery) + "','" +
parse_str(w.designation) + "'," + parse_str(w.price) +
",'" + parse_str(w.taster.twitter) + "')")
if w.taster.twitter is not None:
tasters[w.taster.twitter] = "('" + parse_str(
w.taster.twitter) + "','" + parse_str(w.taster.name) + "')"
wines_queries = []
# split the query in multiple part to prevent the query to be too big
for subwines in utilities.split_list(
wines, int(utilities.byte_size(wines) /
1048576)): # 1048576 is the default size of mysql query
wines_query = "INSERT INTO Wines(points, title, variety, description, country, province, region1, region2, " \
"winery, designation, price, taster_twitter) VALUES"+ ",".join(subwines) + ";"
wines_queries.append(wines_query)
tasters_query = "INSERT INTO Tasters(twitter, name) VALUES" + ",".join(str(x) for x in tasters.values())\
+" ON DUPLICATE KEY UPDATE name=name;"
# execute the queries
mysql.query_db(tasters_query)
for wines_query in wines_queries:
mysql.query_db(wines_query)
def update(reviews):
if not isinstance(reviews, collections.Iterable):
reviews = [reviews]
wines = []
tasters = {}
# define the query
for wine in reviews:
wines.append("SET points=" + parse_str(wine.points) + ",variety='" +
parse_str(wine.variety) + "',description='" +
parse_str(wine.description) + "',country='" +
parse_str(wine.country) + "',province='" +
parse_str(wine.province) + "',region1='" +
parse_str(wine.region1) + "',region2='" +
parse_str(wine.region2) + "',winery='" +
parse_str(wine.winery) + "',designation='" +
parse_str(wine.designation) + "',price=" +
parse_str(wine.price) + ",taster_twitter='" +
parse_str(wine.taster.twitter) + "' WHERE id=" +
parse_str(wine.id))
wines_queries = []
# split the query in multiple part to prevent the query to be too big
for subwines in utilities.split_list(
wines, int(utilities.byte_size(wines) /
1048576)): # 1048576 is the default size of mysql query
for w in subwines:
wines_query = "UPDATE Wines " + w + ";"
mysql.query_db(wines_query)
def create():
# First create the database
tasters_query = "CREATE TABLE Tasters(" \
"twitter VARCHAR(50) NOT NULL UNIQUE, " \
"name VARCHAR(100), " \
"PRIMARY KEY (twitter));"
wines_query = "CREATE TABLE Wines(" \
"id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY," \
"points INT(6) UNSIGNED," \
"title VARCHAR(300)," \
"variety VARCHAR(100)," \
"description VARCHAR(1000)," \
"country VARCHAR(50)," \
"province VARCHAR(50)," \
"region1 VARCHAR(50)," \
"region2 VARCHAR(50)," \
"winery VARCHAR(300)," \
"designation VARCHAR(100)," \
"price INT(6) UNSIGNED," \
"taster_twitter VARCHAR(50)," \
"FOREIGN KEY (taster_twitter) REFERENCES Tasters(twitter)" \
");"
mysql.query_db(tasters_query)
mysql.query_db(wines_query)
def delete(table_id, ids):
tablename = {
1: "Wines",
2: "Tasters",
}[table_id]
parsed_ids = map(lambda x: parse_str(x), ids)
query = "DELETE FROM " + tablename + " WHERE id IN (" + ",".join(
parsed_ids) + ");"
mysql.query_db(query)
def getall():
reviews = []
query = "SELECT id, points, title,variety, description, country, province, region1, region2, winery, " \
"designation, price, twitter, name " \
"FROM Wines LEFT JOIN Tasters ON twitter = taster_twitter"
raw_reviews = mysql.query_db(query)
for raw in raw_reviews:
w = wine.Wine(raw[1], raw[2], raw[3], raw[4], raw[5], raw[6], raw[7],
raw[8], raw[9], raw[10], raw[11], raw[12], raw[13])
w.set_id(raw[0])
reviews.append(w)
return reviews
def get(q):
return mysql.query_db(q)
def drop():
tasters_query = "DROP TABLE Tasters;"
wines_query = "DROP TABLE Wines;"
mysql.query_db(wines_query)
mysql.query_db(tasters_query)