Esempio n. 1
0
def fk_mappings_to_database(fk_mappings):
    if not fk_mappings:
        return

    fk_patch_data = []

    for mapping in fk_mappings:
        # FK tables have the same name as FK column
        table_name = column_name = mapping['column_name']

        db_results = dbutils.select_all(column_name, ['id', 'value'])

        db_set = set()
        for result in db_results:
            db_set.add(result['value'])

        diff_set = mapping['dataset'] - db_set

        # Insert only new values in the database
        for value in diff_set:
            dbutils.insert(table_name, {'value': value})

        fk_patch_data.append({
            'csv_index': mapping['csv_index'],
            'db_values': dbutils.select_all(table_name, ['id', 'value']),
        })

    return fk_patch_data
Esempio n. 2
0
def title_wordpair(db_con):
    print 'statistic word co-ocurence from title...'

    codict, dfdict = dict(), dict()
    cnt = 0
    for r in dbutils.iterRec(db_con, 'document', 'kw_title'):
        words = r[0].split(' ')
        for wp in itertools.combinations(words, 2):
            if wp[0] > wp[1]: wp = (wp[1], wp[0])
            codict[wp] = (codict[wp] + 1) if wp in codict else 1
        for w in words:
            dfdict[w] = (dfdict[w] + 1) if w in dfdict else 1

        cnt += 1
    print 'doc num: %d' % cnt
    print 'number of wordpair in title_wordpair: %d' % len(codict)
    cnt = 0
    for wp, co in codict.iteritems():
        weight = co / math.sqrt(dfdict[wp[0]] * dfdict[wp[1]])
        if co >= 2 and weight > 1e-3:
            cnt += 1
        dbutils.insert(db_con, 't_wordpair', {
            'word1': wp[0],
            'word2': wp[1],
            'coocur_num': co,
            'weight': weight
        })
    print 'number of edges ' + str(cnt)
    db_con.commit()
Esempio n. 3
0
def fk_mappings_to_database(fk_mappings):
    if not fk_mappings:
        return

    fk_patch_data = []

    for mapping in fk_mappings:
        # FK tables have the same name as FK column
        table_name = column_name = mapping['column_name']

        db_results = dbutils.select_all(column_name, ['id', 'value'])

        db_set = set()
        for result in db_results:
            db_set.add(result['value'])

        diff_set = mapping['dataset'] - db_set

        # Insert only new values in the database
        for value in diff_set:
            dbutils.insert(table_name, {'value': value})

        fk_patch_data.append({
            'csv_index':
            mapping['csv_index'],
            'db_values':
            dbutils.select_all(table_name, ['id', 'value']),
        })

    return fk_patch_data
Esempio n. 4
0
 def insert(self):
     for i, x in RESULT.items():
         b = list(RESULT[i].values())
         sql_insert = '''insert into users(username,age,tel,email)  values('{}', {},'{}','{}');'''.format(b[0],
                                                                                                          b[1],
                                                                                                          b[2],
                                                                                                          b[3])
         dbutils.insert(sql_insert)
Esempio n. 5
0
def main():
    dbutils.create_db("database.db")

    name = input("Enter the name to add to the database: ")
    phone = input(
        "Enter the phone number (without dashes) to add to the database: ")

    dbutils.insert("database.db", name, phone)
Esempio n. 6
0
    def process_item(self, item, spider):
        reload(sys)
        sys.setdefaultencoding('utf8')

        item_dict = textutils.convert(dict(item))

        dbutils.insert(ms, JOB_TABLE, item_dict)

        return item
Esempio n. 7
0
    def test_select_all(self):
        table_name = 'hmmm3'

        dbutils.create_table(table_name, [{
            'column_name': 'yeah',
            'data_type': 'VARCHAR(-1)'
        }])

        dbutils.insert(table_name, {'yeah': 'bla'})
        dbutils.insert(table_name, {'yeah': 'ble'})

        all_results = dbutils.select_all(table_name, ['id', 'yeah'])

        self.assertEqual(all_results[0]['yeah'], 'bla')
