def profile():
myform = MyForm()
if request.method == 'POST':
if myform.validate_on_submit():
firstname = myform.firstname.data
lastname = myform.lastname.data
email = myform.email.data
location = myform.location.data
biography = myform.biography.data
gender = myform.gender.data
photo = myform.photo.data
filename = secure_filename(photo.filename)
photo.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
userid = str(uuid.uuid4())
created_on = format_date_joined()
db = connect_db()
cur = db.cursor()
query = "insert into Profiles (firstname, lastname, email, location, biography, gender, photo, userid, created_on) values (%s, %s, %s, %s, %s, %s, %s, %s, %s);"
data = (firstname, lastname, email, location, biography, gender,
filename, userid, created_on)
cur.execute(query, data)
db.commit()
flash('Profile successfully added!', 'success')
return redirect(url_for("profiles"))
flash_errors(myform)
return render_template('profile.html', form=myform)
def admin_insert_book():
if request.method == "POST":
bookname = request.form.get("bookname")
author = request.form.get("author")
quantity = request.form.get("quantity")
section = request.form.get("section")
serial_no = request.form.get("serial_no")
# db.execute("INSERT INTO Ibook(bookname) VALUES(:bookname)",{'bookname':bookname})
db.execute(
"INSERT INTO bookcn(bookname) VALUES(:bookname)", {"bookname": bookname}
)
db.execute(
"INSERT INTO book(bookname,author,quantity,section,serial_no) VALUES(:bookname,:author,:quantity,:section,:serial_no)",
{
"bookname": bookname,
"author": author,
"quantity": quantity,
"section": section,
"serial_no": serial_no,
},
)
# connection.commit()
db.commit()
flash("book inserted successfully", "success")
return render_template("admin_insert_book.html")
def registers():
if request.method == "POST":
name = request.form.get("name")
username = request.form.get("username")
password = request.form.get("password")
confirm = request.form.get("confirm")
# secure_password=sha256_crypt.encrypt(str(password))
user_data = db.execute(
"SELECT username FROM users WHERE username=:username",
{"username": username},
).fetchone()
if user_data is not None:
flash("username already available please use some other", "danger")
else:
if password == confirm:
db.execute(
"INSERT INTO users(name,username,password) VALUES(:name,:username,:password)",
{"name": name, "username": username, "password": password},
)
# db.execute()
db.commit()
flash("you can login now", "success")
return redirect(url_for("login"))
else:
flash("password does not match", "danger")
return render_template("registers.html")
return render_template("registers.html")
def heaven_for_users():
try:
db = pymysql.connect(host="rm-uf6gw23j409s5ui7qmoo.mysql.rds.aliyuncs.com", port=3306, user="******",
password="******",
charset="utf8")
res = request.get_json()
user_id = res.get('user_id')
open_id = res.get('open_id')
new_user_id = res.get('new_user_id')
new_open_id = res.get('new_open_id')
if not all([user_id, open_id, new_user_id, new_open_id]):
return jsonify(errno=-1, errmsg="参数不完整")
cursor = db.cursor()
timeStamp = int(time.time())
dateArray = datetime.datetime.utcfromtimestamp(timeStamp)
otherStyleTime = dateArray.strftime("%Y-%m-%d %H:%M:%S")
sql = f"INSERT INTO zjlivenew.zj_heaven_for_user(create_time,update_time,user_id,openid,new_user_id,new_openid,status)" \
f"VALUES('{otherStyleTime}','{otherStyleTime}',{int(user_id)},'{open_id}',{int(new_user_id)},'{new_open_id}',{int(0)})"
print(sql)
cursor.execute(sql)
db.commit()
db.close()
return jsonify(errno=0, errmsg="OK")
except Exception as e:
Logging.logger.error('errmsg:{0}'.format(e))
return jsonify(errno=-1, errmsg='网络异常')
def freeze_admin():
if (request.method == 'POST'):
print "freeze applications"
sql = "UPDATE main_table SET freeze_status = \'true\' WHERE status = \'submitted\'"
try:
cursor.execute(sql)
db.commit()
print sql
except:
print "Db error"
sql = "SELECT application_no, name, position_applied FROM main_table WHERE status = '%s';" % "submitted"
cursor.execute(sql)
rows1 = list(cursor.fetchall())
rows = []
for i in range(len(rows1)):
temp = [
rows1[i][0], rows1[i][1], rows1[i][2],
str(rows1[i][0]) + ".pdf"
]
rows.append(temp)
print rows
return render_template('admin_view_part1.html',
email_=session['email'],
rows=rows)
def update_ad_teams():
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
cur.execute("SELECT userid FROM tblusers WHERE team IS NULL")
users = {}
while True:
rc = cur.fetchone()
if not rc:
break
users[rc[0].strip().lower()] = rc[0].strip()
mapping = json.loads(config.group_mapping)
userMapping = {}
for group in mapping:
adGroup = group[0]
name = group[1]
print "Processing %s (%s)" % (adGroup, name)
allGroupUsers = ad.get_all_members_of_group(adGroup)
for u in allGroupUsers:
if u.lower() in users:
userMapping[users[u.lower()]] = name
del users[u.lower()]
for u in userMapping:
print "Mapping %s to %s" % (u, userMapping[u])
cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s", [userMapping[u], u])
db.commit()
def register():
myform = MyForm()
if request.method == 'POST':
if myform.validate_on_submit():
username = myform.username.data
password = myform.password.data
firstname = myform.firstname.data
lastname = myform.lastname.data
email = myform.email.data
location = myform.location.data
biography = myform.biography.data
photo = myform.photo.data
filename = secure_filename(photo.filename)
photo.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
db = connect_db()
cur = db.cursor()
joined_on=date.today()
cur.execute('insert into Users (username,password,firstname,lastname,email,location,biography,joined_on) values (%s, %s, %s, %s, %s, %s, %s,%s)',(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],joined_on))
#cur.execute('insert into Users (username,password,firstname,lastname,email,location,biography,photo) values (%s,%s, %s, %s, %s, %s, %s, %s, %s)',(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],request.form['photo']))
db.commit()
flash('You have successfully filled out the form', 'success')
#SAVING DATA TO DATABASE WITH SQLALCHEMY BELOW
#user = Users(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],request.form['photo'],joined_on)
user = Users(request.form['username'],request.form['password'],request.form['firstname'],request.form['lastname'],request.form['email'], request.form['location'],request.form['biography'],joined_on)
db.session.add(user)
db.session.commit()
return render_template('result.html', username=username, password=password, firstname=firstname, lastname=lastname, email=email,
location=location, biography=biography,filename=filename)
flash_errors(myform)
return render_template('register.html', form=myform)
def profile():
"""Render the website's adding profile page."""
User = UserProfile()
if request.method == 'POST':
if User.validate_on_submit():
db = connect_db()
cur = db.cursor()
firstname = User.firstname.data
lastname = User.lastname.data
gender = User.gender.data
email = User.email.data
location = User.location.data
biography = User.biography.data
profilepic = User.profilepic.data
date = User.date.data
filename = secure_filename(profilepic.filename)
profilepic.save(os.path.join(app.config['UPLOAD_FOLDER'],
filename))
cur.execute(
'insert into user_profiles (firstname, lastname, gender, email, location, biography, date)values (%s, %s, %s, %s, %s, %s %s)',
(request.form['firstname'], request.form['lastname'],
request.form['gender'], request.form['email'],
request.form['location'], request.form['biography'],
request.form['date']))
db.commit()
flash('The profile was succesfully added', 'success')
return redirect(url_for('profiles'), filename=filename, form=User)
flash_errors(User)
return render_template('profile.html')
def extract():
global PERFORM_FETCH
timestamp = time.time()
config = Config()
api = Mobileclient()
manager = ApiManager(api, config)
manager.login()
now = datetime.datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0)
data_playcounts, data_genres, data_artists = manager.get_plays(now)
manager.logout()
if data_playcounts is not None:
graph_dict = graph_data(data_genres)
if PERFORM_FETCH:
inserts = len(data_playcounts)
inserts += DatabaseInsert.genre_playcount(data_genres, now)
inserts += DatabaseInsert.artist_playcount(data_artists, now)
else:
inserts = 0
app.logger.info("%s inserts have been made into the database." % inserts)
timestamp = time.time() - timestamp
record = models.Metrics(date=now, query_time=timestamp)
db.add(record)
db.commit()
else:
graph_dict = None
app.logger.info("Database has already been updated for today, avoiding double entry.")
return data_playcounts, graph_dict, data_artists
def post(self, *args, **kwargs):
data = request.get_json()
bankname = data.get("bankname", '')
universalbranchcode = data.get("universalbranchcode", "")
schema = Kanpai.Object({
"bankname": (Kanpai.String().required().max(32)),
"universalbranchcode": (Kanpai.String().required())
})
validation_results = schema.validate(data)
Validations.validate_error(validation_results)
if data:
bankcode = BankCodeDB(bankname=bankname,
universalbranchcode=universalbranchcode)
db.add(bankcode)
db.commit()
return jsonify({
"status": "success",
"data": validation_results["data"]
})
def buyer_register():
db = pymysql.connect(**config)
cur = db.cursor()
sql_s = 'SELECT * FROM users WHERE email = %s'
data = cur.execute(sql_s, [session[constants.JWT_PAYLOAD]['email']])
user = '******'
if int(data) == 0:
sql = '''INSERT INTO users (user_category, email, user_name) VALUES (%s, %s, %s)'''
cur.execute(sql, [
user, session[constants.JWT_PAYLOAD]['email'],
session[constants.JWT_PAYLOAD]['name']
])
db.commit()
is_buyer = True
else:
is_buyer = False
cur.execute(sql_s, [session[constants.JWT_PAYLOAD]['email']])
check = cur.fetchone()
user = check[1]
return render_template('accounts/register_customer.html',
user=user,
is_buyer=is_buyer,
userinfo=session[constants.PROFILE_KEY])
def do_import(file_path, file_size):
try:
with open(file_path, 'r') as f:
large_file = float(file_size / 1000000) > 100
record_list = list()
for line in f:
param = line.split(' | ')
new_record = Journal(level=param[0],
title=param[1],
detail=param[2],
datetime=param[3])
record_list.append(new_record)
if large_file:
if len(record_list) > 10000:
db.session.add_all(record_list)
db.commit()
record_list = list()
else:
pass
if len(record_list) > 0:
db.session.add_all(record_list)
db.commit()
return True
except Exception as e:
return False
def register():
if request.method == 'POST':
fname=request.form['fname']
mname=request.form['mname']
lname=request.form['lname']
dob=request.form['dob']
gender=request.form['gender']
street=request.form['street']
city=request.form['city']
phone=request.form['phone']
global patnum
patnum += 1
#patnum= cursor.execute('SELECT MAX(patientID) FROM patients')
pID= 'P-0000000' + str(patnum)
#execute sql commands
cursor.execute("INSERT INTO patients VALUES ('{}','{}','{}','{}','{}','{}')".format(pID,lname,fname,mname,gender,dob))
cursor.execute("INSERT INTO patient_phoneno VALUES ('{}','{}')".format(pID,phone))
cursor.execute("INSERT INTO patient_address VALUES ('{}','{}','{}')".format(pID,city,street))
#commit changes to the database
db.commit()
#in case there is any error
#db.rollback()
return render_template("register.html")
def register(user, address, account):
userModel = addressModel = None
try:
try:
userModel = UserModel(**user)
userModel = userDao.save(userModel, autocommit=False)
except Exception as e:
print(e)
if 'Duplicate' in str(e):
raise ValueError('Username already taken')
raise ValueError('User informations are not valid')
try:
addressModel = AddressModel(**address)
addressModel = addressDao.save(addressModel, autocommit=False)
except Exception as e:
print(e)
raise ValueError('Address informations are not valid')
try:
accountModel = AccountModel(**account)
accountModel.id_User = userModel.id
accountModel.id_Address = addressModel.id
accountDao.save(accountModel, autocommit=False)
except Exception as e:
print(e)
raise ValueError('Account informations are not valid')
db.commit()
return userDao.getById(userModel.id)
except Exception as e:
db.rollback()
raise e
def newNotebook():
if current_user.enable:
res = requests.post(f'{APISERVER}/api/notebook',
json={'name': request.form['name']})
if res.status_code == 201:
dates = json.loads(res.text)
notebook_id = dates.get('id')
db = mysql.connect(host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PSSW,
database=MYSQL_DB)
cur = db.cursor()
cur.execute(
'INSERT INTO user_object_id (object_id, object_type, user_name) VALUES (%s,%s,%s)',
(
notebook_id,
"notebook",
current_user.username,
))
db.commit()
db.close()
flash(f'Notebook created!')
else:
flash(res.status_code)
return redirect(url_for('notebooks'))
else:
return redirect(url_for('403'))
def addMeal(n_meal):
cursor = db.cursor()
command_get_meal_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'meal';"
command_meal = "insert into meal (calorie, num_servings) values (%s, %s);"
command_made_from = "insert into made_from (meal_id, recipe_id) values (%s, %s);"
# get increment value
cursor.execute(command_get_meal_id)
response = cursor.fetchall()
n_meal_id = response[0][0]
# insert meal
cursor.execute(command_meal, (
n_meal.calorie,
n_meal.num_serving,
))
db.commit()
# insert made_from
cursor.execute(command_made_from, (
n_meal_id,
n_meal.recipe_id,
))
db.commit()
def delete_user(data):
sql = "SELECT * FROM users WHERE user_email = '%s'" % data["user_email"]
print(sql)
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchone()
print(result[7])
if result[7] == 4 or result[7] == 15 or result[7] == 7:
sql2 = "DELETE FROM users WHERE user_email = '%s'" % data[
"user_email"]
print(sql2)
cursor.execute(sql2)
db.commit()
status = 200
res = "User deleted successfully"
else:
status = 201
res = "Not authorized"
except:
print("Error: unable to fetch data")
return res, status
def updateUser(user):
cursor = db.cursor()
command = "update users set first_name=%s, last_name=%s, email=%s, gender=%s, password=%s where user_id=%s;"
cursor.execute(command, (user.fname, user.lname, user.email, user.gender,
user.password, user.id))
db.commit()
def updateRecipe(recipe):
cursor = db.cursor()
command = "update recipe set name=%s, created_date=%s, calorie=%s, image_url=%s where recipe_id=%s;"
cursor.execute(command, (recipe.name, recipe.date_created, recipe.calorie,
recipe.image_url, recipe.id))
db.commit()
def addNewRecipe(user_id, recipe):
cursor = db.cursor()
command_get_recipe_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'recipe';"
command_recipe = "insert into recipe (name, created_date, calorie, image_url) values (%s, %s, %s, %s);"
command_create = "insert into creates (user_id, recipe_id) values ( %s, %s);"
# get increment value
cursor.execute(command_get_recipe_id)
response = cursor.fetchall()
n_recipe_id = response[0][0]
cursor.execute(command_recipe, (
recipe.name,
recipe.date_created,
recipe.calorie,
recipe.image_url,
))
db.commit()
cursor.execute(command_create, (
user_id,
n_recipe_id,
))
db.commit()
for ingredient in recipe.ingredients:
addIngredient(n_recipe_id, ingredient)
for instruction in recipe.instructions:
addInstruction(n_recipe_id, instruction)
def post(self, *args, **kwargs):
data = request.get_json()
username = data.get("username", "")
roledesc = data.get("roledesc", "")
status = data.get("status", "")
schema = Kanpai.Object({
"username":
(Kanpai.String(error="user name should be string").trim().required(
error="user name should required").max(
32, error="should not be more than 32 characters")),
"roledesc":
(Kanpai.String(error="role desc should be string").trim().required(
error="role desc should required").max(
32, error="should not be more than 32 characters")),
"status":
(Kanpai.String(error="role desc should be string").trim().required(
error="role desc should required").max(
32, error="should not be more than 32 characters"))
})
validation_results = schema.validate(data)
Validations.validate_error(obj=validation_results)
check_username_indb = session.query(UserDB).filter_by(
username=username).first()
check_roledesc_indb = session.query(RoleDB).filter_by(
roledesc=roledesc).first()
if not check_username_indb:
return jsonify({
"status":
"error",
"errors":
f"user {check_username_indb} not present in db"
})
if not check_roledesc_indb:
return jsonify({
"status":
"error",
"errors":
f"role description {check_roledesc_indb} not present in db"
})
if data:
userrole = UserRoleDB(userid=check_username_indb.id,
roleid=check_roledesc_indb.id,
status=status)
db.add(userrole)
db.commit()
return jsonify({
"status": "success",
"data": validation_results["data"]
})
def addInstruction(recipe_id, n_instruction):
cursor = db.cursor()
command_get_instr_id = "SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'instructions';"
command_prepare = "insert into prepare (recipe_id, instruction_id, step_no) values (%s, %s, %s);"
command_instructions = "insert into instructions (step_no, step_description) values (%s, %s);"
# get increment value
cursor.execute(command_get_instr_id)
response = cursor.fetchall()
n_instruction_id = response[0][0]
# insert instruction
cursor.execute(command_instructions, (
n_instruction.step_no,
n_instruction.description,
))
db.commit()
# insert prepare
cursor.execute(command_prepare, (
recipe_id,
n_instruction_id,
n_instruction.step_no,
))
db.commit()
def before_request():
g.user=current_user
if g.user.is_authenticated():
a=str(datetime.utcnow());
cur.execute('update user set last_seen = "'+a+'" where id= %d'%(g.user.id));
db.commit();
g.search_form=SearchForm();
def update_user(request:Request, department_id, department_data: schemas.DepartmentIn):
department = db.query(models.Department).get(department_id)
if not department:
raise HTTPException(status_code=404, detail=f"Could not find Department with ID {department_id}")
department.name = department_data.name
db.commit()
return {'data':department}
def delete_table(table_name):
query = '''DROP TABLE IF EXISTS ''' + table_name
db = get_db()
#db.execute("PRAGMA busy_timeout = 30000")
cur = db.execute(query)
db.commit()
cur.close()
def attend(event_id):
schema = EventSchema()
event = Event.get(id=event_id)
event.attended_by.add(g.current_user.id)
db.commit()
return schema.dumps(event)
def post(self, *args, **kwargs):
schema = Kanpai.Object({
'name': (Kanpai.String(
error="Description should be string").trim().required(
error="Description should required").max(
64, error="Should not be more than 64 characters")),
'idnumber': (Kanpai.String(
error="Description should be string").trim().required(
error="Description should required").max(
64, error="Should not be more than 64 characters"))
})
data = request.get_json()
validation_result = schema.validate(data)
if validation_result.get('success', False) is False:
return jsonify({
"status": "error",
"errors": validation_result.get('error')
})
if data:
idtype = IdTypeDB(name=data['name'], idnumber=data['idnumber'])
db.add(idtype)
db.commit()
return jsonify({
"status": "success",
"result": validation_result['data']
})
def new_password_t():
L = request.get_data()
L = json.loads(L)
db = pymysql.connect(
host='database-1.clr3d8nnckz4.us-east-2.rds.amazonaws.com',
user='******',
password='******',
port=3306,
db='chatbot')
cursor = db.cursor()
sql = "SELECT password from staff where id=%s"
id = L['id']
password = L['old_passwd']
new_password = L['new_passwd']
cursor.execute(sql, id)
((results, ), ) = cursor.fetchall()
if check_password_hash(results, password):
new_password = generate_password_hash(new_password)
sql = "UPDATE staff set password=%s WHERE id = %s "
cursor.execute(sql, (new_password, id))
db.commit()
db.close()
return '200'
else:
return '300'
def register():
form = RegistrationForm()
if form.validate_on_submit():
user_check = db.execute(
"SELECT * FROM users WHERE username = :username", {
"username": form.username.data
}).fetchone()
# check on duplicate username
if user_check:
flash('User already exists')
return redirect(url_for('register'))
email_check = db.execute("SELECT * FROM users WHERE email = :email", {
"email": form.email.data
}).fetchone()
# check on duplicate email
if email_check:
flash('Email already exists')
return redirect(url_for('register'))
# create user
username = form.username.data
email = form.email.data
password_hash = generate_password_hash(form.password.data)
db.execute(
"INSERT INTO users (username, email, password_hash) VALUES (:username, :email, :password_hash)",
{
"username": username,
"email": email,
"password_hash": password_hash
})
db.commit()
flash('Congratulations, you are now a registered user!')
return redirect(url_for('login'))
return render_template('register.html', title='Register', form=form)
def query_db(query, args=(), username='******', one=False):
try:
cur = get_db().execute(query, args)
rv = cur.fetchall()
cur.close()
return (rv[0] if rv else None) if one else rv
except:
sql_create_table = """ CREATE TABLE IF NOT EXISTS """ + current_user.username + """ (
user_id text NOT NULL,
title text NOT NULL,
date text NOT NULL,
is_done integer default 0
)"""
query = ''' INSERT INTO ''' + username + '''(user_id,title,date,is_done)
VALUES(?,?,?,?) '''
db = get_db()
#db.execute("PRAGMA busy_timeout = 30000")
cur = db.execute(sql_create_table)
db.commit()
cur.close()
# #create the first todo for first-time user
# args = (username, 'This is your first todo', datetime.datetime.now().strftime("%m/%d/%Y, %H:%M:%S"),0)
# cur = db.execute(query, args)
# #cur.commit()
query = 'select * from ' + current_user.username
cur = get_db().execute(query, ())
rv = cur.fetchall()
cur.close()
return (rv[0] if rv else None) if one else rv
def update_ad_teams():
db = app.db.dbWriteInstance()
ad = app.ad.ActiveDirectory()
cur = db.cursor()
cur.execute("SELECT userid FROM tblusers WHERE team IS NULL")
users = {}
while True:
rc = cur.fetchone()
if not rc:
break
users[rc[0].strip().lower()] = rc[0].strip()
mapping = json.loads(config.group_mapping)
userMapping = {}
for group in mapping:
adGroup = group[0]
name = group[1]
print "Processing %s (%s)" % (adGroup, name)
allGroupUsers = ad.get_all_members_of_group(adGroup)
for u in allGroupUsers:
if u.lower() in users:
userMapping[users[u.lower()]] = name
del users[u.lower()]
for u in userMapping:
print "Mapping %s to %s" % (u, userMapping[u])
cur.execute("UPDATE tblusers SET team=%s WHERE userid=%s",
[userMapping[u], u])
db.commit()
def select_achievement():
db = pymysql.connect("127.0.0.1", "root", "638436", "adms")
cursor = db.cursor()
# username = "******"
# r_mname = "ADNI_002_S_0295_MR_HarP_135_final_release_2015_Br_20150226095012465_S13408_I474728.nii"
# mri = "select * from mri where m_name='" + r_mname + "'"
# 方法一：
# select Score, (select count(distinct Score) from Scores where Score>=s.Score) as Rank from Scores as s order by Score desc;
# rank = "select ea_eid, ea_score, (select count(distinct ea_score) from eachievement where ea_score >= s.ea_score) " \
# "as Rank from eachievement as s order by ea_score desc"
username = "******"
resu = "select * from resu where r_uid='" + username + "'"
cursor.execute(resu)
rhipvlou_r = cursor.fetchall()
print(rhipvlou_r)
data = []
for i in rhipvlou_r:
x_data = "海马体数量" + "-" + i[4].strftime('%Y.%m.%d')
y_data = int(i[6])
print(x_data)
print(y_data)
data_dict = {"name":x_data,"num":y_data}
data.append(data_dict)
print(data)
# r_uid = mri_r[0][1]
# r_utime = mri_r[0][3]
# print(mri_r)
# print(r_uid)
# print(r_utime)
db.commit()
db.close()
def create_user(user):
password = hash_password(user["password"])
sql = "INSERT INTO users(user_name, user_email, user_password, user_address, user_phone, created_by, actions) VALUES ('%s','%s','%s','%s','%s', %d, %d)" % (
user["user_name"], user["user_email"], password, user["user_address"],
user["user_phone"], user["created_by"], user["actions"])
status = 0
res = ''
try:
cursor.execute(sql)
db.commit()
status = 200
res = 'User created successfully'
except pymysql.Error as e:
try:
print("MySQL Error [%d]: %s") % (e.args[0], e.args[1])
return None
except IndexError:
print("MySQL Error: %s") % str(e)
return None
except:
db.rollback()
status = 401
res = 'Something went wrong. Try again'
return res, status
def update_phone_from_discount(input):
# Se actualiza la información del celular en la base sifco_discount
client_name = Sifco_nits.Sifco_nits.get_name_by_nit(input)
phone = input['phone']
card = input['card']
if len(phone) != 10:
raise BadLengthException("El número de celular no es válido")
cursor = db.cursor()
sql_insert_query = """
update
sifco_discount_{} as sd
set
celular = '{}'
from (
select nu_tarjeta_sha256 as tarjeta,
fecha_compra as fecha
from sifco_discount_{}
where
nu_tarjeta_sha256 = '{}'
order by fecha desc limit 1
) as last_val
where
last_val.tarjeta = sd.nu_tarjeta_sha256
and last_val.fecha = sd.fecha_compra
""".format(client_name, phone, client_name, card)
cursor.execute(sql_insert_query)
db.commit()
cursor.close()
return None
def getAthleteStats(challenges): client = stravalib.client.Client(access_token=config.TOKEN) for c in challenges: athStats=models.AthleteStats(client.get_athlete_stats(c.athId)) if athStats.YTDCount > minNumberRuns: db.add(athId=c.athId,athStats=athStats) db.add(c) db.commit()
def add_entry():
if not session.get('logged_in'):
abort(401)
db = get_db()
print [request.form['title'], request.form['text']]
db.cursor().execute('insert into entries (title, text) values (%s, %s)', [request.form['title'], request.form['text']])
db.commit()
flash('New entry was successfully posted')
return redirect(url_for('show_entries'))
def delete_delivery_jobs(id):
"""
Delete delivery jobs based on id parameter
"""
d = DeliveryJob.query.filter_by(id=id)
if d is not None:
d.delete()
db.commit()
return redirect('/delivery-jobs/')
return page_not_found(d)
def delete(self, userId):
"""This method deletes a user and returns result none.
Args:
userId - An integer, primary key that identifies the user.
"""
user = User.query.get(userId)
db.session.delete(user)
db.commit()
return buildOkResponse(None)
def remove_user(username):
'''
Remove the user with the given username
Return True if the user is deleted
'''
user = User.query.filter(User.username == username).first()
if user:
db.session.delete(user)
db.commit()
return True
else:
return False
def lookup_parts(self):
for bompart in self.bomparts:
vendor = vendors[bompart.lookup_source]
vendor_part_number = bompart.lookup_id
query = VendorPart.query
query = query.filter_by(vendor=vendor,
vendor_part_number=vendor_part_number)
query = query.order_by(VendorPart.fetch_timestamp).desc()
vendor_part = query.first()
if vendor_part:
bompart.part = vendor_part.part
db.commit()
def __init__(self,nickname,email):
self.nickname=nickname;
self.email=email;
if(cur.execute('select * from user where nickname="'+self.nickname+'"')==0):
cur.execute('insert into user(nickname,email) values("'+self.nickname+'","'+self.email+'")');
db.commit();
cur.execute('select * from user where nickname="'+self.nickname+'"')
for a in cur:
self.id=a[0];
self.about_me=a[3];
self.last_seen=a[4];
break;
def artist_playcount(data, now):
app.logger.info("Inserting Artist PlayCount Data ...")
insertions = 0
for record in data:
insert = models.Artists(
date=now,
artist=record[0],
playCount=record[1]
)
db.add(insert)
insertions += 1
db.commit()
return insertions
def genre_playcount(data, now):
app.logger.info("Inserting Genre PlayCount Data ...")
insertions = 0
for record in data:
insert = models.Genre(
date=now,
genre=record[0],
playCount=record[1]
)
db.add(insert)
insertions += 1
db.commit()
return insertions
def index():
if g.user:
return redirect(url_for('profile'))
form = RegisterForm(request.form)
if request.method == 'POST' and form.is_valid():
user = form.save()
user.add_role('user')
db.commit() # commit BEFORE doing auth.login!
auth.login(user)
return redirect(url_for('profile'))
return render_template('index.html', **locals())
def create_categories():
# Create all unique categories in the db.
categories = set()
for node in nodes:
if node['categories']:
categories.update(node['categories'])
categories = [category for category in categories if len(category) > 2]
for name in categories:
category = Category(name)
db.add(category)
db.commit()
def create_key_people():
# Create all key_people in the db.
key_people = set()
for node in nodes:
if node['key_people']:
for kp in node['key_people']:
key_people.add(kp['name'])
key_people = list(key_people)
for person in key_people:
key_person = Keyperson(person)
db.add(key_person)
db.commit()
def delete_group(group_id):
data = request.json
group = Group.query.get(group_id)
try:
db.session.delete(group)
db.commit()
status, code = "success", 200
except:
status, code = "Service down", 500
finally:
db.session.close()
return jsonify({"result" : status}), code
def facebook_authorized(resp):
if resp is None:
flash(u'Access denied: reason=%s error=%s' % (
request.args['error_reason'],
request.args['error_description'])
)
return redirect(url_for('sign_in'))
session['facebook_token'] = (resp['access_token'], '')
me = facebook.get('/me')
# >>> me.data
# {
# "username": "******",
# "id": "581604320",
# "email": "*****@*****.**"
# "locale": "en_US",
# "timezone": -5,
# "first_name": "Juan-Pablo",
# "last_name": "Scaletti",
# "name": "Juan-Pablo Scaletti",
# "gender": "male",
# "link": "http://www.facebook.com/jpscaletti",
# "updated_time": "2013-04-15T06:33:55+0000",
# }
user = db.query(User).filter(User.facebook_id == me.data['id']).first()
if not user: # new user!
if g.user:
user = g.user
else:
login = get_unique_login(me.data.get('username'))
user = User(login=login)
db.add(user)
user.facebook_id = me.data['id']
user.last_sign_in = datetime.utcnow()
# in any case we update the authentication token in the db
# In case the user temporarily revoked access we will have
# new tokens here.
user.facebook_token = resp['access_token']
# don't forget to commit **before** doing ``auth.login(user)`
db.commit()
auth.login(user)
next = request.args.get('next') or url_for('profile')
return redirect(next)
def edit():
form=EditForm();
if form.validate_on_submit():
a=cur.execute('select * from user where nickname="'+form.nickname.data+'"')
if(a==0 or form.nickname.data==g.user.nickname):
cur.execute('update user set nickname = "'+form.nickname.data+'",about_me="'+form.about_me.data+'" where id= %d'%(g.user.id));
db.commit();
flash('Your changes have been saved.')
return redirect(url_for('user',nickname=g.user.nickname))
else:
flash('The name you set has been used by others, please choose another name');
return redirect(url_for('edit'));
else:
form.nickname.data=g.user.nickname
form.about_me.data=g.user.about_me
return render_template('edit.html',form=form);
def after_login(resp):
if resp.email is None or resp.email == "":
flash('Invalid login. Please try again')
return redirect(url_for('login'))
user = User.query.filter_by(email=resp.email).first()
if user is None:
nickname = resp.nickname
user = User(nickname=nickname, email=resp.email)
db.session.add(user)
db.commit()
remember_me = False
if "remember_me" in session:
remember_me = session["remember_me"]
session.pop("remember_me", None)
login_user(user, remember=remember_me)
return redirect(request.args.get("next") or url_for("index"))
def _empty_tables():
parsed = urlparse(DATABASE_URL)
app_tables_query = """
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND
table_catalog = '{0}' AND
table_name != 'schema_version';""".format(parsed.path.strip('/'))
cursor = db.cursor()
cursor.execute(app_tables_query)
tables = [r[0] for r in cursor.fetchall()]
for t in tables:
query = 'TRUNCATE TABLE {0} CASCADE;'.format(t)
cursor.execute(query)
db.commit()
cursor.close()
def song_playcount(data, now):
app.logger.info("Inserting Song PlayCount Data ...")
insertions = 0
for record in data:
insert = models.Songs(
date=now,
google_id=record[0],
playCount=record[1],
artist=record[2],
album=record[3],
title=record[4],
trackNumber=record[5],
genre=record[6]
)
db.add(insert)
insertions += 1
db.commit()
return insertions
def create_tournament():
form = CreateTournament(request.form)
db = get_db()
if request.method == 'POST':
nam = request.form['tourn_name']
loc = request.form['location']
cal = request.form['calendar']
sys = request.form['system']
tie = request.form['tie_break']
db.execute('''INSERT INTO tournament \
(id, name, location, calendar, system, tie_break, round_num) \
VALUES (NULL, ?, ?, ?, ?, ?, ?)''', (nam, loc, cal, sys, tie, 1))
db.commit()
tournamentID = str(
db.execute('''SELECT max(id) FROM tournament''').fetchone()[0])
db.close()
return redirect(url_for('add_players', tournamentID=tournamentID))
return render_template('create_tournament.html', title='Create new tournament', form=form)
def _isValidGroup(self, name):
db = self.getDB()
cur = db.cursor()
cur.execute("SELECT groupid FROM tblgroups WHERE name=%s", [name])
rc = cur.fetchone()
if rc:
return True
# Might be a new group
if self.getAD().is_valid_group(name):
# Cache the group members
db = self.getWriteDB()
cur = db.cursor()
cur.execute("INSERT INTO tblgroups (name) VALUES (%s) RETURNING groupid", [name])
rc = cur.fetchone()
groupid = rc[0]
members = self.getAD().get_all_members_of_group(name)
for m in members:
cur.execute("INSERT INTO tblgroupusers (groupid, userid) VALUES (%s, %s)", [groupid, m])
db.commit()
return True
return False
def twitter_authorized(resp):
if resp is None:
flash(u'You denied the request to sign in.')
return redirect(url_for('sign_in'))
session['twitter_token'] = resp['oauth_token']
# >>> resp
# {
# "oauth_token_secret": "...",
# "oauth_token": "...",
# "user_id": "11640332",
# "screen_name": "jpscaletti"
# }
user = db.query(User).filter(User.twitter_id == resp['user_id']).first()
if not user: # new user!
if g.user:
user = g.user
else:
login = get_unique_login(resp['screen_name'])
user = User(login=login)
db.add(user)
user.twitter_id = resp['user_id']
user.last_sign_in = datetime.utcnow()
# in any case we update the authentication token in the db
# In case the user temporarily revoked access we will have
# new tokens here.
user.twitter_username = resp['screen_name']
user.twitter_token = resp['oauth_token']
user.twitter_secret = resp['oauth_token_secret']
# don't forget to commit **before** doing ``auth.login(user)`
db.commit()
auth.login(user)
next = request.args.get('next') or url_for('profile')
return redirect(next)
def __init__(self,body,timestamp,user_id):
self.body=body;
self.timestamp=str(timestamp);
self.user_id=user_id;
cur.execute("insert into post(body,timestamp,user_id) values ('"+self.body+"','"+self.timestamp+"',%d)"%(self.user_id));
db.commit();
def unfollow(self,user):
if self.is_following(user):
cur.execute('delete from followers where follower_id=%d and followed_id=%d'%(self.id,user.id));
db.commit();
return self;
else: return 0;
# Create all relations in the db.
# create_relations()
# Create all cities in the db.
#create_cities()
for node in nodes:
create_entity(node)
# Manually defining IDs broken in civic.json...
old_to_new[289] = old_to_new[902] # New York Tech Meetup
old_to_new[541] = old_to_new[552] # Open Government Partnership
# Make all connections between entities.
make_connections()
db.commit()
print "Wrote %d Entity entries." % len(Entity.query.all())
print "Wrote %d Category entries." % len(Category.query.all())
print "Wrote %d Location entries." % len(Location.query.all())
print "Wrote %d Keyperson entries." % len(Keyperson.query.all())
print "Wrote %d Revenue entries." % len(Revenue.query.all())
print "Wrote %d Expense entries." % len(Expense.query.all())
print "Wrote %d Grant entries." % len(Grant.query.all())
print "Wrote %d Investment entries." % len(Investment.query.all())
print "Wrote %d Dataconnection entries." % len(Dataconnection.query.all())
print "Wrote %d Relation entries." % len(Relation.query.all())
print "Wrote %d Collaboration entries." % len(Collaboration.query.all())
def init_db():
with closing(connect_db()) as db:
with app.open_resource('schema.sql', mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
def follow(self,user):
if not self.is_following(user):
cur.execute('insert followers (follower_id,followed_id) values (%d,%d)'%(self.id,user.id));
db.commit();
return self;
else: return 0;
