def map_user_and_value_from_age_range(age_min, age_max, type):
connection = connection_db.connect()
try:
cursor = connection.cursor()
postgreSQL_select_Query = "select dataset.value from dataset join users on dataset.userid=users.userid where dataset.type=" + str(
type) + " and users.age<" + str(age_max) + " and users.age>" + str(
age_min)
cursor.execute(postgreSQL_select_Query)
results = cursor.fetchall()
results = np.squeeze(np.asarray(results))
results = results.tolist()
return results
#map=defaultdict(list)
#for k,value in results:
# map[k].append(value)
#return map
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def pull_2types_from_range_age(type_1, type_2, age_min, age_max):
connection = connection_db.connect()
try:
cursor = connection.cursor()
postgreSQL_select_Query = "select query3.value1, query3.value2 " +\
"from(" +\
" select value1, value2, query1.userid " + \
" from ( " +\
" select userid, avg(value) as value1 " +\
" from dataset " +\
" where type=" + str(type_1) + \
" group by userid) as query1 join (" +\
" select userid, avg(value) as value2 " +\
" from dataset " +\
" where type=" + str(type_2) +\
" group by userid) as query2 " +\
" on query1.userid = query2.userid) as query3 join users on query3.userid = users.userid " +\
"where users.age<"+str(age_max)+" and users.age>"+str(age_min)
cursor.execute(postgreSQL_select_Query)
results = cursor.fetchall()
return results
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def prova():
connection = connection_db.connect()
try:
cursor = connection.cursor()
postgreSQL_select_Query = "select value from dataset where userid='102b72d02aebae82c5c1ee3d4dcbc4d82de7c7ee' and type=5"
cursor.execute(postgreSQL_select_Query)
results = cursor.fetchall()
results = np.squeeze(np.asarray(results))
return results
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def pull_2types_values(type_1, type_2):
try:
connection = connection_db.connect()
cursor = connection.cursor()
postgreSQL_select_Query = "select value1, value2 from ( select userid, avg(value) as value1 from dataset where type="+str(type_1)+\
"group by userid) as query1 join (select userid, avg(value) as value2 from dataset where type="+str(type_2)+\
"group by userid) as query2 on query1.userid = query2.userid "
cursor.execute(postgreSQL_select_Query)
results = cursor.fetchall()
return results
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def pull_1type_from_range_age(type, age_min, age_max):
connection = connection_db.connect()
try:
cursor = connection.cursor()
postgreSQL_select_Query = "select query.media from(select avg(dataset.value) as media, dataset.userid from users join dataset on users.userid = dataset.userid where dataset.type=" + str(
type) + " and users.age<" + str(age_max) + " and users.age>" + str(
age_min) + "group by dataset.userid) as query"
cursor.execute(postgreSQL_select_Query)
results = cursor.fetchall()
return results
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def take_typ5(type):
connection = connection_db.connect()
try:
cursor = connection.cursor()
postgreSQL_select_Query = "select value, userid from dataset where type=5 order by dataset.userid"
cursor.execute(postgreSQL_select_Query)
data = cursor.fetchall()
output = open("list_age_userid.txt", "w+")
for i in data:
output.write(str(i[0]))
output.write("\t%s\n" % str(i[1]))
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def createFileByType_age():
try:
connection = connection_db.connect()
cursor = connection.cursor()
postgreSQL_select_Query = "select distinct users.age, users.userid from dataset join users on dataset.userid=users.userid"
cursor.execute(postgreSQL_select_Query)
print("Selecting data from users join dataset table using cursor.fetchall")
data = cursor.fetchall()
output = open("list_age_userid.txt", "w+")
for i in data:
output.write("UserId: %s\t" %str(i[0]))
output.write(" Age: %s\r\n" %str(i[1]))
except (Exception, psycopg2.Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
print("PostgreSQL connection is closed")
def saveDataUser():
conn = connection_db.connect()
cur = conn.cursor()
cur.execute("""
CREATE TABLE users(
id integer PRIMARY KEY,
userID text,
time_zone text,
none text,
gender text,
age integer,
value integer
)
""")
with open('userinfo.csv', 'r') as f:
reader = csv.reader(f)
next(reader) # Skip the header row.
for row in reader:
cur.execute(
"INSERT INTO users VALUES (%s, %s, %s, %s, %s, %s, %s)", row)
conn.commit()
def saveDataset():
conn = connection_db.connect()
cur = conn.cursor()
cur.execute("""
CREATE TABLE dataset(
id integer PRIMARY KEY,
userID text,
date text,
type integer,
value float
)
""")
with open('data.csv', 'r') as f:
reader = csv.reader(f)
next(reader) # Skip the header row.
j = 0
for row in reader:
cur.execute("INSERT INTO dataset VALUES (%s, %s, %s, %s, %s)", row)
print(j)
j = j + 1
conn.commit()