Esempio n. 8
0
    def test_select_all(self):
        table_name = 'hmmm3'

        dbutils.create_table(table_name, [{
            'column_name': 'yeah',
            'data_type': 'VARCHAR(-1)'
        }])

        dbutils.insert(table_name, {'yeah': 'bla'})
        dbutils.insert(table_name, {'yeah': 'ble'})

        all_results = dbutils.select_all(table_name, ['id', 'yeah'])

        self.assertEqual(all_results[0]['yeah'], 'bla')
Esempio n. 9
0
def capture(interface, dbfile, analyzing):
  #Load captured packets into database, analyze if analyzing
  if interface is not None: #if interface is specified, capture it, else capture default
    pcap = pycap.capture.capture(device=interface)
  else:
    pcap = pycap.capture.capture();
  print "Starting packet capture on interface: %s" % pcap.device
  print "Press CNTL + C to stop..."
  while True:
    dbobj_list = dbutils.parsecapture(pcap, INC_PARSE)
    #Parse capture from next packetID to end of section
    #if analyzing, analyze
    if analyzing:
      analyze(dbobj_list)
    dbutils.insert(dbobj_list, dbfile)  #insert parsed packets into database
  return
Esempio n. 10
0
def saveDB():
    '''
    存储到数据库中
    :return:
    '''

    for username, info in RESULT.items():
        sql = "SELECT {} FROM 51reboot_users WHERE name = '{}';".format(
            ','.join(FIELDS), username)
        print(sql)
        resp, ok = select(sql)  # select需要一个单独的判断是否为空的情况
        if not ok:
            fields_string = ','.join(FIELDS)
            values_string = "'{}', {}, '{}', '{}'".format(
                info['name'], info['age'], info['tel'], info['email'])
            sql = '''INSERT INTO 51reboot_users({}) VALUES({})'''.format(
                fields_string, values_string)
            print(sql)
            resp, ok = insert(sql)
            if ok:
                print('username: {} save succ.'.format(username))
            else:
                print('username: {} save fail.'.format(username))
        else:
            print('username: {} already exists.'.format(username))
Esempio n. 11
0
    def append(self):

        #数据库操作

        lab = str(e1.get())
        ip = str(e2.get())
        user = str(e10.get())
        password = str(e11.get())
        #print(lab,ip,user,password)
        if not Utils.isIP(ip):
            textvalue = 'ip不合法!'
        else:
            conn = sqlite3.connect(db_path)
            curs = conn.cursor()
            vals = [lab, ip, user, password]
            curs, flag = dbutils.insert("LabIP", vals, curs)
            conn.commit()
            curs.close()
            if flag == False:
                textvalue = 'Lab已存在!'
            else:
                textvalue = '添加成功!'

        #弹窗
        messagebox.showinfo(title='提示', message=textvalue)
Esempio n. 12
0
def save():

    msg = ''
    flag = True
    sql_data = load()
    for k, v in RESULT.items():
        if k in sql_data:
            if v != sql_data[k]:
                sql = ''' update users set age = {},tel='{}',email='{}' where username='******';
                '''.format(RESULT[k]['age'], RESULT[k]['tel'],
                           RESULT[k]['email'], RESULT[k]['username'])
                print(sql)
                updateMsg, ok = dbutils.update(sql)
                print('updateMsg:%s' % updateMsg)

        else:
            print('新增数据:%s' % k)
            sql = ''' insert into users(username,age,tel,email) \
            values('{}',{},'{}','{}');
            '''.format(RESULT[k]['username'], RESULT[k]['age'],
                       RESULT[k]['tel'], RESULT[k]['email'])
            print(sql)
            insertMsg, ok = dbutils.insert(sql)
            print('insertMsg:%s' % insertMsg)
    for i in sql_data:
        if i not in RESULT:
            # sql='''  '''
            sql = ''' delete from users where username = '******'; '''.format(i)
            print(sql)
            deleteMsg, ok = dbutils.delete(sql)
            print(deleteMsg)
    return msg, flag
