def insert(knowledge):
i_sql = u"insert into knowledge(uid,pid,puid,knowledge,ischild,stage,subject) values('{uid}',{pid},'{puid}','{knowledge}',{ischild},'{stage}','{subject}') on Duplicate key update uid='{uid}';"
print i_sql.format(**knowledge)
db_helper.execute(i_sql.format(**knowledge))
s_sql = u"select id from knowledge where uid = '{uid}'".format(**knowledge)
_id = db_helper.query_one(s_sql)
return _id['id']
def db_insert_setting(setting):
"""
Args:
setting (Setting): The setting to insert into the database
"""
db_helper.execute(INSERT_STATEMENT,
(setting.module, setting.key, setting.value))
def db_update_setting(setting):
"""
Args:
setting (Setting): The setting to update in the database (if it exists, nothing happens otherwise)
"""
db_helper.execute(UPDATE_STATEMENT,
(setting.value, setting.module, setting.key))
def db_replace_setting(setting):
"""
Args:
setting (Setting): The setting to replace in the database
"""
db_helper.execute(REPLACE_STATEMENT,
(setting.module, setting.key, setting.value))
def update_title_image():
source_id = 'walmart'
wmt_ids = [r['id'] for r in execute("""select distinct r.retailer_id id
from product_reviews r left join product_details d
on r.source_id = d.source_id and r.retailer_id = d.retailer_id
where r.source_id = 'walmart' and (d.title is null or d.img_url is null)""")]
cq = '''select item_id, upc, img150, item_name from dotcom_products where item_id in (%s)''' % \
(', '.join(str(x) for x in wmt_ids))
data = execute_cia(cq)
ins_data = []
for img_data in data:
url = "#"
title = img_data['item_name']
id = img_data['item_id']
if img_data['img150'] is None:
upc = img_data['upc']
if upc is not None:
upc_s = "%013d" % upc
url = "http://i.walmartimages.com/i/p/%s/%s/%s/%s/%s/%s_150X150.jpg" % (
upc_s[0:2], upc_s[2:4], upc_s[4:6], upc_s[6:8], upc_s[8:10], upc_s)
else:
url = img_data['img150']
upc = img_data['upc']
ins_data.append((id, title, url, upc))
q = """insert into product_details (source_id, retailer_id, title, img_url)
values %s
on duplicate key update
title = values(title), img_url = values(img_url) """ % (', '.join(
["('%s', '%s', '%s' , '%s')" % (escape_sql(source_id), r[0],
escape_sql(r[1]), escape_sql(r[2]))
for r in ins_data]))
execute(q)
def update_positivity_from_alchemy(sorting, start_id, end_id):
table = 'product_reviews'
q = "select id, review from %s where id >= %s and id < %s order by id %s " % (table, start_id, end_id, sorting)
qs = []
for id, rev in [(r['id'], r['review']) for r in execute(q)]:
start = time()
s = get_s(rev)
uq = update_positivity(id, s)
execute(uq)
print id, 'score = %4f' % s, 'done in %3f' % (time() - start)
def db_save_quote(quote):
"""Save the quote, either adds a new quote or
updates an existing one, based on if the
qid is None"""
if quote.qid is None:
newid = db_helper.execute_return_id(INSERT_TABLE_STATEMENT,
(quote.quote, quote.date))
quote.qid = newid
else:
db_helper.execute(UPDATE_TABLE_STATEMENT,
(quote.quote, quote.date, quote.qid))
def update_summary_from_db(refresh_all=False):
qs = []
for id, title, rev in [(r['id'], r['title'], r['review']) for r in execute(
"""select id, title, review from product_reviews r, product_details d
where r.retailer_id = d.retailer_id and r.source_id=d.source_id
%s""" % ('' if refresh_all else 'and summary is null'))]:
if title is None or title == '':
title = ' '.join(feat_map.keys()) + "good bad don't buy cost price"
summary = Summarize(title, rev)
summary = '\n'.join(summary)
qs.append(update_summary(id, summary))
execute(qs)
def insert_to_details_table(source_id, retailer_id, title):
table = "product_details"
q = """
insert into %s(source_id, retailer_id, title)
values('%s', '%s', '%s')
""" % (
table,
escape_sql(source_id),
escape_sql(retailer_id),
escape_sql(title),
)
execute(q)
def insert_to_reviews_table(source_id, retailer_id, username, review_title, review, date):
table = "product_reviews"
q = """
insert ignore into %s(source_id, retailer_id, user_name, review_title, review, date)
values('%s', '%s' , '%s', '%s', '%s', '%s' )
""" % (
table,
escape_sql(source_id),
escape_sql(retailer_id),
escape_sql(username),
escape_sql(review_title),
escape_sql(review),
date,
)
execute(q)
def add_tags_to_reviews(refresh_all=False):
start = time()
feats = read_feats()
qs = []
q = '''select id, review from %s %s''' % (
product_reviews, '' if refresh_all else "where tags is null")
for id, rev in [(r['id'], r['review']) for r in execute(q)]:
tags = set()
for f in feats:
if rev.find(f) >= 0:
tags.add(f)
q = "update %s set tags='%s' where id=%s" % (product_reviews,
', '.join(tags), id)
qs.append(q)
execute(qs)
print 'Completed add tags in %3fs' % (time() - start)
def report():
if request.method == 'GET':
context = {
'timestamp': datetime.now(),
'customers': db_helper.query('SELECT * FROM customers ORDER BY name'),
}
if 'message' in session:
context['message'] = session.pop('message')
if 'error' in session:
context['error'] = session.pop('error')
return render_template('report-create.html', **context)
customer_id = request.form['customer-id']
units = request.form['units']
if customer_id and units:
db_helper.execute('INSERT INTO reports (customer_id, units, datetime) VALUES (?, ?, datetime(CURRENT_TIMESTAMP,"localtime"))', [customer_id, units])
customer_name = db_helper.query('SELECT name FROM customers WHERE id = ?', [customer_id])[0].get('name')
session['message'] = "Added %s units to %s" % (units, customer_name)
return redirect(url_for('report'))
else:
session['error'] = "Both customer and units must be chosen"
return redirect(url_for('report'))
def db_delete_setting(setting):
"""
Args:
setting (Setting): The setting that is deleted
"""
db_helper.execute(DELETE_STATEMENT, (setting.module, setting.key))
def truncate_all():
for t in tables.keys():
execute("truncate %s" % t)
def db_create_table():
db_helper.execute(CREATE_TABLE_STATEMENT)
def db_update_regex(regres):
db_helper.execute(UPDATE_REGEX_STATEMENT,
(regres.response, regres.cooldown, regres.regex))
def load(self, config):
db_helper.execute(CREATE_TABLE_STATEMENT)
self.regres = db_get_all()
self.irc.messagespreader.add(self.msglistener)
def db_delete(tm):
db_helper.execute(DELETE_STATEMENT, (tm.id, ))
def db_update_command(command):
db_helper.execute(UPDATE_STATEMENT, (command.response, command.channel_cooldown, command.user_cooldown, command.mod_only, command.broadcaster_only, command.enabled, command.name))
def db_add_command(command):
db_helper.execute(INSERT_STATEMENT, (command.name, command.response, command.channel_cooldown, command.user_cooldown, command.mod_only, command.broadcaster_only, command.enabled))
def get_un_processed_sid_retailer_ids(all):
q = """select source_id, retailer_id from product_reviews
%s
group by source_id, retailer_id
""" % ('' if all else 'where positivity is null')
return execute(q)
if img_data['img150'] is None:
upc = img_data['upc']
if upc is not None:
upc_s = "%013d" % upc
url = "http://i.walmartimages.com/i/p/%s/%s/%s/%s/%s/%s_150X150.jpg" % (
upc_s[0:2], upc_s[2:4], upc_s[4:6], upc_s[6:8], upc_s[8:10], upc_s)
else:
url = img_data['img150']
upc = img_data['upc']
ins_data.append((id, title, url, upc))
q = """insert into product_details (source_id, retailer_id, title, img_url)
values %s
on duplicate key update
title = values(title), img_url = values(img_url) """ % (', '.join(
["('%s', '%s', '%s' , '%s')" % (escape_sql(source_id), r[0],
escape_sql(r[1]), escape_sql(r[2]))
for r in ins_data]))
execute(q)
if __name__ == '__main__':
# update_title_image()
execute("show tables; desc product_details")
refresh_cursor()
print execute("show tables")
def db_update(tm):
db_helper.execute(
UPDATE_STATEMENT,
(tm.message, tm.inittime, tm.looptime, 1 if tm.enabled else 0, tm.id))
def db_set_name(oldname, newname):
db_helper.execute(ALTER_NAME_STATEMENT,(newname,oldname))
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
db_helper.execute(CREATE_TABLE_STATEMENT)
self.timedmessages = list()
db_helper.add_db_change_listener(self.db_reload)
def db_set_response(name, response):
db_helper.execute(ALTER_RESPONSE_STATEMENT,(response, name))
def db_add_regex(regres):
db_helper.execute(INSERT_STATEMENT,
(regres.regex, regres.response, regres.cooldown))
def db_set_channel_cooldown(name, channel_cooldown):
db_helper.execute(ALTER_CHANNEL_COOLDOWN_STATEMENT,(channel_cooldown, name))
def db_set_name(old, new):
db_helper.execute(UPDATE_REGEX_STATEMENT, (new, old))
def db_set_user_cooldown(name, user_cooldown):
db_helper.execute(ALTER_USER_COOLDOWN_STATEMENT,(user_cooldown, name))
def drop_all():
for t in tables.keys():
execute("drop table if exists %s" % t)
def create_tables():
for qq in tables.values():
execute(qq)
def get_reviews(source, ret_id):
res = execute(
"select distinct review, id, review_title from product_reviews where source_id = '%s' and retailer_id = '%s'" % (
source, ret_id))
return res
def insert(list_path, knowledge):
i_sql = u"insert into link_knowledge(question_id,knowledge_id)select a.id as quesiton_id , b.id as knowledge_id from knowledge as b ,question as a where b.uid ='{uid}' and a.list_path='{list_path}' on Duplicate key update question_id=question_id;"
i_sql = i_sql.format(list_path=list_path, uid=knowledge['id'])
print i_sql
db_helper.execute(i_sql)
def db_delete_quote(quoteid):
"""Delete a quote based on the given ID (int)"""
db_helper.execute(DELETE_STATEMENT, (quoteid, ))
def db_remove_command(name):
db_helper.execute(REMOVE_STATEMENT,(name,))
def db_set_mod_only(name, mod_only):
db_helper.execute(ALTER_MOD_ONLY_STATEMENT,(mod_only, name))
def update_attribute_stats_from_db(refresh_all=False):
"""
Once reviews and product titles updated in db it updates all other fields
"""
rint = randint(0, 1111)
positivity_data_file = open('positivity_data_file%s' % rint, 'w')
attrib_data_file = open('attrib_data_file%s' % rint, 'w')
start = time()
for sid, ret_id in [(r['source_id'], r['retailer_id']) for r in
get_un_processed_sid_retailer_ids(refresh_all)]:
res = get_reviews(sid, ret_id)
revs = [r['review'] for r in res]
print ret_id
stats = analyse(revs)
for attrib, scores in stats.items():
for sm, sc in scores.items():
attrib_data_file.writelines(['%s\t%s\t%s\t%s\t%s' % (sid, ret_id, attrib, sm, sc), '\n'])
for r in res:
pos = TextBlob(r['review']).sentiment.polarity
positivity_data_file.writelines(['%s\t%s' % (r['id'], pos), '\n'])
positivity_data_file.close()
attrib_data_file.close()
#positivity insert
tmp_positivity_table = 'tmp_positivity_table%s' % (rint)
execute('create table %s(id int, positivity float, primary key(id))' %
(tmp_positivity_table))
field_termination = "fields terminated by '\\t' escaped by '\\\\' lines terminated by '\\n'"
execute("load data local infile '%s' into table %s %s (id, positivity)" % (
positivity_data_file.name, tmp_positivity_table, field_termination))
execute(
"update %s r, %s t set r.positivity=t.positivity where r.id = t.id " % (product_reviews, tmp_positivity_table))
execute("drop table %s" % (tmp_positivity_table))
os.remove(positivity_data_file.name)
#attrib_insert
tmp_attrib_table = 'tmp_attrib_table%s' % (rint)
execute("create table %s like %s" % (tmp_attrib_table, product_attribute_stats))
execute(
"load data local infile '%s' into table %s %s (source_id, retailer_id, attribute, scoring_method, score)" % (
attrib_data_file.name, tmp_attrib_table, field_termination))
execute("insert into %s (select * from %s) on duplicate key update score=values(score)" % (
product_attribute_stats, tmp_attrib_table))
execute("drop table %s" % tmp_attrib_table)
os.remove(attrib_data_file.name)
print 'Completed attribute stats in %3fs' % (time() - start)
def update_question(question):
sql = "update question set answer='{answer}' ,analysis='{analysis}' where id={id}"
db_helper.execute(sql.format(**question))
def db_set_broadcaster_only(name, broadcaster_only):
db_helper.execute(ALTER_BROADCASTER_ONLY_STATEMENT,(broadcaster_only, name))
def insert(question):
i_sql = u"insert into question(uid,type,diffculty,body,answer,knowledges,list_path) values('{uid}','{type}','{diffculty}','{body}','{answer}','{knowledges}','{list_path}') on Duplicate key update uid='{uid}';"
print i_sql.format(**question)
db_helper.execute(i_sql.format(**question))
def db_set_enabled(name, enabled):
db_helper.execute(ALTER_ENABLED_STATEMENT,(enabled, name))
