def setups():
"""set up classifier if not yet trained"""
if philosophyfilter.is_ready():
return
db.close()
db.connection(db='test_opp')
ham = scraper.Doc(url='http://umsu.de/papers/magnetism2.pdf')
ham.load_from_db()
ham.content = readfile(os.path.join(testdir, 'attitudes.txt'))
ham.update_db()
spam = scraper.Doc(url='http://umsu.de/papers/spam.pdf')
spam.load_from_db()
spam.content = """
Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do
eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut
enim ad minim veniam, quis nostrud exercitation ullamco laboris
nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor
in reprehenderit in voluptate velit esse cillum dolore eu
fugiat nulla pariatur. Excepteur sint occaecat cupidatat non
proident, sunt in culpa qui officia deserunt mollit anim id est
laborum.
"""
spam.update_db()
cur = db.cursor()
query = "SELECT cat_id FROM cats WHERE label=%s LIMIT 1"
cur.execute(query, ('philosophy',))
cat_id = cur.fetchall()[0]
query = ("INSERT IGNORE INTO docs2cats (doc_id, cat_id, strength, is_training)"
"VALUES (%s, %s, %s, %s)")
cur.execute(query, (ham.doc_id, cat_id, 1, 1))
cur.execute(query, (spam.doc_id, cat_id, 0, 1))
philosophyfilter.update()
def meovaobep(tenmeovat):
if request.method == 'POST':
search = request.form.get("search")
listData = searching_meo(search)
return render_template('meovaobep.html',
getLink=getlinkstatic(),
listmeovat=listData,
menu=getlinkstatic())
else:
results = Meovaobep.query.\
with_entities(Meovaobep.name, Meovaobep.mo_ta, Meovaobep.image).\
join(Meovat).filter(Meovaobep.id_meo == Meovat.id).filter(Meovat.name == tenmeovat.decode('utf-8')).all()
meo = []
for result in results:
jsonData = {
"name": result[0],
"mota": result[1],
"image": result[2]
}
meo.append(jsonData)
connection().close()
return render_template('app_meovat.html',
getLink=getlinkstatic(),
meo=meo,
title=tenmeovat,
menu=getlinkstatic())
def main():
"""Connect to the server and run the bot forever"""
main_args = parse_args()
password = read_password(main_args.username)
bot = BaguetteJabberBot(main_args.username, password)
bot.room = main_args.room
bot.fromm = main_args.fromm
bot.mail_to = main_args.to
bot.subject = main_args.subject
bot.nick = main_args.nick
# create a regex to check if a message is a direct message
bot.direct_message_re = re.compile(r'^%s?[^\w]?' % main_args.nick)
db.connection(main_args.mongoUrl, main_args.mongoUser, main_args.mongoPass)
for modname,module in inspect.getmembers(commands, inspect.ismodule):
if modname in ('base', 'pkgutil'):
continue
for cmdname,function in inspect.getmembers(module, inspect.isfunction):
if getattr(function, '_jabberbot_command', False):
bot.register_command(cmdname, function)
elif cmdname == "init":
function()
try:
bot.muc_join_room(main_args.room, main_args.nick)
except AttributeError:
# Connection error is check after
pass
bot.serve_forever()
def hello():
if request.headers['Content-Type'] == 'application/json':
data_responce = {
'ok': Response().status,
'error': {
'code': Response().status_code,
'message': 'POST data accepted'
}
}
js = json.dumps(data_responce)
req = request.json
print(req, type(req), type(request.json))
c, conn = connection()
c.execute("INSERT INTO data (name, value, date) VALUES (%s, %s, %s)",
(req['name'], req['value'], req['date']))
conn.commit()
c.close()
conn.close()
return js
else:
name = request.form['name']
value = request.form['value']
date = request.form['date']
c, conn = connection()
c.execute("INSERT INTO data (name, value, date) VALUES (%s, %s, %s)",
(name, value, date))
conn.commit()
c.close()
conn.close()
return render_template('form_action.html',
name=name,
value=value,
date=date)
def meovaobep(tenmeovat):
cursor = connection().cursor()
query = "SELECT meobep.mota, meobep.img from meobep inner join meovat on meobep.ID=meovat.ID where meovat.Ten like %s"
cursor.execute(query, (tenmeovat, ))
results = cursor.fetchall()
meo = []
for result in results:
jsonData = {"image": result[1], "mota": result[0].decode('utf-8')}
meo.append(jsonData)
connection().close()
return render_template('app_meovat.html',
getLink=getlinkstatic(),
meo=meo,
title=tenmeovat)
def login():
data = request.get_json()
email = data['email']
password = data['password']
# password=md5(password.encode())
# password=password.hexdigest()
db, c = connection()
query = ("SELECT password FROM teachers WHERE username= %s")
c.execute(query, (email, ))
result = c.fetchall()
if len(result) == 0:
msg = {'error': "email doesn't exists"}
return jsonify(msg), 400
elif result[0][0] == password:
token = jwt.encode(
{
'email': email,
'exp':
datetime.datetime.utcnow() + datetime.timedelta(minutes=60)
}, app.config['SECRET_KEY'])
msg = {'success': "Login succesfull", 'token': token.decode('UTF-8')}
return jsonify(msg), 200
elif result[0][0] != password:
msg = {'error': "incorrect password"}
return jsonify(msg), 404
def get_definitions(user_locale, path):
db_connection = db.connection()
entity_definitions = []
entity = db.Entity(user_locale=user_locale)
for entity_definition_keyname in [x.keyname for x in db_connection.query('SELECT keyname FROM entity_definition WHERE public_path = %s;', path)]:
entity_definitions.append(entity.get(entity_id=0, entity_definition_keyname=entity_definition_keyname, full_definition=True, limit=1, only_public=True))
return entity_definitions
def tskdeldoc():
cm, con = connection()
kkd = request.args.get("docid")
ddlt2 = "delete from task_document where task_id='" + kkd + "'"
cm.execute(ddlt2)
con.commit()
return jsonify(status="ok")
def deldoc():
did = request.args.get("docid")
d = "delete from document where document_id='" + did + "'"
cm, con = connection()
cm.execute(d)
con.commit()
return jsonify(status="ok")
def add_request(self, jobname, run_cmd, args, max_vmem,
cpu_bound = True, force = False):
job_id = 0
with db.connection(self):
job_id = self._add_request(jobname, run_cmd, args,
max_vmem, cpu_bound, force)
return job_id
def register():
try:
form = RegistrationForm(request.form)
session['logged_in'] = False
if request.method == "POST" and form.validate():
username_p = str(form.username.data)
email = str(form.email.data)
password = sha256_crypt.encrypt((str(form.password.data)))
c, conn = connection()
x = c.execute("SELECT * FROM DG_User WHERE username = (%s)", [thwart(username_p)])
if int(x) > 0:
flash("That username is already taken, please choose another")
return render_template('register.html', form=form)
else:
c.execute("INSERT INTO DG_User (username, password, email) VALUES (%s, %s, %s)", (username_p, password, email))
conn.commit()
flash("Thanks for registering!")
c.close()
conn.close()
gc.collect()
session['logged_in'] = True
session['username'] = username_p
return redirect(url_for('home'))
return render_template("register.html", form=form)
except Exception as e:
return(str(e))
def get_job_table(self, state_filter, limit = 50, offset = 0):
limit += 1
data = []
state_filter = state_filter.split('|')
if state_filter:
for s in state_filter[:]:
if s != 'all' and s not in self.all_state:
state_filter.remove(s)
state_filter = tuple(state_filter)
if not state_filter:
state_filter = tuple([ 'fail', 'pending', 'running' ])
if 'all' in state_filter:
state_filter = tuple([ x for x in self.all_state ])
with db.connection(self):
fmt_strs = ', '.join(['%s'] * len(state_filter))
q = 'SELECT * FROM job WHERE job_state IN (' + fmt_strs + ') ORDER BY job_id DESC LIMIT %s OFFSET %s'
#print >> sys.stderr, q % (state_filter + (limit, ) + (offset,))
self.cursor.execute(q, state_filter + (limit, ) + (offset,))
data = self.cursor.fetchall()
has_next = True if len(data) == limit else False
return data[:limit-1], has_next
def post(self):
file_id = self.get_argument('file_id', None)
delimiter = self.get_argument('delimiter', ',')
first_row = self.get_argument('first_row', 1)
parent_entity_id = self.get_argument('parent_entity_id', None)
entity_definition_keyname = self.get_argument('entity_definition_keyname', None)
first_row = int(first_row)
db_connection = db.connection()
tmp_file = db_connection.get('SELECT * FROM tmp_file WHERE id = %s AND created_by = %s LIMIT 1;', file_id, self.current_user.id)
if not tmp_file:
return
entity = db.Entity(user_locale=self.get_user_locale(), user_id=self.current_user.id)
item = entity.get(entity_id=0, entity_definition_keyname=entity_definition_keyname, limit=1, full_definition=True)
properties = item.get('properties', {}).values()
row_num = 0
for row in csv.reader(StringIO.StringIO(tmp_file.file), delimiter=str(delimiter)):
row_num += 1
if row_num < first_row:
continue
entity_id = entity.create(entity_definition_keyname=entity_definition_keyname, parent_entity_id=parent_entity_id)
for p in properties:
field = self.get_argument('field_%s' % p.get('dataproperty'), None)
if not field:
continue
value = row[int(field)]
if not value:
continue
property_id = entity.set_property(entity_id=entity_id, property_definition_keyname=p.get('keyname'), value=value)
def delete_task():
cm, con = connection()
task_id = request.args.get("task_id")
cm.execute("delete from task_document where task_id='" + task_id + "'")
cm.execute("delete from task where task_id='" + task_id + "'")
con.commit()
return jsonify(status="ok")
def orgreg():
cm, con = connection()
oname = request.form["orgnm"]
place = request.form["plc"]
post = request.form["post"]
pin = request.form["zcode"]
email = request.form["email"]
wbst = request.form["wsite"]
contact = request.form["wmob"]
registry = request.form["creg"]
gstin = request.form["gstin"]
#photo = ""
file = request.files["image"]
if file is None:
photo = "male.png"
else:
file = request.files["image"]
timestr = datetime.now().strftime("%Y%m%d-%H%M%S")
photo = timestr + file.filename
if file and allowed_file(photo):
file.save(os.path.join(app.root_path, "static/uploads/" + photo))
else:
return (
"""<script>alert("Only JPEG, JPG & PNG Files Allowed..!!");</script>"""
)
print(oname)
print(place)
print(post)
print(pin)
print(email)
print(wbst)
print(contact)
print(registry)
print(gstin)
ademp =(
"insert into `organisation`(`org_name`,`place`,`post`,`pin`,`email`,`website`,`contact`,`registry`,`gstin`,`image`) values('"
+ oname
+ "','"
+ place
+ "','"
+ post
+ "','"
+ pin
+ "','"
+ email
+ "','"
+ wbst
+ "','"
+ contact
+ "','"
+ registry
+ "','"
+ gstin
+ "','"
+ photo
+ "')")
cm.execute(ademp)
con.commit()
return jsonify(status="ok")
def log():
cm, con = connection()
username = request.args.get("user")
password = request.args.get("password")
r = (
"select * from login where username='******' and password='******'"
)
cm.execute(r)
ab = cm.fetchone()
if ab is not None:
if password == ab[2]:
session["login"] = "******"
session["uid"] = ab[0]
print("haii")
print(session["uid"])
return jsonify(
status="ok", id=ab[0], username=ab[1], password=ab[2], user_type=ab[3]
)
else:
return jsonify(status="Incorrect Password")
else:
return jsonify(status="Incorrect UserName")
def delete_employee():
dem = request.args.get("employee_id")
cm, con = connection()
cm.execute("delete from employee where employee_id='" + dem + "'")
cm.execute("delete from login where id='" + dem + "'")
con.commit()
return jsonify(status="ok")
def updtsk():
cm, con = connection()
kk = request.args.get("docid")
qq = request.args.get("task")
rr = request.args.get("processday")
yy = request.args.get("cost")
ii = (
"update task_reg set task='"
+ qq
+ "',processdays='"
+ rr
+ "',cost='"
+ yy
+ "' where task_id='"
+ kk
+ "'"
)
cm.execute(ii)
con.commit()
xx = request.args.get("docid")
val = xx.split(",")
for i in val:
njk = (
"insert into task_document(task_id,document_id)VALUES ('"
+ kk
+ "','"
+ (i)
+ "')"
)
cm.execute(njk)
con.commit()
return jsonify(status="ok")
def getAllFiles():
print "inside getAllFiles"
allData = []
try:
c, conn = connection()
sqlquery = ("select file_name from files")
c.execute(sqlquery)
rows = c.fetchall()
#print type(rows)
#print rows
res_list = [x[0] for x in rows]
#print type(res_list)
#print res_list
for name in res_list:
file_name1 = {'name': name}
allData.append(file_name1)
# print name
#print res_list
#resp = json.dumps(res_list)
print allData
print type(allData)
#for i in res_list:
# print res_list[i]
conn.commit()
c.close()
conn.close()
return (allData)
except Exception as e:
print "inside exe getAllFiles"
print(str(e))
return (str(e))
def simplesqlstat():
# Get user input
sql_id = util.input_with_default('SQL_ID', 'acrg0q0qtx3gr')
# Use my db login credentials
user = util.my_oracle_user
password = util.get_oracle_password(database)
# Build and run query
q = perfq.simplesqlstat(sql_id)
c = db.connection(user, password, database)
r = c.run_return_flipped_results(q)
# plot query
title = "Sql_id " + sql_id + " on " + database + " database"
top_label = "Number of executions"
bottom_label = "Averaged Elapsed Milliseconds"
date_time = r[0]
executions = r[1]
avg_elapsed = r[2]
myplot.frequency_average(title, top_label, bottom_label, date_time,
executions, avg_elapsed)
def add_request(self, jobname, run_cmd, args, max_vmem,
cpu_bound = True, force = False):
job_id = 0
with db.connection(self):
job_id = self._add_request(jobname, run_cmd, args,
max_vmem, cpu_bound, force)
return job_id
def GetExistingID(ester_id):
db_connection = db.connection()
entity = db_connection.get('SELECT property.entity_id, entity.entity_definition_keyname FROM property, entity, property_definition WHERE entity.id = property.entity_id AND property_definition.keyname = property.property_definition_keyname AND property_definition.dataproperty = \'ester-id\' AND property.value_string = %s AND property.deleted IS NULL LIMIT 1', ester_id)
if not entity:
return {}
return entity
def get_job_table(self, state_filter, limit = 50, offset = 0):
limit += 1
data = []
state_filter = state_filter.split('|')
if state_filter:
for s in state_filter[:]:
if s != 'all' and s not in self.all_state:
state_filter.remove(s)
state_filter = tuple(state_filter)
if not state_filter:
state_filter = tuple([ 'fail', 'pending', 'running' ])
if 'all' in state_filter:
state_filter = tuple([ x for x in self.all_state ])
with db.connection(self):
fmt_strs = ', '.join(['%s'] * len(state_filter))
q = 'SELECT * FROM job WHERE job_state IN (' + fmt_strs + ') ORDER BY job_id DESC LIMIT %s OFFSET %s'
#print >> sys.stderr, q % (state_filter + (limit, ) + (offset,))
self.cursor.execute(q, state_filter + (limit, ) + (offset,))
data = self.cursor.fetchall()
has_next = True if len(data) == limit else False
return data[:limit-1], has_next
def tskreg():
tname = request.args.get("task")
tcost = request.args.get("cost")
tday = request.args.get("days")
cm, con = connection()
tsk = (
"insert into task_registration(task,processdays,cost)VALUES ('"
+ tname
+ "','"
+ tday
+ "','"
+ tcost
+ "')"
)
cm.execute(tsk)
con.commit()
tskid = cm.lastrowid
did = request.args.get("docid")
val = did.split(",")
for i in val:
res = (
"insert into task_document(task_id,document_id)VALUES ('"
+ str(tskid)
+ "','"
+ (i)
+ "')"
)
cm.execute(res)
con.commit()
return jsonify(status="ok")
def do_admin_login():
POST_USERNAME = str(request.form['username'])
POST_PASSWORD = str(request.form['password'])
if POST_USERNAME == "":
return login()
Session = sessionmaker()
# s = Session()
c, conn = connection()
name = c.execute("SELECT username FROM DG_User WHERE username = (%s)", [thwart(POST_USERNAME)])
pw = c.execute("SELECT password FROM DG_User WHERE username = (%s)", [thwart(POST_USERNAME)])
pw = c.fetchone()[0]
if sha256_crypt.verify(request.form['password'], pw):
session['logged_in'] = True
session['username'] = request.form['username']
# query = s.query(User).filter(User.username.in_([POST_USERNAME]), User.password.in_([POST_PASSWORD]) )
# if pw == POST_PASSWORD:
# session['logged_in'] = True
else:
flash('wrong password!')
return login()
def editdoc():
did = request.args.get("docid")
edtdoc = "select * from document where document_id='" + did + "'"
cm, con = connection()
cm.execute(edtdoc)
ab = cm.fetchone()
return jsonify(status="ok", document_id=ab[0], document_name=ab[1])
def allsql():
# Use my db login credentials
user = util.my_oracle_user
password = util.get_oracle_password(database)
# Build and run query
q = perfq.allsql()
c = db.connection(user, password, database)
r = c.run_return_flipped_results(q)
# plot query
title = "All SQL statements on " + database + " database"
top_label = "Number of executions"
bottom_label = "Averaged Elapsed Milliseconds"
date_time = r[0]
executions = r[1]
avg_elapsed = r[2]
myplot.frequency_average(title, top_label, bottom_label, date_time,
executions, avg_elapsed)
def edittsk():
cm, con = connection()
uppt = request.args.get("tid")
utt = "select * from task_registration where task_id='" + uppt + "'"
cm.execute(utt)
ab = cm.fetchone()
return jsonify(status="ok", id=ab[0], task=ab[1], processdays=ab[2], cost=ab[3])
def update_hotel():
cm, con = connection()
id = request.form['hid']
name = request.form['hotel']
star = request.form['star']
description = request.form['desc']
address = request.form['address']
Latitude = request.form['lati']
Longitude = request.form['longi']
contact = request.form['contact']
Email = request.form['email']
Website = request.form['website']
amenities = request.form.getlist('amenities[]')
am = ""
if amenities is not None:
for i in amenities:
am += i + ","
cm.execute("UPDATE hotel set name='" + name + "', star='" + star +
"',description='" + description + "',address='" + address +
"',ameneties='" + am + "',latitude='" + Latitude +
"',longtitude='" + Longitude + "',contact='" + contact +
"',email='" + Email + "',website='" + Website +
"' WHERE `id`='" + id + "'")
con.commit()
return '''<script>alert("Updated successfully"); </script>'''
def onewait():
# Get user input
wait_event = util.input_with_default('wait event',
'db file sequential read')
min_waits = int(
util.input_with_default('minimum number of waits per hour', '0'))
# Use my db login credentials
user = util.my_oracle_user
password = util.get_oracle_password(database)
# Build and run query
q = perfq.onewait(wait_event, min_waits)
c = db.connection(user, password, database)
r = c.run_return_flipped_results(q)
# plot query
title = "'" + wait_event + "' waits on " + database + " database, minimum waits=" + str(
min_waits)
top_label = "Number of events"
bottom_label = "Averaged Elapsed Microseconds"
date_time = r[0]
num_events = r[1]
avg_elapsed = r[2]
myplot.frequency_average(title, top_label, bottom_label, date_time,
num_events, avg_elapsed)
def update_events():
cm, con = connection()
id = request.form['eventid']
event = request.form['ename']
description = request.form['edesc']
event_date = request.form['edate']
location = request.form['eloc']
client = request.form['client']
address = request.form['addr']
quantity = request.form['quan']
cm.execute("UPDATE `event` SET `event_name`='" + event +
"',`description`='" + description + "',`event_date`='" +
event_date + "',`event_location`='" + location +
"',`client_name`='" + client + "',`address`='" + address +
"',`quantity`='" + quantity + "' WHERE id='" + str(id) + "'")
cm.execute("DELETE from `event_employee` where event_id='" + str(id) + "'")
members = request.form.getlist('members[]')
mem = ""
if members is not None:
for i in members:
cm.execute("INSERT INTO event_employee VALUES(NULL,'" + str(id) +
"','" + str(i) + "')")
con.commit()
return '''<script>alert("Updated successfully"); </script>'''
def info():
data = request.get_json()
# fname = data['fname']
# lname = data['lname']
lecture = data['lec']
# branch = data['branch']
year = data['year']
db, c = connection()
sub_query = "SELECT sub_id from subjects where name=%s" #selecting sub_id of particular subject
c.execute(sub_query, (lecture, ))
result = c.fetchall()
global sub_id #setting sub_id as gloabl variable
sub_id = result[0][0]
print(sub_id)
db.commit()
c.close()
db.close()
if sub_id:
msg = "Succes"
return jsonify(msg), 200
else:
msg = "fail"
return jsonify(msg), 400
def update_contest_statistics(result):
"""
更新比赛统计数据
:param result:
:return:
"""
count = db.select_int('select count(*) from t_ranking where contest_id=? and problem_id=? and username=?',
result['contest_id'], result['problem_id'], result['user_id'])
if count == 0:
record = dict(contest_id=result['contest_id'], problem_id=result['problem_id'], username=result['user_id'])
db.insert('t_ranking', **record)
p_time = db.select_one('select AC_time from t_ranking where contest_id=? and problem_id=? and username=?',
result['contest_id'], result['problem_id'], result['user_id']).get('AC_time')
if p_time == 0: # 本题还没有AC
if result['result'] == configs.result_code.AC: # 本题初次AC
with db.connection():
submit_time = db.select_one('select submit_time from t_submission where submission_id=?',
result['solution_id']).get('submit_time') # 提交时间, datetime类型
date_time = db.select_one('select contest_date, start_time from t_contest where contest_id=?',
result['contest_id'])
c_date = date_time.get('contest_date') # 比赛开始日期, date类型
c_time = date_time.get('start_time') # 比赛开始时间, timedelta类型
# 转换为比赛开始时间, datetime类型
contest_time = datetime.strptime(c_date.strftime('%Y-%m-%d %H:%M:%S'), '%Y-%m-%d %H:%M:%S') + c_time
ac_time = (submit_time - contest_time).total_seconds() / 60 # 本题初次AC所用时间, 单位为分钟, float类型
with db.transaction():
db.update('update t_ranking set AC_time=? where contest_id=? and problem_id=? and username=?',
ac_time, result['contest_id'], result['problem_id'], result['user_id']) # AC题目所用时间
else:
db.update('update t_ranking set wrong_submit = wrong_submit + 1 where contest_id=? and problem_id=? '
'and username=?', result['contest_id'], result['problem_id'], result['user_id'])
def post():
data = request.get_json()
fname = data['fname']
lname = data['lname']
email = data['email']
password = data['password']
# password = md5(password.encode())
# password = password.hexdigest()
db, c = connection()
query = ("SELECT * FROM teachers WHERE username=%s")
c.execute(query, (email, ))
result = c.fetchall()
if len(result) != 0:
msg = {'error': "email id already exist's"}
return jsonify(msg), 400
else:
add_teacher = (
"INSERT INTO teachers (f_name,l_name,username,password) VALUES (%s, %s, %s, %s)"
)
data_teacher = (fname, lname, email, password)
c.execute(add_teacher, data_teacher)
db.commit()
c.close()
db.close()
msg = {'success': "registered successfully"}
return jsonify(msg), 200
def upload():
file = request.files['file']
file_data = request.files['file'].read()
file_name = str(file.filename)
file_path = app.config['UPLOAD_FOLDER']
print "all files values"
print file
print file.filename
print app.config['UPLOAD_FOLDER']
print "All file types"
print type(file)
print type(file_name)
try:
c, conn = connection()
file.save(os.path.join(app.config['UPLOAD_FOLDER'], file_name))
file_path = file_path + "/" + file_name
print " file data"
print file_name
print file_path
query = """insert files(file_name, file_path, file_content) values (%s,%s,%s)"""
c.execute(query, (file_name, file_path, file_data))
#sqlquery = ("INSERT INTO files(file_name, file_content) VALUES (%s,%s)",(file_name,path))
#c.execute(sqlquery)
conn.commit()
c.close()
conn.close()
#print type(getAllFiles())
#res = "okey"
res = getAllFiles()
return render_template('uploaded.html', data=res)
except Exception as e:
print "inside exe"
return (str(e))
def getAlbum():
username = session['username']
c, conn = connection()
album = c.execute("SELECT albumLink FROM DG_User WHERE username = (%s)",
[thwart(username)])
album = c.fetchone()[0]
return album
def manager_details():
cm, con = connection()
memid = request.get('memid')
cm.execute("SELECT image,f_name FROM employee WHERE emp_id='" + memid +
"'")
ab = cm.fetchone()
return jsonify(status="ok", mname=ab[1], mimage=ab[0])
def testdb():
"""set up test database"""
db.close()
db.connection(db='test_opp')
cur = db.cursor()
for t in ('sources', 'links', 'docs'):
cur.execute('DELETE FROM {}'.format(t))
db.commit()
scraper.Source(
url='http://umsu.de/papers/',
sourcetype='personal',
status=0,
last_checked='2016-01-01 12:34').save_to_db()
scraper.Source(
url='http://consc.net/papers.html',
sourcetype='personal',
status=1).save_to_db()
def pending_request(self, limit = 16, offset = 0):
data = []
with db.connection(self):
self.cursor.execute("SELECT * FROM job WHERE job_state='pending' LIMIT %s OFFSET %s",
[ limit, offset ])
data = self.cursor.fetchall()
return data
def update_accounting(self):
jobs = {}
with db.connection(self):
q = 'SELECT job_id, sge_jobnumber, sge_hostname FROM accounting WHERE sge_hostname=""'
self.cursor.execute(q)
for data in self.cursor.fetchall():
jobs[data['sge_jobnumber']] = [ data ]
if not len(jobs):
return
self.search_accounting(jobs)
with db.connection(self):
fields = [ 'hostname', 'qsub_time', 'start_time', 'end_time',
'failed', 'exit_status', 'ru_utime', 'ru_stime',
'ru_wallclock', 'used_maxvmem' ]
set_str = []
for f in fields:
set_str.append('sge_%s=%%(%s)s' % (f, f))
set_str = ', '.join(set_str)
for sge_jobnumber in jobs:
sge_jobnumber = int(sge_jobnumber)
# Accounting not found, it'll found in the next run.
if len(jobs[sge_jobnumber]) <= 1:
continue
q = "UPDATE accounting SET " + set_str
# We can't let execute() do the quoting for jobnumber, but
# sge_jobnumber is forced to int so this code is sql injection
# safe.
q += ' WHERE sge_jobnumber=%d' % sge_jobnumber
# Kludge, execute() don't accept a namedtuple nor an
# OrderedDict so convert it explicitly to a dict.
d = jobs[sge_jobnumber][1]._asdict()
d = dict(zip(d.keys(), d.values()))
self.cursor.execute(q, d)
job = jobs[sge_jobnumber][0]
new_state = 'success'
if int(d['failed']) or int(d['exit_status']):
new_state = 'fail'
q = 'UPDATE job SET job_state=%s WHERE job_id=%s'
self.cursor.execute(q, [ new_state, job['job_id'] ])
def execute(cls, query, *args):
"""
Used for writes and updates, returns row id of last insert.
"""
start_time = time.time()
result = db.connection().execute(query, *args)
query_run_time = (time.time() - start_time) * 1000
logging.debug((query % args) + " [%s ms]" % query_run_time)
return result
def __init__(self, *args, **kwargs):
self._id = None
self.connection = db.connection()
self.errors = error.Errors()
# populate existing Properties from any keyword arguments
self._populate_properties(False, **kwargs)
# hook for subclasses.
self.initialize(*args, **kwargs)
def query(cls, query, *args):
"""
A raw query that doesn't try to coerce the results into
Model objects.
"""
start_time = time.time()
result = db.connection().query(query, *args)
query_run_time = (time.time() - start_time) * 1000
logging.debug((query % args) + " [%s ms]" % query_run_time)
return result
def run_override_database(mode, dbtype, host, port, username, password, database, alias):
dbo = db.DatabaseInfo()
dbo.dbtype = dbtype
dbo.host = host
dbo.port = port
dbo.username = username
dbo.password = password
dbo.database = database
dbo.alias = alias
dbo.connection = db.connection(dbo)
run(dbo, mode)
def update_db(lang, bookname):
import hocr_request
db_hocr = hocr_request.DbHocr()
with db.connection(db_hocr):
path = cache_path(bookname, lang)
if os.path.exists(path + 'sha1.sum'):
sha1 = read_sha1(path)
db_hocr.add_update_row(bookname, lang, sha1)
else:
print >> sys.stderr, "Can't locate sha1.sum", path
def check_running(self):
sge_running = qstat.running_jobs('')
if sge_running:
with db.connection(self):
q = 'SELECT job_id, sge_jobnumber, job_args FROM job WHERE job_state="running"'
self.cursor.execute(q)
for r in self.cursor.fetchall():
if not r['sge_jobnumber'] in sge_running:
self._exec_check(r)
return len(sge_running)
return None
def run_alias(mode, alias):
if MULTIPLE_DATABASES_TYPE == "smcom":
dbo = smcom.get_database_info(alias)
elif MULTIPLE_DATABASES_TYPE == "map" and alias != "%":
dbo = db.get_multiple_database_info(alias)
dbo.alias = alias
if dbo.database == "FAIL":
print "Invalid database alias '%s'" % (alias)
return
else:
dbo.connection = db.connection(dbo)
run(dbo, mode)
def import_comments():
"""imports all proceeding comments into filing table and documents into filing_docs table"""
conn = db.connection()
cur = conn.cursor()
cur.execute("SELECT id, number FROM proceedings where status = 'Open'")
try:
proceedings = cur.fetchall()
except Exception, e:
warn("cannot fetch proceeding numbers", e)
raise
def exec_request(self, r):
sge_job_nr = 0
# This is a bit convoluted but we need it to avoid a race condition:
# we set the job as running before starting it so on if this script
# run twice in parallel we don't try to start the same job twice. Then
# when the job really started or fail to start we update its state
# again. As we don't know yet the sge job number, we setup it as zero.
# Note this could be done in pending_request() but I prefer to protect
# it locally.
really_pending = False
with db.connection(self):
q = 'UPDATE job SET job_state=%s, sge_jobnumber=%s WHERE job_id=%s AND job_state="pending"'
if self.cursor.execute(q, [ 'running', 0, r['job_id'] ]):
really_pending = True
if not really_pending:
print >> sys.stderr, "run request for job_id %s cancelled, as it's no longer pending" % r['job_id']
return
cmdline_arg = job_cmdline_arg(r, 'job_run_cmd')
sge_cmdline = sge_cmdline_arg(r)
ls = subprocess.Popen(sge_cmdline + cmdline_arg,
stdin=None, stdout=subprocess.PIPE,
close_fds = True)
text = ls.stdout.read()
ls.wait()
try:
sge_job_nr = int(re.search('Your job (\d+) ', text).group(1))
new_state = 'running'
except:
utils.print_traceback("sge failure to exec job: %d" % r['job_id'], text)
new_state = 'sge_fail'
# Now we can really update the job state, see comment above.
with db.connection(self):
q = 'UPDATE job SET job_state=%s, sge_jobnumber=%s WHERE job_id=%s'
self.cursor.execute(q, [ new_state, sge_job_nr, r['job_id'] ])
def import_comments(proceeding_parser):
"""imports all proceeding comments into filing table and documents into
filing_docs table. proceeding_parser is either based on a search or an rss feed"""
conn = db.connection()
cur = conn.cursor()
cur.execute("SELECT id as proceeding_id, number FROM proceedings where status = 'Open'")
try:
proceedings = cur.fetchall()
except Exception, e:
warn("cannot fetch proceeding numbers", e)
raise
def _update_stats(pi, info_hash, my_pid, now):
with db.connection().pipeline() as p:
pi = pi_ints(pi)
if pi.get('left', None) == 0:
p.zadd("torrent|%s|seeders" % info_hash, my_pid, now)
p.zrem("torrent|%s|leechers" % info_hash, my_pid)
p.zadd("torrents|seeding", info_hash, now)
else:
p.zrem("torrent|%s|seeders" % info_hash, my_pid)
p.zadd("torrent|%s|leechers" % info_hash, my_pid, now)
p.zadd("torrents|leeching", info_hash, now)
p.zadd("torrents|seen", info_hash, now)
def post(self):
if not self.request.files.get('file', None):
return
uploaded_file = self.request.files.get('file', [])[0] if self.request.files.get('file', None) else None
if not uploaded_file:
return
db_connection = db.connection()
file_id = db_connection.execute_lastrowid('INSERT INTO tmp_file SET filename = %s, file = %s, created_by = %s, created = NOW();', uploaded_file['filename'], uploaded_file['body'], self.current_user.id)
# csv.reader(zf.read('0010711996.txt').split('\n'), delimiter='\t')
self.write(str(file_id))
def update_document(data, content=None):
"""updates database with extracted data."""
conn = db.connection()
cur = conn.cursor()
filing_doc_id = data['filing_doc_id']
cur.execute("select id from filing_docs where id = %s and status = 'public'",
(filing_doc_id,))
if cur.rowcount == 1:
return
if data.get('status', 'public') == 'public':
#TODO: avoid doing repeated work. Probably easiest to ignore
# use dedicated buckets for batch and online extraction.
cur.execute("update filing_docs set status = 'public', pagecount = %s where id = %s",
(data['pagecount'], filing_doc_id))
if 'pages' in data:
if not content:
size = 0
for page in data['pages']:
size += int(page['size'])
if size > 0:
raise Exception("content is unexpectedly empty for data: %s" % (str(data),))
cur.execute("delete from doc_pages where filing_doc_id = %s",
(filing_doc_id,))
for page in data['pages']:
offset, size = int(page['offset']), int(page['size'])
pagetext = content[offset:offset+size]
wordcount = len(pagetext.split(' ')) #roughly
record = dict(filing_doc_id=filing_doc_id,
pagenumber=page['number'],
pagetext=pagetext,
wordcount=wordcount)
cur.execute(*db.dict_to_sql_insert('doc_pages', record))
else:
cur.execute("update filing_docs set status = 'failed' where id = %s", (filing_doc_id,))
if VERBOSE:
warn("updated", data)
conn.commit()
def delete(bookname, lang):
if type(bookname) == type(u''):
bookname = bookname.encode('utf-8')
bookname = bookname.replace(' ', '_')
path = hocr.cache_path(bookname, lang)
sha1 = hocr.read_sha1(path)
db_hocr = hocr_request.DbHocr()
with db.connection(db_hocr):
q = 'delete from hocr where sha1=%s and lang=%s and title=%s'
db_hocr.cursor.execute(q, [sha1, lang, bookname])
print db_hocr.cursor.fetchall()
if os.path.exists(path + 'sha1.sum'):
os.remove(path + 'sha1.sum')
def get(self, test_id=None):
test_id = test_id.strip('-')
entity = db.Entity(user_locale=self.get_user_locale(), user_id=self.current_user.id)
if not test_id:
return self.redirect('/test')
test_ids = entity.get_relatives(ids_only=True, related_entity_id=self.current_user.id, entity_definition_id=60, relation_type='leecher', reverse_relation=True)
if not test_ids:
return self.redirect('/')
test_id = int(test_id)
if test_id not in test_ids:
return self.redirect('/test')
db_con = db.connection()
test_questions = entity.get_relatives(ids_only=True, entity_id=test_id, relation_type='child')
unanswered = False
for q in test_questions:
prop = db_con.get(
"""
SELECT
property.value_string AS value
FROM
relationship,
property
WHERE property.relationship_id = relationship.id
AND relationship.relationship_definition_id = 10
AND relationship.entity_id = %s
AND relationship.related_entity_id = %s
AND relationship.deleted IS NULL
AND property.deleted IS NULL
LIMIT 1;
""",
q,
self.current_user.id
)
if not prop:
unanswered = True
if unanswered == False:
entity.set_relations(entity_id=test_id, related_entity_id=self.current_user.id, relationship_type='leecher', delete=True)
return self.redirect('/test')
self.redirect('/test-%s' % test_id)
def inject_by_query(query):
"""Inject documents based on query results"""
conn = db.connection()
cur = conn.cursor()
sqs_conn = get_sqs_connection()
queue = get_queue(sqs_conn, config('default.injector-queue'))
cur.execute(query)
for filing_doc_id, fcc_num, url in cur:
queue.write(dict_to_msg(dict(filing_doc_id=filing_doc_id, fcc_num=fcc_num, url=url))) #TODO batch sends
conn.commit()
conn.close()
def get(self, path=None):
path = path.strip('/').strip('-')
db_connection = db.connection()
if not path:
path = db_connection.get('SELECT public_path FROM entity_definition WHERE public_path IS NOT NULL ORDER BY public_path LIMIT 1;')
if path:
return self.redirect('/public-%s' % path.public_path)
else:
return self.missing()
self.render('public/start.html',
paths = get_paths(self.get_user_locale()),
path = path,
search = '',
entity_definitions = get_definitions(self.get_user_locale(), path)
)
def get_accounting_table(self, limit = 50, offset = 0, job_ids = None):
limit += 1
data = []
if not job_ids:
job_ids = []
if type(job_ids) != type([]):
jobs_ids = [ job_ids ]
with db.connection(self):
q = 'SELECT * from accounting '
if job_ids:
fmt_strs = ', '.join(['%s'] * len(job_ids))
q += 'WHERE job_id in (' + fmt_strs + ') '
q += 'ORDER BY job_id DESC, sge_jobnumber DESC, sge_hostname LIMIT %s OFFSET %s'
self.cursor.execute(q, tuple(job_ids) + (limit, ) + (offset,))
data = self.cursor.fetchall()
has_next = True if len(data) == limit else False
return data[:limit-1], has_next
def post(self):
ester_id = self.get_argument('ester_id', default=None, strip=True)
parent_entity_id = self.get_argument('parent_entity_id', default=None, strip=True)
entity_definition_keyname = self.get_argument('entity_definition_keyname', default=None, strip=True)
if not ester_id or not parent_entity_id or not entity_definition_keyname:
return
entity = GetExistingID(ester_id)
if entity.get('entity_id'):
return self.write(str(entity.get('entity_id')))
response = httpclient.HTTPClient().fetch('http://tallinn.ester.ee/search~S1?/.'+ester_id+'/.'+ester_id+'/1,1,1,B/marc~'+ester_id)
marc = response.body.split('<pre>')[1].split('</pre>')[0].strip()
# item = ParseMARC(HTMLParser().unescape((marc))
item = ParseMARC(marc)
if not item:
return
item['ester-id'] = ester_id
db_connection = db.connection()
logging.debug(str(item))
entity = db.Entity(user_locale=self.get_user_locale(), user_id=self.current_user.id)
entity_id = entity.create(entity_definition_keyname=entity_definition_keyname, parent_entity_id=parent_entity_id)
for field, values in item.iteritems():
sql = 'SELECT keyname FROM property_definition WHERE dataproperty = \'%s\' AND entity_definition_keyname = \'%s\' LIMIT 1;' % (field, entity_definition_keyname)
logging.debug(sql)
property_definition = db_connection.get(sql)
if not property_definition:
logging.warning('%s: %s' % (field, values))
continue
if type(values) is not list:
values = [values]
for value in values:
entity.set_property(entity_id=entity_id, property_definition_keyname=property_definition.keyname, value=value)
self.write(str(entity_id))
def exec_request(self, r):
sge_job_nr = 0
cmdline_arg = job_cmdline_arg(r, 'job_run_cmd')
sge_cmdline = sge_cmdline_arg(r)
ls = subprocess.Popen(sge_cmdline + cmdline_arg,
stdin=None, stdout=subprocess.PIPE,
close_fds = True)
text = ls.stdout.read()
ls.wait()
try:
sge_job_nr = int(re.search('Your job (\d+) ', text).group(1))
new_state = 'running'
except:
utils.print_traceback("sge failure to exec job: %d" % r['job_id'], text)
new_state = 'sge_fail'
with db.connection(self):
q = 'UPDATE job SET job_state=%s, sge_jobnumber=%s WHERE job_id=%s'
self.cursor.execute(q, [ new_state, sge_job_nr, r['job_id'] ])
