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)