def query_packets_with_signed(): """ Query the database and return a list of currently open packets and the number of signatures they currently have :return: a list of results: intro members with open packets, their name, username, and number of signatures received """ try: return db.engine.execute(""" SELECT packets.username AS username, packets.name AS name, packets.onfloor AS onfloor, coalesce(packets.sigs_recvd, 0) AS received FROM ( ( SELECT freshman.rit_username AS username, freshman.name AS name, freshman.onfloor AS onfloor, packet.id AS id, packet.start AS start, packet.end AS end FROM freshman INNER JOIN packet ON freshman.rit_username = packet.freshman_username) AS a LEFT JOIN ( SELECT totals.id AS id, coalesce(sum(totals.signed), 0) AS sigs_recvd FROM ( SELECT packet.id AS id, coalesce(count(signature_fresh.signed), 0) AS signed FROM packet FULL OUTER JOIN signature_fresh ON signature_fresh.packet_id = packet.id WHERE signature_fresh.signed = TRUE AND packet.start < now() AND now() < packet.end GROUP BY packet.id UNION SELECT packet.id AS id, coalesce(count(signature_upper.signed), 0) AS signed FROM packet FULL OUTER JOIN signature_upper ON signature_upper.packet_id = packet.id WHERE signature_upper.signed = TRUE AND packet.start < now() AND now() < packet.end GROUP BY packet.id ) totals GROUP BY totals.id ) AS b ON a.id = b.id ) AS packets WHERE packets.start < now() AND now() < packets.end; """) except exc.SQLAlchemyError: raise exc.SQLAlchemyError("Error: Failed to get open packets with signatures received from database")
def test_add_tasting_fail(admin_client): with patch('juleol.db.db.session') as SessionMock: SessionMock.commit.side_effect = exc.SQLAlchemyError() ret = admin_client.post('/admin/', data={'year': 2000, 'beers': 2}) assert ret.status_code == 200 assert b'Error creating tasting' in ret.data assert SessionMock.mock_calls[4][0] == 'rollback'
def test_delete_beer_heat(admin_client): with patch('juleol.db.Beers') as BeersMock: # noqa: F841 db.Beers.query.filter.return_value.first.return_value = None ret = admin_client.delete('/admin/beer/1/heat') assert ret.status_code == 404 assert json.loads(ret.data)['error'] == 'Invalid beer id' with patch('juleol.db.db.session') as SessionMock: test_beer = db.Beers() test_beer.id = 1 test_beer.number = 1 test_beer.name = 'test beer 1' test_beer.heat = 1 db.Beers.query.filter.return_value.first.return_value = test_beer ret = admin_client.delete('/admin/beer/1/heat') assert ret.status_code == 200 assert json.loads(ret.data)['message'] == 'Beer heat deleted' assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_beer, ) assert SessionMock.mock_calls[1][0] == 'commit' assert test_beer.heat is None SessionMock.commit.side_effect = exc.SQLAlchemyError() SessionMock.reset_mock() ret = admin_client.delete('/admin/beer/1/heat') assert ret.status_code == 500 assert json.loads(ret.data)['error'] == 'Error deleting beer heat' assert SessionMock.mock_calls[2][0] == 'rollback'
def createValidEngine(uri_list): """ Create DB engine if connection is valid Attempts each uri in the list until a valid connection is made :param uri_list: list of connection uri's :return: DB engine object :raise: SQLAlchemyError if all connections fail """ errors = [] for conn_uri in uri_list: try: db_engine = create_engine(conn_uri, echo=True, pool_recycle=10, isolation_level="READ UNCOMMITTED", connect_args={"connect_timeout": 5}) # test connection _ = db_engine.connect() # conn good return it return db_engine except Exception as ex: errors.append(ex) # we failed to return good connection raise exceptions if settings.DEBUG: for ex in errors: debugException(ex, log_ex=False, print_ex=True, showstack=False) try: raise sql_exceptions.SQLAlchemyError(errors) except: raise Exception(errors)
def test_update_tasting(admin_client): with patch('juleol.db.Tastings') as TastingsMock: with patch('juleol.db.db.session') as SessionMock: test_tasting = db.Tastings() test_tasting.year = 2000 test_tasting.locked = False TastingsMock.query.filter.return_value.first.return_value = None ret = admin_client.put('/admin/tasting/2001', data={"locked": "true"}) assert ret.status_code == 404 assert b'Invalid year' in ret.data TastingsMock.query.filter.return_value.first.return_value = test_tasting ret = admin_client.put('/admin/tasting/2000', data={"invalid": "123"}) assert ret.status_code == 400 assert b'Invalid argument' in ret.data ret = admin_client.put('/admin/tasting/2000', data={"locked": "true"}) assert ret.status_code == 200 assert test_tasting.locked == True assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_tasting, ) assert SessionMock.mock_calls[1][0] == 'commit' SessionMock.reset_mock() SessionMock.commit.side_effect = exc.SQLAlchemyError() ret = admin_client.put('/admin/tasting/2000', data={"locked": "true"}) assert ret.status_code == 500 assert b'Error updating tasting' in ret.data assert SessionMock.mock_calls[2][0] == 'rollback'
def get(self, record_id): try: record = self.model.query.filter_by(id=record_id).first() if not record: raise exc.SQLAlchemyError('Not Found') return record except exc.SQLAlchemyError: self.session.rollback() raise
def test_update_participant(admin_client): with patch('juleol.db.Tastings') as TastingsMock: test_tasting = db.Tastings() test_tasting.year = 2000 TastingsMock.query.filter.return_value.first.return_value = test_tasting TastingsMock.query.filter.return_value.first.return_value = None ret = admin_client.post('/admin/2000/participant/1') assert ret.status_code == 302 ret = admin_client.get('/admin/') assert b'Invalid year' in ret.data TastingsMock.query.filter.return_value.first.return_value = test_tasting with patch('juleol.db.Participants') as ParticipantsMock: ParticipantsMock.query.filter.return_value.filter.return_value.first.return_value = None ret = admin_client.post('/admin/2000/participant/1') assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Invalid participant' in ret.data test_participant = db.Participants() test_participant.tasting = test_tasting ParticipantsMock.query.filter.return_value.filter.return_value.first.return_value = test_participant ret = admin_client.post('/admin/2000/participant/1') assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Invalid form data' in ret.data ret = admin_client.post('/admin/2000/participant/1', data={'email': 'email-invalid'}) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Invalid form data' in ret.data with patch('juleol.db.db.session') as SessionMock: ret = admin_client.post( '/admin/2000/participant/1', data={'email': '*****@*****.**'}) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' assert test_participant.email == '*****@*****.**' assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_participant, ) assert SessionMock.mock_calls[1][0] == 'commit' SessionMock.reset_mock() SessionMock.commit.side_effect = exc.SQLAlchemyError() ret = admin_client.post('/admin/2000/participant/1', data={'email': '*****@*****.**'}) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Error updating email' in ret.data assert SessionMock.mock_calls[2][0] == 'rollback'
def set_response(id, retval, retry): try: engine = create_engine(SQLALCHEMY_DATABASE_URI) engine.execute("UPDATE rbz_api SET Response = %s WHERE Id = %s", (retval, str(id))) except exc.SQLAlchemyError(e): print("No entry in Database with ID: " + str(id)) print(e) if retry: set_response(id, retval, False)
def _check_EPD_con(con: Engine) -> None: """Check EPD connection object works. Use simple example query which should be achievable with any instance of the EPD. """ try: pd.read_sql_query('select * from entity limit10;', con=con) except exc.SQLAlchemyError as e: logging.error('Could not connect to the EPD with the supplied engine.') raise exc.SQLAlchemyError(e) logging.info('Test query successfully run against EPD.')
def test_ibm_db_sa_raise_if_duplicate_entry_error_no_match(self): # Tests that the session._raise_if_duplicate_entry_error method # does not raise a DBDuplicateEntry exception when it's not a matching # integrity error. statement = ('ALTER TABLE instance_types ADD CONSTRAINT ' 'uniq_name_x_deleted UNIQUE (name, deleted)') params = None orig = sqla_exc.SQLAlchemyError( 'SQL0542N The column named "NAME" cannot be a column of a ' 'primary key or unique key constraint because it can contain null ' 'values.') integrity_error = sqla_exc.IntegrityError(statement, params, orig) session._raise_if_duplicate_entry_error(integrity_error, 'ibm_db_sa')
def get_misc_signatures(): packet_misc_sigs = {} try: result = db.engine.execute(""" SELECT packet.freshman_username AS username, count(signature_misc.member) AS signatures FROM packet RIGHT OUTER JOIN signature_misc ON packet.id = signature_misc.packet_id GROUP BY packet.freshman_username; """) for packet in result: packet_misc_sigs[packet.username] = packet.signatures except exc.SQLAlchemyError: raise exc.SQLAlchemyError( "Error: Unable to query miscellaneous signatures from database") return packet_misc_sigs
def query_signed_upperclassman(member): """ Query the database and return the list of packets signed by the given upperclassman :param member: the user making the query :return: list of results matching the query """ s = text("SELECT DISTINCT packet.freshman_username AS username, signature_upper.signed AS signed FROM packet " "INNER JOIN signature_upper ON packet.id = signature_upper.packet_id " "WHERE signature_upper.member = :member;") try: return db.engine.execute(s, member=member) except exc.SQLAlchemyError: raise exc.SQLAlchemyError("Error: Failed to get upperclassman's signatures from database")
def query_signed_alumni(member): """ Query the database and return the list of packets signed by the given alumni/off-floor :param member: the user making the query :return: list of results matching the query """ s = text("SELECT DISTINCT packet.freshman_username AS username, signature_misc.member AS signed FROM packet " "LEFT OUTER JOIN signature_misc ON packet.id = signature_misc.packet_id " "WHERE signature_misc.member = :member OR signature_misc.member ISNULL;") try: return db.engine.execute(s, member=member) except exc.SQLAlchemyError: raise exc.SQLAlchemyError("Error: Failed to get alumni's signatures from database")
def _check_unicode_returns(self, connection): return True # XXX Lately this hangs the database: cursor = self._get_raw_cursor(connection) item = cursor.columns('SYSTABLES').fetchone() try: item[1].encode('ascii') except UnicodeEncodeError, e: # In Linux, if the connection has been opened with # unicode_results=True and an unpatched pyodbc (see # progress_sa distribution) then the Progress ODBC driver # returns UTF-8 interpreted by pyodbc as UCS-2. # Things go downhill from there unless we abort. raise exc.SQLAlchemyError( "Non-ASCII in SYSTABLES. " "Are you using an unpatched pyodbc?", )
def test_get_update_note(admin_client): with patch('juleol.db.Notes') as NotesMock: # noqa: F841 db.Notes.query.filter.return_value.first.return_value = None ret = admin_client.get('/admin/note/1') assert ret.status_code == 404 assert json.loads(ret.data)['error'] == 'Invalid note id' test_note = db.Notes() test_note.id = 1 test_note.note = 'test' db.Notes.query.filter.return_value.first.return_value = test_note ret = admin_client.get('/admin/note/1') assert ret.status_code == 200 data = json.loads(ret.data) assert data['id'] == 1 assert data['note'] == 'test' with patch('juleol.db.db.session') as SessionMock: ret = admin_client.put('/admin/note/1') assert ret.status_code == 400 assert json.loads(ret.data)['error'] == 'Invalid arguments' ret = admin_client.put('/admin/note/1', data={'note': 'new test'}) assert ret.status_code == 200 assert json.loads(ret.data)['message'] == 'Note updated' assert test_note.note == 'new test' assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_note, ) assert SessionMock.mock_calls[1][0] == 'commit' SessionMock.reset_mock() ret = admin_client.delete('/admin/note/1') assert ret.status_code == 200 assert json.loads(ret.data)['message'] == 'Note deleted' assert SessionMock.mock_calls[0][0] == 'delete' assert SessionMock.mock_calls[0][1] == (test_note, ) assert SessionMock.mock_calls[1][0] == 'commit' SessionMock.reset_mock() SessionMock.commit.side_effect = exc.SQLAlchemyError() ret = admin_client.put('/admin/note/1', data={'note': 'new test'}) assert ret.status_code == 500 assert json.loads(ret.data)['error'] == 'Error updating note' ret = admin_client.delete('/admin/note/1') assert ret.status_code == 500 assert json.loads(ret.data)['error'] == 'Error deleting note' assert SessionMock.mock_calls[2][0] == 'rollback'
def test_ibm_db_sa_raise_if_duplicate_entry_error_duplicate(self): # Tests that the session._raise_if_duplicate_entry_error method # translates the duplicate entry integrity error for the DB2 engine. statement = ('INSERT INTO key_pairs (created_at, updated_at, ' 'deleted_at, deleted, name, user_id, fingerprint) VALUES ' '(?, ?, ?, ?, ?, ?, ?)') params = ['20130918001123627099', None, None, 0, 'keypair-23474772', '974a7c9ffde6419f9811fcf94a917f47', '7d:2c:58:7f:97:66:14:3f:27:c7:09:3c:26:95:66:4d'] orig = sqla_exc.SQLAlchemyError( 'SQL0803N One or more values in the INSERT statement, UPDATE ' 'statement, or foreign key update caused by a DELETE statement are' ' not valid because the primary key, unique constraint or unique ' 'index identified by "2" constrains table "NOVA.KEY_PAIRS" from ' 'having duplicate values for the index key.') integrity_error = sqla_exc.IntegrityError(statement, params, orig) self.assertRaises(db_exc.DBDuplicateEntry, session._raise_if_duplicate_entry_error, integrity_error, 'ibm_db_sa')
def test_create_note(admin_client): with patch('juleol.db.Tastings') as TastingsMock: test_tasting = db.Tastings() test_tasting.year = 2000 TastingsMock.query.filter.return_value.first.return_value = test_tasting TastingsMock.query.filter.return_value.first.return_value = None ret = admin_client.post('/admin/2000/note') assert ret.status_code == 302 ret = admin_client.get('/admin/') assert b'Invalid year' in ret.data TastingsMock.query.filter.return_value.first.return_value = test_tasting with patch('juleol.db.Notes') as NotesMock: ret = admin_client.post('/admin/2000/note') assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Invalid form data' in ret.data with patch('juleol.db.db.session') as SessionMock: ret = admin_client.post('/admin/2000/note', data={'note': 'test'}) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' assert NotesMock.mock_calls[0][2]['note'] == 'test' assert NotesMock.mock_calls[0][2]['tasting'] == test_tasting assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (db.Notes( note='test', tasting=test_tasting), ) assert SessionMock.mock_calls[1][0] == 'commit' SessionMock.reset_mock() SessionMock.commit.side_effect = exc.SQLAlchemyError() ret = admin_client.post('/admin/2000/note', data={'note': 'test'}) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Error creating note' in ret.data assert SessionMock.mock_calls[2][0] == 'rollback'
def createValidEngine(uri_list): """ Create DB engine if connection is valid Attempts each uri in the list until a valid connection is made This method uses a singleton pattern and returns db_engine if created :param uri_list: list of connection uri's :return: DB engine object :raise: SQLAlchemyError if all connections fail """ if 'db_engine' in globals(): return globals()['db_engine'] errors = [] for conn_uri in uri_list: try: db_engine = create_engine(conn_uri, echo=True, pool_recycle=300, pool_size=10, isolation_level="READ UNCOMMITTED", connect_args={"connect_timeout": 5}) # test connection _ = db_engine.connect() # conn good return it return db_engine except Exception as ex: errors.append(ex) # we failed to return good connection raise exceptions if settings.DEBUG: for ex in errors: debugException(ex) try: raise sql_exceptions.SQLAlchemyError(errors) except: raise Exception(errors)
def raises_sql_exc_no_args(msg): raise exc.SQLAlchemyError(msg)
def raises_sql_exc(msg): raise exc.SQLAlchemyError(msg)
def test_put_rate_beer(client_authorized): ret = client_authorized.post("/login", data={'year': '2000'}) ret = client_authorized.get('/login') with patch('juleol.db.Beers') as MockBeers: # noqa: F841 with patch('juleol.db.ScoreTaste') as MockScoreTaste: # noqa: F841 with patch('juleol.db.ScoreAftertaste' ) as MockScoreAfterTaste: # noqa: F841 with patch( 'juleol.db.ScoreLook') as MockScoreLook: # noqa: F841 with patch('juleol.db.ScoreSmell' ) as MockScoreSmell: # noqa: F841 with patch('juleol.db.ScoreXmas' ) as MockScoreXmas: # noqa: F841 test_beer = db.Beers() test_beer.id = 1 test_beer.name = 'test' db.Beers.query.filter.return_value.filter.return_value.first.return_value = test_beer test_score = MagicMock() test_score.score = 0 db.ScoreTaste.query.filter.return_value.filter.return_value.first.return_value = test_score db.ScoreAftertaste.query.filter.return_value.filter.return_value.first.return_value = test_score db.ScoreLook.query.filter.return_value.filter.return_value.first.return_value = test_score db.ScoreSmell.query.filter.return_value.filter.return_value.first.return_value = test_score db.ScoreXmas.query.filter.return_value.filter.return_value.first.return_value = test_score test_beer.tasting.locked = True ret = client_authorized.put('/rate/2000/1', data={'look': 10}) assert ret.status_code == 403 assert b'Tasting is locked' in ret.data test_beer.tasting.locked = False ret = client_authorized.put('/rate/2000/1', data={'look': 'bogus'}) assert ret.status_code == 400 assert b'Not a valid integer value' in ret.data ret = client_authorized.put('/rate/2000/1', data={'smell': 10}) assert ret.status_code == 400 assert b'Number must be between' in ret.data with patch('juleol.db.db.session') as SessionMock: ret = client_authorized.put('/rate/2000/1', data={'taste': 1}) assert test_score.score == 1 ret = client_authorized.put( '/rate/2000/1', data={'aftertaste': 3}) assert test_score.score == 3 ret = client_authorized.put('/rate/2000/1', data={'smell': 2}) assert test_score.score == 2 ret = client_authorized.put('/rate/2000/1', data={'look': 1}) assert test_score.score == 1 ret = client_authorized.put('/rate/2000/1', data={'xmas': 2}) assert test_score.score == 2 SessionMock.commit.side_effect = exc.SQLAlchemyError( ) ret = client_authorized.put('/rate/2000/1', data={'xmas': 2}) assert ret.status_code == 500 assert b'Error updating scores' in ret.data
def search_table(self): region_id = self.request.args.get("region_id", -1) if region_id: region_id = int(region_id) if region_id != -1: self.q = self.q.filter(Patient.region_id == region_id) self.search_form.region_id.default = region_id self.search_params.append( (_("Регион"), Region.query.filter_by(id=region_id).first().name)) filt = dict() job_category_id = self.request.args.get("job_category_id", "-1") if job_category_id != "-1": job_category_disp_name = _("Неизвестно") if job_category_id == "None": job_category_id = None else: job_category_id = int(job_category_id) job_category_disp_name = JobCategory.query.filter_by( id=job_category_id).first().name self.search_params.append( (_("Категория Работы"), job_category_disp_name)) self.q = self.q.filter(Patient.job_category_id == job_category_id) self.search_form.job_category_id.default = job_category_id is_found = self.request.args.get("is_found", "-1") if is_found != "-1": filt["is_found"] = is_found == "1" self.search_form.is_found.default = is_found self.search_params.append( (_("Найден"), _("Да") if is_found == "1" else _("Нет"))) is_currently_infected = self.request.args.get("is_currently_infected", "-1") if is_currently_infected != "-1": filt["is_infected"] = is_currently_infected == "1" self.search_form.is_currently_infected.default = is_currently_infected self.search_params.append( (_("Инфицирован"), _("Да") if filt["is_infected"] else _("Нет"))) def name_search(param, param_str, q, param_disp_name): if param_str in request.args: req_str = request.args[param_str] if req_str: q = q.filter(func.lower(param).contains(req_str.lower())) param = getattr(self.search_form, param_str, None) if param: setattr(param, 'default', req_str) self.search_params.append((param_disp_name, req_str)) return q self.q = name_search(Patient.first_name, "first_name", self.q, _("Имя")) self.q = name_search(Patient.second_name, "second_name", self.q, _("Фамилия")) self.q = name_search(Patient.patronymic_name, "patronymic_name", self.q, _("Отчество")) iin = request.args.get("iin", "") if iin: self.q = self.q.filter(Patient.iin.contains(iin)) self.search_form.iin.default = iin self.search_params.append((_("ИИН"), iin)) pass_num = request.args.get("pass_num", "") if pass_num: self.q = self.q.filter(Patient.pass_num.contains(pass_num)) self.search_form.pass_num.default = pass_num self.search_params.append((_("Номер Паспорта"), pass_num)) travel_type = request.args.get("travel_type", c.all_travel_types[0]) if travel_type and travel_type != c.all_travel_types[0]: try: travel_type_query = TravelType.query.filter_by( value=travel_type).first() travel_type_id = travel_type_query.id except (exc.SQLAlchemyError, AttributeError): raise exc.SQLAlchemyError( "Travel Type Error - {}".format(travel_type)) if travel_type_id: filt["travel_type_id"] = travel_type_id self.search_form.travel_type.default = travel_type self.search_params.append( (_("Тип Въезда"), travel_type_query.name)) # Created_date range date_range_start = request.args.get("date_range_start", None) if date_range_start: date_range_start = parse_date(date_range_start) self.q = self.q.filter(Patient.created_date >= date_range_start) self.search_form.date_range_start.default = date_range_start self.search_params.append((_("Дата Создания (Начало)"), date_range_start.strftime('%Y-%m-%d'))) date_range_end = request.args.get("date_range_end", None) if date_range_end: date_range_end = parse_date(date_range_end) self.q = self.q.filter(Patient.created_date <= date_range_end) self.search_form.date_range_end.default = date_range_end self.search_params.append((_("Дата Создания (Конец)"), date_range_end.strftime('%Y-%m-%d'))) self.q = self.q.filter_by(**filt) # State search patient_state = self.request.args.get("patient_state", "-1") if patient_state != "-1": # State Filter patient_state_val = State.query.filter_by( value=patient_state).first() patient_state_id = patient_state_val.id patient_state_disp_name = patient_state_val.name self.q = self.q.join(PatientState, PatientState.patient_id == Patient.id) self.q = self.q.filter(PatientState.state_id == patient_state_id) self.search_form.patient_state.default = patient_state # State Date # State Date Start state_date_range_start = request.args.get("state_date_range_start", None) if state_date_range_start: state_date_range_start = parse_date(state_date_range_start) self.q = self.q.filter( PatientState.detection_date >= state_date_range_start) self.search_form.state_date_range_start.default = state_date_range_start patient_state_disp_name = "{} {}".format( patient_state_disp_name, state_date_range_start.strftime('%Y-%m-%d')) # State Date End state_date_range_end = request.args.get("state_date_range_end", None) if state_date_range_end: state_date_range_end = parse_date(state_date_range_end) self.q = self.q.filter( PatientState.detection_date <= state_date_range_end) self.search_form.state_date_range_end.default = state_date_range_end patient_state_disp_name = "{}:{}".format( patient_state_disp_name, state_date_range_end.strftime('%Y-%m-%d')) self.search_params.append( (_("Статус Пациента"), patient_state_disp_name)) # State Count # State Count Min state_count_min = request.args.get("state_count_min", None) if state_count_min: self.q = self.q.having( func.count(PatientState.id) >= state_count_min) self.search_form.state_count_min.default = state_count_min self.search_params.append( (_("Минимум Статусов"), state_count_min)) # State Count Max state_count_max = request.args.get("state_count_max", None) if state_count_max: self.q = self.q.having( func.count(PatientState.id) <= state_count_max) self.search_form.state_count_max.default = state_count_max self.search_params.append( (_("Максимум Статусов"), state_count_max)) # State Infec if patient_state_val.value == c.state_infec[0]: state_infec_type = request.args.get("state_infec_type", "None") if state_infec_type != "None": param = "state_infec_type" self.q = self.q.filter( text( "CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'" .format(param, state_infec_type))) self.search_form.state_infec_type.default = state_infec_type self.search_params.append( (_("Инфицирован - Тип"), dict(c.state_infec_types)[state_infec_type])) state_infec_illness_symptoms = request.args.get( "state_infec_illness_symptoms", "None") if state_infec_illness_symptoms != "None": param = "state_infec_illness_symptoms" self.q = self.q.filter( text( "CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'" .format(param, state_infec_illness_symptoms))) self.search_form.state_infec_illness_symptoms.default = state_infec_illness_symptoms self.search_params.append( (_("Инфицирован - Симптомы"), dict( c.illness_symptoms)[state_infec_illness_symptoms])) state_infec_illness_severity = request.args.get( "state_infec_illness_severity", "None") if state_infec_illness_severity != "None": param = "state_infec_illness_severity" self.q = self.q.filter( text( "CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'" .format(param, state_infec_illness_severity))) self.search_form.state_infec_illness_severity.default = state_infec_illness_severity self.search_params.append(( _("Инфицирован - Тяжесть Болезни"), dict( c.illness_severity)[state_infec_illness_severity])) # State Dead if patient_state_val.value == c.state_dead[0]: state_dead_reason = request.args.get("state_dead_reason", "None") if state_dead_reason != "None": param = "state_dead_reason" self.q = self.q.filter( text( "CAST(\"PatientState\".attrs ->> '{}' AS VARCHAR) = '{}'" .format(param, state_dead_reason))) self.search_form.state_dead_reason.default = state_dead_reason self.search_params.append( (_("Умер - Причина Смерти"), dict(c.death_reasons)[state_dead_reason])) self.q = self.q.group_by(Patient.id) # Is contacted contacted = self.request.args.get("contacted", "-1") if contacted != "-1": contacted_disp_name = None if contacted == "contacted": self.q = self.q.join( ContactedPersons, ContactedPersons.contacted_patient_id == Patient.id) self.q = self.q.group_by(Patient.id) contacted_disp_name = _("Контактный") elif contacted == "with_contacts": self.q = self.q.join( ContactedPersons, ContactedPersons.infected_patient_id == Patient.id) self.q = self.q.group_by(Patient.id) contacted_disp_name = _("С Контактами") elif contacted == "contacted_close": self.q = self.q.join( ContactedPersons, ContactedPersons.contacted_patient_id == Patient.id) self.q = self.q.filter( ContactedPersons.is_potential_contact == False) self.q = self.q.group_by(Patient.id) contacted_disp_name = _("Контактный (БК)") elif contacted == "contacted_potential": self.q = self.q.join( ContactedPersons, ContactedPersons.contacted_patient_id == Patient.id) self.q = self.q.filter( ContactedPersons.is_potential_contact == True) self.q = self.q.group_by(Patient.id) contacted_disp_name = _("Контактный (ПК)") self.search_form.contacted.default = contacted self.search_params.append((_("Контакты"), contacted_disp_name)) is_iin_fail = request.args.get("is_iin_fail", None) if is_iin_fail: if is_iin_fail == "is_iin_empty": self.q = self.q.filter_by(iin='') self.search_form.is_iin_fail.default = "is_iin_empty" elif is_iin_fail == "is_iin_invalid": self.q = self.q.filter(Patient.iin != '') self.q = self.q.filter(func.length(Patient.iin) != 12) self.search_form.is_iin_fail.default = "is_iin_invalid" elif is_iin_fail == "is_iin_valid": self.q = self.q.filter(func.length(Patient.iin) == 12) self.search_form.is_iin_fail.default = "is_iin_valid" address = self.request.args.get("address", None) if address: self.q = self.q.join(Address, Patient.home_address_id == Address.id) self.q = self.q.join(Country, Country.id == Address.country_id) self.q = self.q.group_by(Patient.id) self.q = self.q.filter( func.lower( func.concat(Country.name, ' ', Address.city, ' ', Address.street, ' ', Address.house, ' ', Address.flat)).contains(address.lower())) self.search_form.address.default = address self.search_params.append((_("Адрес"), address)) current_country = Country.query.filter_by( code=c.current_country).first() home_address_location_type_id = self.request.args.get( "home_address_location_type_id", "-1") if home_address_location_type_id != "-1": location_type = AddressLocationType.query.filter_by( id=home_address_location_type_id).first() self.q = self.q.join(Address, Patient.home_address_id == Address.id) self.q = self.q.group_by(Patient.id) self.q = self.q.filter( Address.location_type_id == home_address_location_type_id) self.search_form.home_address_location_type_id.default = home_address_location_type_id self.search_params.append((_("Село/Город"), location_type)) if travel_type and travel_type != c.all_travel_types[0]: # FlightTravel if travel_type_query.value == c.flight_type[0]: self.q = self.q.join(FlightTravel) flight_code_id = request.args.get("flight_code_id", None) if flight_code_id != None: self.q = self.q.filter( FlightTravel.flight_code_id == flight_code_id) self.search_params.append((_("Номер Рейса"), FlightCode.query.filter_by( id=flight_code_id).first())) travel_in_out = request.args.get("travel_departure_outer", "all_travel") if travel_in_out != "all_travel": self.q = self.q.join( FlightCode, FlightTravel.flight_code_id == FlightCode.id) if travel_in_out == "outer_travel": self.q = self.q.filter( FlightCode.from_country != current_country) elif travel_in_out == "domestic_travel": self.q = self.q.filter( FlightCode.from_country == current_country) self.search_form.travel_departure_outer.default = travel_in_out # TrainTravel elif travel_type_query.value == c.train_type[0]: self.q = self.q.join(TrainTravel) train_id = request.args.get("train_id", None) if train_id != None: self.q = self.q.filter(TrainTravel.train_id == train_id) travel_in_out = request.args.get("travel_departure_outer", "all_travel") if travel_in_out != "all_travel": self.q = self.q.join(Train, TrainTravel.train_id == Train.id) if travel_in_out == "outer_travel": self.q = self.q.filter( Train.from_country != current_country) elif travel_in_out == "domestic_travel": self.q = self.q.filter( Train.from_country == current_country) self.search_form.travel_departure_outer.default = travel_in_out self.search_params.append( (_("Поезд"), Train.query.filter_by(id=train_id).first())) # Blockpost elif travel_type_query.value == c.blockpost_type[0]: self.q = self.q.join(BlockpostTravel) arrival_date = request.args.get("arrival_date", None) if arrival_date: self.q = self.q.filter( BlockpostTravel.date == arrival_date) self.search_form.arrival_date.default = parse_date( arrival_date) blockpost_region_id = request.args.get("blockpost_region_id", "-1") if blockpost_region_id != "-1": self.q = self.q.filter( BlockpostTravel.region_id == blockpost_region_id) self.search_form.blockpost_region_id.default = blockpost_region_id self.search_params.append( (_("Блокпост"), Region.query.filter_by( id=blockpost_region_id).first().name)) # Auto elif (travel_type_query.value, travel_type_query.name) in c.various_travel_types: self.q = self.q.join(VariousTravel) arrival_date = request.args.get("arrival_date", None) if arrival_date: self.q = self.q.filter(VariousTravel.date == arrival_date) self.search_form.arrival_date.default = parse_date( arrival_date) border_id = request.args.get("auto_border_id", "-1") if border_id != "-1": self.search_form.auto_border_id.default = border_id border_list = [ ("auto_border_id", self.search_form.auto_border_id), ("foot_border_id", self.search_form.foot_border_id), ("sea_border_id", self.search_form.sea_border_id) ] for border_type in border_list: if border_type[0] in request.args: if request.args[border_type[0]] != "-1": border_id = request.args[border_type[0]] border_type[1].default = border_id self.q = self.q.filter( VariousTravel.border_control_id == border_id) self.search_params.append( (_("Граница"), BorderControl.query.filter_by( id=border_id).first().name)) break self.search_form.process()
def test_create_participant(admin_client): with patch('juleol.db.Tastings') as TastingsMock: TastingsMock.query.filter.return_value.first.return_value = None ret = admin_client.post('/admin/2000/participant') assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/' ret = admin_client.get('/admin/') assert b'Invalid year' in ret.data test_tasting = db.Tastings() test_tasting.year = 2000 test_beer = db.Beers() test_beer.name = 'test beer' test_beer.number = 1 test_tasting.beers = [test_beer] test_tasting.participants = [] TastingsMock.query.filter.return_value.first.return_value = test_tasting ret = admin_client.post('/admin/2000/participant', data={'name': 'test'}) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Invalid form data' in ret.data ret = admin_client.post('/admin/2000/participant', data={ 'name': 'test', 'email': 'email-invalid' }) assert ret.status_code == 302 assert ret.headers['Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Invalid form data' in ret.data with patch('juleol.db.Beers') as BeersMock: # noqa: F841 with patch('juleol.db.Participants') as ParticpantsMock: with patch('juleol.db.ScoreTaste') as MockScoreTaste: with patch('juleol.db.ScoreAftertaste' ) as MockScoreAftertaste: with patch('juleol.db.ScoreLook') as MockScoreLook: with patch( 'juleol.db.ScoreSmell') as MockScoreSmell: with patch('juleol.db.ScoreXmas' ) as MockScoreXmas: with patch('juleol.db.db.session' ) as SessionMock: ret = admin_client.post( '/admin/2000/participant', data={ 'name': 'test', 'email': '*****@*****.**' }) assert ret.status_code == 302 assert ret.headers[ 'Location'] == 'http://localhost/admin/2000' assert ParticpantsMock.mock_calls[0][ 2]['tasting'] == test_tasting assert ParticpantsMock.mock_calls[0][ 2]['name'] == 'test' assert ParticpantsMock.mock_calls[0][ 2]['email'] == '*****@*****.**' assert MockScoreLook.mock_calls[0][2][ 'tasting'] == test_tasting assert MockScoreLook.mock_calls[0][2][ 'beer'] == test_beer assert MockScoreSmell.mock_calls[0][2][ 'tasting'] == test_tasting assert MockScoreSmell.mock_calls[0][2][ 'beer'] == test_beer assert MockScoreTaste.mock_calls[0][2][ 'tasting'] == test_tasting assert MockScoreTaste.mock_calls[0][2][ 'beer'] == test_beer assert MockScoreAftertaste.mock_calls[ 0][2]['tasting'] == test_tasting assert MockScoreAftertaste.mock_calls[ 0][2]['beer'] == test_beer assert MockScoreXmas.mock_calls[0][2][ 'beer'] == test_beer assert MockScoreXmas.mock_calls[0][2][ 'tasting'] == test_tasting assert SessionMock.mock_calls[0][ 0] == 'add' assert SessionMock.mock_calls[0][ 1] == (db.Participants( name='test', email='*****@*****.**', tasting=test_tasting), ) assert SessionMock.mock_calls[1][ 0] == 'add' assert SessionMock.mock_calls[1][ 1] == (db.ScoreLook( tasting=test_tasting, beer=test_beer, participant=db.Participants( name='test', email='*****@*****.**', tasting=test_tasting)), ) assert SessionMock.mock_calls[2][ 0] == 'add' assert SessionMock.mock_calls[2][ 1] == (db.ScoreSmell( tasting=test_tasting, beer=test_beer, participant=db.Participants( name='test', email='*****@*****.**', tasting=test_tasting)), ) assert SessionMock.mock_calls[3][ 0] == 'add' assert SessionMock.mock_calls[3][ 1] == (db.ScoreTaste( tasting=test_tasting, beer=test_beer, participant=db.Participants( name='test', email='*****@*****.**', tasting=test_tasting)), ) assert SessionMock.mock_calls[4][ 0] == 'add' assert SessionMock.mock_calls[4][ 1] == (db.ScoreAftertaste( tasting=test_tasting, beer=test_beer, participant=db.Participants( name='test', email='*****@*****.**', tasting=test_tasting)), ) assert SessionMock.mock_calls[5][ 0] == 'add' assert SessionMock.mock_calls[5][ 1] == (db.ScoreXmas( tasting=test_tasting, beer=test_beer, participant=db.Participants( name='test', email='*****@*****.**', tasting=test_tasting)), ) assert SessionMock.mock_calls[6][ 0] == 'commit' SessionMock.reset_mock() SessionMock.commit.side_effect = exc.SQLAlchemyError( ) ret = admin_client.post( '/admin/2000/participant', data={ 'name': 'test', 'email': '*****@*****.**' }) assert ret.status_code == 302 assert ret.headers[ 'Location'] == 'http://localhost/admin/2000' ret = admin_client.get('/admin/2000') assert b'Error creating participant' in ret.data assert SessionMock.mock_calls[7][ 0] == 'rollback'
def test_update_beer(admin_client): with patch('juleol.db.Beers') as BeersMock: # noqa: F841 db.Beers.query.filter.return_value.first.return_value = None ret = admin_client.put('/admin/beer/1') assert ret.status_code == 404 assert json.loads(ret.data)['error'] == 'Invalid beer id' test_beer = db.Beers() test_beer.id = 1 test_beer.number = 1 test_beer.name = 'test beer 1' db.Beers.query.filter.return_value.first.return_value = test_beer ret = admin_client.put('/admin/beer/1') assert ret.status_code == 400 assert json.loads(ret.data)['error'] == 'Invalid arguments' with patch('juleol.db.db.session') as SessionMock: ret = admin_client.put('/admin/beer/1', data={'name': 'new name'}) assert ret.status_code == 200 assert json.loads(ret.data)['message'] == 'Beer name updated' assert test_beer.name == 'new name' assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_beer, ) assert SessionMock.mock_calls[1][0] == 'commit' SessionMock.reset_mock() SessionMock.commit.side_effect = exc.SQLAlchemyError() ret = admin_client.put('/admin/beer/1', data={'name': 'new name'}) assert ret.status_code == 500 assert json.loads(ret.data)['error'] == 'Error updating beer name' assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_beer, ) assert SessionMock.mock_calls[1][0] == 'commit' assert SessionMock.mock_calls[2][0] == 'rollback' with patch('juleol.db.Heats') as HeatsMock: # noqa: F841 test_heat = db.Heats() test_heat.id = 1 test_heat.name = 'test' db.Heats.query.filter.return_value.filter.return_value.first.return_value = None ret = admin_client.put('/admin/beer/1', data={'heat': 1}) assert ret.status_code == 404 assert json.loads(ret.data)['error'] == 'Invalid heat' db.Heats.query.filter.return_value.filter.return_value.first.return_value = test_heat SessionMock.reset_mock() SessionMock.commit.side_effect = None ret = admin_client.put('/admin/beer/1', data={'heat': 1}) assert ret.status_code == 200 assert json.loads(ret.data)['message'] == 'Beer heat updated' assert test_beer.name == 'new name' assert SessionMock.mock_calls[0][0] == 'add' assert SessionMock.mock_calls[0][1] == (test_beer, ) assert SessionMock.mock_calls[1][0] == 'commit' assert test_beer.heat == test_heat SessionMock.commit.side_effect = exc.SQLAlchemyError() SessionMock.reset_mock() ret = admin_client.put('/admin/beer/1', data={'heat': 1}) assert ret.status_code == 500 assert json.loads( ret.data)['error'] == 'Error updating beer heat' assert SessionMock.mock_calls[2][0] == 'rollback'