Esempio n. 13
0
    def in_blacklist(self):
        blackip = Utils.get_arg("SSH_BF")
        if blackip == False:
            messagebox.showinfo(title='提示', message="没有需要加入黑名单的IP")
        elif len(blackip) == 0:
            messagebox.showinfo(title='提示', message="没有需要加入黑名单的IP")
        else:
            conn = sqlite3.connect(db_path)
            curs = conn.cursor()
            success = []
            fail = []
            for elem in blackip:

                vals = [elem, Utils.getlocal()]
                curs, flag = dbutils.insert("Blacklist", vals, curs)
                if flag == False:
                    fail.append(elem)
                else:
                    success.append(elem)
            conn.commit()
            curs.close()
            textvalue = ''
            if len(success) > 0:
                textvalue += ','.join(success) + "添加成功\n"
            if len(fail) > 0:
                textvalue += ','.join(fail) + "已存在或添加失败"
            messagebox.showinfo(title='提示', message=textvalue)
Esempio n. 14
0
def topic_keyword(dbcon):
    dbcon.execute("create table if not exists topic (name text unique not null, doc_num integer default 0, keyword text)")
    cur = dbcon.execute('select cats, count(docid) from document group by cats')
    for r in cur: 
        kwset = dict()
        cur2 = dbcon.execute('select kw_title from document where cats=?', (r[0],))
        for kr in cur2:
            for w in kr[0].split():
                try:
                    kwset[w] += 1
                except KeyError:
                    kwset[w] = 1

        kw_str = ' '.join([w for w,f in kwset.iteritems() if f>1])
        dbutils.insert(dbcon, 'topic', {'name':r[0], 'doc_num':r[1], 'keyword':kw_str})
    dbcon.commit()
Esempio n. 15
0
def capture(interface, dbfile, analyzing):
    #Load captured packets into database, analyze if analyzing
    if interface is not None:  #if interface is specified, capture it, else capture default
        pcap = pycap.capture.capture(device=interface)
    else:
        pcap = pycap.capture.capture()
    print "Starting packet capture on interface: %s" % pcap.device
    print "Press CNTL + C to stop..."
    while True:
        dbobj_list = dbutils.parsecapture(pcap, INC_PARSE)
        #Parse capture from next packetID to end of section
        #if analyzing, analyze
        if analyzing:
            analyze(dbobj_list)
        dbutils.insert(dbobj_list,
                       dbfile)  #insert parsed packets into database
    return
Esempio n. 16
0
def load(pcapfile, dbfile, analyzing):
  #Load pcapfile into database, analyze if analyzing
  if os.path.isfile(pcapfile):
    print "Loading database from file: %s" % pcapfile
  else:
    print "File: %s does not exist" % pcapfile
    return
  pcap = pycap.capture.capture()
  pcap = pcap.fromFile(pcapfile)
  while True:
    dbobj_list = dbutils.parsecapture(pcap, INC_PARSE)
    #Parse capture from next packetID to end of section
    if len(dbobj_list) == 0: #if end of file, break loop
      break
    #if analyzing, analyze
    if analyzing:
      analyze(dbobj_list)
    dbutils.insert(dbobj_list, dbfile) #insert parsed packets into database
  return
Esempio n. 17
0
def load(pcapfile, dbfile, analyzing):
    #Load pcapfile into database, analyze if analyzing
    if os.path.isfile(pcapfile):
        print "Loading database from file: %s" % pcapfile
    else:
        print "File: %s does not exist" % pcapfile
        return
    pcap = pycap.capture.capture()
    pcap = pcap.fromFile(pcapfile)
    while True:
        dbobj_list = dbutils.parsecapture(pcap, INC_PARSE)
        #Parse capture from next packetID to end of section
        if len(dbobj_list) == 0:  #if end of file, break loop
            break
        #if analyzing, analyze
        if analyzing:
            analyze(dbobj_list)
        dbutils.insert(dbobj_list,
                       dbfile)  #insert parsed packets into database
    return
Esempio n. 18
0
    def topic_keyword(self):
        self.dbcon.execute("create table if not exists topic (name text unique not null, doc_num integer default 0, keyword text, weight text)")
        cur = self.dbcon.execute('select cats, count(docid) from document group by cats')
        for r in cur: 
            kwset = dict()
            cur2 = self.dbcon.execute('select kw_title from document where cats=?', (r[0],))
            for kr in cur2:
                for w in kr[0].split():
                    try:
                        kwset[w] += 1
                    except KeyError:
                        kwset[w] = 1

            sum_weight = float(sum(kwset.itervalues()))
            items = kwset.items()
            items.sort(key=lambda x:x[1],reverse=True)
            kw_str = ' '.join([w for w,f in items])
            weight_str = ' '.join([str(f/sum_weight) for w,f in items])
            dbutils.insert(self.dbcon, 'topic', {'name':r[0], 'doc_num':r[1], 'keyword':kw_str, 'weight':weight_str})
        self.dbcon.commit()
