Exemple #1
0
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)
Exemple #2
0
def createtables_db():
    # Connect to our database.
    db.connect()
    
    # Create the tables.
    db.create_tables([Contribution, File])

    print "Created tables"
Exemple #3
0
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)))
Exemple #4
0
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)
Exemple #5
0
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()
Exemple #8
0
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
Exemple #9
0
 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
Exemple #10
0
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
Exemple #13
0
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()
Exemple #14
0
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()
Exemple #15
0
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
Exemple #16
0
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
Exemple #18
0
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
Exemple #19
0
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
Exemple #21
0
 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
Exemple #22
0
 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
Exemple #24
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 ""
Exemple #26
0
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()
Exemple #27
0
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
Exemple #29
0
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
Exemple #30
0
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
Exemple #31
0
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()
Exemple #32
0
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
Exemple #33
0
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
Exemple #36
0
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
Exemple #38
0
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
Exemple #40
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()
Exemple #43
0
 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)
	
Exemple #45
0
def initialize():
    db.connect()
    db.create_tables([Match, MatchPlayer, Player], safe=True)
    db.close()