Python utils.db_connect函数代码示例

def register():
	form = RegistrationForm(request.form)
	if request.method == 'POST' and form.validate():
		db = db_connect()
		cur = db.cursor()
		salt = get_salt()
		password_hash = phash(form.password.data + salt)
		# TODO: Clean up this handling.
		# Handles case where email is not present, inserts NULL below.
		# Notice lack of single quotes in query which facilitates this.
		if form.email.data != "":
			email = "'{}'".format(form.email.data)
		else:
			email = "NULL"

		query = "INSERT INTO users (username, l_username, first_name, " \
				"last_name, password_hash, salt, email) VALUES " \
				"('{username}', LOWER('{username}'), '{first_name}', " \
				"'{last_name}', '{password_hash}', '{salt}', {email})".format(
						username=form.username.data,
						first_name=form.first_name.data,
						last_name=form.last_name.data,
						password_hash=password_hash,
						salt=salt,
						email=email
					)
		cur.execute(query)
		db.commit()

		session['logged'] = form.username.data
		return redirect(url_for('homepage'))
	
	return render_template("register.html", form=form)

def hours():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  fname=request.args.get('firstname')
  lname=request.args.get('lastname')
  classes=request.args.get('subject')
  username=session['username']
  query = "SELECT numId FROM users WHERE email='%s'" % (username)
  cur.execute(query)
  student = cur.fetchone()
  sId = student['numId']
  username = fname + " " + lname
  b=[]
  IDquery = "SELECT numId FROM users WHERE firstname = '%s' AND lastname = '%s'" % (fname, lname)
  cur.execute(IDquery)
  user = cur.fetchone()
  numId = user['numId']
  appQuery = "SELECT dayofweek, hourof FROM times WHERE studentId = '%s' AND available = '0'" % (numId)
  cur.execute(appQuery)
  apps = cur.fetchall()
  for thing in apps:
    time = thing['hourof']
    day = thing['dayofweek']
    app = time + day
    b.append(app)
  return render_template('hours.html', name=username, a=b, tutorId=numId, studentId=sId, course=classes)

def another_page():
  
    print('anotherpage')
    scoop = {'postername': MySQLdb.escape_string(request.form['postername']),
               'activity': MySQLdb.escape_string(request.form['activity']),
               'rank': request.form['rank']
             
               }
    
    if request.method == 'POST':
        db = utils.db_connect()
        cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
        
        query = "INSERT INTO club_name (postername) VALUES ('" + MySQLdb.escape_string(request.form['postername']) + "')"
        # Print query to console (useful for debugging)
        print query
        cur.execute(query)
        id=cur.lastrowid
        #db.commit()
        
        query2 = "INSERT INTO activity (club_id, activity, rank) VALUES (" + str(id) + ", '" + MySQLdb.escape_string(request.form['activity']) + "', '" + request.form['rank'] + "')"
        # Print query to console (useful for debugging)
        print query2
        cur.execute(query2)
        db.commit()
        
        
    cur.execute('SELECT DISTINCT cn.postername, a.activity, a.rank FROM club_name cn NATURAL JOIN activity a')
    rows = cur.fetchall()

    return render_template('another_page.html', club_name=rows, activity = rows, scoop = scoop)

def register():
  #If they registered for an account
  if request.method == 'POST':
    
    #set up database connections
    db = utils.db_connect()
    cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    
    #get form results.
    username = MySQLdb.escape_string(request.form['username'])
    password = MySQLdb.escape_string(request.form['pw'])
    zipcode = MySQLdb.escape_string(request.form['zipcode'])
    
    #testing in terminal
    print "Hi " + username + " " + password + " " + zipcode
    
    #Insert into 'users' table
    #query = "INSERT INTO users (username, password, zipcode) VALUES ('";
    #query += request.form['username'] + "','" + request.form['pw'] + "','" + request.form['zipcode'] + "')"
    #Hash it
    ###ADD ZIPCODE TO USERS TABLE  
    query = "INSERT INTO users (username, password, zipcode) VALUES ('%s', SHA2('%s', 0), '%d')" % (username, password, int(zipcode))
    print query          #testing in terminal
      
    cur.execute(query)
    db.commit()
    
    return render_template('login.html', selectedMenu='Login')
  
  return render_template('register.html', selectedMenu='Register', name = currentUser)