Esempio n. 19
0
def add_user(userinfo):
    while len(userinfo) == 4:
        sql = '''insert into users(username,age,tel,email)  values('{}','{}','{}','{}');'''.format(
            userinfo[0], userinfo[1], userinfo[2], userinfo[3])
        insertMsg, ok = insert(sql)
        if not ok:
            print(insertMsg)
        else:
            cur_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            return ("\033[5;31m[INFO] {}\033[0m Add {} succ.\n".format(
                cur_time, userinfo[0]))
    else:
        return (
            "\033[1;31mInput Error!\033[0m\n\033[5;33;42mUsage: add [{}] [{}] [{}] [{}]\033[0m\n"
        ).format(FIELDS[0], FIELDS[1], FIELDS[2], FIELDS[3])
Esempio n. 20
0
def topic_keyword(dbcon):
    dbcon.execute(
        "create table if not exists topic (name text unique not null, doc_num integer default 0, keyword text)"
    )
    cur = dbcon.execute(
        'select cats, count(docid) from document group by cats')
    for r in cur:
        kwset = dict()
        cur2 = dbcon.execute('select kw_title from document where cats=?',
                             (r[0], ))
        for kr in cur2:
            for w in kr[0].split():
                try:
                    kwset[w] += 1
                except KeyError:
                    kwset[w] = 1

        kw_str = ' '.join([w for w, f in kwset.iteritems() if f > 1])
        dbutils.insert(dbcon, 'topic', {
            'name': r[0],
            'doc_num': r[1],
            'keyword': kw_str
        })
    dbcon.commit()
Esempio n. 21
0
def title_wordpair(db_con):
    print 'statistic word co-ocurence from title...'

    codict,dfdict=dict(),dict()
    cnt = 0
    for r in dbutils.iterRec(db_con, 'document','kw_title'):
        words = r[0].split(' ')
        for wp in itertools.combinations(words, 2):
            if wp[0]>wp[1]: wp = (wp[1],wp[0])
            codict[wp] = (codict[wp]+1) if wp in codict else 1
        for w  in words:
            dfdict[w] = (dfdict[w]+1) if w in dfdict else 1

        cnt+=1
    print 'doc num: %d' % cnt
    print 'number of wordpair in title_wordpair: %d' % len(codict)
    cnt = 0
    for wp,co in codict.iteritems():
        weight = co/math.sqrt(dfdict[wp[0]]*dfdict[wp[1]])
        if co>=2 and weight>1e-3:
            cnt += 1
        dbutils.insert(db_con, 't_wordpair', {'word1':wp[0], 'word2':wp[1], 'coocur_num':co, 'weight':weight})
    print 'number of edges '+str(cnt)
    db_con.commit()        
Esempio n. 22
0
def user_save(users):
    """
    数据保存到本地文件功能
    :param users: 存储于文件中的用户信息字典
    :return:
    """
    msg = ''
    flag = True

    # v2数据存储于数据库
    sql_data = load()
    # 判断内存中的数据是否在sql中,
    # 如果在判断是否一致,数据不一致,执行sql更新
    # 如果不在将该数据插入sql中,执行sql新增
    for k, v in RESULT.items():
        # print('='*50)
        if k in sql_data:
            if v != sql_data[k]:
                sql = ''' update users set age = {},tel='{}',email='{}' where username='******';
                '''.format(RESULT[k]['age'], RESULT[k]['tel'],
                           RESULT[k]['email'], RESULT[k]['username'])
                print(sql)
                updateMsg, ok = dbutils.update(sql)
                print('updateMsg:%s' % updateMsg)

        else:
            # sql新增
            print('新增数据:%s' % k)
            sql = ''' insert into users(username,age,tel,email) \
            values('{}',{},'{}','{}');
            '''.format(RESULT[k]['username'], RESULT[k]['age'],
                       RESULT[k]['tel'], RESULT[k]['email'])
            print(sql)
            insertMsg, ok = dbutils.insert(sql)
            print('insertMsg:%s' % insertMsg)

    # 判断数据库中的数据与内存中的数据,不存在则删除该数据
    for i in sql_data:
        if i not in RESULT:
            # sql='''  '''
            sql = ''' delete from users where username = '******'; '''.format(i)
            print(sql)
            deleteMsg, ok = dbutils.delete(sql)
            print(deleteMsg)
    return msg, flag
