def analyze_propmtness_of_answers(c):
db_tools.add_column(c,"answers","promptness","int")
# cursor for updating
cur = c.cursor()
# cursor for selecting from answers
a_cur = c.cursor()
# cursor for selecting from questions
q_cur = c.cursor()
a_cur.execute("SELECT id, pid, creationdate FROM answers")
while True:
a_data = a_cur.fetchone()
if a_data == None:
break
a_id = int(a_data[0])
q_id = int(a_data[1])
a_creationdate = a_data[2]
q_cur.execute("SELECT creationdate FROM questions WHERE id = %i" % q_id)
q_data = q_cur.fetchone()
if q_data == None:
continue
q_creationdate = q_data[0]
promptness = diff_times(a_creationdate,q_creationdate)
cur.execute("UPDATE answers SET promptness = %i WHERE id = %i" % (promptness,a_id))
c.commit()
def normalize_text(c):
db_tools.add_column(c,"answers","links","int")
db_tools.add_column(c,"questions","links","int")
# cursor for updating
cur = c.cursor()
# cursor for selecting from answers
a_cur = c.cursor()
# cursor for selecting from questions
q_cur = c.cursor()
q_cur.execute("SELECT id, body, title FROM questions")
while True:
data = q_cur.fetchone()
if data == None:
break
q_id = int(data[0])
q_body = data[1]
q_title = data[2]
q_norm_body = text_parser.normalize_text(q_body).encode('ascii','ignore')
q_norm_title = text_parser.normalize_text(q_title).encode('ascii','ignore')
cur.execute('UPDATE questions SET body = ? where id = ?',(q_norm_body, q_id))
cur.execute('UPDATE questions SET title = ? where id = ?',(q_norm_title, q_id))
c.commit()
a_cur.execute("SELECT id, body FROM answers")
while True:
data = a_cur.fetchone()
if data == None:
break
a_id = int(data[0])
a_body = data[1]
a_norm_body = text_parser.normalize_text(a_body).encode('ascii','ignore')
cur.execute('UPDATE answers SET body = ? where id = ?',(a_norm_body, a_id))
c.commit()
def get_anchor_counts(c):
db_tools.add_column(c,"answers","links","int")
db_tools.add_column(c,"questions","links","int")
# cursor for updating
cur = c.cursor()
# cursor for selecting from answers
a_cur = c.cursor()
# cursor for selecting from questions
q_cur = c.cursor()
q_cur.execute("SELECT id, body FROM questions")
while True:
data = q_cur.fetchone()
if data == None:
break
q_id = int(data[0])
q_body = data[1]
q_num_links = text_parser.get_anchor_count(q_body)
cur.execute("UPDATE questions SET links = %i where id = %i" % (q_num_links,q_id))
c.commit()
a_cur.execute("SELECT id, body FROM answers")
while True:
data = a_cur.fetchone()
if data == None:
break
a_id = int(data[0])
a_body = data[1]
a_num_links = text_parser.get_anchor_count(a_body)
cur.execute("UPDATE answers SET links = %i where id = %i" % (a_num_links,a_id))
c.commit()
def analyze_sentiment(c):
db_tools.add_column(c,"questions","bodysentipolarity","real")
db_tools.add_column(c,"questions","bodysentisubjectivity","real")
db_tools.add_column(c,"questions","titlesentipolarity","real")
db_tools.add_column(c,"questions","titlesentisubjectivity","real")
db_tools.add_column(c,"answers","bodysentipolarity","real")
db_tools.add_column(c,"answers","bodysentisubjectivity","real")
# cursor for updating
cur = c.cursor()
# cursor for selecting from answers
a_cur = c.cursor()
# cursor for selecting from questions
q_cur = c.cursor()
q_cur.execute("SELECT id, body, title FROM questions")
while True:
data = q_cur.fetchone()
if data == None:
break
q_id = int(data[0])
q_body = data[1]
q_title = data[2]
q_senti_body_tuple = sentiment_tools.get_sentiment(q_body)
q_senti_body_polarirty = q_senti_body_tuple[0]
q_senti_body_subjectivity = q_senti_body_tuple[1]
cur.execute('UPDATE questions SET bodysentipolarity = ? where id = ?',(q_senti_body_polarirty, q_id))
cur.execute('UPDATE questions SET bodysentisubjectivity = ? where id = ?',(q_senti_body_subjectivity, q_id))
q_senti_title_tuple = sentiment_tools.get_sentiment(q_body)
q_senti_title_polarirty = q_senti_title_tuple[0]
q_senti_title_subjectivity = q_senti_title_tuple[1]
cur.execute('UPDATE questions SET titlesentipolarity = ? where id = ?',(q_senti_title_polarirty, q_id))
cur.execute('UPDATE questions SET titlesentisubjectivity = ? where id = ?',(q_senti_title_subjectivity, q_id))
c.commit()
a_cur.execute("SELECT id, body FROM answers")
while True:
data = a_cur.fetchone()
if data == None:
break
a_id = int(data[0])
a_body = data[1]
a_senti_body_tuple = sentiment_tools.get_sentiment(a_body)
a_senti_body_polarirty = a_senti_body_tuple[0]
a_senti_body_subjectivity = a_senti_body_tuple[1]
cur.execute('UPDATE answers SET bodysentipolarity = ? where id = ?',(a_senti_body_polarirty, a_id))
cur.execute('UPDATE answers SET bodysentisubjectivity = ? where id = ?',(a_senti_body_subjectivity, a_id))
c.commit()