def confirm_wallet_event(gateway_event): wallet_event = db.session.query(db.WalletsEvent).\ filter_by(source_id=gateway_event.id).first() wallet_event.status = 'done' db.commit() return wallet_event
def set_default_flag(self, state): if state != True and state != False: raise ValueError("Requires True or False") if state == True: flag = 1 elif state == False: flag = 0 db = database.connect() cursor = database.get_cursor(db) # Get id of own type cursor.execute("select id from address_types where address_type = :input_type", \ input_type = self.get_type()) type_id = cursor.fetchone()[0] if state == True and self.get_person(): # If address has a person remove other true flags so only one remains of this type cursor.execute("update addresses set default_flag = 0 \ where person_id = :input_id and default_flag = 1 \ and address_type_id = :input_tid" , \ input_id = self.get_person().get_id(), input_tid = type_id) database.commit(db) cursor.execute("update addresses set default_flag = :input_flag \ where id = :input_id" , \ input_flag = flag, input_id = self.get_id()) database.commit(db) database.disconnect(db)
def new_order(customer): db = database.connect() cursor = database.get_cursor(db) returned_id = cursor.var(database.cx_Oracle.NUMBER) cursor.execute("select id from order_statuses where order_status = :input_status", \ input_status = "pending") status_id = cursor.fetchone()[0] if isinstance(customer, person.Person): cursor.execute("insert into orders (person_id, status_id) \ values (:input_pid, :input_sid) \ returning id into :output_id" , \ input_pid = customer.get_id(), input_sid = status_id, \ output_id = returned_id) database.commit(db) else: raise ValueError("Requires valid person instance") returned_id = int(returned_id.getvalue()) this_order = Order(returned_id) database.disconnect(db) ship_addr = customer.get_default_address("shipping") if ship_addr: this_order.modify_shipping_address(ship_addr) return this_order
def dropCourse(self, idnum): sidnum = str(idnum) CRN = input( "Enter the CRN code of the course you would like to remove") sql_command = """SELECT 1 FROM COURSE WHERE CRN = """ + CRN + """;""" database.cursor.execute(sql_command) query_result = database.cursor.fetchall() if query_result == []: print("The CRN code does not exist") return else: sql_command = """SELECT 1 FROM STUDENTCOURSE WHERE STUDENTID = """ + sidnum + """ AND CRN = """ + CRN + """; """ database.cursor.execute(sql_command) query_result = database.cursor.fetchall() if query_result == []: print("The your not taking this course") return else: sql_command = """DELETE FROM STUDENTCOURSE WHERE STUDENTID = """ + sidnum + """ AND CRN = """ + CRN + """; """ database.cursor.execute(sql_command) sql_command = """SELECT TITLE FROM COURSE WHERE CRN = """ + CRN + """; """ database.cursor.execute(sql_command) query_result = database.cursor.fetchall() for i in query_result: print(i) print(" Has been removed from your courses to your courses") database.commit() return
def cmd_download_artist_schedule(*args): """queues retrieving more tracks by the specified artists""" from ardj.database import DownloadRequest, commit for arg in args[1:]: if DownloadRequest.find_by_artist(arg) is None: DownloadRequest(artist=arg, owner="console").put() commit()
def adminadd(self): dpt = input( "What department are you adding the course to? insert '' marks for string inputs\n" ) sql_command = """SELECT INSTRUCTOR.ID,INSTRUCTOR.NAME, INSTRUCTOR.SURNAME FROM INSTRUCTOR WHERE INSTRUCTOR.DEPT = """ + dpt + """ ; """ print(sql_command) database.cursor.execute(sql_command) query_result = database.cursor.fetchall() print("Here are the available instructors from " + dpt + "\n") for i in query_result: print(i) id = input( "Enter the available instructors id too add this instructor to the course\n" ) title = input("Enter the title of the course ''\n") CRN = input("Create the CRN code for this course") time = input("Enter time for course ''") days = input("Enter days of course ''") semester = input("Enter the semester for this course ''") year = input("Enter the year for this course ''") credits = input("Enter the amount of credits for this course") sql_command = """ INSERT INTO COURSE VALUES (""" + title + """,""" + CRN + """,""" + dpt + """,""" + id + """,""" + time + """,""" + days + """,""" + semester + """,""" + year + """,""" + credits + """); """ print(sql_command) database.cursor.execute(sql_command) print(title + " Has been successfully added to the database") database.commit()
def POST(self): try: args = web.input(track_id="", token=None) logging.debug("Vote request: %s" % args) sender = auth.get_id_by_token(args.token) if sender is None: raise web.forbidden("Bad token.") if args.track_id.isdigit(): track_id = int(args.track_id) else: track_id = tracks.get_last_track_id() weight = tracks.add_vote(track_id, sender, self.vote_value) if weight is None: return {"status": "error", "message": "No such track."} database.commit() message = 'OK, current weight of track #%u is %.04f.' % (track_id, weight) return { "status": "ok", "message": message, "id": track_id, "weight": weight, } except web.Forbidden: raise except Exception, e: log.log_error(str(e), e) return {"status": "error", "message": str(e)}
def __create_db(cports: list, changes: list, conn: sqlite3.Connection, cursor: sqlite3.Cursor): ''' This function writes data to sqlite cports, changes: result from parser conn, cursor: database related ''' cursor.execute('DELETE FROM CPORTS') sqlStr = db.genBaseSqlStr(cports, 'CPORTS') for item in cports: values = (int(item['processID']), item['processName'], item['protocol'], item['localPort'], item['localAddress'], item['remotePort'], item['remoteAddress'], item['state'], item['processPath'], item['pathType']) db.insertTable(cursor, sqlStr, values) db.commit(conn) cursor.execute('DELETE FROM CPORTS_CHANGELIST') sqlStr = db.genBaseSqlStr(changes, 'CPORTS_CHANGELIST') for item in changes: values = (item['programName'], item['date'], item['status'], item['protocol'], item['source'], item['destination']) db.insertTable(cursor, sqlStr, values) db.commit(conn) return
def modify_address(self, new_address_reference): # Ensuring address' ID is in addresses table AND it is a billing address db = database.connect() cursor = database.get_cursor(db) cursor.execute("select id from addresses \ where id = :input_id" , \ input_id = new_address_reference.get_id()) address_id = cursor.fetchone() if not address_id: raise ValueError("Address not found in addresses table") else: address_id = address_id[0] address_reference = address.Address(address_id) if not address_reference.get_type() == "billing": raise ValueError("Address must be billing type. Type is %s." % address_reference.get_type()) cursor.execute("update credit_cards set billing_addr_id = :input_addr \ where id = :input_id" ,\ input_addr = address_id, input_id = self.get_id()) database.commit(db) database.disconnect(db)
def modify_expiration_date(self, new_expiration_month, new_expiration_year): # Type- and value-checking inputs if not isinstance(new_expiration_month, int): raise ValueError("Expiration month must be integer value") if not isinstance(new_expiration_year, int): raise ValueError("Expiration year must be integer value") if new_expiration_month > 12 or new_expiration_month < 1: raise ValueError("Expiration month must be between 1 and 12") if not len(str(new_expiration_year)) == 4: raise ValueError("Expiration year must be 4 digit integer") # Formatting the date expiration_date = format_date(new_expiration_month, new_expiration_year) db = database.connect() cursor = database.get_cursor(db) cursor.execute("update credit_cards set expiration_date = :input_date \ where id = :input_id" ,\ input_date = expiration_date, input_id = self.get_id()) database.commit(db) database.disconnect(db)
async def unregister(ctx: discord.ext.commands.Context): if await isRegistered(ctx) == False: return if await inPublicChannel( ctx, msg= f"Hey, <@{ctx.author.id}>, don't leave a team in public channels..." ): return if SETTINGS['registration'] == 'disabled': await ctx.send("registration is disabled") return with db.db_session: user = db.User.get(name=username(ctx)) if user == None: logger.debug(f"user {username(ctx)} not registered to play.") await ctx.send("You weren't registered") return if SETTINGS['_debug'] and SETTINGS['_debug_level'] > 0: logger.debug(f"{user.name} left team {user.team.name}") await ctx.send(f"Leaving team {user.team.name}... bye.") unaffiliated = db.Team.get(name='__unaffiliated__') user.team = unaffiliated db.commit()
def create_token(login, login_type=None): if login_type == "jid": raise RuntimeError("Tokens can only be sent by email.") token = Token.create(login) TokenMailer(login, token["token"]).deliver() commit() return token
def new_warehouse(capacity, street, city, state_string, zip_code, apartment_no=None): # Adding new warehouse to database, and returning reference to that warehouse db = database.connect() cursor = database.get_cursor(db) warehouse_address = address.Address.new_address(street, city, state_string, zip_code,\ "warehouse", apt_no = apartment_no) address_id = warehouse_address.get_id() if isinstance(capacity, (int, float)): returned_id = cursor.var(database.cx_Oracle.NUMBER) capacity = int(capacity) cursor.execute("insert into warehouses \ (capacity, address_id) values (:input_capacity, :input_address) \ returning id into :new_warehouse_id" , \ input_capacity = capacity, input_address = address_id, \ new_warehouse_id = returned_id) database.commit(db) returned_id = int(returned_id.getvalue()) database.disconnect(db) return Warehouse(returned_id)
def modify_quantity(self, product, new_quantity): db = database.connect() cursor = database.get_cursor(db) if isinstance(new_quantity, int) and new_quantity >= 0: product_id = product.get_id() cursor.execute("select quantity from warehouse_to_product \ where product_id = :input_pid and warehouse_id = :input_wid" , \ input_pid = product_id, input_wid = self.get_id()) current_quantity = cursor.fetchone() if current_quantity: # Ensuring product is in warehouse stock current_quantity = int(current_quantity[0]) if current_quantity == new_quantity: # Do nothing if quantity doesn't change pass elif new_quantity == 0: # Remove line in DB is new quantity is zero cursor.execute("delete from warehouse_to_product \ where product_id = :input_pid and warehouse_id = :input_wid" , \ input_pid = product_id, input_wid = self.get_id()) database.commit(db) else: # Otherwise just update the quantity cursor.execute("update warehouse_to_product set quantity = :input_quantity \ where product_id = :input_pid and warehouse_id = :input_wid" , \ input_quantity = new_quantity, input_pid = product_id, \ input_wid = self.get_id()) database.commit(db) else: raise ValueError("new quantity must be positive integer value") database.disconnect(db)
def create_car_brand_table(conn): """ The method creates a database table of car brands. It crawls bilbasen.dk for a list of car brands, and creates and stores these brands in a table called 'Brands' in the database. The method is called each time the method download_data_to_database is called. """ conn.request("GET", "/") res = conn.getresponse() content = res.read() parsed_html = BeautifulSoup(content, from_encoding='utf8') cur, con = database.connect_to_database() # delete the old table if database.check_if_table_exist(cur, 'Brands'): database.delete_table(cur, 'Brands') cur.execute("CREATE TABLE Brands(Brand CHAR(100))") # extract car brands and insert to database table brands_tags = parsed_html.find("optgroup", {"label": "Alle mærker"}) for child in brands_tags.children: brand = smart_str(child.string) if any(c.isalpha() for c in brand): command = "INSERT INTO Brands(Brand) VALUES('%s')" \ % smart_str(brand) cur.execute(command) database.commit(cur, con)
def weekend(weekend_id=None): uri = request.url_root + "api/v1/" if request.method == "POST": week_of = str(request.form["week_of"]) place = request.form["place"] ok, weekend = database.post_weekend(week_of=week_of, place=place) if ok: database.commit() return resource.weekend(uri, weekend), 201 return "Resource Already exists", 409 if request.method == "GET" and weekend_id: ok, weekend = database.get_weekend(weekend_id) if ok: return resource.weekend(uri, weekend), 200 return "Resource not found", 404 if request.method == "GET": ok , weekend = database.get_weekend() if ok: return resource.weekend(uri, weekend), 200 return "Resource not found", 404 if request.method == "DELETE" and weekend_id: ok = database.del_weekend(weekend_id) if ok: database.commit() return "", 200 return "Resource Not Found", 404 return "Resource Not Found", 404
def adminadduser(self): choice = input("Too add a user:S for student or I for instructor\n") if (choice == 'S'): id = input("Enter a unique id for the student\n") fname = input("Enter the first name of the student''\n") sname = input("Enter the last name of the student''\n") grady = input("Enter the students graduation year\n") major = input("Enter the students major''\n") email = input("Enter the students email''\n") sql_command = """ INSERT INTO STUDENT VALUES (""" + id + """,""" + fname + """,""" + sname + """,""" + grady + """,""" + major + """,""" + email + """); """ database.cursor.execute(sql_command) print(sname + " " + fname + " Has been added to the database\n") database.commit() elif (choice == 'I'): id = input("Enter a unique id for the instructor\n") fname = input("Enter the first name of the instructor''\n") sname = input("Enter the last name of the instructor''\n") title = input("Enter the instructors title''\n") hirey = input("Enter the hire year for the instructor\n") dept = input("Enter the insturtors dept.''\n") email = input("Enter the instructors email''\n") sql_command = """ INSERT INTO INSTRUCTOR VALUES (""" + id + """,""" + fname + """,""" + sname + """,""" + title + """,""" + hirey + """,""" + dept + """,""" + email + """); """ database.cursor.execute(sql_command) print(sname + " " + fname + " Has been added to the database\n") database.commit() else: print("Invalid choice\n")
def POST(self): args = web.input( token=None, id=None, title=None, artist=None, tag=[]) sender = auth.get_id_by_token(args.token) if sender is None: raise UsageError("bad token") track = database.Track.get_by_id(int(args.id)) if track is None: raise UsageError("track not found") if args.artist: track["artist"] = args.artist log_debug("{0} set artist for track {1} to {2}", sender, args.id, args.artist) if args.title: track["title"] = args.title log_debug("{0} set title for track {1} to {2}", sender, args.id, args.title) track.put() if args.tag: track.set_labels(args.tag) log_debug("{0} set labels for track {1} to {2}", sender, args.id, ", ".join(args.tag)) database.commit() return {"success": True}
def remove_product(self, product): db = database.connect() cursor = database.get_cursor(db) product_id = product.get_id() cursor.execute("select quantity from warehouse_to_product \ where product_id = :input_pid and warehouse_id = :input_wid" , \ input_pid = product_id, input_wid = self.get_id()) current_quantity = cursor.fetchone() if current_quantity: # The item is already in this table in the DB, just decriment quantity decrimented_quantity = int(current_quantity[0]) - 1 if decrimented_quantity > 0: # Removing one will not remove all instances of that product cursor.execute("update warehouse_to_product set quantity = :input_quantity \ where product_id = :input_pid and warehouse_id = :input_wid" , \ input_quantity = decrimented_quantity, input_pid = product_id, \ input_wid = self.get_id()) database.commit(db) else: # Remove the line from the DB if product has quantity of zero cursor.execute("delete from warehouse_to_product \ where product_id = :input_pid and warehouse_id = :input_wid" , \ input_pid = product_id, input_wid = self.get_id()) database.commit(db) else: # The item is not yet in the warehouse's stock, so do nothing pass database.disconnect(db)
def processRequest(req): debug = wsgi_app.debug c = req.path_info_pop() func = getattr(cmd, 'cmd_' + c, None) if func != None: if debug: debugprint('->', '/' + c, req.body) try: data = json.loads(req.body.decode('utf8')) except: return badRequest() try: resp = func(data) resp = json.dumps(resp) except: database.rollback() raise else: database.commit() if debug: debugprint('<-', resp) return webob.Response(resp, 200) return notFound()
def new_product(name, type_string, description=None, nutrition_facts=None, alcohol_content=None, size=1): # Adding new product to database, returning reference to new product instance db = database.connect() cursor = database.get_cursor(db) # Getting type_id from type_string, ensuring it's a valid type string (returns int ID) cursor.execute( "select id from product_types where product_type = :input_type", input_type=type_string) type_id = cursor.fetchone()[0] if type_id: if isinstance(type_id, int): # Adding new product to database (ONLY name and type_id) and retrieving generated product ID returned_id = cursor.var(database.cx_Oracle.NUMBER) cursor.execute("insert into products (name, product_type_id, product_size) values \ (:product_name, :ptype_id, :psize) returning id into :new_product_id" , product_name = name, \ ptype_id = type_id, new_product_id = returned_id, psize = size) database.commit(db) # Getting product reference returned_id = int(returned_id.getvalue()) new_product = Product(returned_id) # Now adding description, nutrition facts, and alcohol content (automatically generating defaults) new_product.modify_description(description) new_product.modify_nutrition_facts(nutrition_facts) new_product.modify_alcohol_content(alcohol_content) return new_product
def POST(self): try: args = web.input(track_id="", token=None) logging.debug("Vote request: %s" % args) sender = auth.get_id_by_token(args.token) if sender is None: raise web.forbidden("Bad token.") if args.track_id.isdigit(): track_id = int(args.track_id) else: track_id = tracks.get_last_track_id() weight = tracks.add_vote(track_id, sender, self.vote_value) if weight is None: return {"status": "error", "message": "No such track."} database.commit() message = 'OK, current weight of track #%u is %.04f.' % (track_id, weight) return { "status": "ok", "message": message, "id": track_id, "weight": weight, } except web.Forbidden: raise except Exception, e: log.log_exception(str(e), e) return {"status": "error", "message": str(e)}
def remove(self): db = database.connect() cursor = database.get_cursor(db) cursor.execute("delete from products where id = :input_id", input_id=self.get_id()) database.commit(db) database.disconnect(db)
def appendNew(cls, title: str, url: str, image_url: str) -> 'BookmarkService': """ æ·»åŠ æ–°çš„ä¹¦ç¾ """ no_slash_url = url.split("#")[0] base_url = no_slash_url.split('?')[0] domain = no_slash_url.split('/')[2] bookmark = BookMark.filter(BookMark.url == no_slash_url).first() if not bookmark: bookmark = BookMark(domain=domain, title=title, base_url=base_url, url=no_slash_url, image_url=image_url, post_count=1, hidden=0) insert(bookmark) commit() conn = cls.redis_conn() conn.set('NEWEST:alva', str(bookmark.id)) else: bookmark.post_count += 1 commit() return BookmarkService().withId(bookmark.id)
def modify_name(self, new_name): db = database.connect() cursor = database.get_cursor(db) if new_name: cursor.execute("update products set name = :name_string where id = :product_id", name_string = new_name, \ product_id = self.get_id()) database.commit(db) database.disconnect(db)
def modify_size(self, new_size): # ensuring it's a number: db = database.connect() cursor = database.get_cursor(db) if isinstance(new_size, (int, float, long)): cursor.execute("update products set product_size = :psize where id = :product_id", \ psize = new_size, product_id = self.get_id()) database.commit(db) database.disconnect(db)
def remove(self): c = database.cursor() c.execute(''' DELETE FROM room_queue WHERE item_id = ?''' , (self.item_id,)) database.commit()
def GET(self): args = web.input(track=None, token=None) if args.track: sender = auth.get_id_by_token(args.token) console.on_queue("-s " + str(args.track), sender or "Anonymous Coward") database.commit() return {"status": "ok"}
def _store_city(resp): """Check cities exists in the DB, and if not stores them in the places table.""" city = resp['city'] row = database.query_row('SELECT COUNT(*) FROM Place WHERE Id = ?', (city['id'],)) if (row[0] == 0): database.execute('INSERT INTO Place (Id, Name, CoordLon, CoordLat, Country) VALUES (?,?,?,?,?)', (city['id'], city['name'], city['coord']['lon'], city['coord']['lat'], city['country'])) database.commit() app.logger.info('Place %s added', city['name']) return city['id']
def new_person(username, password, first_name, last_name, type_string, middle_initial = None, \ salary = None, job_title = None): db = database.connect() cursor = database.get_cursor(db) returned_id = cursor.var(database.cx_Oracle.NUMBER) # getting person type ID from type_string cursor.execute("select id from person_types where person_type = :input_type", \ input_type = type_string) type_id = cursor.fetchone() if type_id: type_id = type_id[0] else: raise ValueError("Type given (%s) not valid person type" % str(type_string)) hashed_password = hash_password(username, password) if middle_initial: # Trimming middle initial middle_initial = middle_initial[0] cursor.execute("insert into persons \ (username, password, first_name, middle_initial, last_name, \ person_type_id, balance) \ values (:input_username, :input_password, \ :input_first, :input_middle, :input_last, :input_type_id, 0) \ returning id into :output_id" , \ input_username = username, input_password = hashed_password, \ input_first = first_name, input_middle = middle_initial, \ input_last = last_name, input_type_id = type_id, \ output_id = returned_id) database.commit(db) else: cursor.execute("insert into persons \ (username, password, first_name, last_name, \ person_type_id, balance) \ values (:input_username, :input_password, \ :input_first, :input_last, :input_type_id, 0) \ returning id into :output_id" , \ input_username = username, input_password = hashed_password, \ input_first = first_name, \ input_last = last_name, input_type_id = type_id, \ output_id = returned_id) database.commit(db) database.disconnect(db) returned_id = returned_id.getvalue() reference = Person(returned_id) if salary: reference.modify_salary(salary) if job_title: reference.modify_job_title(job_title) return reference
def inventory_refill(): """\ Sets the stock of all drugs in the inventory to its maximum capacity """ inventory = get_inventory() for i in inventory: i.stock = i.capacity database.update_inventory(i) database.commit()
def adminremove(self): CRN = input("Enter the CRN for the course you want to remove") database.cursor.execute("""SELECT TITLE FROM COURSE WHERE CRN = """ + CRN + """ ;""") print("Has been removed from database") sql_command = """DELETE FROM COURSE WHERE CRN = """ + CRN + """; """ database.cursor.execute(sql_command) sql_command = """DELETE FROM STUDENTCOURSE WHERE CRN = """ + CRN + """; """ database.cursor.execute(sql_command) database.commit()
def modify_balance(self, addition): db = database.connect() cursor = database.get_cursor(db) new_balance = self.get_balance() + addition cursor.execute("update persons set balance = :input_balance where id = :input_id", \ input_balance = new_balance, input_id = self.get_id()) database.commit(db) database.disconnect(db)
def GET(self): args = web.input(track=None, token=None) if not args.track: raise UsageError("track id not specified (id)") sender = auth.get_id_by_token(args.token) console.on_queue("-s " + str(args.track), sender or "Anonymous Coward") database.commit() return {"success": True}
def change_teamname(self, oldname:str, newname:str): """Allows you to update a team if a user made a mistake or is undesirable...""" team = db.Team.get(name=oldname) resp = input(f"Are you sure you want to change team '{oldname}' to '{newname}'? [y/N]") if resp == 'y': team.name = newname db.commit() print("Done.") return print('Cancelling... ')
def modify_job_title(self, new_title): db = database.connect() cursor = database.get_cursor(db) cursor.execute("update persons \ set job_title = :input_title \ where id = :input_id" , \ input_title = str(new_title), input_id = self.get_id()) database.commit(db) database.disconnect(db)
def update_rank(self, rank): self.__rank = rank c = database.cursor() c.execute(''' UPDATE room_queue SET rank = ? WHERE item_id = ?''' , (rank, self.item_id)) database.commit()
def cmd_xmpp_send(*args): """send a Jabber message""" if len(args) < 1: print "Usage: ardj xmpp-send \"message text\" [recipient_jid]" exit(1) recipient = None if len(args) > 1: recipient = args[1].decode("utf-8") from database import Message, commit Message(message=args[0].decode("utf-8"), re=recipient).put() commit()
def create(name, owner): c = database.cursor() c.execute(''' INSERT INTO rooms ( name , owner) VALUES(?, ?)''' , (name, owner.user_id)) room_id = c.lastrowid database.commit() return room_id
def create(name, password): password_hashed = pwd_context.encrypt(password) c = database.cursor() c.execute(''' INSERT INTO users ( name , password) VALUES(?, ?)''' , (name, password_hashed)) user_id = c.lastrowid database.commit() return user_id
def create_test_table(): """ Create a test table to have specific data to test on. """ cur, con = database.connect_to_database() if not database.check_if_table_exist(cur, 'testTable'): sql = "CREATE TABLE testTable (Model CHAR(100), Link CHAR(100), \ Description MEDIUMTEXT, Kms INT(20), Year INT(4), \ Kml FLOAT(20), Kmt FLOAT(20), Moth CHAR(30), Trailer CHAR(30), \ Location CHAR(50), Price INT(20))" database.query(cur, sql) sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \ Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \ '%s','%s','%s','%s','%s','%s','%s','%s')" % ( 'Audi A1', 'www.test.dk/audi1', 'Audi A1 description text', '100', '2000', '2', '6', '0', 'No trailer', 'Sjælland', '50000') database.query(cur, sql) sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \ Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \ '%s','%s','%s','%s','%s','%s','%s','%s')" % ( 'Audi A2', 'www.test.dk/audi2', 'Audi A2 description text', '200', '2001', '3', '7', '0', 'No trailer', 'Fyn', '60000') database.query(cur, sql) sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \ Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \ '%s','%s','%s','%s','%s','%s','%s','%s')" % ( 'Audi A3', 'www.test.dk/audi3', 'Audi A3 description text', '300', '2002', '4', '8', '0', 'No trailer', 'Bornholm', '70000') database.query(cur, sql) sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \ Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \ '%s','%s','%s','%s','%s','%s','%s','%s')" % ( 'Ford Mondeo', 'www.test.dk/ford1', 'Ford Mondeo description text', '400', '2003', '5', '12', '0', 'No trailer', 'Jylland', '8000') database.query(cur, sql) sql = "INSERT INTO testTable(Model, Link, Description, Kms, Year, \ Kml, Kmt, Moth, Trailer, Location, Price) VALUES('%s', '%s', '%s', \ '%s','%s','%s','%s','%s','%s','%s','%s')" % ( 'Mercedes SLK', 'www.test.dk/mercedes1', 'Mercedes SLK description text', '500', '2004', '2', '4', '0', 'No trailer', 'Fyn', '100000') database.query(cur, sql) database.commit(cur, con)
def create(room_id, rank, service, url, title, duration, start_time): c = database.cursor() c.execute(''' INSERT INTO room_queue ( room_id , rank , service , url , title , duration , start_time) VALUES(?, ?, ?, ?, ?, ?, ?)''' , (room_id, rank, service, url, title, duration, start_time)) item_id = c.lastrowid database.commit() return item_id
def cmd_fix_artist_names(*args): """correct names according to Last.fm""" from ardj.scrobbler import LastFM from ardj.database import Track, commit cli = LastFM().authorize() if cli is None: print "Last.fm authentication failed." return False names = Track.get_artist_names() print "Correcting %u artists." % len(names) for name in names: new_name = cli.get_corrected_name(name) if new_name is not None and new_name != name: logging.info("Correcting artist name \"%s\" to \"%s\"" % (name.encode("utf-8"), new_name.encode("utf-8"))) Track.rename_artist(name, new_name) commit()
def _store_measurements(place_id, resp): """Stores the measurement values in the database.""" i = 0 j = 0 for item in resp['list']: m = {} dt = datetime.datetime.utcfromtimestamp(int(item['dt'])) if 'main' in item: if 'temp' in item['main']: m[MT_TEMP] = item['main']['temp'] if 'temp_min' in item['main']: m[MT_TEMP_MIN] = item['main']['temp_min'] if 'temp_max' in item['main']: m[MT_TEMP_MAX] = item['main']['temp_max'] if 'pressure' in item['main']: m[MT_PRESS] = item['main']['pressure'] if 'sea_level' in item['main']: m[MT_PRESS_SEALEV] = item['main']['sea_level'] if 'grnd_level' in item['main']: m[MT_PRESS_GRNDLEV] = item['main']['grnd_level'] if 'humidity' in item['main']: m[MT_HUMID] = item['main']['humidity'] if 'wind' in item: if 'speed' in item['wind']: m[MT_WIND_SPEED] = item['wind']['speed'] if 'deg' in item['wind']: m[MT_WIND_DEG] = item['wind']['deg'] if 'rain' in item: if '3h' in item['rain']: m[MT_RAIN_3H] = item['rain']['3h'] for key in m: row = database.query_row('SELECT COUNT(*) FROM Measurement WHERE Time = ? AND Place = ? AND Type = ?', (dt, place_id, key)) if row[0] == 0: (rowcount, lastid) = database.execute('INSERT INTO Measurement (Time, Place, Type, Value) VALUES (?,?,?,?)', (dt, place_id, key, m[key])) i = i + 1 else: j = j + 1 database.commit() app.logger.info('%s measurements inserted for %s' % (i, place_id)) if j > 0: app.logger.info('%s measurements skipped for %s' % (j, place_id))
def create_token(login, login_type): if login_type == "jid": login = resolve_alias(login) tmp = tempfile.mktemp(suffix="", prefix="", dir="") token = Token(token=tmp, login=login, login_type=login_type, active=1) token.put(force_insert=True) base_url = get_setting("web_api_root", "http://localhost:8080").rstrip("/") url = "%s/api/auth?token=%s" % (base_url, tmp) message = "A third-party application is requesting access to your account. If that was you, follow this link:\n%s\n" % url if login_type == "jid": msg = Message(re=login, message=message) msg.put() else: run(["mail", "-s", "Your token", login], stdin_data=message) commit() return url
def profile(profile_id=None): uri = request.url_root + "api/v1/" if request.method == "POST": name = request.form["name"] number = request.form["number"] company= request.form["company"] title = request.form["title"] address = request.form["address"] ok, profile = database.post_profile(name=name, number= number, company = company, title=title, address=address) if ok: database.commit() return resource.profile(uri, profile), 201 return "Resource already exists", 409 if request.method == "GET" and profile_id: ok , profile = database.get_profile(profile_id) if ok: return resource.profile(uri, profile), 200 return "No Resource Found", 404 if request.method == "GET": ok , profile = database.get_profile() if ok: return resource.profile(uri, profile), 200 return "No Resource Found", 404 if request.method == "DELETE" and profile_id: ok = database.del_profile(profile_id) if ok: database.commit() return "", 200 return "No Resource Found", 404 return "Resource Not Found", 404
def participants(participant_id=None): uri = request.url_root + "api/v1/" if request.method == "POST": weekend_id = request.form["weekend_id"] profile_id = request.form["profile_id"] ok, participants = database.post_participants(weekend_id=weekend_id, profile_id=profile_id) if ok: database.commit() return resource.participant(uri, participants), 201 return "Resource already exists", 409 if request.args.get('weekend_id'): weekend_id = request.args.get('weekend_id') else: weekend_id = None if request.method == "GET" and weekend_id: ok , participants = database.get_participants(weekend_id=weekend_id) if ok: return resource.participant(uri, participants), 200 return "Resource Not Found", 404 if request.method == "GET" and participant_id: ok , participants = database.get_participants(participant_id=participant_id) if ok: return resource.participant(uri, participants), 200 return "Resource Not Found", 404 if request.method == "GET": ok , participants = database.get_participants() if ok: database.commit() return resource.participant(uri, participants), 200 return "Resource Not Found", 404 if request.method == "DELETE" and participant_id: ok = database.del_participant(participant_id) if ok: database.commit() return "", 200 return "No resource found", 404 return "Resource Not Found", 404
)''') # Populate settings good = False settings = {'server':None, 'username': None, 'email': None, 'password': None} while not good: settings['server'] = "http://prodgame01.lordofultima.com/%s/"%raw_input("Server (e.g. 35 for world 23): ") settings['username'] = raw_input("Bot's username: "******"LoU login email: ") settings['password'] = raw_input("LoU password: "******"Is that correct? [Y/n]").strip().lower() good = True if ans and ans[0] == 'n': good = False for setting in settings: cursor.execute('insert into settings (name, value) values (?, ?)', (setting.upper(), settings[setting])) print "Database created." database.commit() cursor.close()
def process(text,sender,chatroom): userary = ['void1', 'void2'] gods = ['mousepak','beobachten'] # bosstry lvl boss unit if text.find('!boss') > -1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: bosstypes = {"d":0, "h":1, "m":2} suggested_troops = {"berserker":[0,1,0,50,300,2000,4000,10000,15000,20000,30000,45000,60000], "ranger":[0,1,0,84,500,3334,6667,16667,25000,33334,50000,75000,100000], "guardian":[0,1,0,250,1500,10000,20000,50000,75000,100000,150000,225000,300000], "xbow":[1,0,0,63,375,2500,5000,12500,18750,25000,37500,56250,75000], "knight":[1,0,0,28,167,1112,2223,5556,8334,11112,16667,25000,33334], "mage":[0,0,1,36,215,1429,2858,7143,10715,14286,21429,32143,42858], "warlock":[0,0,1,21,125,834,1667,4167,6250,8334,12500,18750,25000], "templar":[0,1,0,100,600,4000,8000,20000,30000,40000,60000,90000,120000], "paladin":[1,0,0,42,250,1667,3334,8334,12500,16667,25000,37500,50000]} research_bonus = [1, 0.99, 0.97, 0.94, 0.90, 0.85, 0.80, 0.75, 0.70, 0.65, 0.60, 0.55, 0.5] try: tokens = text.split() cmd = tokens[0] type = tokens[1][:1] num = tokens[1][1:] unit = tokens[2] research_lvl = 0 if len(tokens) == 4: research_lvl = int(tokens[3]) if research_lvl > 12: research_lvl = 12 # print research_lvl # print len(tokens) troops = suggested_troops[unit][int(float(num))+2] if suggested_troops[unit][bosstypes[type]] == 1: troops = suggested_troops[unit][int(float(num))+2]*0.67 troops = troops * research_bonus[research_lvl] response = 'suggested troop numbers to send : '+str(int(round(troops, 0))) bot.whisper(response, sender) except (IndexError, ValueError, TypeError, KeyError): bot.whisper('try again: !boss {d|h|m}{1-10} type - type can be berserker, ranger, guardian, xbow, knight, mage, warlock, templar or paladin', sender) bot.whisper('e.g. !boss d7 knight', sender) return # quit if sender.lower() in gods and text.find('!die') >= 0: bot.whisper('Cya!', sender) sys.exit(0) # restart all bots if sender.lower() in gods and text.find('!restart') >= 0: bot.whisper('Attempting restart!', sender) os.system("pkill -f 'python run.py -r'") # help if text.find('!help') > -1 and len(text.split(' ',1)) == 1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] bot.output('/whisper '+sender+' Available commands.. !fight, !shoot, !tickle and !parp.','C') if p !=None: bot.output('/whisper '+sender+' Most commands whisper to remove chat spam. Type `help name` to find out more about the function `name`. Available commands.. help, boss, check, city, claim, hunt.','C') if sender in str(userary): bot.output('/whisper '+sender+' *Privileged commands.. history','C') if sender in str(gods): bot.output('/whisper '+sender+' *Master commands.. alliance, die, msg, whisper','C') return if text.find('!help') > -1 and len(text.split(' ',1)) > 1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: try: arg = text.split(' ',1)[1] if arg == 'boss': bot.output('/whisper '+sender+' info about boss command.','C') elif arg == 'city': bot.output('/whisper '+sender+' info about city command.','C') else: bot.output('/whisper '+sender+' Not found','C') except ValueError: bot.output('/whisper '+sender+' something went wrong','C') except (Exception, e): print 'Something bad just happened!\n', e return # record alliance members to database table if sender.lower() in gods and text.find('!alliance') > -1 and chatroom == 'rivatein': alliance_data = connect.getAllianceInfo(7) db = database.get_db() db.execute('delete from alliance_names') db.execute('vacuum') for index, item in enumerate(alliance_data): rep = item['n'] db.execute('insert or replace into alliance_names (player) values (?)',(rep,)) database.commit() bot.whisper('Done', sender) # print(alliance_data) return # !history response if text.find('!history') > -1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: histname = text.split(' ',1)[1] lynxcmd = "lynx -source -auth=="+config.EXTUSER+":"+config.EXTPASS+" '"+config.EXTURL+"bot_lookup.php?world=39&player="+histname+"'" histdata = os.popen(lynxcmd).read() print(sender+' '+histdata) histdata2 = histdata.split('#') bot.output('/whisper '+sender+' '+histdata2[0],'C') bot.output('/whisper '+sender+' '+histdata2[1],'C') bot.output('/whisper '+sender+' '+histdata2[2],'C') bot.output('/whisper '+sender+' '+histdata2[3],'C') return # !city info response if text.find('!city') > -1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: print('room = '+chatroom) cityxy = text.split(' ',1)[1] cityxy = str(cityxy).replace("[coords]","") cityxy = cityxy.replace("[/coords]","") cityxy = cityxy.replace(" ","") # print(cityxy) cityx = cityxy.split(':')[0] cityy = cityxy.split(':')[1] lynxcmd = "lynx -source -auth="+config.EXTUSER+":"+config.EXTPASS+" '"+config.EXTURL+"bot_city.php?world=39&x="+cityx+"&y="+cityy+"'" citydata = os.popen(lynxcmd).read() # print(sender+' '+lynxcmd) print(sender+' '+citydata) citydata2 = citydata.split('#') # print(len(citydata2)) # print(citydata2[0]) for botout in range(0, len(citydata2)-1): bot.output('/whisper '+sender+' '+citydata2[botout],'C') else: bot.output('/whisper '+sender+' done','C') return # Make the bot speak if sender.lower() in gods and text.find('!msg') > -1: chandata = text.replace('!msg ','') bot.output(chandata,'C') return # Make the bot whisper if text.strip().find('!whisper') == 0 and sender.lower() in gods: tokens = text.split() cmd = tokens[0] target = tokens[1] message = ' '.join(tokens[2:]) bot.whisper(message, target) return # add claim if text.find('!claim') > -1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: try: arg = text.split(' ',1)[1] xy = arg.split(':',1) x = str(xy[0]).replace("[coords]","") x = x.replace(" ","") y = str(xy[1]).replace("[/coords]","") if len(x) > 3 or len(y) > 3: raise IndexError if database.check_claim(x,y): bot.whisper('Location already claimed by ' + database.check_claim(x,y) + '. :(', sender) else: database.add_claim(x,y,sender) bot.whisper(x + ':' + y + ' successfully claimed!', sender) except IndexError: bot.whisper('Looks like you made a typo.', sender) return return # check claim if text.find('!check') > -1 and chatroom == 'rivatein': # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: try: arg = text.split(' ',1)[1] xy = arg.split(':',1) x = str(xy[0]).replace("[coords]","") x = x.replace(" ","") y = str(xy[1]).replace("[/coords]","") if len(x) > 3 or len(y) > 3: raise IndexError if database.check_claim(x,y): bot.whisper(x + ':' + y + ' is claimed by ' + database.check_claim(x,y) + '. :(', sender) else: bot.whisper(x + ':' + y + ' is not claimed yet!', sender) except IndexError: bot.whisper('Looks like you made a typo.', sender) return return # Look for bosses, but only on whispers if '!hunt' in text and 'rivate' in chatroom: # check within alliance table db = database.get_db() d = db.execute('select player from alliance_names where player=?',(sender,)) p = None for r in d: p = r[0] if p !=None: cooldown = 60 * 5 x = None y = None cont = None lvl_max = 10 lvl_min = 7 # Let's not give some people an unfair advantage... if 'lekke' in sender.lower(): bot.whisper('Sorry Lekke, you are too good to use me...', sender) return # First check that player isn't spamming this function be enforcing a cool down period now = int(time.time()) db = database.get_db() resp = db.execute('select time from boss_cooldown where player = ?',(sender.lower(),)) last_req = resp.fetchone() if last_req: last_req = int(last_req[0]) elapsed = now - last_req if elapsed < cooldown: if sender.lower() in gods: bot.whisper("Cheating... [elapsed is %d secs]"%elapsed, sender) else: def ptime(s): # pretty time mins = s/60 secs = s%60 out = "" if mins > 0: out += '%d min, '%mins out += '%d sec'%secs return out bot.whisper("Easy there killer :P Wait %s, then try again"%(ptime(cooldown - elapsed))) return try: tokens = text.split() cmd = tokens[0] source = tokens[1] source = str(source).replace("[coords]","") source = source.replace("[/coords]","") source = source.replace(" ","") if len(tokens) > 2: lvl_min = int(tokens[2]) if len(tokens) > 3: lvl_max = int(tokens[3]) if ':' in source: # source is a city x,y = map(int, source.split(':')) elif 'c' in source.lower(): # source is a continent cont = int(source.lower().strip('c')) x = ((cont % 10) * 100) + 50 y = ((cont / 10) * 100) + 50 else: raise ValueError except (IndexError, ValueError, TypeError): bot.whisper('try again: !hunt city_coords OR cont [min level=7] [max level=10]>', sender) bot.whisper('e.g. !hunt c22 8', sender) return db.execute('insert or replace into boss_cooldown (player, time) values (?, ?)',(sender,now)) database.commit() if lvl_min < 1: lvl_min = 1 if lvl_max > 10: lvl_max = 10 if lvl_max < lvl_min: lvl_min = lvl_max # get all bosses on the continent cells = world.pos2contcells(x,y) world_data = connect.getWorldInfo(cells) bosses = world.parse_bosses(world_data) # limit to the lvl_min and continent we're interested in bosses = [b for b in bosses if b['alive'] and b['level'] >= lvl_min and b['level'] <= lvl_max and world.get_cont(b['x'],b['y']) == world.get_cont(x,y)] # sort by highest level first or distance depending on request if cont: bosses = sorted(bosses, key=lambda a: -a['level']) else: import math bosses = sorted(bosses, key=lambda a: math.sqrt((x - a['x'])**2 + (y - a['y'])**2)) # restrict number returned if len(bosses) > 10: bosses = bosses[:10] # create the response if len(bosses) > 0: response = ', '.join(["%s%d %03d:%03d"%(b['name'][0], b['level'], b['x'], b['y']) for b in bosses]) else: response = 'None found, mate.' bot.whisper(response, sender) return # Player commands # if 'botx' in text.lower(): # bot.output(sender+' touched me in an inappropriate way','C') # Random facts if 'random' in text.lower() and 'botx' not in text.lower(): db = database.get_db() resp = db.execute('select fact from random_facts order by random() limit 1') responce = 'RANDOM..'+str(resp.fetchone())[3:-3] bot.output(responce,chatroom)
def initdb(): create_schema() insert_testdata() database.commit()
def __del__(self): logging.debug("Request finished, closing the transaction.") database.commit()
"&IncludeLeasing=" + IncludeLeasing + "&Cartypes=" + \ Cartypes + "&HpFrom=" + HpFrom + "&HpTo=" + HpTo + "&page=" + page conn.request("GET", query) res = conn.getresponse() content = res.read() parsed_html = BeautifulSoup(content, from_encoding='utf8') # in case of database error except mdb.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1) # close connection to database if con: database.commit(cur, con) def main(): """ The method is the main method of the script. The which will run by a command 'python bilbasen.py arg', where arg is the number of pages to crawl. Do not provide any argument if all pages should be crawled. """ if len(sys.argv) == 1: download_data_to_database() else: download_data_to_database(sys.argv[1]) if __name__ == '__main__':
def POST(self): database.commit() return {"status": "ok"}
def cmd_queue_flush(*args): """delete everything from the queue""" from ardj.database import Queue, commit Queue.delete_all() commit()
def create_edges_full_data_table(): print('create_edges_full_data_table') connection = database.create_connection() database.run_command('delete from edges_full_data', None, connection, True) database.run_command('vacuum', None, connection, True) sql_query = 'select a.id, a.shape_x_y, b.x from_x, b.y from_y, c.x to_x, c.y to_y \ from oulu_edges a \ inner join oulu_nodes b on a.`from` = b.id \ inner join oulu_nodes c on a.`to` = c.id' count = 0 edges = database.query_all(sql_query, None, connection) for edge in edges: sequence = 1 if edge['shape_x_y'] is None: distance = utility.measure_distance(edge['from_x'], edge['from_y'], edge['to_x'], edge['to_y']) if distance <= config.MAX_DISTANCE: sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \ values (?, ?, ?, ?, ?, ?, ?)' parameter = (edge['id'], sequence, edge['from_x'], edge['from_y'], edge['to_x'], edge['to_y'], distance) database.run_command(sql_script, parameter, connection, True) sequence += 1 else: splited_dots = split_line(edge['from_x'], edge['from_y'], edge['to_x'], edge['to_y'], config.MAX_DISTANCE) for i in range(len(splited_dots)-1): splited_distance = utility.measure_distance(splited_dots[i][0], splited_dots[i][1], splited_dots[i+1][0], splited_dots[i+1][1]) sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \ values (?, ?, ?, ?, ?, ?, ?)' parameter = (edge['id'], sequence, splited_dots[i][0], splited_dots[i][1], splited_dots[i+1][0], splited_dots[i+1][1], splited_distance) database.run_command(sql_script, parameter, connection, False) sequence += 1 database.commit(connection) else: lines = edge['shape_x_y'].split(' ') for i in range(len(lines)-1): coord_from = lines[i].split(',') coord_to = lines[i+1].split(',') distance = sqrt(pow(float(coord_from[0]) - float(coord_to[0]), 2) + pow(float(coord_from[1]) - float(coord_to[1]), 2)) if distance <= config.MAX_DISTANCE: sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \ values (?, ?, ?, ?, ?, ?, ?)' parameter = (edge['id'], sequence, coord_from[0], coord_from[1], coord_to[0], coord_to[1], distance) database.run_command(sql_script, parameter, connection, False) sequence += 1 else: splited_dots = split_line(coord_from[0], coord_from[1], coord_to[0], coord_to[1], config.MAX_DISTANCE) for j in range(len(splited_dots)-1): splited_distance = utility.measure_distance(splited_dots[j][0], splited_dots[j][1], splited_dots[j+1][0], splited_dots[j+1][1]) sql_script = ' insert into "edges_full_data" ("edge_id", "sequence", "from_x", "from_y", "to_x", "to_y", "distance") \ values (?, ?, ?, ?, ?, ?, ?)' parameter = (edge['id'], sequence, splited_dots[j][0], splited_dots[j][1], splited_dots[j+1][0], splited_dots[j+1][1], splited_distance) database.run_command(sql_script, parameter, connection, False) sequence += 1 database.commit(connection) count += 1 if count%1000 == 0: print(count) database.close_connection(connection, True) print('finish create_edges_full_data_table')