def export():
	# Request for export data.
	if request.method == 'POST':
		db = db_connect()
		cur = db.cursor(MySQLdb.cursors.DictCursor);
		query = "SELECT DATE(h.entry_start) AS start_date, TIME(h.entry_start) AS start_time, DATE(h.entry_end) AS end_date, TIME(h.entry_end) AS end_time, h.severity FROM headache_entries h JOIN users u ON h.user_id = u.id WHERE u.l_username = LOWER('{}')".format(session['logged'])
		cur.execute(query)
		# Get all entries at once.
		entries = cur.fetchall();

		# Save results parsed as csv to file in-memory.
		string_buffer = StringIO.StringIO()
		w = csv.DictWriter(string_buffer, entries[0].keys())
		w.writeheader()
		w.writerows(entries)
		csv_content = string_buffer.getvalue()
		string_buffer.close()

		# Response with export data.
		return Response(csv_content,
				mimetype="text/csv",
				headers={
					"Content-Disposition": "attachment;filename=export.csv"
					})

	return render_template("export.html")

def register():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  errorMail = ""
  errorFirst = ""
  errorLast = ""
  errorPass = ""
  error = ""
  if request.method == 'POST':
    firstname=request.form['firstname']
    lastname=request.form['lastname']
    email=request.form['email']
    password=request.form['password']
    if "mail.umw.edu" in email and firstname and lastname and password:
      query = "INSERT INTO users (firstname,lastname,email,password,accountStatus) VALUES('%s','%s','%s','%s',3);" % (firstname,lastname,email,password)
      cur.execute(query)
      db.commit()
      return redirect(url_for('login'))
    else:
      error = "true"
      if "mail.umw.edu" or "umw.edu" not in email or not email:
        errorMail = "true"
      if not firstname:
        errorFirst = "true"
      if not lastname:
        errorLast = "true"
      if not password:
        errorPass = "true"
  return render_template('register.html', errorMail=errorMail, errorFirst=errorFirst, errorLast=errorLast, errorPass=errorPass, error=error)

def estateadd2():
    db = utils.db_connect()
    cur = db.cursor()    

    if request.method == 'POST':       #if user has submitted something
      
      if 'address' in request.form:  #if user is adding an estate
        damageType = MySQLdb.escape_string(request.form['damageType'])
        address = request.form['address']
        query = "INSERT INTO basicHouse (address,county,state,price) VALUES ('" + address +"', '"+MySQLdb.escape_string(request.form['county'])+"', '"+MySQLdb.escape_string(request.form['state'])+"', "+MySQLdb.escape_string(request.form['price'])+")"
        print(query)
        cur.execute(query)
        db.commit()
        query = "INSERT INTO house_damages (type,house_id,cost) VALUES ('"
        query+=damageType+"', (SELECT house_id FROM basicHouse WHERE address= '"+ address+"' GROUP BY address) , '"+ MySQLdb.escape_string(request.form['damageCost']) + "');" 
        print(query)
        cur.execute(query)
        #rows = cur.fetchall()
        db.commit()
        
      if 'damAddress' in request.form: #if adding damages to existing estate
        address = MySQLdb.escape_string(request.form['damAddress'])
        damageType = MySQLdb.escape_string(request.form['damDamageType'])
        damageCost = MySQLdb.escape_string(request.form['damDamageCost'])
        query = "INSERT INTO house_damages (house_id,type,cost) VALUES ((SELECT house_id FROM basicHouse WHERE address = '" + address + "'),'"+ damageType+"',"+damageCost + ");" 
        print(query)
        cur.execute(query)
        db.commit()
        
    return render_template('index.html', name = currentUser)

