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
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()
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
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)
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)
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
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')
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')
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
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))
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)
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
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)
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()
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
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
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
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()
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])
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()
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()
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
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)
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()
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 = []
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()
