Ejemplo n.º 1
0
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']
Ejemplo n.º 2
0
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))
Ejemplo n.º 3
0
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))
Ejemplo n.º 4
0
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)
Ejemplo n.º 7
0
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))
Ejemplo n.º 8
0
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)
Ejemplo n.º 9
0
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)
Ejemplo n.º 10
0
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)
Ejemplo n.º 11
0
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)
Ejemplo n.º 12
0
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'))
Ejemplo n.º 13
0
def db_delete_setting(setting):
    """
    Args:
        setting (Setting): The setting that is deleted
    """
    db_helper.execute(DELETE_STATEMENT, (setting.module, setting.key))
Ejemplo n.º 14
0
def truncate_all():
    for t in tables.keys():
        execute("truncate %s" % t)
Ejemplo n.º 15
0
def db_create_table():
    db_helper.execute(CREATE_TABLE_STATEMENT)
Ejemplo n.º 16
0
def db_update_regex(regres):
    db_helper.execute(UPDATE_REGEX_STATEMENT,
                      (regres.response, regres.cooldown, regres.regex))
Ejemplo n.º 17
0
 def load(self, config):
     db_helper.execute(CREATE_TABLE_STATEMENT)
     self.regres = db_get_all()
     self.irc.messagespreader.add(self.msglistener)
Ejemplo n.º 18
0
def db_delete(tm):
    db_helper.execute(DELETE_STATEMENT, (tm.id, ))
Ejemplo n.º 19
0
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))
Ejemplo n.º 20
0
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))
Ejemplo n.º 21
0
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")





Ejemplo n.º 23
0
def db_update(tm):
    db_helper.execute(
        UPDATE_STATEMENT,
        (tm.message, tm.inittime, tm.looptime, 1 if tm.enabled else 0, tm.id))
Ejemplo n.º 24
0
def db_set_name(oldname, newname):
    db_helper.execute(ALTER_NAME_STATEMENT,(newname,oldname))
Ejemplo n.º 25
0
 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)
Ejemplo n.º 26
0
def db_set_response(name, response):
    db_helper.execute(ALTER_RESPONSE_STATEMENT,(response, name))
Ejemplo n.º 27
0
def db_add_regex(regres):
    db_helper.execute(INSERT_STATEMENT,
                      (regres.regex, regres.response, regres.cooldown))
Ejemplo n.º 28
0
def db_set_channel_cooldown(name, channel_cooldown):
    db_helper.execute(ALTER_CHANNEL_COOLDOWN_STATEMENT,(channel_cooldown, name))
Ejemplo n.º 29
0
def db_set_name(old, new):
    db_helper.execute(UPDATE_REGEX_STATEMENT, (new, old))
Ejemplo n.º 30
0
def db_set_user_cooldown(name, user_cooldown):
    db_helper.execute(ALTER_USER_COOLDOWN_STATEMENT,(user_cooldown, name))
Ejemplo n.º 31
0
def drop_all():
    for t in tables.keys():
        execute("drop table if exists %s" % t)
Ejemplo n.º 32
0
def create_tables():
    for qq in tables.values():
        execute(qq)
Ejemplo n.º 33
0
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
Ejemplo n.º 34
0
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)
Ejemplo n.º 35
0
def db_delete_quote(quoteid):
    """Delete a quote based on the given ID (int)"""
    db_helper.execute(DELETE_STATEMENT, (quoteid, ))
Ejemplo n.º 36
0
def db_remove_command(name):
    db_helper.execute(REMOVE_STATEMENT,(name,))
Ejemplo n.º 37
0
def db_set_mod_only(name, mod_only):
    db_helper.execute(ALTER_MOD_ONLY_STATEMENT,(mod_only, name))
Ejemplo n.º 38
0
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)
Ejemplo n.º 39
0
def update_question(question):
    sql = "update question set answer='{answer}' ,analysis='{analysis}' where id={id}"
    db_helper.execute(sql.format(**question))
Ejemplo n.º 40
0
def db_set_broadcaster_only(name, broadcaster_only):
    db_helper.execute(ALTER_BROADCASTER_ONLY_STATEMENT,(broadcaster_only, name))
Ejemplo n.º 41
0
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))
Ejemplo n.º 42
0
def db_set_enabled(name, enabled):
    db_helper.execute(ALTER_ENABLED_STATEMENT,(enabled, name))