def createTutor():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  created = " "
  if request.method == 'POST':
      row = []
      first = request.form['firstName']
      last = request.form['lastName']
      email = request.form['email']
      course = request.form['course']
      password = request.form['password']
      query2 = "SELECT * FROM users WHERE email = '%s';" % (email)
      cur.execute(query2)
      test = cur.fetchone()
      if test:
        if test['accountStatus'] == 1:
          created = "admin"
        elif test['accountStatus'] == 2:
          created = "no"
        elif test['accountStatus'] == 3:
          created = "updated"
          #if the query here does not activate, take out classes + and leave it '%s'
          query3 = "UPDATE users SET accountStatus = 2, classes = classes + '%s' WHERE email = '%s';" % (course, email)
          cur.execute(query3)
      else:
        created = "yes"
        query = "INSERT INTO users (firstname,lastname,email,password,accountStatus,classes) VALUES('%s','%s','%s','%s',2, '%s');" % (first,last,email,password, course)
        cur.execute(query)
        db.commit()
  return render_template('createTutor.html', created=created)

def randome():     
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  trivia =  request.form.get("triviatype")
  ranNum = random.randint(1,5)
  
  if ranNum == 1:
    tCol = 'laws'
    cur.execute('select content,state from '+tCol+' order by rand() limit 1')
  elif ranNum == 2:
    tCol = 'trivia'
    cur.execute('select content from '+tCol+' order by rand() limit 1')
  elif ranNum == 3:
    tCol = 'sayings'
    cur.execute('select content, author from '+tCol+' order by rand() limit 1')
  elif ranNum == 4:  
    tCol = 'fortuneCookies'
    cur.execute('select content from '+tCol+' order by rand() limit 1')
    print 'select content from '+tCol+' order by rand() limit 1'
  elif ranNum == 5:  
    tCol = 'meme'
    cur.execute('select imageLink,content from '+tCol+' order by rand() limit 1')
  rows = cur.fetchall()
  print ranNum, rows
  
  return render_template('randdisplay.html', trivia = trivia, rows = rows)

