def modify_record(category, id):
if request.method == 'POST':
if not g.user.is_admin():
flash(u'权限不足，请联系管理员，3 秒钟内将返回首页……')
return render_template('flash.html', target=url_for('music.show_music', category=category))
else:
number = request.form['number']
# 如果 number 非空，插入记录
if number.isdigit():
cn_title = request.form['cn_title']
jp_title = request.form['jp_title']
artist = request.form['artist']
rate = request.form['rate']
begin = request.form['begin']
end = request.form['end']
db = connect_db()
cur = db.music.update({'_id': ObjectId(id)}, {'$set': {'number': int(number), 'cn_title': cn_title, 'jp_title': jp_title, 'artist': artist, 'rate': None if not rate.isdigit() else int(rate), 'begin': int(begin), 'end': int(end)}})
return redirect(url_for('music.show_music', category=category))
elif request.method == 'GET':
db = connect_db()
cur = db.music.find_one({'_id': ObjectId(id)})
if not cur:
abort(404)
else:
return render_template('modify_music.html', record=cur)
def mode_2_idiom(self):
pattern = re.compile(u'#')
matched = re.search(pattern, self.message)
if matched:
db = database.connect_db()
database.change_state(self.uid, 0, db)
return '不想玩了吗？那就结束吧ヾ(≧▽≦*)o'
if idiom.check_answer(self.message, self.uid):
try:
gg = random.randint(1, 4)
if gg == 4:
db = database.connect_db()
database.change_state(self.uid, 0, db)
return '这是啥呀？我认输还不行吗o((>ω< ))o'
ret = idiom.get_next(self.message, self.uid)
if ret is None:
db = database.connect_db()
database.change_state(self.uid, 0, db)
return '这是啥呀？我认输还不行吗o((>ω< ))o'
return ret
except Exception:
db = database.connect_db()
database.change_state(self.uid, 0, db)
return '这是啥呀？我认输还不行吗o((>ω< ))o'
else:
gg = random.randint(1, 3)
print gg
if gg == 3:
db = database.connect_db()
database.change_state(self.uid, 0, db)
return '乱接可是不对的哟，现在游戏结束'
else:
return '再给你一个机会吧'
def generate_info(parser):
args = parser.parse_args()
if args.file_type not in lg.allowed_file_types:
print "Unknown filetype, allowed types are:"
print ", ".join(f for f in lg.allowed_file_types)
sys.exit()
for field in args.fields:
if field not in allowed_fields:
print "Unknown field, allowed values are:"
print ", ".join(f for f in allowed_fields)
sys.exit()
if args.list_fields:
print "ALLOWED FIELDS:"
print ", ".join(f for f in allowed_fields)
sys.exit()
if not args.module:
parser.print_help()
print "Require a module argument"
sys.exit()
# For each module requested, get files in the range
# and list information for each file (NA if missing)
database.connect_db(args.db_server, args.db_port, args.db_name)
if args.label:
file_info = generate_info_by_label(args.label, args.file_type,
args.module, args.run_range,
args.fields)
elif args.version:
file_info = generate_info_by_version(args.version, args.file_type,
args.module, args.run_range,
args.fields)
else:
parser.print_help()
print "Incorrect usage; see help above"
sys.exit()
# Save the information to disk
with open(args.output, 'w') as f:
f.write('%s\n' % '\t'.join(field
for field in (['name'] + args.fields)))
for line in file_info:
f.write('%s\n' % '\t'.join(str(val) for val in line))
print "Saved information for %s files" % len(file_info)
def update_list(self):
db,cu = database.connect_db()
input_letter = self.letterLineEdit.text().toUtf8()
input_letter = str(input_letter).strip()
input_value = self.valueLineEdit.text()
label = 'one'
try:
input_value = int(input_value)
database.insert_data(input_letter,input_value,label,cu,db)
self.letterLineEdit.clear()
self.valueLineEdit.clear()
try:
for i in range(self.verticalLayoutScroll.count()): self.verticalLayoutScroll.itemAt(i).widget().close()
except:
pass
self.list_alpha(label)
except database.DataErorr as e :
QtGui.QMessageBox.warning(self, "Cannot store value",
e.message,
QtGui.QMessageBox.Cancel, QtGui.QMessageBox.NoButton,
QtGui.QMessageBox.NoButton)
return
except Exception as foo :
QtGui.QMessageBox.warning(self, "Cannot store value",
"Please check values",
QtGui.QMessageBox.Cancel, QtGui.QMessageBox.NoButton,
QtGui.QMessageBox.NoButton)
return
def insert_indicador_target():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.indicador( id, nombre, titulo, ubicacion, alerta, estado )
select 1, 'Máximo nivel estudio', 'Estudios', 1 , 0, 1 union
select 2, 'Trayectoria política','Trayectoria Politica', 1,0,0 union
select 3, 'Candidatos', 'Candidatos', 1 , 0, 1 union
select 4, 'Congreso Actual','Representación Congreso Actual',1 ,0,1 union
select 5, 'Trayectoria política reducido','Trayectoria Política',1, 0,1 union
select 6, 'Votación vacancia Vizcarra','Votación vacancia presidencial',1 ,0, 0 union
select 7, 'Educación Superior', 'Educ. Superior',1,0,1 union
select 8, 'Tipos sentencias civil', 'Tipo Civil',1,0,1 union
select 9, 'Tipos sentencias penal', 'Tipo Penal',1,0,1 union
select 10, 'Ingresos promedios anuales', 'Ingresos', 1 , 0, 1 union
select 11, 'PARTIDOS ANTERIORES', 'PARTIDOS ANTERIORES',1,0,1 union
select 12, 'Sin Sentencias', 'Sin Sentencias', 1 , 0, 1
"""
cur.execute(query)
con.commit()
con.close()
print ("Indicador inserts success!")
except (Exception, psycopg2.Error) as error :
print ("Error while fetching data", error)
finally:
if(con):
cur.close()
con.close()
def get_prices(listing_id):
""" Get the prices within [start_date, end_date] given a listing_id.
:raise: 404 Error if no listing with listing_id is found
:return: 200 if success, with data field in the response body.
"""
start_date = request.args.get('start-date')
end_date = request.args.get('end-date')
cursor = connect_db()
query = "SELECT * FROM Prices WHERE listingID=%s AND date >= %s AND date <= %s"
cursor.execute(query, (listing_id, start_date, end_date))
if cursor.rowcount == 0:
abort(
404,
'Listing with id=%s does not exist or does not have any prices listed yet'
% (listing_id, ))
data = cursor.fetchall()
return jsonify({
"data": construct_results(cursor, data),
}), 200
def list_alpha(self,label):
global obj_list
db,cu = database.connect_db()
self.letter_list = database.fetch_data(label,cu)
font = QtGui.QFont()
font.setPointSize(22)
obj_list = []
for letter,value in self.letter_list:
valEdit = letter+"_Edit"
value = str(value)
self.letter = QtGui.QLabel(self.formLayoutWidget_2)
self.letter.setObjectName(_fromUtf8(letter))
self.formLayout.setWidget(0, QtGui.QFormLayout.LabelRole, self.letter)
self.letter.setText(_translate("MainWindow", letter, None))
self.letter.setFont(font)
self.formLayout.addWidget(self.letter)
self.valEdit = QtGui.QLineEdit(self.formLayoutWidget_2)
self.valEdit.setObjectName(_fromUtf8(valEdit))
self.valEdit.setText(_translate("MainWindow", value, None))
self.formLayout.setWidget(0, QtGui.QFormLayout.FieldRole, self.valEdit)
self.verticalLayoutScroll.addWidget(self.letter)
self.verticalLayoutScroll.addWidget(self.valEdit)
objs = self.letter,self.valEdit
obj_list.append(objs)
self.scrollArea.setWidgetResizable(True)
self.show()
return
def insert_candidato_experiencia_target():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.candidato_experiencia(tipo, jne_idhojavida, jne_idhvexpelaboral,
item_expelaboral, centro_trabajo, ocupacion_profesion, anio_trabajo_desde, anio_trabajo_hasta)
select 1 tipo , idhojavida, idhvexpelaboral, intitemexpelaboral,strcentrotrabajo,
strocupacionprofesion, straniotrabajodesde, straniotrabajohasta from jne.candidato_exp_laboral
where strtengoexpelaboral='1'
union
select 2 tipo, idhojavida, idhvcargopartidario, intitemcargopartidario, strorgpolcargopartidario,
strcargopartidario, straniocargopartidesde, straniocargopartihasta from jne.candidato_cargo_partidario
where strtengocargopartidario = '1'
union
select 3 tipo, idhojavida, idhvcargoeleccion, intitemcargoeleccion, strorgpolcargoelec, strcargoeleccion2,
straniocargoelecdesde, straniocargoelechasta from jne.candidato_cargo_eleccion where strcargoeleccion = '1';
"""
cur.execute(query)
con.commit()
con.close()
print("Candidato Experiencia inserts success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def calculate_value(input_text,label):
db,cu = database.connect_db()
key = database.fetch_data(label,cu)
stripped_text = strip(input_text)
format_string = format_text(stripped_text)
letter = unshape(format_string)
return parse(key,letter)
def insert_institucion_target():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.institucion(institucion)
select strcenestudioposgrado
from jne.candidato_post_grado cpg
where strtengoposgrado = '1' and strcenestudioposgrado is not null
union
select struniversidad
from jne.candidato_edu_uni where strtengoeduuniversitaria = '1' and struniversidad is not null
union
select strcentroestudionouni
FROM jne.candidato_edu_no_uni
where strtengonouniversitaria = '1' and strconcluidonouni <> '0' and strcentroestudionouni is not null
union
select strcenestudiotecnico
FROM jne.candidato_edu_tecnica
where strtengoedutecnico = '1' and strconcluidoedutecnico <> '0' and strcenestudiotecnico is not null;
"""
cur.execute(query)
con.commit()
con.close()
print("Institucion inserts success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def start():
'''
This is the main function of the project
Connects to Postgres DB
Retrieves stock fundamental data and runs value cals
'''
STOCK = 'TSLA'
# Connect to postgres db
conn = db.connect_db()
# Get income statement relevant columns for stock of interest
income_stmt_query = f"""SELECT
ticker,
report_date,
shares_basic,
revenue,
net_income
FROM
f_income_stmts_annual
WHERE
ticker = '{STOCK}';"""
income_df_columns = ["Ticker", "Date", "Shares", "Revenue", "Net Income"]
income_df = db.postgres_to_df(conn, income_stmt_query, income_df_columns)
# Get balance sheet columns
balance_stmt_query = f"""SELECT
total_equity
FROM
f_balance_sheets_annual b
WHERE
b.ticker = '{STOCK}';"""
balance_df_cols = ["Total Equity"]
balance_df = db.postgres_to_df(conn, balance_stmt_query, balance_df_cols)
# Get cash flow stmt columns
cashflow_stmt_query = f"""SELECT
net_cash_operating_activities
FROM
f_cashflow_annual c
WHERE
c.ticker = '{STOCK}';"""
cashflow_df_cols = ["Net Cash from Operating Act"]
cashflow_df = db.postgres_to_df(conn, cashflow_stmt_query,
cashflow_df_cols)
# Join 3 resultant dfs together
stock_df = pd.concat([income_df, balance_df, cashflow_df],
axis=1,
sort=False)
stock_df[[
"Sales Per Share", "EPS", "Equity Per Share", "Op. Cash Per Share"
]] = stock_df[[
"Revenue", "Net Income", "Total Equity", "Net Cash from Operating Act"
]].div(stock_df["Shares"], axis=0)
print(stock_df)
def main(days_ago, **kwargs):
db = connect_db()
day = datetime.utcnow().replace(
hour=0, minute=0, second=0, microsecond=0
)
for x in range(days_ago + 1):
start = day - timedelta(days=x)
end = start + timedelta(days=1)
query = {
'type': 'delete_msg',
'date': {
'$gte': start,
'$lt': end,
},
}
del_count = 0
chat_reg = set()
for event in db.event.find(query):
del_count += 1
chat_reg.add(get_chat_id(event))
db.day_stat.find_one_and_update(
{'date': start},
{'$set': {
'delete_msg': del_count,
'chat': len(chat_reg),
}},
upsert=True,
)
print('Date: %s' % start)
print(' * delete_msg: %d' % del_count)
print(' * chat: %d' % len(chat_reg))
def insert_history(uid, pwd, column_id):
conn_l = connect_db(PG_DATABASE_LOCALHOST)
cursor_l = conn_l.cursor()
cursor_l.execute(
"SELECT * FROM USER_LOGIN_HISTORY WHERE uid = %(uid)s LIMIT 1",
{'uid': uid})
if not cursor_l.fetchone():
this = Drcom(uid, pwd)
status_code = this.login()
if status_code == 0:
history = this.get_records()
for each_ in history:
cursor_l.execute(
"INSERT INTO USER_LOGIN_HISTORY "
"(UID, LOGIN_TIME, LOGOUT_TIME, DURATION, USAGE_FLOW, SOURCE_IP) "
"VALUES (%(uid)s, %(start_time)s, %(end_time)s, %(usage_duration)s, %(usage_flow)s, %(source_ip)s)",
each_)
if len(history) == 0:
cursor_l.execute(
"DELETE FROM UID_PASSWORD WHERE UID = %(uid)s",
{'uid': uid})
print u'ID={}, 0 条记录，删除此账户'.format(uid)
else:
print u'ID={}, {} 条记录'.format(uid, len(history))
conn_l.commit()
elif status_code == 2:
cursor_l.execute("DELETE FROM UID_PASSWORD WHERE ID = %(id)s",
{'id': column_id})
conn_l.commit()
print u'ID={}, 密码错误删除此账户'.format(uid)
else:
print u'ID={}, 状态码={}'.format(uid, status_code)
else:
print u'ID={}, 此账户已录入数据'.format(uid)
conn_l.close()
def add_record(category, n):
if category not in ('op', 'ed', 'in'):
abort(404)
if request.method == 'POST':
items = dict(request.form)
db = connect_db()
# 循环获得记录信息
for i in range(0, n):
number = items['number'][i]
# 如果 number 非空，插入记录
if number.isdigit():
cn_title = request.form['cn_title'][i]
jp_title = request.form['jp_title'][i]
artist = request.form['artist'][i]
rate = request.form['rate'][i]
begin = request.form['begin'][i]
end = request.form['end'][i]
data = {
'number': int(number),
'cn_title': cn_title,
'jp_title': jp_title,
'artist': artist,
'rate': None if not rate.isdigit() else int(rate),
'begin': begin,
'end': end,
'category': category.upper(),
}
cur = db.music.insert(data)
return redirect(url_for('music.show_music', category=category))
elif request.method == 'GET':
return render_template('add_music.html', n=n)
def __main():
conn, cursor = connect_db()
init_db(cursor)
allbook = AllBook()
start_url = 'https://book.douban.com/subject/1390650/'
explore_book(allbook, start_url, conn, cursor)
def add_record(category, n):
if category not in ('tv', 'movie'):
abort(404)
if request.method == 'POST':
items = dict(request.form)
db = connect_db()
# 循环获得记录信息
for i in range(0, n):
number = items['number'][i]
# 如果 number 非空，插入记录
if number.isdigit():
cn_title = items['cn_title'][i]
jp_title = items['jp_title'][i]
rate = items['rate'][i]
date_str = items['date'][i]
data = {
'number': int(number),
'cn_title': cn_title,
'jp_title': jp_title,
'rate': None if not rate.isdigit() else int(rate),
'date': str_to_datetime(date_str)
}
cur = db[category].insert(data)
return redirect(url_for('anime.show_anime', category=category))
elif request.method == 'GET':
return render_template('add.html', n=n)
def show_profile():
error = None
# user = db.user.find_one({'_id': ObjectId(id)}, {'username': 1, 'email': 1, 'date': 1})
user = g.user
id = user.get_id()
avatar_url = get_avatar(user.get_email(), 170)
if request.method == 'POST':
db = connect_db()
# 邮箱重复验证
email_conflict = db.user.find_one({'_id': {'$ne': ObjectId(id)}, 'email': request.form['email']})
if request.form['email'] and email_conflict:
error = u'邮箱已存在'
else:
# 密码字段不为空，更新密码和邮箱
if request.form['password']:
db.user.update({'_id': ObjectId(id)}, {'$set': {'password': hashlib.md5(request.form['password']).hexdigest(), 'email': request.form['email']}})
flash(u'密码更改成功，请重新登录……')
return render_template('flash.html', target=url_for('logout'))
# 密码字段为空，只更新邮箱
else:
db.user.update({'_id': ObjectId(id)}, {'$set': {'email': request.form['email']}})
flash(u'更改成功，3 秒钟内将转到个人页面……')
return render_template('flash.html', target=url_for('show_profile'))
return render_template('profile.html', error=error, avatar_url=avatar_url)
def insert_candidato_judicial_target():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.candidato_judicial(sentencia, nro_expediente, fallo,\
cumple_fallo, fecha_sentencia, modalidad, tipo_proceso, estado_proceso, \
jne_idhojavida, jne_idhvsentencia)
SELECT strdelitopenal, strexpedientepenal, strfallopenal,strcumplefallo, \
to_date(strfechasentenciapenal,'DD/MM/YYYY'), strmodalidad, 'penal', 'sentenciado', \
idhojavida, idhvsentenciapenal FROM jne.candidato_sent_penal \
WHERE strtengosentenciapenal = '1' UNION
SELECT strmateriasentencia, strexpedienteobliga, strfalloobliga,NULL, NULL, NULL,'civil',\
'sentenciado', idhojavida, idhvsentenciaobliga FROM jne.candidato_sent_civil \
WHERE strtengosentenciaobliga = '1';
"""
cur.execute(query)
con.commit()
con.close()
print("Candidato Judicial inserts success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def insert_candidato_bien_inmuebles():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.candidato_inmueble(jne_idhojavida, jne_idhvbieninmueble,
jne_strinmueblesunarp, jne_decautovaluo, direccion, valor, "order", comentario,
partida_sunarp, tipo)
SELECT idhojavida, idhvbieninmueble, cast(strinmueblesunarp as int), decautovaluo,
strinmuebledireccion, case when decautovaluo = -1 then 0 else decautovaluo end as valor,
intiteminmueble, strcomentario, strpartidasunarp, strtipobieninmueble
FROM jne.candidato_bien_inmueble where strtengoinmueble='1';
"""
cur.execute(query)
con.commit()
con.close()
print("Candidato bien inmuebles inserts success!")
except (Exception, psycopg2.Error) as error :
print ("Error while fetching data", error)
finally:
if(con):
cur.close()
con.close()
def reread_pictures(app):
for filename in os.listdir(config.PICTURE_DIR):
file_path = os.path.join(config.PICTURE_DIR, filename)
im=Image.open(file_path)
width, height = im.size
db = database.connect_db(app)
database.add_picture(db, filename, width, height, False)
def calculate_value(input_text, label):
db, cu = database.connect_db()
key = database.fetch_data(label, cu)
stripped_text = strip(input_text)
format_string = format_text(stripped_text)
letter = unshape(format_string)
return parse(key, letter)
def insert_estudio_target():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.estudio(estudio)
select strespecialidadposgrado
from jne.candidato_post_grado cpg
where strtengoposgrado = '1' and strespecialidadposgrado is not null
group by strespecialidadposgrado
union
select strcarrerauni
from jne.candidato_edu_uni
where strtengoeduuniversitaria = '1' and strcarrerauni is not null
union
select strcarreranouni
FROM jne.candidato_edu_no_uni
where strtengonouniversitaria = '1' and strconcluidonouni <> '0' and strcarreranouni is not null
union
select strcarreratecnico
FROM jne.candidato_edu_tecnica
where strtengoedutecnico = '1' and strconcluidoedutecnico <> '0' and strcarreratecnico is not null
"""
cur.execute(query)
con.commit()
con.close()
print ("Estudio inserts success!")
except (Exception, psycopg2.Error) as error :
print ("Error while fetching data", error)
finally:
if(con):
cur.close()
con.close()
def show_message(page):
messages = []
total_pages = 0
# 分页
messages_per_page = 5
offset = (page - 1) * messages_per_page
db = connect_db()
# 自动解引用
db.add_son_manipulator(NamespaceInjector())
db.add_son_manipulator(AutoReference(db))
if not g.user.is_authenticated:
# 若未登录，则显示 visible 为 public 的留言
messages = db.message.find({'visible': 'public'}).sort([('date', -1)]).skip(offset).limit(messages_per_page)
elif g.user.is_admin():
# 若为超级用户，则显示所有留言
messages = db.message.find().sort([('date', -1)]).skip(offset).limit(messages_per_page)
else:
# 若为普通用户，则显示 visible 为 public 的留言和自己的留言
messages = db.message.find({'$or': [{'visible': 'public'}, {'name': g.user.get_username()}]}).sort([('date', -1)]).skip(offset).limit(messages_per_page)
total_messages = messages.count(with_limit_and_skip=False)
cur_messages = messages.count(with_limit_and_skip=True)
total_pages = int(ceil(total_messages / messages_per_page)) # from __future__ import division
if page != 1 and not cur_messages:
abort(404)
return render_template('guestbook.html', messages=messages, cur_page=page, total_pages=total_pages)
def index():
db = connect_db()
if (request.method == 'POST'):
date = request.form['date']
db_date = datetime.strptime(date, "%Y-%m-%d") #yyyy-mm-dd
final_date = datetime.strftime(db_date, "%Y%m%d") ## yyyymmdd
#pretty_date = datetime.strftime(db_date,"%B %d, %Y")#January 12, 1990
db.execute("INSERT INTO log_date(entry_date) VALUES(?)",
(final_date, ))
db.commit()
cur = db.execute(
"SELECT log_date.entry_date, SUM(food.protein) AS total_protein,SUM(food.carbohydrates) \
AS total_carbohydrates,SUM(food.fat) AS total_fat,SUM(food.calories) AS \
total_calories FROM log_date LEFT JOIN food_date ON food_date.log_date_id = log_date.id \
LEFT JOIN food on food.id = food_date.food_id \
GROUP BY log_date.id ORDER BY log_date.entry_date DESC")
results = cur.fetchall()
data_result = []
for i in results:
single_date = {}
single_date['date'] = i['entry_date']
single_date['protein'] = i['total_protein']
single_date['fat'] = i['total_fat']
single_date['carbohydrates'] = i['total_carbohydrates']
single_date['calories'] = i['total_calories']
d = datetime.strptime(str(i['entry_date']), '%Y%m%d')
single_date['entry_date'] = datetime.strftime(d, "%B %d, %Y")
data_result.append(single_date)
return render_template("home.html", results=data_result)
def test_init_db(self):
#when
init_db(self.dbname)
self.db = connect_db(self.dbname)
#expect
self.assertEqual(self.db.execute('select * from user').fetchall(), [])
self.assertEqual(self.db.execute('select * from entry').fetchall(), [])
def show_user():
if not g.user.is_admin():
flash(u'权限不足，请联系管理员，3 秒钟内将返回管理首页……')
return render_template('flash.html', target=url_for('admin.index'))
else:
db = connect_db()
users = db.user.find().sort([('date', -1)])
return render_template('user.html', users=users)
def delete_record(category, id):
if not g.user.is_admin():
flash(u'权限不足，请联系管理员，3 秒钟内将返回首页……')
return render_template('flash.html', target=url_for('music.show_music', category=category))
else:
db = connect_db()
cur = db.music.remove({'_id': ObjectId(id)})
return redirect(url_for('music.show_music', category=category))
def show_visit():
if not g.user.is_admin():
flash(u'权限不足，请联系管理员，3 秒钟内将返回管理首页……')
return render_template('flash.html', target=url_for('admin.index'))
else:
db = connect_db()
records = db.visit.find().sort([('date', -1)])
return render_template('visit.html', records=records)
def unhide_message(id):
if not g.user.is_admin():
flash(u'权限不足，3 秒钟内将返回留言页面……')
return render_template('flash.html', target=url_for('show_message'))
else:
db = connect_db()
db.message.update({'_id': ObjectId(id)}, {'$set': {'visible': 'public'}})
return redirect(url_for('show_message'))
def main():
database.connect_db()
update = updatecls.Update()
tiket= defaultdict(dict)
update.setKonten(general.get_updates(update.offset))
last_textchat = (update.konten, update.chatId)
while True:
update.setKonten(general.get_updates(update.offset))
if(not bool(tiket[update.chatId])):
if(bool(database.isApproved(update.chatId))):
tiket[update.chatId]=tiketcls.Tiket(chatId=update.chatId,isApproved=1)
else:
tiket[update.chatId]=tiketcls.Tiket(chatId=update.chatId,isApproved=0)
if (update.konten, update.chatId) != last_textchat:
if(update.chatId!=0):
if(tiket[update.chatId].isApproved==1):
if(tiket[update.chatId].state=="none"):
tiket=update.cek_command(tiket)
else:
tiket=update.cek_konten_sesuai_state(tiket)
try:
tiket[update.chatId].print_all()
except AttributeError as e:
print(e)
else:
if(tiket[update.chatId].state=="daftar"):
if(update.tipeKonten=="text"):
array = update.konten[0].split("_")
try:
database.daftar(update.chatId,array)
tiket[update.chatId].send_message("Selesai terdaftar")
tiket[update.chatId].setIsApproved(1)
tiket[update.chatId].setState("none")
except:
tiket[update.chatId].send_message("Format yang dikirimkan salah")
elif(tiket[update.chatId].state=="none"):
if "/daftar" in update.konten[0]:
tiket[update.chatId].setState("daftar")
tiket[update.chatId].send_message("kirim dengan format: NIK_NAMA_LOKER")
else:
tiket[update.chatId].send_message("ID Anda belum terdaftar atau belum mendapat izin. Silahkan mendaftar /daftar")
last_textchat = (update.konten, update.chatId)
time.sleep(0.1)
def delete_reply(mid, rid):
if not g.user.is_admin():
flash(u'权限不足，3 秒钟内将返回留言页面……')
return render_template('flash.html', target=url_for('show_message'))
else:
db = connect_db()
db.message.update({'_id': ObjectId(mid)}, {'$pull': {'replies': {'_id': ObjectId(rid)}}})
# flash(u'删除成功，3 秒钟内将返回留言页面……')
# return render_template('flash.html', target=url_for('show_message'))
return redirect(url_for('show_message'))
def before_request():
"""Make sure we are the connected to the database each request and look
up the current user to make sure they are connected
"""
g.db = database.connect_db()
g.user = None
if 'uid' in session:
g.user = database.query_db('select * from users where uid = ?',
[session['uid']], one=True)
g.whatapi = msgpackrpc.Client(msgpackrpc.Address("localhost", 18800), timeout=20)
def delete_message(id):
if not g.user.is_admin():
flash(u'权限不足，3 秒钟内将返回留言页面……')
return render_template('flash.html', target=url_for('show_message'))
else:
db = connect_db()
db.message.remove({'_id': ObjectId(id)})
# flash(u'删除成功，3 秒钟内将返回留言页面……')
# return render_template('flash.html', target=url_for('show_message'))
return redirect(url_for('show_message'))
def clear_db():
with closing(connect_db()) as db:
drop = """
DROP TABLE list_users;
DROP TABLE list_items;
DROP TABLE lists;
DROP TABLE users;
DROP TABLE colors;
"""
db.cursor().execute(drop)
db.commit()
def clear_db():
with closing(connect_db()) as db:
drop = """
DROP TABLE list_users;
DROP TABLE list_items;
DROP TABLE lists;
DROP TABLE users;
DROP TABLE colors;
"""
db.cursor().execute(drop)
db.commit()
def delete_user(id):
if not g.user.is_admin():
flash(u'权限不足，请联系管理员，3 秒钟内将返回管理首页……')
return render_template('flash.html', target=url_for('admin.index'))
elif g.user.get_id() == id:
flash(u'不能删除你自己，3 秒钟内将返回管理首页……')
return render_template('flash.html', target=url_for('admin.index'))
else:
db = connect_db()
db.user.remove({'_id': ObjectId(id)})
return redirect(url_for('admin.index'))
def select_submissions(test):
with closing(connect_db()) as db:
c = db.execute("""SELECT upload,user,timestamp FROM (
SELECT test,user,timestamp,grade,upload
FROM grades
WHERE test == ?
AND grade <> -1
ORDER BY grade ASC, timestamp DESC
) as tmp group by test, user;""",
[test])
return list(c)
def update():
# Connect to postgres db
conn = db.connect_db()
# Section to deal with industry update routing
if UPDATE == "industries":
update_industries(conn)
if UPDATE == "stocks":
update_stocks_table(conn)
'''
def init_bot_with_mode(mode):
with open('var/config.json') as inp:
config = json.load(inp)
if mode == 'test':
token = config['test_api_token']
else:
token = config['api_token']
db = connect_db()
bot = create_bot(token, db)
return bot
def load_candidates(self):
print "Generating autocomplete tree..."
db = connect_db()
cur = db.cursor()
cur.execute("SELECT DISTINCT Name FROM Product")
candidates = cur.fetchall()
for candidate in candidates:
self.insert_product(candidate[0])
print "AutoCompleteProvider loaded"
db.close()
def load_candidates(self):
print "Generating autocomplete tree..."
db = connect_db()
cur = db.cursor()
cur.execute("SELECT DISTINCT Name FROM Product")
candidates = cur.fetchall()
for candidate in candidates:
self.insert_product(candidate[0])
print "AutoCompleteProvider loaded"
db.close()
def main():
db = None
try:
db = database.connect_db()
add_users_from_ldap(db)
add_users_from_static(db)
remove_email_hashes(db)
finally:
if db:
db.close()
def main():
db = None
try:
db = database.connect_db()
rooms = building.getRooms()
for room in rooms:
processRoom(room, db)
finally:
if db:
db.close()
def main():
db = None
try:
db = database.connect_db()
rooms = building.getRooms()
for room in rooms:
processRoom(room, db)
finally:
if db:
db.close()
def reply_message(id):
error = None
if request.method == 'POST':
if not request.form['content']:
error = u'回复内容不能为空'
elif request.form['captcha'].upper() != session['captcha'].upper():
error = u'验证码不正确'
else:
uid = g.user.get_id() if g.user.is_authenticated else None
db = connect_db()
# ip = request.headers.get('x-forwarded-for', request.remote_addr)
if 'x-forwarded-for' in request.headers:
ip = request.headers['x-forwarded-for'].split(', ')[0]
else:
ip = request.remote_addr
# dbref
db.message.update({'_id': ObjectId(id)}, {'$push': {'replies': {'_id': ObjectId(), 'user': DBRef('user', ObjectId(uid)), 'content': request.form['content'], 'date': datetime.datetime.now(), 'ip': ip}}})
flash(u'回复成功，3 秒钟内将返回留言页面……')
return render_template('flash.html', target=url_for('show_message'))
db = connect_db()
message = db.message.find_one({'_id': ObjectId(id)})
return render_template('reply.html', message=message, error=error)
def modify_record(category, id):
if request.method == 'POST':
if not g.user.is_admin():
flash(u'权限不足，请联系管理员，3 秒钟内将返回首页……')
return render_template('flash.html', target=url_for('anime.show_anime', category=category))
else:
number = request.form['number']
# 如果 number 非空，插入记录
if number.isdigit():
cn_title = request.form['cn_title']
jp_title = request.form['jp_title']
rate = request.form['rate']
date_str = request.form['date']
db = connect_db()
cur = db[category.lower()].update({'_id': ObjectId(id)}, {'$set': {'number': int(number), 'cn_title': cn_title, 'jp_title': jp_title, 'rate': None if not rate.isdigit() else int(rate), 'date': str_to_datetime(date_str)}})
return redirect(url_for('anime.show_anime', category=category))
elif request.method == 'GET':
db = connect_db()
cur = db[category.lower()].find_one({'_id': ObjectId(id)})
if not cur:
abort(404)
else:
return render_template('modify.html', record=cur)
def get_instrument_data(self, ticker):
instrument_id = id_for_instrument(ticker)
instrument_url = urllib.parse.quote_plus(
urls['instrument_url'](instrument_id))
url = urls['instrument_data'](instrument_url)
db, cursor = database.connect_db()
query = "SELECT oauth FROM accounts WHERE name='*****@*****.**' LIMIT 1;"
cursor.execute(query)
oauth_token = cursor.fetchall()[0][0]
self.login(oauth=oauth_token)
result = self.send_get(url)
return result['results'][0]
def run():
while True:
db = connect_db()
for pa in parser:
if pa.check_for_change():
get_log_supp().info("updates available for '%s'" % (pa.get_name()))
try:
data = pa.parse(db)
processor.process(db, data)
except Exception as e:
get_log_supp().info("error getting data for %s:\n%s" % (pa.get_name(), str(e)))
db.close()
time.sleep(SUPPLY_UPDATE_INTERVAL)
def before_request():
g.user = current_user
# ip 统计
if 'x-forwarded-for' in request.headers:
ip = request.headers['x-forwarded-for'].split(', ')[0]
else:
ip = request.remote_addr
url = request.url
method = request.method
user_agent = request.user_agent.__dict__
referer = request.referrer
date = datetime.datetime.now()
db = connect_db()
# if not db.visit.find({'ip': ip, 'date': datetime.datetime.now()}).count():
db.visit.insert({'ip': ip, 'date': date, 'url': url, 'method': method, 'referer': referer, 'user_agent': user_agent})
def process(message_data):
user_db = database.connect_db()
type = message_data.get('post_type')
message_type = message_data.get('message_type')
if type == 'message' and message_type == 'private':
uid = message_data.get('user_id')
message = message_data.get('message')
exist = database.check_user(uid, user_db)
matched = re.search(pattern=r"[(.*?)]",
string=message,
flags=re.M | re.I)
if exist == 0:
if matched:
database.user_register(uid, matched.group(1), user_db)
return 0
else:
send._pri_m('数据库中并没有查询到您的数据呢QAQ\n来注册一个吧', uid)
send._pri_m('注册格式为: [您喜欢的名称] (保留方括号噢@A@)', uid)
send._pri_m('请尽量使用英文字母噢(*^_^*)', uid)
return 0
else:
reply = response.reply(uid, type, message)
data = reply.get_response()
if data != 'ERROR':
send._pri_m(data, uid)
return 0
elif type == 'message' and message_type == 'group':
uid = message_data.get('user_id')
gid = message_data.get('group_id')
message = message_data.get('message')
self_id = message_data.get('self_id')
if not database.check_user(uid, user_db):
stri = '数据库中并没有查询到您的数据呢QAQ\n来注册一个吧\n向机器人私聊发送消息完成注册'
send._group_m(stri, gid, uid)
return 0
reply = response.group_reply(str(self_id), uid, type, message)
data = reply.get_response()
print data
if data != 'ERROR':
send._group_m(data, gid, uid)
return 0
elif type == 'requests':
uid = message_data.get('user_id')
flag = message_data.get('flag')
send._acc_request(flag)
return 0
def insert_cargo_target():
try:
con = connect_db()
cur = con.cursor()
cur.execute("INSERT INTO cargo(id, cargo, estado) \
select idcargoeleccion, strcargoeleccion, 1 from jne.candidato_info_electoral \
group by idcargoeleccion,strcargoeleccion")
con.commit()
con.close()
print("Cargo inserts success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def insert_proceso_target():
try:
con = connect_db()
cur = con.cursor()
data = ('ELECCIONES GENERALES 2021', 1, 110) # change per 2021 process
cur.execute(
"INSERT INTO proceso(nombre, estado, jne_idproceso) \
values(%s, %s, %s)", data)
con.commit()
con.close()
print("Proceso inserts success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def update_candidato_bien_muebles():
try:
con = connect_db()
cur = con.cursor()
query = """
UPDATE public.candidato_mueble set valor = 2500.00
where jne_idhojavida ='135973' and jne_idhvbienmueble='146373';
"""
cur.execute(query)
con.commit()
con.close()
print("Update fix candidato bien mueble success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def insert_candidato_info_adicional():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.candidato_info_adicional(jne_idhojavida, jne_idhvinfoadicional, info_adicional)
select idhojavida, idhvinfoadicional, strinfoadicional from jne.candidato_info_adicional where strtengoinfoadicional='1';
"""
cur.execute(query)
con.commit()
con.close()
print("Candidato info adicional inserts success!")
except (Exception, psycopg2.Error) as error:
print("Error while fetching data", error)
finally:
if (con):
cur.close()
con.close()
def get_photo_from_db(self, email_hash):
found = False
photo = None
db = database.connect_db()
c = db.cursor()
c.execute("SELECT photo_hash FROM emails WHERE email_hash = ?", [email_hash])
photo_hash = c.fetchone()
if photo_hash:
c.execute("SELECT photo FROM photos WHERE photo_hash = ?", [photo_hash[0]])
photodata = c.fetchone()
if photodata:
found = True
photo = bytes(photodata[0])
db.close()
return (found, photo)
def insert_candidato_target():
try:
con = connect_db()
cur = con.cursor()
query = """
INSERT INTO public.candidato(jne_idcandidato, jne_idhojavida, jne_estado_lista,
jne_estado_expediente, jne_estado_hojavida, jne_posicion, jne_organizacion_politica,
cargo_id, proceso_id, organizacion_politica_id, documento_identidad, apellido_paterno,
apellido_materno, nombres, fecha_nacimiento, profesion, region, distrito_electoral,
ubigeo_postula, ruta_archivo)
SELECT cie.idcandidato, cie.idhojavida, op.strestadolista,cie.strestadoexp,
cip.strhojavida, cie.intposicion,opn.nombre, cie.idcargoeleccion, prc.id as procesoid, opn.id,
cip.strdocumentoidentidad, cip.strapellidopaterno, cip.strapellidomaterno,
cip.strnombres,TO_DATE(cip.strfechanacimiento,'DD/MM/YYYY'), '' profesion,
cie.strubiregionpostula, cip.strpostuladistrito, cie.strubigeopostula,
CONCAT('https://declara.jne.gob.pe', cip.strrutaarchivo)
FROM jne.candidato_info_electoral cie
join jne.candidato_info_personal cip on cie.idhojavida = cip.idhojavida
join jne.organizacion_politica_region op on op.idorganizacionpolitica = cie.idorganizacionpolitica
and op.idsolicitudlista = cie.idsolicitudlista
left join organizacion_politica opn on op.idorganizacionpolitica = jne_idorganizacionpolitica
left join proceso prc on prc.jne_idproceso = cie.idprocesoelectoral;
--where op.strestadolista not in ('INADMISIBLE', 'IMPROCEDENTE') -- no aplicar
--and cie.strestadoexp not in ('INADMISIBLE', 'EXCLUSION', 'RETIRO', 'IMPROCEDENTE','RENUNCIA');
"""
cur.execute(query)
query_fix = """
delete from candidato where id = (select id from candidato where jne_idhojavida = 136564 limit 1);
"""
cur.execute(query_fix)
con.commit()
con.close()
print("Candidato inserts success!")
except (Exception, psycopg2.Error) as error :
print ("Error while fetching data", error)
finally:
if(con):
cur.close()
con.close()
def login():
next = get_redirect_target()
if g.user.is_authenticated:
return redirect(url_for('index'))
error = None
if request.method == 'POST':
if not request.form['username']:
error = u'用户名不能为空'
else:
db = connect_db()
encrypted_password = hashlib.md5(request.form['password']).hexdigest()
cur = db.user.find_one({'username': request.form['username'], 'password': encrypted_password})
if not cur:
error = u'用户名或密码不正确'
else:
user = User(cur)
login_user(user, remember=('remember' in request.form))
flash(u'登陆成功，3 秒钟内将返回登录前页面……')
return redirect_back('index')
return render_template('login.html', error=error, next=next)
def show_anime(category, page):
if category not in ('tv', 'movie'):
abort(404)
# 分页
records = []
total_pages = 0
records_per_page = 100
offset = (page - 1) * records_per_page
db = connect_db()
records = db[category].find().sort([('number', 1)]).skip(offset).limit(records_per_page)
total_records = records.count(with_limit_and_skip=False)
cur_records = records.count(with_limit_and_skip=True)
total_pages = int(ceil(total_records / records_per_page)) # from __future__ import division
if page != 1 and not cur_records:
abort(404)
return render_template('show.html', records=records, category=category, cur_page=page, total_pages=total_pages)
def register():
# 已登录用户则返回首页
if g.user.is_authenticated:
return redirect(url_for('index'))
error = None
if request.method == 'POST':
if not request.form['username']:
error = u'用户名不能为空'
elif not request.form['password']:
error = u'密码不能为空'
elif request.form['captcha'].upper() != session['captcha'].upper():
error = u'验证码不正确'
else:
# 判断用户是否存在
db = connect_db()
username_conflict = db.user.find_one({'username': request.form['username']})
email_conflict = db.user.find_one({'email': request.form['email']})
if username_conflict:
error = u'用户名已存在'
elif request.form['email'] and email_conflict:
error = u'邮箱已存在'
else:
# 将用户写入数据库
if 'x-forwarded-for' in request.headers:
ip = request.headers['x-forwarded-for'].split(', ')[0]
else:
ip = request.remote_addr
db.user.insert({
'username': request.form['username'],
'password': hashlib.md5(request.form['password']).hexdigest(),
'email': request.form['email'],
'date': datetime.datetime.now(),
'ip': ip,
'group': 'guest'
})
flash(u'注册成功，3 秒钟内将转到登陆页面……')
return render_template('flash.html', target=url_for('login'))
return render_template('register.html', error=error)
def leave_message():
error = None
if request.method == 'POST':
if not request.form['name']:
error = u'姓名不能为空'
elif not request.form['content']:
error = u'留言内容不能为空'
elif request.form['captcha'].upper() != session['captcha'].upper():
error = u'验证码不正确'
else:
id = g.user.get_id() if g.user.is_authenticated else None
db = connect_db()
# ip = request.headers.get('x-forwarded-for', request.remote_addr)
if 'x-forwarded-for' in request.headers:
ip = request.headers['x-forwarded-for'].split(', ')[0]
else:
ip = request.remote_addr
# dbref
db.message.insert({'uid': id, 'name': request.form['name'], 'email': request.form['email'], 'content': request.form['content'], 'date': datetime.datetime.now(), 'ip': ip, 'visible': 'protected'})
flash(u'留言成功，3 秒钟内将返回留言页面……')
return render_template('flash.html', target=url_for('show_message'))
return render_template('contact.html', error=error)
