def post_review(place_id, user_id, review, rating):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO review (placeID, userID, reviewDate, review,rating) VALUES(%s, %s,now(), %s, %s)"
cur.execute(sql_string, (place_id, user_id, review, rating))
conn.commit()
cur.close()
conn.close()
def signup(username, email, password):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO users (userName, email, password) VALUES(%s, %s, %s)"
cur.execute(sql_string, (username, email, password))
conn.commit()
cur.close()
conn.close()
def get_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM users WHERE userID= %s"
cur.execute(sql_string, (user_id, ))
info = cur.fetchone()
conn.close()
return info[1]
def block_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "UPDATE users SET isBlock = 1 WHERE userID = %s "
cur.execute(sql_string, (user_id, ))
conn.commit()
cur.close()
conn.close()
def approve_place(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "UPDATE places SET approved = 1 WHERE placeID = %s "
cur.execute(sql_string, (place_id, ))
conn.commit()
cur.close()
conn.close()
def get_email(email):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM users WHERE email= %s"
cur.execute(sql_string, (email, ))
info = cur.fetchone()
conn.close()
return info
def get_by_username(username):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM users WHERE userName= %s"
cur.execute(sql_string, (username, ))
info = cur.fetchone()
conn.close()
return info
def delete_review(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "DELETE FROM review WHERE placeID = %s"
cur.execute(sql_string, (place_id, ))
conn.commit()
cur.close()
conn.close()
def get_place_review(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM review WHERE placeID =%s ORDER BY reviewDate DESC"
cur.execute(sql_string, (place_id, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def get_place_by_name(name):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE name = %s"
cur.execute(sql_string, (name, ))
info = cur.fetchone()
cur.close()
conn.close()
return info
def get_review_by_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM review WHERE userID =%s ORDER BY reviewDate DESC"
cur.execute(sql_string, (user_id, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def get_to_approve():
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE approved = false ORDER BY postedDate DESC"
cur.execute(sql_string, )
info = cur.fetchall()
cur.close()
conn.close()
return info
def get_place_by_id(place_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE placeID = %s"
cur.execute(sql_string, (place_id, ))
info = cur.fetchone()
cur.close()
conn.close()
return info
def get_all_place_user(user_id):
conn.connect()
cur = conn.cursor()
sql_string = "SELECT placeID,image_location FROM places WHERE userID =%s ORDER BY postedDate DESC"
cur.execute(sql_string, (user_id, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
def new_place(user_id, name, address, website, phone, image_loc, description):
conn.connect()
cur = conn.cursor()
sql_string = "INSERT INTO places (userID, name, address, website, phone, image_location, description, postedDate) VALUES (%s, %s, %s, %s, %s, %s, %s, now())"
cur.execute(
sql_string,
(user_id, name, address, website, phone, image_loc, description))
conn.commit()
cur.close()
conn.close()
def get_place_by_name1(name):
name = '%' + name + '%'
conn.connect()
cur = conn.cursor()
sql_string = "SELECT * FROM places WHERE name LIKE %s"
cur.execute(sql_string, (name, ))
info = cur.fetchall()
cur.close()
conn.close()
return info
for row in data:
achid = str(row[0])
stamp = str(row[1])
URL = Host + "/wow/achievement/" + achid + "?fields=news&locale=en_US&apikey=" + key
r = requests.get(URL)
c = r.json()
achname = c["title"]
POST = (
"Wiping with Style has completed: %s \n http://www.wowhead.com/achievement=%s #worldofwarcraft #achievement"
% (achname, achid)
)
print POST
logging.warning("Posting: %s" % POST)
try:
os.system('python /var/www/wwsguild/twitter-scripts/posttwit.py -m "%s"' % POST)
except:
print "Error"
raise
fixql = "UPDATE wwspost.news SET posted='1' WHERE stamp =" + stamp
try:
x.execute(fixql)
logging.warning("Updating: %s" % fixql)
conn.commit()
except mdb.Error, e:
try:
print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
except IndexError:
print "MySQL Error: %s" % str(e)
conn.rollback()
conn.close()
group_id SERIAL NOT NULL REFERENCES Dim_Group (id),
name VARCHAR(200) NOT NULL,
min FLOAT,
max FLOAT,
units VARCHAR(200) NOT NULL
)
"""
cur.execute(query4)
print("Table -> Dim_Type_Analysis is created successfully")
#Query5: Create Fact_Observation table.
query5 = """
CREATE TABLE Fact_Observation
(
patient_id SERIAL NOT NULL REFERENCES Dim_Patient (id),
sample_date_id timestamp NOT NULL REFERENCES Dim_Date (sample_timestamp),
type_analysis_id VARCHAR(200) NOT NULL REFERENCES Dim_Type_Analysis (code),
result_value FLOAT NOT NULL,
relative_discrepancy FLoat,
consequence VARCHAR(200)
)
"""
cur.execute(query5)
print("Table -> Fact_Observation is created successfully")
# Close the cursor
cur.close()
# Close the connection
conn.close()
