def randomqueryexecution():
select1 = request.form.get('CITY').encode('ascii', 'ignore')
select2 = request.form.get('LorM').encode('ascii', 'ignore')
cursor = connection.cursor()
d = dict(enumerate(string.ascii_lowercase, 1))
pos = string.lowercase.index(select2)
columnname = ''
if pos == 11:
columnname = 'aug'
else:
columnname = 'sep'
starttime = time.time()
number = 50
sql = "Select * from newdata WHERE country_or_territory='"+select1+"' AND "+columnname+" <= '"+str(number)+"';"
cursor.execute(sql)
print('Executing Queries..')
all_data = cursor.fetchall()
stoptime = time.time()
#Calculate total time for data insertion
insert_time = stoptime - starttime
print ("Time taken to insert: " + str(insert_time) + "sec")
city_result = []
html = "<center><h2> Dynamic query result</h2></center>"
html = "<h4> Data " + str('CITY') + " : <br></h4>"
html += "<table style = 'border: 1px solid black'><tr><th>country</th> <th>count</th></tr>"
for data in all_data:
#print data["CITY"]
# if data["CITY"] == str(CITY):
html += "<tr><td>" + data[1].encode('ascii', 'ignore') + "</td><td>" + str(data[pos]) + "</td><tr>"
html += "</table>"
return html
def sql_connect(data, hashtags):
# Connect to the database
connection = pymysql.connect(host=config['host'],
user=config['user'],
password=config['password'],
db=config['db'],
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `instagram_data` (`id`, `image_link`, `image_name`, `latitude`, `longitude`, `location_name`, `bucket_url`) VALUES (%s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql, (data[0][2], data[0][0], data[0][1], data[1],
data[2], data[3], data[5]))
if len(hashtags) > 0:
for i in range(len(hashtags)):
sql = "INSERT INTO `instagram_data_tags` (`image_name`, `tag`) VALUES (%s, %s)"
cursor.execute(sql, (data[0][2], hashtags[i]))
connection.commit()
finally:
connection.close()
return
def query():
cursor = connection.cursor()
sql = "Select DISTINCT country_or_territory from newdata"
cursor.execute(sql)
values = cursor.fetchall()
array = []
for value in values:
print (str(value[0].encode('ascii', 'ignore')))
array.append(str(value[0].encode('ascii', 'ignore')))
#print array
return render_template("dynamic_query.html", array = array)
def randomQueryGeneration(total):
cursor = connection.cursor()
sql = "USE cloudDB"
cursor.execute(sql)
print('Executing Queries..')
range_value = range(1,total)
start = time.time()
for count in range_value:
#id = random.randint(1000, 300000)
id=4000
query = "Select iddata from data where iddata = " + str(id+count)
cursor.execute(query)
#print "query no : " + str(count)
end = time.time()
taken_time = end - start
print ("time taken for "+str(total)+" queries without memcache : " + str(taken_time) + " Seconds.")
def randomlargetuple(number):
cursor = connection.cursor()
sql = "USE cloudDB"
cursor.execute(sql)
print ("Executing Queries")
#query
#it will generate random queries
range_value = range(1,number)
start = time.time()
for count in range_value:
id = random.triangular(1000, 300000, 150000) #generates random key value to use for query
query = "Select iddata from data where id = " + str(id)+" LIMIT 500"
cursor.execute(query)
print ("qeury no : " + str(count))
end = time.time()
taken_time = end - start
print ("time taken for "+str(number)+" queries : " + str(taken_time) + " Seconds.")
def display(table):
cursor = connection.cursor()
sql = "USE cloudDB"
cursor.execute(sql)
display_result = "Select * from "+table+" LIMIT 20;"
cursor.execute(display_result)
rows = cursor.fetchall()
connection.commit()
html = "<table><tr><th>FIRST NAME </th> <th> Last Name</th></tr>"
for row in rows:
firstname = str(row[1])
lastname = str(row[0])
html += "<tr><td>" + firstname + "</td><td>" + lastname + "</td><tr>"
html += "</table>"
return """
<!doctype html>
<form action = "/dynamic_query" method ="post" class='form-signin' enctype = "multipart/form-data">
<input type = "submit" value="Query" />
</form>
<title>Uploaded</title>
<h1>%s</h1>
</html>
"""%html
access_key = ''
secret_key = ''
filename= "dummy1.csv"
#connecting to rds instance
cloud6331.cygo3cs6hcoz.us-west-2.rds.amazonaws.com',
'username': '',
'password': ''}
user=''
password=''
host=''
database=''
connection = mysql.connector.connect(user=user, password=password,host=host,database=database)
cursor = connection.cursor()
sql = "USE cloudDB"
cursor.execute(sql)
#inserting
print ("Successfully Connected to mySQL instance (RDS)")
filename = "dummy.csv"
bucket = 'cloud6331-assign3'
conn = boto.connect_s3(access_key, secret_key)
rs = conn.get_all_buckets()
for b in rs:
print (b)
nonexistent = conn.lookup(bucket)
if nonexistent is None:
print ('Not there!')
bucket = conn.get_bucket(bucket, validate=True)