def test_save_book_as_to_database(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
data1 = [
["A", "B", "C"],
[1, 2, 3],
[4, 5, 6]
]
sheet_dict = {
Signature.__tablename__: data,
Signature2.__tablename__: data1
}
pe.save_book_as(bookdict=sheet_dict,
dest_session=self.session,
dest_tables=[Signature, Signature2])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.get_dict(session=self.session, table=Signature2)
assert result == {
"A": [1, 4],
"B": [2, 5],
"C": [3, 6]
}
def test_book_save_a_dict2(self):
data = [
[1, 4, 'X'],
[2, 5, 'Y'],
[3, 6, 'Z']
]
data1 = [
[1, 4, 'A'],
[2, 5, 'B'],
[3, 6, 'C']
]
sheet_dict = {
"sheet": data,
"sheet1": data1
}
book = pe.Book(sheet_dict)
book['sheet'].transpose()
book['sheet'].name_columns_by_row(2)
book['sheet1'].transpose()
book['sheet1'].name_columns_by_row(2)
book.save_to_database(
self.session,
[Signature,Signature2])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.get_dict(session=self.session, table=Signature2)
assert result == {
"A": [1, 4],
"B": [2, 5],
"C": [3, 6]
}
def test_get_dict_from_sql(self):
adict = pe.get_dict(session=Session(), table=Signature)
assert adict == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
def test_get_dict_from_dict(self):
data = {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.get_dict(adict=data)
assert result == data
def test_get_dict_from_file(self):
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
testfile = "testfile.xls"
sheet.save_as(testfile)
result = pe.get_dict(file_name=testfile)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
os.unlink(testfile)
def test_save_an_array(self):
data = [[1, 4, "X"], [2, 5, "Y"], [3, 6, "Z"]]
sheet = pe.Sheet(data)
sheet.transpose()
sheet.name_columns_by_row(2)
sheet.save_to_database(self.session, Signature)
result = pe.get_dict(session=self.session, table=Signature)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_save_an_array4(self):
data = [["A", "B", "C"], [1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
sheet.name_columns_by_row(0)
mapdict = {"A": "X", "B": "Y", "C": "Z"}
sheet.save_to_database(self.session, Signature, mapdict=mapdict)
result = pe.get_dict(session=self.session, table=Signature)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_save_as_to_database(self):
adict = {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
pe.save_as(adict=adict, dest_session=self.session, dest_table=Signature)
result = pe.get_dict(session=self.session, table=Signature)
assert adict == result
def test_issue_10(self):
thedict = OrderedDict()
thedict.update({"Column 1": [1,2,3]})
thedict.update({"Column 2": [1,2,3]})
thedict.update({"Column 3": [1,2,3]})
pe.save_as(adict=thedict, dest_file_name="issue10.xls")
newdict = pe.get_dict(file_name="issue10.xls")
assert isinstance(newdict, OrderedDict) == True
assert thedict == newdict
def test_book_save_a_dict(self):
data = [[1, 4, "X"], [2, 5, "Y"], [3, 6, "Z"]]
sheet_dict = {"sheet": data}
book = pe.Book(sheet_dict)
book["sheet"].transpose()
book["sheet"].name_columns_by_row(2)
book.save_to_database(self.session, [Signature])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_save_a_dict2(self):
adict = {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
sheet = pe.get_sheet(adict=adict)
sheet.save_to_database(self.session, Signature)
result = pe.get_dict(session=self.session, table=Signature)
assert adict == result
def test_save_a_dict3(self):
adict = {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
sheet = pe.get_sheet(adict=adict, name_columns_by_row=0)
sheet.save_to_database(self.session, Signature)
result = pe.get_dict(session=self.session, table=(Signature))
assert adict == result
def test_save_an_array7(self):
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
sheet = pe.Sheet(data)
sheet.name_columns_by_row(0)
def make_signature(row):
return Signature(X=row["X"], Y=row["Y"], Z=row["Z"])
sheet.save_to_database(self.session, Signature, initializer=make_signature)
result = pe.get_dict(session=self.session, table=Signature)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_get_dict_from_records(self):
data = [
{"X": 1, "Y": 2, "Z": 3},
{"X": 4, "Y": 5, "Z": 6}
]
expected = {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
result = pe.get_dict(records=data)
assert result == expected
def test_get_dict_from_array(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
result = pe.get_dict(array=data)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
def test_get_dict_from_memory(self):
data = [
["X", "Y", "Z"],
[1, 2, 3],
[4, 5, 6]
]
content = pe.save_as(dest_file_type="xls", array=data)
adict = pe.get_dict(file_content=content.getvalue(), file_type="xls")
assert adict == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
def get_dict(self, **keywords):
"""Get a dictionary from the file
:param sheet_name: For an excel book, there could be multiple
sheets. If it is left unspecified, the
sheet at index 0 is loaded. For 'csv',
'tsv' file, *sheet_name* should be None anyway.
:param keywords: additional key words
:returns: A dictionary
"""
params = self.get_params(**keywords)
if 'name_columns_by_row' not in params:
params['name_columns_by_row'] = 0
return pe.get_dict(**params)
def test_book_save_a_dict(self):
data = [
[1, 4, 'X'],
[2, 5, 'Y'],
[3, 6, 'Z']
]
sheet1 = Signature.__tablename__
sheet_dict = {
sheet1: data
}
book = pe.Book(sheet_dict)
book[sheet1].transpose()
book[sheet1].name_columns_by_row(2)
book.save_to_database(self.session, [Signature])
result = pe.get_dict(session=self.session, table=Signature)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
def test_save_an_array3(self):
data = [
[1, 4, 'A'],
[2, 5, 'B'],
[3, 6, 'C']
]
sheet = pe.Sheet(data)
sheet.transpose()
sheet.name_columns_by_row(2)
mapdict = [
'X',
'Y',
'Z'
]
sheet.save_to_database(self.session, Signature, mapdict=mapdict)
result = pe.get_dict(session=self.session, table=Signature)
assert result == {
"X": [1, 4],
"Y": [2, 5],
"Z": [3, 6]
}
return_value = 0
except ValueError:
return_value = 0
else:
return_value = (3600 * int(h)) + (60 * int(m)) + int(s)
elif input_type == 'B':
return_value = ws['%s%d' % (column_position, row)].value
elif input_type == 'S':
return_value = str(ws['%s%d' % (column_position, row)].value)
else:
raise ValueError("Invalid input type in valid_input")
return return_value
SELF_PATH = os.path.dirname(path.dirname(path.abspath(__file__)))
constants = pe.get_dict(file_name='%s/bin/config.xlsx' % SELF_PATH,
name_columns_by_row=0)
index_dict = {}
for index, item in enumerate(constants['Constant']):
index_dict[item] = index
GO_PIC = constants['Argument'][index_dict['GO_PIC']]
ACCUM_PIC = constants['Argument'][index_dict['ACCUM_PIC']]
QUIT_PIC = constants['Argument'][index_dict['QUIT_PIC']]
SEARCH_PIC = constants['Argument'][index_dict['SEARCH_PIC']]
SETTINGS_PIC = constants['Argument'][index_dict['SETTINGS_PIC']]
CALL_SLA_ARG = constants['Argument'][index_dict['CALL_SLA_ARG']]
ACCUM_ARG = constants['Argument'][index_dict['ACCUM_ARG']]
SPREADSHEET_VIEWER_FILE_TEMPLATE = constants['Argument'][
index_dict['SPREADSHEET_VIEWER_FILE_TEMPLATE']]
ACCUM_VIEWER_FILE = constants['Argument'][index_dict['ACCUM_VIEWER_FILE']]
TEST_SPREADSHEET = constants['Argument'][index_dict['TEST_SPREADSHEET']]
# book.save("test.xls")
# # ????????????
######## pyexcel ###########
import pyexcel
from pyexcel._compact import OrderedDict
# Чтение
# Чтобы получить данные в массиве, можно использовать функцию
# get_array (), которая содержится в пакете pyexcel:
my_array = pyexcel.get_array(file_name="example.xls")
# print(my_array)
# Также можно получить данные в упорядоченном словаре списков,
# используя функцию get_dict ():
my_dict = pyexcel.get_dict(file_name="example.xls", name_columns_by_row=0)
# print(my_dict)
# Тоже упорядоченный словарь, только элементы словаря - листы
book_dict = pyexcel.get_book_dict(file_name="example.xls")
# print(book_dict)
# Наконец, вы можете просто получить записи с pyexcel благодаря функции
# get_records (). Просто передайте аргумент file_name функции и обратно получите список словарей:
records = pyexcel.get_records(file_name="example.xls")
# print(records)
##Запись
# # можно также легко экспортировать массивы обратно в электронную таблицу.
# # Для этого используется функция save_as ()
# # с передачей массива и имени целевого файла в аргумент dest_file_name:
def test_get_dict(self):
expected = pe.get_dict(x="something")
assert expected == None
def pktgen(pkt,lpath,opfile):
files=[]
file_paths=[]
for root,dirs,fs in os.walk(pkt): #walking through dir tree to get its files and file_paths
for filename in fs:
files.append(filename[:-4])
filepath=os.path.join(root,filename)
file_paths.append(filepath)
sw=stopwords.words('english') # extracting common words like 'a','the' in english
sw=[x.encode("utf-8") for x in sw] # converting sw to utf-8 encoding
bw=pyexcel.get_dict(file_name='/home/suneha/Desktop/seminar stuff/badwords.xlsx',column_limit=2,name_columns_by_row=-1,name_rows_by_column=0)
dict=pyexcel.get_dict(file_name=lpath,start_row=2,column_limit=2,name_columns_by_row=-1, name_rows_by_column=0)
j=0
while j<len(files):
tree=ET.parse(file_paths[j]) # tree generation for xml file
root=tree.getroot()
count=0
for child in root: # counting no. of children
count=count+1
i=0
posts=[] # extracting posts from xml file
while i<count:
posts.append(root[i][2].text)
i=i+1
posts=[x for x in posts if x!=None]
i=0
while i<len(posts):
posts[i]=posts[i].encode("utf-8") #encoding posts from unicode to utf-8 str
i=i+1
i=0
bad=[]
fname=[]
while i<len(posts):
p=posts[i].split() # converting individual posts to list of words
p=[w for w in p if w.lower() not in sw] # removing stopwords from the post
b=[w for w in p if w.lower() in bw] # retaining bad words in post if any
bad.append(b)
bad=[x for x in bad if x!=[]] #removing empty values in bad
i=i+1
i=1
while i<len(bad):
k=0
while k<len(bad[i]): #converting list of list of words to list of words
bad[0].append(bad[i][k])
k=k+1
i=i+1
if len(bad)==0: #differentiating empty lists from non empty ones
NUM=0
SEV=0
else:
bad1=bad[0] #getting NUM and SEV for non empty word lists
bad1=[x.lower() for x in bad1]
NUM=len(bad1)
SEV=0
i=0
while i<len(bad1): #getting severity of each word and adding to get overall severity
SEV=SEV+int(bw[bad1[i]][0])
i=i+1
#extracting human consensus of corresponding file
t=0
if dict[files[j]][0]=='Y':
t=1
book=openpyxl.load_workbook(opfile) # writing to an xlsx file
sheet=book.active
row=[files[j],NUM,SEV,t]
sheet.append(row)
book.save(opfile)
j=j+1
def detectdemo(fpath,lpath,fname):
tree=ET.parse(fpath) # tree generation for xml file
root=tree.getroot()
count=0
for child in root: # counting no. of children
count=count+1
i=0
posts=[] # extracting posts from xml file
while i<count:
posts.append(root[i][2].text)
i=i+1
posts=[x for x in posts if x!=None]
i=0
while i<len(posts):
posts[i]=posts[i].encode("utf-8")
i=i+1
sw=stopwords.words('english') # extracting common words like 'a','the' in english
sw=[x.encode("utf-8") for x in sw] # converting sw to utf-8 encoding
bw=np.loadtxt('/home/suneha/Desktop/seminar stuff/badwords.txt',dtype=str) # extracting bad words from txt file
i=0
bad=[]
while i<len(posts):
p=posts[i].split() # converting individual posts to list of words
p=[w for w in p if w.lower() not in sw] # removing stopwords from the post
b=[w for w in p if w.lower() in bw] # retaining bad words in post if any
bad.append(b)
i=i+1
i=0
while i<len(posts):
if len(bad[i])!=0:
ans='Y'
break
i=i+1
if i==len(posts):
ans='N'
print "Is bullying present?\nprogram's answer:",ans
dict=pyexcel.get_dict(file_name=lpath,start_row=2,column_limit=2,name_columns_by_row=-1, name_rows_by_column=0)
fn=[]
if fname[-1]=='.':
fn=fname[:12]
if fname[-1]=='0':
fn=fname[:11]
if fname[-2]=='0':
fn=fname[:10]
if fname[-3]=='0':
fn=fname[:9]
if fname[-4]=='0':
fn=fname[:8]
else:
fn=fname
ans1=dict[fn]
ans1=str(ans1)[3:4]
print "actual scenario:",ans1
def gennumsev(fpath,fname,lpath):
tree=ET.parse(fpath) # tree generation for xml file
root=tree.getroot()
count=0
for child in root: # counting no. of children
count=count+1
i=0
posts=[] # extracting posts from xml file
while i<count:
posts.append(root[i][2].text)
i=i+1
posts=[x for x in posts if x!=None]
i=0
while i<len(posts):
posts[i]=posts[i].encode("utf-8")
i=i+1
sw=stopwords.words('english') # extracting common words like 'a','the' in english
sw=[x.encode("utf-8") for x in sw] # converting sw to utf-8 encoding
dict=pyexcel.get_dict(file_name=lpath,start_row=2,column_limit=2,name_columns_by_row=-1, name_rows_by_column=0)
fn=[]
if fname[-1]=='.':
fn=fname[:12]
if fname[-1]=='0':
fn=fname[:11]
if fname[-2]=='0':
fn=fname[:10]
if fname[-3]=='0':
fn=fname[:9]
if fname[-4]=='0':
fn=fname[:8]
else:
fn=fname
bw=pyexcel.get_dict(file_name='/home/suneha/Desktop/seminar stuff/badwords.xlsx',column_limit=2,name_columns_by_row=-1,name_rows_by_column=0)
i=0
bad=[]
while i<len(posts):
p=posts[i].split() # converting individual posts to list of words
p=[w for w in p if w.lower() not in sw] # removing stopwords from the post
b=[w for w in p if w.lower() in bw] # retaining bad words in post if any
bad=[x for x in bad if x!=[]]
bad.append(b)
i=i+1
i=1
while i<len(bad):
k=0
while k<len(bad[i]): #converting list of list of words to list of words
bad[0].append(bad[i][k])
k=k+1
i=i+1
if len(bad)==0: #differentiating empty lists from non empty ones
NUM=0
SEV=0
else:
bad1=bad[0] #getting NUM and SEV for non empty word lists
bad1=[x.lower() for x in bad1]
NUM=len(bad1)
SEV=0
i=0
while i<len(bad1): #getting severity of each word and adding to get overall severity
SEV=SEV+int(bw[bad1[i]][0])
i=i+1
t=dict[fn][0]
#book=openpyxl.load_workbook(opfile)
#sheet=book.active
t=str(t)
row=[NUM,SEV,t]
return row
def test_get_dict_from_array(self):
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
result = pe.get_dict(array=data)
assert result == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_get_dict_from_dict(self):
data = {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
result = pe.get_dict(adict=data)
assert result == data
def test_get_dict_from_records(self):
data = [{"X": 1, "Y": 2, "Z": 3}, {"X": 4, "Y": 5, "Z": 6}]
expected = {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
result = pe.get_dict(records=data)
assert result == expected
def test_save_a_dict3(self):
adict = {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
sheet = pe.get_sheet(adict=adict, name_columns_by_row=0)
sheet.save_to_database(self.session, Signature)
result = pe.get_dict(session=self.session, table=(Signature))
assert adict == result
def test_get_dict_from_sql(self):
adict = pe.get_dict(session=Session(), table=Signature)
assert adict == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}
def test_get_dict(self):
pe.get_dict(x="something")
def test_get_dict_from_memory(self):
data = [["X", "Y", "Z"], [1, 2, 3], [4, 5, 6]]
content = pe.save_as(dest_file_type="xls", array=data)
adict = pe.get_dict(file_content=content.getvalue(), file_type="xls")
assert adict == {"X": [1, 4], "Y": [2, 5], "Z": [3, 6]}