Esempio n. 23
0
    def append(self):
        #数据库操作

        blackip = str(e6.get())

        if not Utils.isIP(blackip):
            textvalue = 'blackip不合法!'
        else:
            conn = sqlite3.connect(db_path)
            curs = conn.cursor()
            vals = [blackip, Utils.getlocal()]
            curs, flag = dbutils.insert("Blacklist", vals, curs)
            conn.commit()
            curs.close()
            if flag == False:
                textvalue = 'BlackIP!'
            else:
                textvalue = '添加成功!'

        #弹窗
        messagebox.showinfo(title='提示', message=textvalue)
Esempio n. 24
0
def addEntry():
    name = input("Enter the name to add: ").strip()
    number = input("Enter the phone number to add: ").replace('-', '')
    print("")
    dbutils.insert(dbFile, name, number)
    doMenu()
Esempio n. 25
0
def handler(url, config, count, currentIndex, type_handler):
    global list
    url = prefix + url
    html = request.urlopen(url).read().decode("utf-8")
    lines = html.split("\r\n")
    find = False
    data = []
    item = {}
    # 解析对应匹配的行数
    for line in lines:
        # re是python官方自带的模块
        if not find:
            result = re.match(config['startPattern'], line)
            if result:
                find = True
        else:
            result = re.match(config['endPattern'], line)
            if result:
                break
            else:
                # 提取出双引号之间的数据
                # 如果为空格则跳过
                if line.strip() == "":
                    continue
                result = re.findall("\"(.*?)\"", line)

                item[result[0]] = result[1]
                item[result[2]] = result[3]
                data.append(item)
                # 清空item
                item = {}

    # 使用json解析工具解析data
    # result = json.loads(data)
    list.append(data)
    # 存入数据库
    if config["count"] == len(list) or currentIndex == count - 1:
        insertSql = "insert into " + config["tableName"] + "("
        # 还是需要keys的因为keys
        index = 0
        for key in config["keys"]:
            if index == len(config["keys"]) - 1:
                insertSql += ("`" + key + "`) values")
            else:
                insertSql += ("`" + key + "`,")
            index += 1

        index = 0
        for item in list:
            # item 为一个字符串,需要转为数组
            # 这并不是json,只是普通的js对象, 需要处理,怎么处理呢用正则匹配吧
            # a = [
            #     {"ID": "Disease Name","Item Name": "<div style='max-height: 200px;overflow: auto'>Abdominal Colic</div>", },
            # ]
            insertSql += "("
            aindex = 0
            for x in item:
                # 这都是值传递,问题来了,怎么搞,可以传对象啊,感觉好蠢
                insertSql = type_handler(index, aindex, insertSql, x, item)
                aindex += 1
            index += 1

        dbutils.insert(insertSql)
        list = []
Esempio n. 26
0
    logger.info("user not found initializing db")
    cursor.execute("CREATE USER " + dbuser + " with password '" + dbpass + "'")

    db = os.environ["ICEES_DATABASE"]
    cursor.execute("CREATE DATABASE " + db)
    cursor.execute("GRANT ALL ON DATABASE " + db + " to " + dbuser)
    dbutils.create()
    csvdir = os.environ.get("DATA_PATH", "db/data/")
    for t in features.features_dict.keys():
        table_dir = csvdir + "/" + t
        if os.path.isdir(table_dir):
            logger.info(table_dir + " exists")
            for f in os.listdir(table_dir):
                table = table_dir + "/" + f
                logger.info("loading " + table)
                dbutils.insert(table, t)
        else:
            logger.info("generating data " + t)
            temp = tempfile.NamedTemporaryFile()
            try:
                sample.generate_data(t, [2010, 2011], 1000, temp.name)
                dbutils.insert(temp.name, t)
            finally:
                temp.close()
    dbutils.create_indices()
    logger.info("db initialized")
else:
    logger.info("db already initialized")

cursor.close()
conn.close()