def gChoose2():     
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  genre =  request.form.get("genreT")
  ranNum = random.randint(1,5)

  if ranNum == 1:
    trivia = 'Laws'
    cur.execute('select content,state from laws where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 2:
    trivia = 'Trivia'
    cur.execute('select content from trivia where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 3:
    trivia = 'Sayings'
    cur.execute('select content, author from sayings where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 4:  
    trivia = 'Fortune Cookies'
    cur.execute('select content from fortuneCookies where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  elif ranNum == 5:  
    trivia = 'Meme'
    cur.execute('select imageLink,content from meme where genreId = (select numId from genre where genreName= \''+genre+'\') order by rand() limit 5')
  
  rows = cur.fetchall()

  return render_template('genreDisplay.html', genre = genre, rows = rows, trivia = trivia)

def trivia2():     
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  trivia =  request.form.get("triviatype")

  if trivia == 'Laws':
    tCol = 'laws'
    cur.execute('select content,state from '+tCol+' order by rand() limit 3')
    #cur.execute('select content, state from ' + tCol + ';')
  elif trivia == 'Trivia':
    tCol = 'trivia'
    cur.execute('select content from '+tCol+' order by rand() limit 3')
    #cur.execute('select content from '+ tCol + ';' )
  elif trivia == 'Sayings':
    tCol = 'sayings'
    cur.execute('select content, author from '+tCol+' order by rand() limit 3')
    #cur.execute('select content, author from ' + tCol + ';')
  elif trivia == 'Fortune Cookies':
    tCol = 'fortuneCookies'
    cur.execute('select content from '+tCol+' order by rand() limit 3')
    #cur.execute('select content from ' + tCol + ';')
  elif trivia == 'Meme':
    tCol = 'meme'
    cur.execute('select content, imageLink from '+tCol+' order by rand() limit 3')
    #cur.execute('select content from ' + tCol + ';')
    
  rows = cur.fetchall()
  print rows
  return render_template('triviadisplay.html', trivia=trivia, rows=rows)

def report():
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  query = 'select * from games'
  cur.execute(query)
  rows = cur.fetchall()
  return render_template('report.html', games=rows, selectedMenu='report')

def report2():
  
  firstname = request.form['firstname']
  lastname = request.form['lastname']
  username = request.form['username']
  password = request.form['password']
  school = request.form['school']
  city = request.form['city']
  state = request.form['state']
  game = request.form['game']
  
  #query = "SELECT id from games where title = '" + game + "'"
  #"(SELECT id from users where users.username ='" + username + "' AND users.password ='" + password + "')"
    
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)

  query = "INSERT INTO users (firstname, lastname, username, password, game) VALUES ('";
  query += request.form['firstname'] + "', '" + request.form['lastname'] + "', '" + username + "', '" + password + "', (SELECT id from games where games.title = '" + game + "'))"
  print query
  cur.execute(query)
  db.commit()
  
  query = "INSERT INTO userInfo (userid, school, city, state) VALUES ((SELECT id from users where users.username ='" + username + "' AND users.password ='" + password + "'),'" + school + "' , '" + city + "', '" + state + "')"   
  print query
  cur.execute(query)
  db.commit()
    
  return redirect(url_for('list'))

def register():
  global currentUser
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  # if user typed in a post ...
  if request.method == 'POST':
    un = MySQLdb.escape_string(request.form['username'])
    pw = MySQLdb.escape_string(request.form['pw'])
    stop = 0
    query = "SELECT COUNT(*) FROM users"
    cur.execute(query)
    countBefore = cur.fetchall()
    query = "INSERT INTO users (username) SELECT name FROM (SELECT '%s' AS name) t WHERE NOT EXISTS (SELECT * FROM users WHERE username = '%s')" % (un, un)
    cur.execute(query)
    db.commit( )
    query = "SELECT COUNT(*) FROM users"
    cur.execute(query)
    countAfter = cur.fetchall()
    if countAfter == countBefore:
      stop = 1
    if stop != 1:
      query2 = "SELECT id FROM users WHERE username = '%s'" % (un)
      cur.execute(query2)
      tida = cur.fetchall( )
      tid = tida[0]['id']
      query3 = "INSERT INTO user_passwords (password, user_id) VALUES (SHA2('%s',0), %d)" % (pw, tid)
      cur.execute(query3)
      db.commit( )
      currentUser = un
      return redirect(url_for('mainIndex'))
    else:
      warn = "That username already exists!"
      return render_template('warning.html', warn = warn)
  return render_template('register.html', curus = currentUser)

def report2():
  query = 'INSERT'
  query2 = 'INSERT'
  query3 = 'INSERT'
  query4 = 'INSERT'
  print query
  print query2
  print query3
  print query4
  db = utils.db_connect()
  cur = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
  query = "INSERT INTO Star_Wars (Name) VALUES ('"
  query += request.form['name'] + "')"
  query2 = "INSERT INTO Appearance (Species, Gender) VALUES ('"
  query2 += request.form['species'] + "', '" + request.form['gender'] + "')"
  query3 = "INSERT INTO Ability_Scores (Str, Dex, Con, Intl, Wis, Cha) VALUES ("
  query3 += request.form['str'] + ", " + request.form['dex'] + ", " + request.form['con'] + ", " + request.form['intl'] + ", " + request.form['wis'] + ", " + request.form['cha'] + ")"
  query4 = "INSERT INTO Class_Levels (Soldier, Jedi, Scoundrel, Scout, Noble) VALUES ("
  query4 += request.form['soldier'] + ", " + request.form['jedi'] + ", " + request.form['scoundrel'] + ", " + request.form['scout'] + ", " + request.form['noble'] + ")"
  
  print query
  print query2
  print query3
  print query4
  cur.execute(query);
  cur.execute(query2);
  cur.execute(query3);
  cur.execute(query4);
  db.commit()
  return redirect(url_for('starWars'))