def setUpClass(cls):
search_path = sys.argv[0].split(':')
import_modules(search_path, 'entities')
EntityType.register(199, 'TestEntity', Entity)
# empty history
Database.instance().write('DELETE FROM history')
def login_action(request):
name = request.POST['name']
password = request.POST['password']
database = Database()
row = database.select_fetchall(
"""SELECT user.name,user.id,user.role_id
FROM user WHERE name =%s and password = %s
""", [name, password])
if len(row) == 0:
params = {"mes": "登录失败"}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
else:
# 创建或修改 session：
request.session['name'] = name
request.session['user_id'] = row[0]['id']
request.session['role_id'] = row[0]['role_id']
remark = "登录成功"
database.execute(
""" insert into log(type,user_id,remark,time)values(%s,%s,%s,%s)""",
[1, row[0]['id'], remark,
datetime.datetime.now()])
params = {"mes": "登录成功"}
return HttpResponseRedirect('/app/new_index') #跳转到index界面
def exit(request):
remark = "注销"
database = Database()
database.execute(""" insert into log(type,user_id,remark,time)values(%s,%s,%s,%s)""",[2,request.session['user_id'],remark,datetime.datetime.now()])
del request.session['name']
del request.session['user_id']
return HttpResponseRedirect('/login') #跳转到index界面
def statistics(request):
name = request.session.get('name',default=None)
if not name:
params={"mes":""}
templateFile = "login.html"
return render_to_response(
templateFile,
params,
RequestContext(request)
)
database = Database()
row = database.select_fetchall("""SELECT COUNT(comment.id) count,user.name
FROM user LEFT JOIN comment on user.id = comment.user_id GROUP BY user_id,user.name """, [])
count =[]
names = [];
for x in row:
count.append(x['count'])
names.append(x['name'])
params={
"name":name,
"names":names,
"count":count
}
templateFile = "statistics.html"
return render_to_response(
templateFile,
params,
RequestContext(request)
)
def index(request):
name = request.session.get('name', default=None)
user_id = request.session.get("user_id", default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
user = request.GET.get("user")
if not user:
user = ""
limit = 10
curpage = request.GET.get('curpage', None)
if not curpage:
curpage = 1
else:
curpage = int(curpage)
start = curpage * limit - limit
database = Database()
row = database.select_fetchall(
"""SELECT user.name,task.id,task.task,task.status
FROM user,task WHERE task.user_id = user.id and task.user_id = %s order by task.id desc limit %s,%s
""", [user_id, start, limit])
lenrow = database.select_fetchall(
"""SELECT count(task.id) len FROM task where user_id =%s """,
[user_id])
# users = database.select_fetchall(""" SELECT user.name,user.id
# FROM user """,[])
params = {"task": row, "count": lenrow[0]['len'], "curpage": curpage}
templateFile = "task/index.html"
return render_to_response(templateFile, params, RequestContext(request))
def jsonIndex(request):
name = request.session.get('name', default=None)
user_id = request.session.get("user_id", default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
user = request.GET.get("user")
if not user:
user = ""
limit = 10
curpage = request.GET.get('curpage', None)
if not curpage:
curpage = 1
else:
curpage = int(curpage)
start = curpage * limit - limit
database = Database()
row = database.select_fetchall(
"""SELECT user.name,task.id,task.task,task.status
FROM user,task WHERE task.user_id = user.id and task.user_id = %s order by task.id desc limit %s,%s
""", [user_id, start, limit])
lenrow = database.select_fetchall(
"""SELECT count(task.id) len FROM task where user_id =%s """,
[user_id])
data = {"row": row, "count": lenrow}
#ensure_ascii=False用于处理中文
return HttpResponse(json.dumps(data, ensure_ascii=False))
pass
def list(cls, typeid=None, name_pattern=None):
''' Lists entities with the given type identifier and whose
names match the given pattern. Both parameters are optional. '''
query = Entity.__list_query_all
query_parameters = { }
if typeid is not None:
if name_pattern is not None:
query = Entity.__list_query_by_type_and_name
query_parameters = { 'type': typeid, 'name': name_pattern }
else:
query = Entity.__list_query_by_type
query_parameters = { 'type': typeid }
elif name_pattern is not None:
query = Entity.__list_query_by_name
query_parameters = { 'name': name_pattern }
query += Entity.__list_query_order_by
selection = None
if query_parameters is not None:
selection = Database.instance().select(query, query_parameters)
else:
selection = Database.instance().select(query)
for row in selection:
# uniqueid, typeid, name, stateid, statevalue, lastcheckin
unique_id, row = row[0], (row[1], row[2], row[3], row[4], row[5])
yield Entity.__create_from_db_row(unique_id, row)
def getUser(request, id):
print id
database = Database()
data = database.select_fetchall(
""" SELECT user.name,user.id,sex,age,password FROM user where id=%s """,
[id])
#ensure_ascii=False用于处理中文
return HttpResponse(json.dumps(data[0], ensure_ascii=False))
def __init__(self):
print(
'###########################\n# S0 SBFspot bridge #\n###########################'
)
self.cfg = Config()
self.db = Database(self.cfg)
self.ser = Serial(self.cfg)
self.ntwrk = Network(self.cfg)
def save(self):
''' Inserts or updates the entity in the database. '''
db = Database.instance()
with db.writer():
row = Database.instance().select(Entity.__exists_query, self.unique_id).fetchone()
if row is None:
db.write(Entity.__insert_stmt, self.unique_id, self.entity_type.type_id, self.name, self.state.id, self.state_value, self.last_checkin)
else:
db.write(Entity.__update_stmt, self.name, self.state.id, self.state_value, self.last_checkin, self.unique_id)
def delUser(request, id):
print id
name = request.session.get('name', default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
role_id = request.session.get('role_id', default=None)
if role_id == 0: #只有操作员有权限删除
database = Database()
database.delOperation("user", id)
return HttpResponseRedirect('/usermanage/index') #跳转到index界面
def __init__(self, server):
self.lower = server.lower
self.logpath = server.get_config_dir("log.txt")
self.dbpath = server.get_config_dir("log.db")
self.sedchans = set()
self.db = Database("sqlite:///" + self.dbpath, cache_limit=None)
self.db.create_all(Base.metadata)
if os.path.exists(self.logpath):
# Perform migration
self.sql_migrate(lower=server.lower)
os.rename(self.logpath, self.logpath + ".old")
# Initialise db and shit
super().__init__(server)
def save_profile_json(username, information):
check_folder(Settings.profile_location)
if (Settings.profile_file_with_timestamp):
file_profile = os.path.join(
Settings.profile_location, username + '_' +
datetime.datetime.now().strftime("%Y-%m-%d %H-%M-%S") +
'.json')
else:
file_profile = os.path.join(Settings.profile_location,
username + '.json')
client = Database()
with open(file_profile, 'w') as fp:
fp.write(json.dumps(information, indent=4))
client.insert(information)
def addTask(request):
name = request.session.get('name', default=None)
user_id = request.session.get('user_id', default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
task = request.POST['task']
database = Database()
row = database.execute(
"""INSERT `task`(task,`status`,user_id) VALUES(%s,%s,%s)
""", [task, 0, user_id])
return HttpResponseRedirect('/task/index') #跳转到index界面
def index(request):
name = request.session.get('name', default=None)
role_id = request.session.get('role_id', default=False)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
database = Database()
users = database.select_fetchall(
""" SELECT user.name,user.id,password,role_id,sex,age
FROM user """, [])
params = {"user": users, "role_id": role_id}
templateFile = "usermanage/index.html"
return render_to_response(templateFile, params, RequestContext(request))
def _updateQ(self, s_id, a_id, reward, discount_factor, similarity,
best_future_utility):
if s_id not in self.q:
self.q[s_id] = {}
if a_id not in self.q[s_id]:
self.q[s_id][a_id] = 0
# this is a typical q-learning except for "similarity," which is factored
# in to help deal with how big the state space is
q_value = (
1 -
self.learning_rate) * self.q[s_id][a_id] + self.learning_rate * (
reward + similarity * discount_factor * best_future_utility)
self.q[s_id][a_id] = q_value
Database.updateQ(s_id, a_id, q_value)
def testRollback(self):
path = 'db/rollback.db'
db = Database.instance(path)
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
db.write('INSERT INTO test1 VALUES (1, 2)')
with db.writer():
db.write('INSERT INTO test1 VALUES (3, 4)')
raise RollbackException
try:
with db.writer():
raise Exception('unmanaged')
except:
pass # expected
db.write('INSERT INTO test1 VALUES (5, 6)')
for s in db.select('SELECT * FROM test1'):
print 'Row:',
for i in s:
print i,
print
print 'Dumping database:'
for d in db.dump():
print d
# Cleanup
import os
os.remove(path)
os.rmdir('db')
def run_iris_example():
iris_df = pd.read_csv("example_datasets/iris.csv")
db_connection = {
"drivername": "sqlite",
"database": "",
"path": "",
}
db = Database(db_connection=db_connection,
dataframe=iris_df,
dfname="iris")
kmeans = lib.kmeans.KMeans(db)
tablename = "iris"
feature_names = ["sepallength", "sepalwidth", "petallength", "petalwidth"]
k = 3
model_identifier = "example"
normalizations = [None, "min-max", "z-score"]
model = kmeans.create_model(tablename, feature_names, k, model_identifier,
normalizations[0])
model.estimate(max_steps=30)
print(f"Information: {model.get_information()}")
axis_order = [3, 0, 2]
model.visualize(feature_names, axis_order)
def query(cls, time_from, time_to, entity_id, limit, offset):
''' Returns history at most "limit" records starting from "offset"
between "time_from" and "time_to" for the entity with the given "entity_id" identifier.
The "limit" and "offset" parameters are required the rest are optional. '''
conditions = []
parameters = dict()
if time_from is not None:
conditions.append('timestamp >= :ts_from')
parameters['ts_from'] = time_from
if time_to is not None:
conditions.append('timestamp <= :ts_to')
parameters['ts_to'] = time_to
if entity_id is not None:
conditions.append('entityid = :eid')
parameters['eid'] = entity_id
query = 'SELECT timestamp, entityid, entityname, action, type FROM ' + EntityHistory.__tablename__
if len(conditions) > 0:
query = query + ' WHERE ' + ' AND '.join(conditions)
query = query + ' ORDER BY timestamp DESC'
if limit is not None:
query = query + ' LIMIT :limit'
parameters['limit'] = limit
if offset is not None:
query = query + ' OFFSET :offset'
parameters['offset'] = offset
db = Database.instance()
for timestamp, entityid, entityname, action, actiontype in db.select(query, parameters):
yield EntityHistory(timestamp, entityid, entityname, action, actiontype)
def addUser(request):
name = request.session.get('name', default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
loginname = request.POST['loginname']
sex = request.POST['sex']
age = request.POST['age']
# password = request.POST['password']
password = "******"
database = Database()
row = database.execute(
"""INSERT `user`(name,`password`,sex,age) VALUES(%s,%s,%s,%s)
""", [loginname, password, sex, age])
return HttpResponseRedirect('/usermanage/index') #跳转到index界面
def printall(self):
print 'DB | uniqueid | typeid | name | state | statevalue | assignedid | lastcheckin'
print 'DB | -------- | ------ | ---- | ----- | ---------- | ---------- | -----------'
for row in Database.instance().select('SELECT * FROM entity'):
print 'DB',
for value in row:
print '|', value,
print
def updateUser(request):
name = request.session.get('name', default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
loginname = request.POST['loginname']
id = request.POST['id']
sex = request.POST['sex']
age = request.POST['age']
password = request.POST['password']
database = Database()
row = database.execute(
"""update `user` set name=%s,password=%s,sex=%s,age=%s where id=%s
""", [loginname, password, sex, age, id])
return HttpResponseRedirect('/usermanage/index') #跳转到index界面
def check_database_table(cls):
''' Checks whether the related database table exists
and creates it if is does not. '''
db = Database.instance()
with db.writer():
try:
db.select(EntityHistory.__table_exists)
except:
db.write(EntityHistory.__table_create)
def create_user(self, username, password):
''' Inserts a new user into the database with the given credentials. '''
db = Database.instance()
with db.writer():
if db.select(Authentication.__user_exists_query, username.lower()).fetchone():
return False
else:
db.write(Authentication.__user_create_stmt, username.lower(), password)
return True
def test_convert_local_ts_to_utc_on_local_timezone(self):
"""
The database sunportal uses contains timestamps not in UTC timezone (as per definition), but
instead in local timezone. convert_local_ts_to_utc converts those timestamps back to UTC for
further processing and timezone independent front-ends.
"""
import time
ts_in_utc = int(
time.time()) # unix timestamps are per definition always in utc
timezone = Database.get_local_timezone(None)
utc_offset = timezone.utcoffset(None).seconds
ts_in_local_time = ts_in_utc + utc_offset
res = Database.convert_local_ts_to_utc(None, ts_in_local_time,
timezone)
self.assertEqual(res, ts_in_utc)
def addComment(request):
name = request.session.get('name',default=None)
if not name:
params={"mes":""}
templateFile = "login.html"
return render_to_response(
templateFile,
params,
RequestContext(request)
)
title = request.POST['title']
comment = request.POST['comment']
name = request.session.get('name',default=None)
dateTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
user_id =request.session['user_id']
database = Database()
row = database.execute("""INSERT `comment`(title,`comment`,user_id,create_time) VALUES(%s,%s,%s,%s)
""", [title,comment,user_id,dateTime])
return HttpResponseRedirect('/app') #跳转到index界面
def edit_user(self, uid, username, password):
''' Modifies the credentials of the user with the given identifier. '''
db = Database.instance()
with db.writer():
existing_uid = db.select(Authentication.__user_exists_query, username.lower()).fetchone()[0]
if existing_uid and existing_uid != uid:
return False
else:
db.write(Authentication.__user_edit_stmt, username.lower(), password, uid)
return True
def testInMemory(self):
db = Database.in_memory_instance('abc')
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
db.write('INSERT INTO test1 VALUES (1, 2)')
db.close()
db = Database.in_memory_instance('abc')
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
db.write('INSERT INTO test1 VALUES (:a, :b)', { 'a': 3, 'b': '4' })
for s in db.select('SELECT * FROM test1'):
print 'Row:',
for i in s:
print i,
print
print 'Dumping database:'
for d in db.dump():
print d
def testSelectTableExists(self):
db = Database.in_memory_instance('def')
try:
print 'Select #1:', db.select('SELECT 1 FROM test1')
except: pass
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
try:
print 'Select #1:', db.select('SELECT 1 FROM test1')
except: pass
db.close()
def __init__(self, args):
self.db = Database(args)
districts_obj = Districts(args.dist_id)
self.districts = districts_obj.get_wss_list_each_district(self.db)
self.main_dir = args.output + datetime.datetime.now().strftime(
'%Y%m%d_%H%M%S') + "_epanet_data"
self.exportdir_list = []
wss_list_obj = WaterSupplySystems()
if args.elevation == True:
wss_list_obj.update_elevations(self.db)
self.wss_list = wss_list_obj.get_wss_list(self.db)
def __init__(self, *args, **kwargs):
self.logger = set_logger()
self.cfg = Config(False)
super().__init__(*args,
command_prefix=get_prefix,
**kwargs,
owner_id=int(self.cfg.get('Core.OwnerID')))
self.db = Database(self)
self.utils = Utils(self)
def __init__(self, server):
self.lower = server.lower
self.logpath = server.get_config_dir("log.txt")
self.dbpath = server.get_config_dir("log.db")
self.sedchans = set()
self.db = Database("sqlite:///" + self.dbpath, cache_limit=None)
self.db.create_all(Base.metadata)
if os.path.exists(self.logpath):
# Perform migration
self.sql_migrate(lower=server.lower)
os.rename(self.logpath, self.logpath + ".old")
# Initialise db and shit
super().__init__(server)
def _selectAction(self, recommended_a_id=None):
# if no action is recommended or we randomly roll below our
# random_action_rate, select a random action. TODO it might be a good idea
# to have state similarity here to pick a closest observed action
possible_actions = getValidActionsInState(self.s)
possible_action_ids = [
Database.upsertAction(action) for action in possible_actions
]
if not recommended_a_id or np.random.random(
) < self.random_action_rate or recommended_a_id not in possible_action_ids:
random_action_index = np.random.randint(len(possible_action_ids))
random_action_id = possible_action_ids[random_action_index]
self._printIfVerbose("agent randomly chose",
possible_actions[random_action_index])
return random_action_id, possible_actions[random_action_index]
else:
action = Database.getAction(recommended_a_id)
self._printIfVerbose("agent chose", action)
Stats.recordStat("{}{}".format(
"chosen_action={}".format(action["action"]),
"_id={}".format(action["card_id"])
if "card_id" in action and action["card_id"] != None else ""))
return recommended_a_id, action
def main_entry():
''' Loads, configures and start system modules,
then waits for the exit condition, finally
it stop all started system modules '''
database = Database.instance()
ModuleLoader.load_and_configure_modules( database )
ModuleLoader.start_modules()
if sysargs.server:
__wait_for_exit_signal()
else:
raw_input('Press ENTER to finish')
ModuleLoader.stop_modules()
def changeStatus(request):
id = request.POST.get('id', None)
type = request.POST.get('type', None)
if id and type:
database = Database()
if type == "start":
database.execute(""" update task set status = %s where id = %s """,
[1, id])
if type == "success":
database.execute(""" update task set status = %s where id = %s """,
[2, id])
if type == "fail":
database.execute(""" update task set status = %s where id = %s """,
[3, id])
return HttpResponse(json.dumps(["success"], ensure_ascii=False))
else:
return HttpResponse(json.dumps(["failed"], ensure_ascii=False))
def get_result(self, fdata):
print(fdata)
host = self.ui_sel.host.text()
port = self.ui_sel.port.text()
user = self.ui_sel.user.text()
passwd = self.ui_sel.passwd.text()
db = self.ui_sel.database.text()
charset = self.ui_sel.charset.text()
table = self.ui_sel.table.text()
Database(host, port, user, passwd, db, charset, table).insert_store(fdata)
self.ui_sel.result.insertPlainText("店名：" + fdata['名称'] + "\n")
text_cur = self.ui_sel.result.textCursor().End
self.ui_sel.result.moveCursor(text_cur)
pass
def db():
print 'Retrieving from database...'
# connects to a mysql db
db = Database('172.17.0.2', 3306, 'root', 'test-pass', 'mysql')
db.connect()
# retrieves a hash table of data from sql
data = db.query('SELECT * FROM People')
print data[0]['id_value']
print data[0]['name']
db.close()
print 'Database connection closed'
def index2(request):
name = request.session.get('name', default=None)
if not name:
params = {"mes": ""}
templateFile = "login.html"
return render_to_response(templateFile, params,
RequestContext(request))
user = request.GET.get("user")
if not user:
user = ""
limit = 10
curpage = request.GET.get('curpage', None)
if not curpage:
curpage = 1
else:
curpage = int(curpage)
start = curpage * limit - limit
database = Database()
if user != "":
row = database.select_fetchall(
"""SELECT user.name,log.id,log.remark,log.type,log.time
FROM user,log WHERE log.user_id = user.id and user.id=%s order by time desc limit %s,%s
""", [user, start, limit])
lenrow = database.select_fetchall(
"""SELECT count(log.id) len FROM log where log.user_id = %s """,
[user])
else:
row = database.select_fetchall(
"""SELECT user.name,log.id,log.remark,log.type,log.time
FROM user,log WHERE log.user_id =user.id order by time desc limit %s,%s
""", [start, limit])
lenrow = database.select_fetchall(
"""SELECT count(log.id) len FROM log """, [])
for x in row:
x['time'] = datetime_to_string(x["time"])
users = database.select_fetchall(
""" SELECT user.name,user.id
FROM user """, [])
params = {
"users": users,
"log": row,
"count": lenrow[0]['len'],
"curpage": curpage
}
templateFile = "log/index2.html"
return render_to_response(templateFile, params, RequestContext(request))
def dashboard_main():
# Database Stats
db = Database()
db_stats = db.dbconn.command("dbstats", 1024)
db_status = int(db_stats.get('ok', -1))
if db_status == 1:
db_status_class = "success"
else:
db_status_class = "danger"
collections = {
collection: db.dbconn[collection].count_documents({})
for collection in db.dbconn.list_collection_names()
}
return render_template('dashboard_main.html',
db_stats=db_stats,
db_status_class=db_status_class,
collections=collections)
def testDefault(self):
db = Database.instance()
db.write('CREATE TABLE test1(a PRIMARY KEY, b)')
db.write('INSERT INTO test1 VALUES (1, 2)')
for s in db.select('SELECT * FROM test1'):
print 'Row:',
for i in s:
print i,
print
print 'Dumping database:'
for d in db.dump():
print d
# Cleanup
import os
os.remove('db/default.db')
os.rmdir('db')
def run_covertype_example():
covertype_df = pd.read_csv("example_datasets/covertype.csv")
db_connection = {
"drivername": "sqlite",
"database": "covertype",
"path": str(Path.home()) + "/covertype.db",
}
init_time = time.time()
db = Database(db_connection=db_connection,
dataframe=covertype_df,
dfname="covertype")
dtc = lib.DecisionTreeClassifier.DecisionTreeClassifier(db)
dtc.initialize(dataset='covertype')
dtc.train_test_split(ratio=0.2, seed=1, encode=True)
dtc.estimate(max_samples=2, max_mutual_inf=0.1)
dtc.predict_table()
acc = dtc.score()
print(
"Accuracy of Covertype Dataset with 20% training data and stopping criteria by .1 mutual information is: {}"
.format(float(acc)))
print("Process took {} seconds".format(time.time() - init_time))
def jsonHome(request):
name = request.session.get('name',default=None)
if not name:
params={"mes":""}
templateFile = "login.html"
return render_to_response(
templateFile,
params,
RequestContext(request)
)
title = request.GET.get("title")
user = request.GET.get("user")
title = request.GET.get("title")
user = request.GET.get("username")
curpage = request.GET.get('curpage',None)
if not title:
title="%%"
if not user:
user=""
database = Database()
limit = 10
if not curpage:
curpage=1
else:
curpage =int(curpage)
start = curpage*limit -limit
if user!="":
row = database.select_fetchall("""SELECT comment.id,comment.title,
comment.comment,user.name,comment.create_time
FROM comment,user WHERE user.id = comment.user_id and comment.title like %s and user.id = %s order by comment.create_time desc
limit %s,%s""", [title,user,start,limit])
lenrow = database.select_fetchall("""SELECT count(comment.id) len FROM comment where comment.user_id = %s and comment.title like %s """, [user,title])
else:
row = database.select_fetchall("""SELECT comment.id,comment.title,
comment.comment,user.name,comment.create_time
FROM comment,user WHERE user.id = comment.user_id and comment.title like %s order by comment.create_time desc
limit %s,%s""", [title,start,limit])
lenrow = database.select_fetchall("""SELECT count(comment.id) len FROM comment where comment.title like %s """, [title])
for x in row:
x['create_time'] = x['create_time'].strftime('%Y-%m-%d %H:%M:%S')
data = {"row": row,"count":lenrow}
#ensure_ascii=False用于处理中文
return HttpResponse(json.dumps(data, ensure_ascii=False))
pass
def run_iris_example():
iris_df = pd.read_csv("example_datasets/iris.csv")
db_connection = {
"drivername": "sqlite",
"database": "iris",
"path": str(Path.home()) + '/iris.db',
}
init_time = time.time()
db = Database(db_connection=db_connection,
dataframe=iris_df,
dfname="iris")
dtc = lib.DecisionTreeClassifier.DecisionTreeClassifier(db)
dtc.initialize(dataset='iris')
dtc.train_test_split()
dtc.estimate()
dtc.predict_table()
acc = dtc.score()
print("\nDecision Tree Visualized:\n")
dtc.visualize_tree()
print(
"Accuracy of Iris Dataset with 80% training data and default stopping criteria is: {}"
.format(float(acc)))
print("Process took {} seconds".format(time.time() - init_time))
def count(cls, time_from, time_to, entity_id):
''' Returns the number of records between "time_from" and "time_to"
for the entity with the given "entity_id" identifier.
All parameters are optional. '''
conditions = []
parameters = dict()
if time_from is not None:
conditions.append('timestamp >= :ts_from')
parameters['ts_from'] = time_from
if time_to is not None:
conditions.append('timestamp <= :ts_to')
parameters['ts_to'] = time_to
if entity_id is not None:
conditions.append('entityid = :eid')
parameters['eid'] = entity_id
query = 'SELECT COUNT(rowid) FROM ' + EntityHistory.__tablename__
if len(conditions) > 0:
query = query + ' WHERE ' + ' AND '.join(conditions)
return Database.instance().select(query, parameters).fetchone()[0]
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.config = config
self.bot_config = self.config['bot']
self.session = aiohttp.ClientSession()
self.logger = logger
self.cache = Cache()
self.database = Database(self.config['database'])
self.pool = None
self.debug = debug
self.title = self.bot_config['title']
self.base_extensions = self.bot_config['extensions']['base']
self.debug_extensions = self.bot_config['extensions']['debug']
if self.debug:
self.bot_extensions = self.base_extensions + self.debug_extensions
self.token = self.bot_config['debug_token']
self.prefix = self.bot_config['debug_prefix']
else:
self.bot_extensions = self.base_extensions
self.token = self.bot_config['production_token']
self.prefix = self.bot_config['production_prefix']
class Logger(Callback):
formatters = {"NICK": nickfmt,
"QUIT": quitfmt,
"PART": partfmt,
"NOTICE": noticefmt,
"PRIVMSG": msgfmt,
"JOIN": joinfmt}
def __init__(self, server):
self.lower = server.lower
self.logpath = server.get_config_dir("log.txt")
self.dbpath = server.get_config_dir("log.db")
self.sedchans = set()
self.db = Database("sqlite:///" + self.dbpath, cache_limit=None)
self.db.create_all(Base.metadata)
if os.path.exists(self.logpath):
# Perform migration
self.sql_migrate(lower=server.lower)
os.rename(self.logpath, self.logpath + ".old")
# Initialise db and shit
super().__init__(server)
def cache_event(self, session, event):
if event.sender_nick is None:
return
cached_event = {
'timestamp': event.timestamp,
'nick': event.sender_nick,
'context': event.context,
'data': event.data
}
if event.type == 'PRIVMSG' and event.context.startswith("#"):
query = session.query(LastSpokeCache).filter(
LastSpokeCache.nick == event.sender_nick,
LastSpokeCache.context == event.context
)
if query.count():
query.update(cached_event)
else:
session.add(LastSpokeCache(**cached_event))
if event.type == 'NICK':
cached_event['newnick'] = event.payload_lower[1:]
else:
cached_event['newnick'] = None
if event.type in HAS_SENDER:
query = session.query(LastEventCache).filter(
LastEventCache.nick == event.sender_nick,
LastEventCache.context == event.context
)
if query.count():
query.update(cached_event)
else:
session.add(LastEventCache(**cached_event))
def sql_migrate(self, logpath=None, lower=str.lower):
""" Migrate existing logs to the new SQL database """
if logpath is None:
logpath = self.logpath
with open(logpath) as logfile:
while True:
with self.db() as session:
finished = True
for line in islice(logfile, None, 100):
finished = False
try:
line = line.rstrip("\n").rstrip("\r")
timestamp, text = line.split(" ", 1)
event = make_event(
text,
timestamp=datetime.utcfromtimestamp(
float(timestamp)
)
)
session.add(event)
self.cache_event(session, event)
except:
print("[Logger] Warning: Could not parse %s" % line)
raise
if finished:
break
@Callback.background
@command("log_migrate", "(.+)", admin=True)
def partial_migration(self, server, message, path):
yield "Migration started..."
self.sql_migrate(logpath=path, lower=server.lower)
yield "Migration complete."
def traceuser(self, hostmask, timestamp):
# Load the logs into memory to compute graph
# FIXME: Paginate log loading
start_nick, x = hostmask.split("!", 1)
start_ident, start_mask = x.split("@", 1)
userinf = {(start_nick, start_ident, start_mask)}
with self.db() as session:
logs = session.query(
Event
).filter(
Event.type.in_(['NICK', 'JOIN']),
Event.timestamp >= timestamp,
).order_by(Event.timestamp).all()
for log in logs:
nick, x = log.sender.split("!", 1)
ident, mask = x.split("@", 1)
if log.type == "NICK" and (nick, ident, mask) in userinf:
userinf.add((log.payload[1:], ident, mask))
elif log.type == "JOIN" and any(
log.sender_nick == self.lower(n) or
(ident, mask) == (i, m) for n, i, m in userinf
):
userinf.add((nick, ident, mask))
return userinf
@Callback.inline
def log(self, server, line) -> "ALL":
timestamp = datetime.utcnow()
event = make_event(line, timestamp=timestamp)
self.db.add(event)
def cache_update(self):
with self.db() as session:
last_entry = session.query(
func.max(LastEventCache.timestamp)
).first()[0] or 0
events = session.query(Event).filter(
Event.timestamp > last_entry,
Event.type.in_(HAS_SENDER)
).all()
for event in events:
self.cache_event(session, event)
@Callback.background
def flush(self, server, line) -> "ALL":
if len(self.db.cache) > 32:
self.db.flush()
self.cache_update()
@command("seen lastseen", r"(\S+)")
def seen(self, server, msg, user):
if server.eq(user, msg.address.nick):
return "04⎟ You're right there!"
context = server.lower(msg.context)
nick = server.lower(user)
# Don't allow seen for pms, for confidentiality
if not context.startswith("#"):
return
self.cache_update()
with self.db() as session:
last = session.query(
LastEventCache
).filter(
(LastEventCache.context == context)
| (LastEventCache.context == None),
(LastEventCache.nick == nick)
| (LastEventCache.newnick == nick)
).order_by(
LastEventCache.timestamp.desc()
).first()
if last is None:
return "04⎟ I haven't seen %s yet." % user
event = make_event(
last.data,
timestamp=last.timestamp,
key=server.lower
)
message = self.formatters[event.type](event)
timestamp = last.timestamp
host = event.sender
if server.isIn(user, server.channels.get(context)):
status = " · \x0312online now"
else:
for nick, _, _ in self.traceuser(host, timestamp):
if server.isIn(nick, server.channels.get(context)):
status = " · \x0312online as %s" % nick
break
else:
status = ""
return "%s · \x1d%s%s" % (message, timefmt(timestamp), status)
@command("last lastspoke lastmsg", r"(\S+)")
def lastspoke(self, server, msg, user):
if server.eq(user, msg.address.nick):
return "04⎟ You just spoke!"
context = server.lower(msg.context)
nick = server.lower(user)
# Don't allow seen for pms, for confidentiality
if not context.startswith("#"):
return
self.cache_update()
with self.db() as session:
last = session.query(LastSpokeCache).filter(
LastSpokeCache.context == context,
LastSpokeCache.nick == nick
).first()
if last is None:
return "04⎟ I haven't seen %s speak yet." % user
event = make_event(last.data, timestamp=last.timestamp)
return "%s · \x1d%s" % (msgfmt(event), timefmt(last.timestamp))
@command("sedon", rank="@")
def sedon(self, server, msg):
self.sedchans.add(server.lower(msg.context))
return "04⎟ Turned on sed."
@command("sedoff", rank="@")
def sedoff(self, server, msg):
self.sedchans.remove(server.lower(msg.context))
return "04⎟ Turned off sed."
# @msghandler
def substitute(self, server, msg):
raise NotImplementedError
if not server.isIn(msg.context, self.sedchans):
return
match = re.match(r"^(\S+:\s+)?s(\W)(.*?)\2(.*?)(\2g?)?$", msg.text)
if match:
target, sep, pattern, sub, flags = match.groups()
if target is not None:
target = target.rstrip(": ")
if flags is not None:
flags = set(flags[1:])
pattern = re.escape(pattern)
for timestamp, line in reversed(self.logs):
# TODO: implement real regular expressions
# also self-regex
try:
evt = IRCEvent(line)
if (
evt.type == "PRIVMSG" and
server.eq(msg.context, evt.args[0]) and
(
target is None or
server.eq(target, evt.sender.nick)
) and
not re.match(
r"^(\S+:\s+)?s(\W)(.*?)\2(.*?)(\2g?)?$",
evt.args[1]
) and
re.search(pattern, evt.args[1], flags=re.IGNORECASE)
):
evt.args[1] = re.sub(
pattern,
"\x1f%s\x1f" % sub,
evt.args[1],
count=0 if 'g' in flags else 1, flags=re.IGNORECASE
)
return msgfmt(evt)
except:
print("[Logger] Warning: Could not parse %s" % line)
return "04⎟ No matches found."
def __destroy__(self, *_):
self.db.flush()
def find(cls, unique_id):
''' Returns the entity registered with "unique_id" identifier. '''
row = Database.instance().select(Entity.__query_find, unique_id).fetchone()
if row:
return Entity.__create_from_db_row(unique_id, row)
def delete_user(self, uid):
''' Deletes the user with the given identifier. '''
db = Database.instance()
with db.writer():
db.write(Authentication.__user_delete_stmt, uid)
class DatabaseTest(unittest.TestCase):
DEBUG_Database_Messages = False
DEBUG_Database_Contents = False
def __init__(self, methodName="runTest"):
unittest.TestCase.__init__(self, methodName=methodName)
# self.path = ':memory:'
self.path = "test.db"
self.db = None
def setUp(self):
Database.DEBUG = DatabaseTest.DEBUG_Database_Messages
self.db = Database(self.path)
def tearDown(self):
if DatabaseTest.DEBUG_Database_Contents:
for line in self.db.dump():
print "D|", line
os.remove(self.path)
def testSelect(self):
""" get cursor for query """
cursor = self.db.select("SELECT 1 as col1, 'abc' as col2")
self.assertIsNotNone(cursor)
""" fetch a row from the cursor """
row = cursor.fetchone()
self.assertIsNotNone(row)
""" check returned values """
self.assertEquals(row[0], 1)
self.assertEquals(row[1], "abc")
self.assertEquals(row["col1"], 1)
self.assertEquals(row["col2"], "abc")
def testWrite(self):
""" Load some sample data into the database """
self.db.write("create table test(a, b, c)")
self.db.write("insert into test values (?, ?, ?)", 1, 2, 3)
self.db.write("insert into test values ('abc', 'def', null)")
self.db.write("insert into test values (:a, :b, :c)", {"a": 1, "b": 2, "c": 9})
""" Select row count """
(numrows,) = self.db.select("select count(*) from test").fetchone()
self.assertEquals(numrows, 3)
""" Select all rows """
cursor = self.db.select("select * from test")
for row in cursor:
""" Test values """
self.assertIn(row["a"], (1, "abc"))
self.assertIn(row["b"], (2, "def"))
self.assertIn(row["c"], (3, 9, None))
def testWriter(self):
with self.db.writer():
self.db.write("create table writer (id, name)")
self.db.write("insert into writer values (?, ?)", 1, "abc")
self.db.write("insert into writer values (:id, :nm)", {"id": 2, "nm": "def"})
with self.db.writer():
for (num,) in self.db.select("select count(*) from writer"):
self.assertEquals(num, 2)
for row in self.db.select("select * from writer"):
for k in row.keys():
self.assertIn(row[k], (1, 2, "abc", "def"))
def testThreads(self):
thread_exceptions = []
def runt():
try:
self.db.select("select 1 as one")
except Exception as ex:
thread_exceptions.append(ex)
def wrt():
try:
thread_id = str(id(threading.current_thread()))
with self.db.writer() as wr:
wr.execute("create table th_" + thread_id + "(a, b, c)")
wr.execute("insert into th_" + thread_id + " values (?, ?, ?)", 1, 2, 3)
wr.execute("insert into th_" + thread_id + " values (?, ?, ?)", 5, 4, 6)
wr.execute("insert into th_" + thread_id + " values (:a, :b, :c)", {"a": 9, "b": 8, "c": 7})
(numrows,) = self.db.select("select count(*) from th_" + thread_id).fetchone()
self.assertEquals(numrows, 3)
except Exception as ex:
thread_exceptions.append(ex)
traceback.print_exc()
import threading
threads = []
for x in xrange(3): # @UnusedVariable
th = threading.Thread(target=wrt)
th.start()
threads.append(th)
for x in xrange(3): # @UnusedVariable
th = threading.Thread(target=runt)
th.start()
threads.append(th)
for th in threads:
th.join()
self.assertEquals(len(thread_exceptions), 0, "Exceptions: " + str(thread_exceptions))
def delete(cls, unique_id):
''' Deletes the entity from the database with the given identifier. '''
db = Database.instance()
with db.writer():
db.write(Entity.__delete_stmt, unique_id)
def authenticate(self, username, password_hash):
''' Executes authentication with the given credentials,
returns the session information if it succeeds. '''
userid, admin = Database.instance().select(Authentication.__auth_query, username.lower(), password_hash).fetchone()
if userid:
return (self.__initialize_session(userid), admin)
def log(cls, entity, action, action_type):
''' Saves an entry to the database. '''
db = Database.instance()
with db.writer():
db.write(EntityHistory.__insert_stmt, time.time(), entity.unique_id, entity.name, action, action_type)
def setUp(self):
Database.DEBUG = DatabaseTest.DEBUG_Database_Messages
self.db = Database(self.path)
def list_users(self):
''' Lists parameters of all known users in the database. '''
for uid, username, admin in Database.instance().select(Authentication.__list_query):
yield (uid, username, admin)
