def process_transfers(): """Advances all CREATED transfers to pending if they look ok. Will not complete the transfer; use /confirm-transfer/<id> for that.""" api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth db = server.get_db() c = db.cursor() transfers = c.execute("SELECT * FROM transfers WHERE status=?", ['CREATED']).fetchall() #x = [] #for row in transfers: # x.append(row2dict(row)) #return {"data": x} ok = [] error = [] # goatnote: # Logic error here allows balances to become overdrawn for xfer in transfers: balance = c.execute("SELECT balance from customers WHERE id=?", [xfer["custID_from"]]).fetchone() if balance["balance"] > xfer["amount"]: c.execute("UPDATE transfers SET status=? WHERE id=?", ['PENDING', xfer['id']]) ok.append(xfer['id']) else: error.append(xfer['id']) response = {"success": True, "pending": ok, "failed": error} db.commit() return response
def getUserFromEmail(email): from server import app, get_db with app.app_context(): cursor = get_db() sql = "SELECT * FROM user WHERE email='{}';" cursor.execute(sql.format(email)) user = cursor.fetchall() if len(user) == 0: return None # On va chercher les informations d'un user, soit ses préférences et cetera sql = "SELECT * FROM preferencesCat WHERE username = '******'" cursor.execute(sql.format(user[0]['username'])) prefCat = cursor.fetchall()[0] prefCat.pop('username', None) sql = "SELECT * FROM preferencesDog WHERE username = '******'" cursor.execute(sql.format(user[0]['username'])) prefDog = cursor.fetchall()[0] prefDog.pop('username', None) sql = "SELECT * FROM preferencesBird WHERE username = '******'" cursor.execute(sql.format(user[0]['username'])) prefBird = cursor.fetchall()[0] prefBird.pop('username', None) if len(user) != 1: return None return { **user[0], 'preferencesCat': prefCat, 'preferencesDog': prefDog, 'preferencesBird': prefBird }
def confirm_transfer(xfer_id): db = server.get_db() c = db.cursor() try: xfer_id = int(xfer_id) except ValueError: response = {"success": False, "error": "Transfer ID must be an integer"} return response xfer = c.execute("SELECT * FROM transfers WHERE id=? LIMIT 1", [xfer_id]).fetchone() if xfer is None: response = {"success": False, "error": "Transfer ID invalid"} return response c.execute("UPDATE transfers SET status=? WHERE id=? AND status=?", ['COMPLETE', xfer['id'], 'PENDING']) if c.rowcount == 0: response = {"success": False, "error": "Transfer ID {} was not in the 'PENDING' state".format(xfer_id)} return response else: customer_to = c.execute("SELECT * from customers WHERE id=?", [xfer['custID_to']]).fetchone() customer_from = c.execute("SELECT * from customers WHERE id=?", [xfer['custID_from']]).fetchone() new_balance_from = customer_from['balance'] - xfer['amount'] c.execute('UPDATE customers SET balance=? WHERE id=?', [new_balance_from, xfer['custID_from']]) new_balance_to = customer_to['balance'] + xfer['amount'] c.execute('UPDATE customers SET balance=? WHERE id=?', [new_balance_to, xfer['custID_to']]) response = {"success": True} db.commit() return response
def setupDatabase(): with app.app_context(): # ouverture connexion cursor = get_db() # supprimer les tables deleteAllTables(cursor) createTables(cursor) createTableIndex(cursor) # insérer des users insertUsers(cursor, 100) # insérer des animaux insertAnimal(cursor, 1000) # insérer des photos d'oiseaux insertBirbPics(cursor) # insérer des photos de doggo insertDoggoPics(cursor) # insérer des photos de chats insertKittehPics(cursor) # insérer des transactions insertRandomTransactions(cursor) # insérer des owners insertRandomOwners(cursor) # insérer des wishlits insertRandomDesired(cursor)
def get_company(comp_id): c = server.get_db().cursor() company = c.execute("SELECT * FROM companies WHERE id=?", [comp_id]).fetchone() if company is not None: result = {"success": True, "data": row2dict(company)} return result else: result = {"success": False, "error": "Company ID {} not found".format(comp_id)} return result
def assign_new_token(): """Builds a new API token and assigns it to the current user's company. If a bank wanted each client to use a unique token, perhaps so that they can identify which API client from logged data, they might use this method. If you think this method looks too sloppy to be real code, you don't do enough source code assessments.""" from hashlib import sha256 api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth db = server.get_db() c = db.cursor() # TODO: SHA256 is secure, but is there a better way to do this? row = c.execute('SELECT MAX(id) FROM tokens').fetchone() old_token_id = row[0] # Hashing methods only take bytes objects. # All this says is "give me '5' as a byte array" num = bytes(str(old_token_id), 'utf-8') h = sha256() h.update(num) out = h.digest() # We don't want bytes, we want 20-character strings using 0-9 and a-z # This causes some truncation and loss of entropy # TODO: longer tokens to maintain entropy? out = out[:20] out_modulo = [int(x) % 36 for x in out] # 36 = 26 letters + 10 digits for i in range(len(out_modulo)): if out_modulo[i] < 10: out_modulo[i] = str(out_modulo[i]) else: out_modulo[i] = chr(ord('a') + out_modulo[i] - 10) new_token = ''.join(out_modulo) c.execute('INSERT INTO tokens VALUES (null, ?, ?)', [new_token, auth["user"]["company"]]) db.commit() result = { "success": True, "id": c.lastrowid, "token": new_token } return result
def checkIfEmailAlreadyUsed(email): from server import get_db cursor = get_db() sql = "SELECT email FROM user" cursor.execute(sql) results = cursor.fetchall() # Si le email est trouvé, ce sera False, sinon on peut l'utiliser for i in results: if i['email'] == email: return False return True
def checkIfUsernameAlreadyUsed(username): from server import get_db cursor = get_db() sql = "SELECT username FROM user" cursor.execute(sql) results = cursor.fetchall() # Si le username est trouvé, ce sera False, sinon on peut l'utiliser for i in results: if i['username'] == username: return False return True
def validatePassword(email, password): from server import get_db cursor = get_db() sql = "SELECT pass FROM user WHERE email='{}'" cursor.execute(sql.format(email)) result = cursor.fetchall() # On compare le password s'il est hashé if result is None: return None if check_password(result[0]['pass'], password): return True else: return False
def check_token(api_token): # TODO: try some fancy JOIN for user + company c = server.get_db().cursor() c.execute("SELECT * FROM tokens WHERE api_token=? LIMIT 1", [api_token]) user = c.fetchone() if user is not None: response = {"success": True} response["user"] = row2dict(user) c.execute("SELECT * FROM companies WHERE id=? LIMIT 1", [user["company"]]) company = c.fetchone() response["company"] = {"name": company["name"], "id": company["id"]} else: response = {"success": False, "error": "Invalid API token."} return response
def updatePreferences(user, preferences): from server import get_db cursor = get_db() user.preferencesCat = {} user.preferencesDog = {} user.preferencesBird = {} preferences.pop('Save', None) # reset all prefs in bd cat for key in defaultPrefCat: sql = "UPDATE preferencesCat SET {} = 0 WHERE username='******';" cursor.execute(sql.format(key, user.username)) # reset all prefs in bd dog for key in defaultPrefDog: sql = "UPDATE preferencesDog SET {} = 0 WHERE username='******';" cursor.execute(sql.format(key, user.username)) # reset all prefs in bd bird for key in defaultPrefBird: sql = "UPDATE preferencesBird SET {} = 0 WHERE username='******';" cursor.execute(sql.format(key, user.username)) # set prefs for key, value in preferences.items(): matchObjDog = match(r'.{0,}Doggo', key, I) matchObjCat = match(r'.{0,}Cat', key, I) matchObjBird = match(r'.{0,}Birb', key, I) if matchObjBird is not None or key == 'bird': sql = "UPDATE preferencesBird SET {} = 1 WHERE username='******';" cursor.execute(sql.format(key, user.username)) user.preferencesBird[key] = 1 elif matchObjCat is not None: sql = "UPDATE preferencesCat SET {} = 1 WHERE username='******';" cursor.execute(sql.format(key, user.username)) user.preferencesCat[key] = 1 elif matchObjDog is not None or key == 'dog': sql = "UPDATE preferencesDog SET {} = 1 WHERE username='******';" cursor.execute(sql.format(key, user.username)) user.preferencesDog[key] = 1 else: print("key error")
def get_customers(): api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth c = server.get_db().cursor() c.execute("SELECT * FROM customers WHERE company=?", [auth["company"]["id"]]) rows = c.fetchall() response = { "success": True, "customers": [] } for row in rows: x = row2dict(row) response["customers"].append(x) return response
def delete_token(token): api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth db = server.get_db() c = db.cursor() # Crazy logic to say "Delete only if there's at least 1 more token for this company." c.execute('DELETE FROM tokens WHERE api_token=?', [token]) db.commit() if c.rowcount > 0: result = {"success": True} else: result = {"success": False, "error": "No such token"} return result
def create_transfer(): api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth content = server.request.json if content is None: response = {"success": False, "error": "Expected JSON content"} return response try: cust_from = int(content["from"]) cust_to = int(content["to"]) ammount = content["ammount"] except KeyError: response = {"success": False, "error": "Missing 'from', 'to', or 'ammount'"} return response except ValueError: response = {"success": False, "error": "Values of 'from' and 'to' should be customer IDs."} return response # Decimals maintain more accuracy than floats (or should) so we don't want # this number as a float. However, all decimals should be valid floats. # TODO: Python has a decimal type, should probably use that? try: float(ammount) except ValueError: response = {"success": False, "error": "Value of 'ammount' should be a decimal number"} return response # goatnote: # Not doing: validation of cust_from or cust_to db = server.get_db() c = db.cursor() try: c.execute('INSERT INTO transfers VALUES (null, ?, ?, ?, ?)', [cust_from, cust_to, ammount, 'CREATED']) db.commit() except: response = {"success": False, "error": "Unknown SQL error"} raise return response response = {"success": True, "id": c.lastrowid} return response
def manager_main(): global app, db, User app = get_app() app.config['host'] = '0.0.0.0' manager = Manager(app) from flask.ext.migrate import Migrate, MigrateCommand bootstrap = Bootstrap(app) db = get_db() import routes import api def make_shell_context(): return dict(app=app, db=db, User=User) manager.add_command("shell", Shell(make_context=make_shell_context)) migrate = Migrate(app, db) manager.add_command('db', MigrateCommand) manager.run()
def get_customer1(id): api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth c = server.get_db().cursor() c.execute("SELECT * FROM customers WHERE id=?", [id]) rows = c.fetchall() response = { "success": True, "customers": [] } for row in rows: x = {} for col in row.keys(): x[col] = row[col] response["customers"].append(x) return response
def createUser(user): from server import app, get_db with app.app_context(): cursor = get_db() sql = "INSERT INTO user(username, pass, nom, prenom, email, telephone, solde, profileImage) VALUES ('{}', '{}', '{}', '{}', '{}', {}, {}, '{}')" hashedPass = hash_password(user.password) cursor.execute( sql.format(user.username, hashedPass, user.nom, user.prenom, user.email, user.telephone, user.solde, user.profileImage)) sql = "INSERT INTO preferencesDog(username, dog, whiteDoggo, blackDoggo, gingerDoggo, brownDoggo, greyDoggo, declawedDoggo, castratedDoggo, femaleGenderDoggo, maleGenderDoggo, 0_20WeightDoggo, 20_40WeightDoggo, 40WeightPlusDoggo, 0_5AgeDoggo, 5_10AgeDoggo, 10AgePlusDoggo) VALUES ('{}', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);" cursor.execute(sql.format(user.username)) sql = "INSERT INTO preferencesBird(username, bird, blackBirb, whiteBirb, blueBirb, beigeBirb, greyBirb, greenBirb, yellowBirb, 0_5AgeBirb, 5_10AgeBirb, 10AgePlusBirb, 0_1WeightBirb, 1_2WeightBirb, 2PlusWeightBirb, femaleBirb, maleBirb) VALUES ('{}', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);" cursor.execute(sql.format(user.username)) sql = "INSERT INTO preferencesCat(username, cat,declawedCat, whiteCat, blackCat, gingerCat, greyCat, brownCat, castratedCat,femaleGenderCat, maleGenderCat, 0_10WeightCat, 10_20WeightCat, 20PlusWeightCat, 0_5AgeCat, 5_10AgeCat, 10PlusAgeCat) VALUES ('{}', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1);" cursor.execute(sql.format(user.username))
def register(): form = RegistrationForm(csrf_enabled=False) if form.validate_on_submit(): username = form.username.data emails = form.email.data password = form.password.data user = User.query.filter_by(username=username).first() if user is None: db = get_db() user_tmp = User(username=username, email=emails) user_tmp.password=password db.session.add(user_tmp) db.session.commit() login_user(user_tmp) session['known'] = False else: session['known'] = True session['name'] = username session['email'] = emails return redirect('/home') else: return render_template("register.html", form=form)
def get_transfers(status=None): api_token = server.request.headers.get('X-API-Token') auth = check_token(api_token) if not auth["success"]: return auth c = server.get_db().cursor() if status is None: c.execute("SELECT * FROM transfers") else: c.execute("SELECT * FROM transfers WHERE status=?", [status]) rows = c.fetchall() response = { "success": True, "transfers": [], "where": status } for row in rows: x = {} for col in row.keys(): x[col] = row[col] response["transfers"].append(x) return response
import csv import sys import server from server import * from parser import * import sys import json from JSONEncoder import JSONEncoder from queries import * from bson.objectid import ObjectId app = Flask(__name__) file = open('Kick-Ass Coders Internship Application_new.csv', 'rb') reader = csv.reader(file) db = server.get_db() reload(sys) sys.setdefaultencoding('utf-8') @app.route('/') def home(): if not session.get('logged_in'): #update_database() return render_template('login.html') else: #resp = make_response(render_template("index.html")) #resp.set_cookie('username', request.form['username']) #return resp return render_template("index.html",
from server import app, get_db with app.app_context(): db = get_db() with app.open_resource('schema.sql', mode='r') as f: db.cursor().executescript(f.read()) db.commit() print('Initialized the database.')
def client(): server.get_db().purge() return testing.TestClient(server.create())
def setUp(self): get_db().drop_all() get_db().create_all()
def tearDown(self): get_db().session.remove() get_db().drop_all()
def login(self): db = server.get_db() db.execute('INSERT OR IGNORE INTO users (uid) VALUES (?)', [TEST_UID]) db.execute('UPDATE users SET access_token = ? WHERE uid = ?', [TEST_ACCESS_TOKEN, TEST_UID]) db.commit()
scraper = create_scraper() # returns a requests.Session object r = scraper.get("https://en.nostalrius.org/") soup = BeautifulSoup(r.content, 'html.parser') blocks = soup.find_all("div", class_="block") pvpblock = blocks[3].find_all("span") pveblock = blocks[4].find_all("span") if pvpblock[0].text == "Online": pvp = int(pvpblock[4].text) else: pvp = 0 if pveblock[0].text == "Online": pve = int(pveblock[4].text) else: pve = 0 with app.app_context(): c = get_db() row = (pve, pvp) # Insert a row of data c.execute("INSERT INTO series VALUES (strftime('%s', 'now')*1000,?,?)", row) # Save (commit) the changes c.commit() print("Inserted %s" % str(row))