def new_art():
db.connect()
# logging.error()
art = ArticleForm(request.form)
# for a in art:
# logging.error(a)
if request.method == 'POST':
if art.validate():
#存入数据库
db.insert_one(table_name = 'arts',
art_tuple = (str(uuid.uuid1()),
request.form['title'],
request.form['author'],
request.form['content'],
datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")))
# db.insert_one(table_name = 'arts',art_tuple = ('art.title',"art.author","art.content"))
logging.error('new art added')
db_query_arts = db.find_all('arts')
arts = []
for db_query_art in db_query_arts:
arts.append(Art(db_query_art[0],db_query_art[1],db_query_art[2],db_query_art[3],db_query_art[4]))
arts.reverse()
return render_template('new_art.html',form = art,arts = arts)
def createtables_db():
# Connect to our database.
db.connect()
# Create the tables.
db.create_tables([Contribution, File])
print "Created tables"
def main():
db.connect()
setup_tables()
register_blueprints()
app.run(debug=config.DEBUG,
host="0.0.0.0",
port=int(os.environ.get("PORT", 5912)))
def show_one_art(art_id):
db.connect()
result = db.c.execute('SELECT * FROM arts WHERE id = ?',(art_id,)).fetchone()
if result:
art = Art(result[0],result[1],result[2],result[3],result[4])
return render_template('one_article.html',art = art)
else:
# errorhandler('404')
abort(404)
def show_art_list():
db.connect()
db_arts = db.c.execute('SELECT * FROM arts').fetchall()
arts = []
if db_arts:
for db_art in db_arts:
arts.append(Art(db_art[0],db_art[1],db_art[2],db_art[3],db_art[4]))
return render_template('art_list.html',arts = arts,num =len(arts))
#2016年6月27日更新
def relatorioinscritos():
response.content_type = 'application/json; charset=charset=UTF8'
dict = clearRequest(request.forms)
codEv = dict.get('codEv')
numEd = dict.get('numEd')
db.connect()
var = db.var("LONG")
json = db.function("RELATORIOS.inscritos", var,[codEv,numEd])
db.disconnect()
return json
def relatorioapresentacao():
response.content_type = 'application/json; charset=charset=UTF8'
dict = clearRequest(request.forms)
codEv = dict.get('codEv')
numEd = dict.get('numEd')
db.connect()
var = db.var("LONG")
codEvs, numeds,json = db.procedure("RELATORIOS.apresentacoes",[codEv,numEd,var])
db.disconnect()
return json.getvalue()
def gen_command():
form = CommandGenForm()
database = db.connect(
host="192.170.232.66", # your host
user="******", # your username
passwd="foobar", # your password
db="mysql",
port=3306,
) # name of the data base
form.tissuetype.choices = helpfuncs._getTissueTypes(database) # fetch tissue types from DB
form.study.choices = helpfuncs._getStudyNames(database) # fetch study names from DB
if form.validate_on_submit():
pfp = form.phenofilepath.data
gdfp = form.genedatafilepath.data
gth = form.genotypeheader.data
gtt = form.genotypetail.data
tissue = form.tissuetyp
e.data
study = form.study.data
output = helpfuncs._generateCommand(pfp, gdfp, gth, gtt, tissue, study)
flash(output)
return redirect(url_for("gen_command"))
return render_template("cmdgen.html", form=form) # TBA
def loop_run(self):
count = 0
while 1:
try:
if count > 60:
db.connect()
maxid = self.get_maxid()
self.delete(maxid - self.rest)
count = 0
db.close()
else:
count += 1
except Exception as e:
logger.error(e)
time.sleep(1)
finally:
time.sleep(1)
if self.is_quit:
break
def login_bootstrap():
form = LoginForm(request.form)
if request.method == "POST":
if form.validate():
#到数据库中查询,返回错误
db.connect()
logging.error('username = %s,psw = %s',form.username.data,form.password.data)
db_password = db.c.execute("SELECT password FROM users WHERE username = ?",(form.username.data,)).fetchone()
if db_password:
if hashlib.md5(KEY + form.password.data).hexdigest() != db_password[0]:
# print 'db_password =%s,input_psw = %s' %(db_password , hashlib.md5(KEY+form.password.data).hexdigest() )
# logging.error('db_password = '******'密码错误')
else:
flash(u'登陆成功')
else:
logging.error(db_password)
flash(u'用户名不存在')
return render_template('login_bootstrap.html',form = form)
def login_user(email):
cmd_get_user = sqlalchemy.text("SELECT id, email, password FROM users WHERE email = :email")
if Utils.check_email_format(email):
with db.connect() as conn:
rows = conn.execute(cmd_get_user, email=email)
if rows.rowcount == 0:
return {}
else:
row = rows.fetchone()
return {"email": email, "hashed_pass": row[2], "user_id": row[0]} # pentru verificarea parolei: pbkdf2_sha256.verify(entered_password, hashed_password)
else:
return {}
def update_ad_status(ad_id, status, user_id):
try:
if status:
status = 1
else:
status = 0
with db.connect() as conn:
conn.execute("UPDATE ads SET status=" + str(status) + " Where id=" + str(ad_id) + " AND user_id=" + str(user_id))
return True
except:
error_client.report_exception()
return False
def delete_tag(recipe_id, tag_name):
conn = db.connect()
# q1 = "SELECT ingredient_id FROM Ingredients WHERE ingredient_name = '{}';".format(ingredient)
# res = conn.execute(q1)
# ing = 0
# for r in res:
# ing = r[0]
query = "DELETE FROM RecipeHasTags where recipe_id = {} and tag_name = '{}';".format(
recipe_id, tag_name) #type 0 means recipe
conn.execute(query)
conn.close()
return fetch_lists()
def edit_posting(data):
print(data)
conn = db.connect()
query = f"""
UPDATE Posting
SET title = "{data["title"]}", description = "{data["description"]}",
location = "{data["location"]}", link = "{data["link"]}",
due_date = "{data["due_date"]}", posted_by = {data["posted_by"]}
WHERE id = {data["id"]};
"""
conn.execute(query)
conn.close()
def fetch_tables():
conn = db.connect()
result = conn.execute('SHOW TABLES')
conn.close()
items = []
for r in result:
n = utils.camel_to_normal(r[0])
item = {"name": n, "link": '/' + utils.normal_to_snake(n)}
items.append(item)
return items
def most_applicants():
query = """
SELECT Posting.id, name, title, COUNT(Application.id) AS num
FROM Application JOIN Posting ON Application.posting_id = Posting.id JOIN Company ON Posting.posted_by = Company.id
WHERE status = "APPLIED"
GROUP BY Posting.id
ORDER BY num DESC;
"""
conn = db.connect()
query_results = conn.execute(query).fetchall()
conn.close()
return [dict(zip(["id", "name", "title", "num"], result)) for result in query_results]
def fetch_advanced_query() -> dict:
"""Fetches results of advanced query"""
conn = db.connect()
query_results = conn.execute(
"SELECT i.instrument_type, COUNT(r.rental_id) AS RentalCount FROM Instruments i JOIN Rentals r ON (instrumentid) GROUP BY i.instrument_type ORDER BY RentalCount DESC"
).fetchall()
conn.close()
query_list = []
for result in query_results:
item = {"instrument_type": result[0], "RentalCount": result[1]}
query_list.append(item)
return query_list
def fetch_lists():
conn = db.connect()
query_results = conn.execute(
"SELECT list_id, name FROM PersonalizedList;").fetchall()
conn.close()
list_name = []
for result in query_results:
item = {"list_id": result[0], "name": result[1]}
list_name.append(item)
return list_name
def init_db():
'''Initial database'''
db.connect()
print('Creating tables ...')
db.create_tables(tables)
print('Tables have been created.')
print('Writing default configurations ...')
Config.create(name='site_name', value=app.config['DEFAULT_SITE_NAME'])
Config.create(name='site_url', value=app.config['DEFAULT_SITE_URL'])
Config.create(name='count_topic',
value=app.config['DEFAULT_TOPICS_PER_PAGE'])
Config.create(name='count_post',
value=app.config['DEFAULT_POSTS_PER_PAGE'])
Config.create(name='count_list_item',
value=app.config['DEFAULT_LIST_ITEM_PER_PAGE'])
# Config.create(name='count_subpost', value=app.config['DEFAULT_SUBPOSTS_PER_PAGE'])
print('Default configurations have been written into database.')
db.close()
def advance_dimitar_query() -> dict:
conn = db.connect()
query_results = conn.execute("SELECT COUNT(CourseID) as numCourses, Professor FROM (SELECT CourseID, Professor FROM CourseProperties NATURAL JOIN CourseStats WHERE Professor LIKE '%%%%') as joined_table GROUP BY Professor ORDER BY numCourses DESC;").fetchall()
conn.close()
final_list = []
for result in query_results:
item = {
"numCourses": result[0],
"Professor": result[1]
}
final_list.append(item)
return final_list
def check_register(self, event_id):
event = Event(event_id).find()
if event is None:
return False
if event[6] == self.id:
return True
conn = db.connect()
cursor = conn.cursor()
cursor.execute("SELECT * FROM Regs WHERE event={} AND user={}".format(
event_id, self.id))
data = cursor.fetchone()
conn.close()
return data is not None
def post_comment(self, event_id, content):
conn = db.connect()
cursor = conn.cursor()
try:
cursor.execute('''
INSERT INTO Comments (event, creator, content) VALUES ({}, {}, '{}')
'''.format(event_id, self.id, escape_string(content)))
except Error:
conn.close()
return False
conn.commit()
conn.close()
return True
def check_session_token(session_token):
cmd_get_id = sqlalchemy.text("SELECT user_id FROM session_tokens WHERE token = ':session_token'")
if Utils.check_keys_format(session_token):
with db.connect() as conn:
rows = conn.execute(cmd_get_id, account_key=session_token)
if rows.rowcount == 0:
return 0
else:
row = rows.fetchone()
user_id = row[0]
return user_id
else:
return 0
def add_friend(self, friendid):
"""Allows the user to add another user to thier friends list
Adds another user to this user's list of friends,
also makes a SQL insert, so changes are maintained on the database
"""
with db.connect() as conn:
# The SQL query goes in the text functon below
# stmt = text()
stmt.bindparams(bindparam("id", type_=str))
result = conn.execute()
pass
def get_api_key(user_id):
try:
command = sqlalchemy.text("SELECT api_key from api_keys WHERE user_id=:user_id")
with db.connect() as conn:
rows = conn.execute(command, user_id=user_id)
if rows.rowcount == 0:
return ""
else:
row = rows.fetchone()
return row[0]
except:
error_client.report_exception()
return ""
def add_tag_by_name(recipe_id, tag_name):
conn = db.connect()
# q1 = "SELECT ingredient_id FROM Ingredients WHERE ingredient_name = '{}';".format(ingredient)
# res = conn.execute(q1)
# ing = 0
# for r in res:
# ing = r[0]
conn.execute(RecipeHasTags.__table__.insert().values(recipe_id=recipe_id,
tag_name=tag_name))
# query = 'Insert Into RecipeHasTags (recipe_id, tag_name) VALUES ({}, "{}");'.format(
# recipe_id, tag_name) #type 0 means recipe
# conn.execute(query)
conn.close()
def create_task(data):
conn = db.connect()
position = conn.execute(f'SELECT MAX(position) FROM Application_Task WHERE application_id = {data["application_id"]}').fetchone()[0]
if position is not None:
position += 1
else:
position = 1
conn.execute(f"""
INSERT INTO Application_Task
(position, application_id, name, due_date, completed)
VALUES ({position}, {data["application_id"]}, "{data["name"]}", "{data["due_date"]}", {data["completed"]})
""")
conn.close()
def search_AdminUser_by_netid(NetID: str) -> dict:
conn = db.connect()
query_results = conn.execute('Select * from AdminUser where NetID LIKE "%%{}%%" order by NetID;'.format(NetID)).fetchall()
conn.close()
final_list = []
for result in query_results:
item = {
"NetID": result[0],
"Password": result[1]
}
final_list.append(item)
return final_list
def insert_new_water(manufacturer_id: int, name: str) -> int:
"""
Insert new water into water table. water_id is auto-generated
"""
conn = db.connect()
query = 'Insert Into Water(manufacturer_id, name) VALUES ({}, "{}");'.format(
manufacturer_id, name)
conn.execute(query)
query_results = conn.execute("Select LAST_INSERT_ID();")
query_results = [x for x in query_results]
water_id = query_results[0][0]
conn.close()
return water_id
def fetch_waters() -> dict:
conn = db.connect()
query_results = conn.execute("Select * from Water;").fetchall()
conn.close()
water_list = []
for result in query_results:
item = {
"manufacturer_id": result[0],
"water_id": result[1],
"water_name": result[2]
}
water_list.append(item)
return water_list
def delete_ingredient(recipe_id, ingredient):
conn = db.connect()
q1 = "SELECT ingredient_id FROM Ingredients WHERE ingredient_name = '{}';".format(
ingredient)
res = conn.execute(q1)
ing = 0
for r in res:
ing = r[0]
query = "DELETE FROM RecipeHasIngredients where recipe_id = {} and ingredient_id = {};".format(
recipe_id, ing) #type 0 means recipe
conn.execute(query)
conn.close()
return fetch_lists()
def get_users_like(username):
conn = db.connect()
query = 'SELECT * FROM User WHERE username LIKE \'{}%%\''.format(username)
query_results = conn.execute(query).fetchall()
conn.close()
review_list = []
for result in query_results:
item = {
"username": result[1],
}
review_list.append(item)
return review_list
def get_winners() -> dict:
conn = db.connect()
query = "SELECT teams.TeamName, COUNT(brackets.BracketID) as num_brackets FROM brackets JOIN predictions ON brackets.BracketID=predictions.BracketID JOIN teams ON predictions.WinnerID=teams.TeamID GROUP BY teams.TeamName ORDER BY num_brackets desc LIMIT 15;"
query_results = conn.execute(query).fetchall()
conn.close()
todo_list = []
for result in query_results:
item = {"id": result[0], "task": result[1]}
todo_list.append(item)
return todo_list
def update_user(uid: int, uname: str):
conn = db.connect()
try:
update = 'UPDATE Users SET name="{}" WHERE user_id = {}'.format(
uname, uid)
conn.execute(update)
print("success update")
except:
print("did not update user")
conn.close()
return 0
def search_ProfessorImages_by_Professor(Professor: str) -> dict:
conn = db.connect()
query_results = conn.execute('Select * from ProfessorImages where Professor LIKE "%%{}%%" order by Professor;'.format(Professor)).fetchall()
conn.close()
final_list = []
for result in query_results:
item = {
"Professor": result[0],
"URL": result[1]
}
final_list.append(item)
return final_list
def fetch_list_recipes(id):
conn = db.connect()
query = "SELECT recipe_id, name FROM PersonalizedListContainsRecipes NATURAL JOIN Recipe WHERE list_id = {};".format(
id)
query_results = conn.execute(query).fetchall()
conn.close()
list_name = []
for result in query_results:
item = {"id": result[0], "name": result[1]}
list_name.append(item)
return list_name
def advance_pratik_query() -> dict:
conn = db.connect()
query_results = conn.execute("SELECT a.Professor, SUM(Rating), COUNT(NetID) FROM (SELECT Professor, Rating FROM Feedback) a JOIN SearchHistory b ON a.Professor = b.Professor GROUP BY a.Professor ORDER BY a.Professor;").fetchall()
conn.close()
final_list = []
for result in query_results:
item = {
"Professor": result[0],
"Ratings": result[1],
"CountofNetID": result[2]
}
final_list.append(item)
return final_list
def reg_bootstrap():
# logging.error(request.method)
form = RegForm(request.form)
if request.method == "POST" :
if form.validate():
form.is_valid = True
session['username'] = form.username.data
session['email'] = form.email.data
session['password'] = form.password.data
db.connect()
db.c.execute("INSERT INTO users VALUES (?,?,?,?)",
(form.username.data,hashlib.md5(KEY+form.password.data).hexdigest(),form.email.data,'salt'))
db.conn.commit()
print "-"*100
for user in db.c.execute('SELECT * FROM users').fetchall():
print user
return redirect(url_for('reg_success'))
else:
form.is_valid = False
flash(u'请根据提示修改表单')
return render_template('reg_bootstrap.html',form = form)
def get_user_of_key(api_key):
try:
command = sqlalchemy.text("SELECT user_id from api_keys WHERE api_key=:api_key")
with db.connect() as conn:
rows = conn.execute(command, api_key=api_key)
if rows.rowcount == 0:
return 0
else:
row = rows.fetchone()
return int(row[0])
except:
error_client.report_exception()
return 0
def create_user(username, password):
conn = db.connect()
query = 'SELECT user_id FROM User WHERE username="******"'.format(username)
query_results = conn.execute(query).fetchall()
if (len(query_results) != 0):
conn.close()
return -1
query = 'INSERT INTO User(username, password) VALUES ("{}", "{}")'.format(
username, password)
conn.execute(query)
conn.close()
return 1
def insert_new_death(city_id: int, date: str, num_deaths: int):
"""Insert new death to Deaths table.
Args:
city_id (int): city id
date (str): month of deaths
num_deaths (int): number of deaths for that month
"""
conn = db.connect()
query = 'Insert Into Deaths (city_id, date, num_deaths) VALUES ({}, "{}", {});'.format(
city_id, date, num_deaths)
conn.execute(query)
conn.close()
from app import app, db from app.models import * db.connect() for table in [ Track, User, Slot, SlotRequest, PlaylistTrack, Wish, RadioStation ]: db.create_table( table, safe = True ) db.close()
def tearDown(self):
namedb = self.app.config['MONGODB_DB']
client = db.connect(namedb)
client.drop_database(namedb)
self.app_context.pop()
from app import db
import datetime
db.connect('microblog')
class User(db.Document):
nickname = db.StringField( max_length=64 )
email = db.StringField( max_length=120, unique=True, required=True )
created = db.DateTimeField( default=datetime.datetime.utcnow ) # utc to keep it universal
meta = {
'indexes' : [
('email', '-created')
]
}
def __repr__(self):
return '<User %r>' % ( self.nickname )
class Post(db.Document):
body = db.StringField( max_length=140 )
timestamp = db.DateTimeField( default=datetime.datetime.utcnow )
author = db.ReferenceField( User )
meta = { 'allow_inheritance' : True }
def __repr__(self):
return '<Post %r>' % (self.body)
def initialize():
db.connect()
db.create_tables([Match, MatchPlayer, Player], safe=True)
db.close()