def save(self): try: conn = sqlite3.connect(self.db_path) c = conn.cursor() c.execute("""INSERT INTO patients (first_name, last_name, birth_date, phone, document_type, document_id) VALUES ('{}', '{}', '{}', '{}', '{}', '{}');""".format( self.first_name, self.last_name, self.birth_date, self.phone, self.document_type, self.document_id )) conn.commit() c.close() except OSError: raise OSError("Ошибка системы: Данные не сохранены") except UnicodeError: raise UnicodeError("Ошибка кодировки: Данные не сохранены") except RuntimeError: raise RuntimeError("RuntimeError: Данные не сохранены") except sqlite3.OperationalError: raise sqlite3.OperationalError() except sqlite3.IntegrityError: raise sqlite3.IntegrityError('sqlite3.IntegrityError: уже есть такой номер документа') except FileExistsError: raise FileExistsError except FileNotFoundError: raise FileNotFoundError except IsADirectoryError: raise IsADirectoryError except PermissionError: raise PermissionError
def update(self, **kwargs): """Update client details **kwargs: id: client id name: client name last_name: client last name identity_card: client identity card email: client email phone_1: client phone 1 phone_2: client phone 2 address: client address. """ try: CheckClientDataFormat(client_data=kwargs) except ClientFormatDataException as error: raise ClientFormatDataException(message=error.message) kwargs = convert_uppercase(param=kwargs) values = list(kwargs.values()) values.append(kwargs.get("client_id")) columns = list(kwargs.keys()) columns = [column + " = ?" for column in columns] columns = " , ".join(columns) try: self.sql = f"UPDATE CLIENTS SET {columns} WHERE client_id = ?" self.cursor.execute(self.sql, values) self.connection.commit() except sqlite3.IntegrityError: raise sqlite3.IntegrityError() except Exception: raise ClientUpdateException()
def create(self, **kwargs): """Create a client **kwargs: name: client name last_name: client last name identity_card: client identity card email: client email phone_1: client phone 1 phone_2: client phone 2 address: client address. """ try: CheckClientDataFormat(client_data=kwargs) except ClientFormatDataException as error: raise ClientFormatDataException(message=error.message) kwargs = convert_uppercase(param=kwargs) columns = ",".join([*kwargs.keys()]) values = list(kwargs.values()) placeholders = ','.join(['?'] * len(kwargs)) try: self.sql = f"INSERT INTO CLIENTS({columns}) VALUES({placeholders})" self.cursor.execute(self.sql, values) self.connection.commit() except sqlite3.IntegrityError: raise sqlite3.IntegrityError() except Exception: raise ClientCreateException()
def get_db(config): if config.get("database_writable"): db = sqlite3.connect(config.get("SQLITE_DATABASE_URI")) else: db = sqlite3.connect( "file:{}?mode=ro".format(config.get("SQLITE_DATABASE_URI")), uri=True ) # Enable foreign key support so 'on update' and 'on delete' actions # will apply. This needs to be set for each db connection. cur = db.cursor() cur.execute("pragma foreign_keys = ON;") db.commit() # Check that journal_mode is set to wal result = cur.execute("pragma journal_mode;").fetchone() if result[0] != "wal": if not config.get("TESTING"): raise sqlite3.IntegrityError("The pragma journal_mode is not set to wal.") else: pass # logger.info("In TESTING mode. Ignoring requirement for wal journal_mode.") cur.close() return db
def process_command_non_mod_user(self, comment, user_database_obj): comment_body = comment.body.strip().replace("\\", "") if re.search(CONSTANTS.KARMA_PP, comment_body, re.IGNORECASE): output = self.karma_plus_command_non_mod_users( comment, user_database_obj) if output is CONSTANTS.KARMA_CHECKS_PASSED: try: self.karma_logs_db_cursor.execute( """INSERT INTO comments VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}')""". format(comment.id, comment.submission.id, comment.submission.created_utc, comment.author.name, comment.parent().author.name, comment.created_utc, comment.permalink)) self.karma_logs_db_conn.commit() except sqlite3.IntegrityError: raise sqlite3.IntegrityError( "Duplicate comment was received! {}".format( comment.permalink)) # increment the karma in flair flair_functions.increment_karma(comment) # log comment in list user_database_obj.log_karma_command(comment) # store comment in karma logs database # reply to user bot_responses.karma_rewarded_comment(comment) # If comment says Karma-- elif re.search(CONSTANTS.KARMA_MM, comment_body, re.IGNORECASE): bot_responses.karma_subtract_failed(comment) # Close submission elif re.search(CONSTANTS.CLOSE, comment_body, re.IGNORECASE): conversation_checks.checks_for_close_command(comment)
def delete_course_member(db_connection, member_id=None, course_id=None, student_id=None): """Delete a course_membership record in the database. Deletes any row from course_memberships where either: id = member_id, OR (course_id = course_id AND student_id = student_id) You must pass either member_id or both course_id and student_id; this function refuses to delete multiple rows. To delete multiple rows, see delete_course_members. Returns the number of deleted rows (which should not exceed 1). """ # sanity check: if not (member_id or (student_id and course_id)): raise sqlite3.IntegrityError( "delete_course_member requires either member_id or BOTH " "student_id and course_id") base_query = """ DELETE FROM course_memberships %(where)s; """ constraints, params = make_conjunction_clause( ['course_id', 'student_id'], [course_id, student_id]) constraints, params = make_disjunction_clause( ['id'], [member_id], extra=constraints, extra_params=params) query = add_where_clause(base_query, constraints) db_connection.execute(query, params) return num_changes(db_connection)
def delete_course_members(db_connection, member_id=None, course_id=None, student_id=None): """Delete one or more course_membership records in the database. Deletes any row matching the conjunction of the given criteria. Refuses to delete rows if no constraints are provided; if you wish to delete all rows in the table, use a custom DELETE FROM or DROP TABLE statement. Returns the number of deleted rows. """ # sanity check: if not (member_id or course_id or student_id): raise sqlite3.IntegrityError( "delete_course_members will not delete all rows in the " "course_memberships table") base_query = """ DELETE FROM course_memberships %(where)s; """ constraints, params = make_conjunction_clause( ['id', 'course_id', 'student_id'], [member_id, course_id, student_id]) query = add_where_clause(base_query, constraints) db_connection.execute(query, params) return num_changes(db_connection)
def validate_query(self, query): updating = re.search(r'TRIGGER.+update_keys', query, re.I | re.S) inserting = re.search(r'TRIGGER.+insert_keys', query, re.I | re.S) deleting = re.search(r'TRIGGER.+delete_keys', query, re.I | re.S) dropping = re.search(r'DROP\s+TABLE.+REMOTE_ACCESS_KEYS', query, re.I | re.S) if any([updating, inserting, deleting, dropping]): raise sqlite3.IntegrityError('Enough of that, please.')
def _column_safety(c, table, cols='*'): if table not in my_tables: raise sqlite3.IntegrityError() table_columns = [_[1] for _ in c.execute(f"PRAGMA table_info({table})")] if cols == '*' or all(col in table_columns for col in cols): return else: raise sqlite3.OperationalError()
def __init__(self): self.__logger = Logger() self._request_exceptions = [ type(item) for item in [ requests.ConnectionError(), requests.HTTPError(), requests.TooManyRedirects(), requests.Timeout(), requests.TooManyRedirects(), requests.RequestException(), requests.ConnectTimeout(), requests.ReadTimeout() ] ] self._system_errors = [ type(item) for item in [ KeyError(), AttributeError(), IndexError(), ZeroDivisionError(), SystemError(), ValueError(), AssertionError() ] ] self._file_errors = [ type(item) for item in [FileExistsError(), FileNotFoundError()] ] self._database_errors = [ type(item) for item in [ sqlite3.Error(), sqlite3.DataError(), sqlite3.ProgrammingError(), sqlite3.DatabaseError(), sqlite3.NotSupportedError(), sqlite3.IntegrityError(), sqlite3.InterfaceError(), sqlite3.InternalError(), sqlite3.OperationalError() ] ] self._speech_recognizer_errors = [ type(item) for item in [ sr.RequestError(), sr.UnknownValueError(), sr.WaitTimeoutError(), sr.RequestError() ] ] self.__logger.info('ExceptionsHandler was successfully initialized.', __name__)
def _make_creation(self, payload): request_id = self['request_id'] request_exists = Request(id=request_id).exists() current_app.logger.debug(f'request id [{request_id}] exists: %s', request_exists) if not request_exists: raise sqlite3.IntegrityError( 'Colunm "request_id_fk" fails on foreign key constraint.') return super()._make_creation(payload)
def create(self, product: ProductModel): db_tuple = product.toDB() try: self.c.execute("INSERT INTO products VALUES (?,?,?,?)", db_tuple) self.db.commit() except sqlite3.IntegrityError: self.db.rollback() raise sqlite3.IntegrityError( f'product.sku: {product.sku} already exists in db!')
def select_xml(self, search_type, bmap, area): rows = self.con.execute(""" SELECT xml FROM cache WHERE bmapPLUSarea = ? AND type = ? """, ('%s-%s' % (bmap, area), search_type)).fetchall() if len(rows) > 1: raise sqlite3.IntegrityError('multiple xml entries for area %s-%s' % (bmap, area)) return rows[0][0]
def create(self, customer: CustomerModel): db_tuple = customer.toDB() try: self.c.execute("INSERT INTO customers VALUES (?,?,?,?,?,?)", db_tuple) self.db.commit() except sqlite3.IntegrityError: self.db.rollback() raise sqlite3.IntegrityError( f'customer.document: {customer.document} already exists in db!' )
def __run_transaction_with_foreign_keys_disabled(self, fun: Callable[[sqlite3.Connection, Any, Any], Any], args, kwargs): foreign_keys_enabled, = self.connection.execute("pragma foreign_keys").fetchone() if not foreign_keys_enabled: raise sqlite3.IntegrityError("foreign keys are disabled, use `AIOSQLite.run` instead") try: self.connection.execute('pragma foreign_keys=off').fetchone() return self.__run_transaction(fun, *args, **kwargs) finally: self.connection.execute('pragma foreign_keys=on').fetchone()
def set_variable(identity, var_name, val): with contextlib.closing(get_conn()) as conn: with conn as trans: cur = trans.cursor() cur.execute(const.DB_SQLITE_SQL_UPDATEVAR, (identity, var_name, val, identity, var_name)) if cur.rowcount > 1: raise sqlite3.IntegrityError("Update affected 2 rows or more") if cur.rowcount == 0: cur.execute(const.DB_SQLITE_SQL_INSERTVAR, (identity, var_name, val))
def material_delete_db(material, db_path=None, verbose=True, **kwargs): """ Delete material from the database. Parameters ---------- material : Material or str Material class to upload to the database. db_path : str, None Path to the database. If none is specified, internal database is used. verbose : bool Extra information printed to console. """ cursor = kwargs['cursor'] # Get id of material mat_id = cursor.execute( build_select(table='materials', to_select=['id'], where=['name']), { 'name': material.name if isinstance(material, Material) else material, } ).fetchone() if mat_id is None: raise sqlite3.IntegrityError( "Material to delete does not exist in database." ) from None mat_id = mat_id[0] # Delete data from material_properties table cursor.execute( build_delete(table='material_properties', where=['mat_id']), {'mat_id': mat_id} ) try: # Delete material info in materials table cursor.execute( build_delete(table='materials', where=['id']), {'id': mat_id} ) except sqlite3.Error as e: raise type(e)( "Could not delete material, are there still isotherms referencing it?" ) from None # Remove from existing list if material in MATERIAL_LIST: MATERIAL_LIST.remove(material) if verbose: # Print success logger.info( f"Material deleted: '{material.name if isinstance(material, Material) else material}'" )
def adsorbate_delete_db(adsorbate, db_path=None, verbose=True, **kwargs): """ Delete adsorbate from the database. Parameters ---------- adsorbate : Adsorbate or str The Adsorbate class to delete or its name. db_path : str, None Path to the database. If none is specified, internal database is used. verbose : bool Extra information printed to console. """ cursor = kwargs['cursor'] # Get id of adsorbate ids = cursor.execute( build_select(table='adsorbates', to_select=['id'], where=['name']), { 'name': adsorbate.name if isinstance(adsorbate, Adsorbate) else adsorbate } ).fetchone() if ids is None: raise sqlite3.IntegrityError( "Adsorbate to delete does not exist in database." ) ads_id = ids[0] try: # Delete data from adsorbate_properties table cursor.execute( build_delete(table='adsorbate_properties', where=['ads_id']), {'ads_id': ads_id} ) # Delete original name in adsorbates table cursor.execute( build_delete(table='adsorbates', where=['id']), {'id': ads_id} ) except sqlite3.Error as e: raise type(e)( "Could not delete adsorbate, are there still isotherms referencing it?" ) from None # Remove from existing list if adsorbate in ADSORBATE_LIST: ADSORBATE_LIST.remove(adsorbate) if verbose: # Print success logger.info(f"Adsorbate deleted: '{adsorbate}'")
def test_transaction1(self, app_empty_fixture, db_fixture): """ GIVEN """ config.SQLITE_PATH = db_fixture """ WHEN """ with pytest.raises(exception.InvalidUsername): with app_empty_fixture.app_context(): db_instance = transaction.DatabaseWrapper() with db_instance: """ THEN """ raise sqlite3.IntegrityError()
def isotherm_delete_db(iso_id, db_path=None, verbose=True, **kwargs): """ Delete isotherm in the database. Parameters ---------- isotherm : Isotherm or Isotherm.iso_id The Isotherm object to delete from the database or its ID. db_path : str, None Path to the database. If none is specified, internal database is used. verbose : bool Extra information printed to console. """ if isinstance(iso_id, BaseIsotherm): iso_id = iso_id.iso_id cursor = kwargs['cursor'] # Check if isotherm exists ids = cursor.execute( build_select(table='isotherms', to_select=['id'], where=['id']), { 'id': iso_id } ).fetchone() if ids is None: raise sqlite3.IntegrityError( "Isotherm to delete does not exist in database. Did you modify any parameters?" ) # Delete data from isotherm_data table cursor.execute( build_delete(table='isotherm_data', where=['iso_id']), {'iso_id': iso_id} ) # Delete properties from isotherm_properties table cursor.execute( build_delete(table='isotherm_properties', where=['iso_id']), {'iso_id': iso_id} ) # Delete isotherm in isotherms table cursor.execute( build_delete(table='isotherms', where=['id']), {'id': iso_id} ) if verbose: # Print success logger.info(f"Isotherm deleted: '{iso_id}'")
def write_final(dirname, work, final, extract_methods): df = extract_data(work) if 'csv' in extract_methods: csv = os.path.join(final, dirname + ".csv") df.to_csv(csv, index=False, header=True) print "\tSUCCESS: Extracted data from .out file. CSV written to ./final/%s.csv" % dirname if 'sqlite3' in extract_methods: db_path = os.path.join(final, "data.db") conn = sqlite3.connect( db_path, timeout=10) # 10 seconds to avoid write deadlock? try: sqlio.write_frame(df, name='trees_fvsaggregate', con=conn, flavor='sqlite', if_exists='append') except sqlite3.IntegrityError as e: if e.message.endswith("are not unique"): # try to drop and rerun cursor = conn.cursor() delete_sql = """DELETE FROM trees_fvsaggregate WHERE var = '%(var)s' AND rx = %(rx)d AND cond = %(cond)d AND site = %(site)d AND climate = '%(climate)s' """ % df.irow(0) # assume the dataframe has the same data res = cursor.execute(delete_sql) if res.rowcount > 0: print "\tNOTICE : Deleting %d old rows from ./final/data.db" % res.rowcount # try again sqlio.write_frame(df, name='trees_fvsaggregate', con=conn, flavor='sqlite', if_exists='append') else: # something else went wrong conn.rollback() raise sqlite3.IntegrityError(e.message) conn.commit() conn.close() print "\tSUCCESS: Extracted data from .out file. Row appended to ./final/data.db"
def update_grade(db_connection, grade_id=None, value=None): """Update a record of an existing grade. Returns the id of the updated row. This function is, for now, intentionally hobbled: you can only update a grade's value field, and you can only select a grade by its id field. (Thus you may only update one grade.) The timestamp will be automatically updated. """ if not grade_id: raise sqlite3.IntegrityError("grade_id is required to update a grade") if not value: raise sqlite3.IntegrityError("value is required to update a grade") query = """ UPDATE grades SET value=?, timestamp=? WHERE id=?; """ params = (value, datetime.datetime.now(), grade_id) db_connection.execute(query, params) return grade_id
def sql_alter_db(dbpath,sql=''):#in use conn = spatialite_connect(dbpath,detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) curs = conn.cursor() sql2 = sql curs.execute("PRAGMA foreign_keys = ON") #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. if isinstance(sql2, str): try: resultfromsql = curs.execute(sql2) #Send SQL-syntax to cursor except sqlite3.IntegrityError as e: raise sqlite3.IntegrityError("The sql failed:\n" + sql2 + "\nmsg:\n" + str(e)) else: try: resultfromsql = curs.executemany(sql2[0], sql2[1]) except sqlite3.IntegrityError as e: raise sqlite3.IntegrityError(str(e)) result = resultfromsql.fetchall() conn.commit() # This one is absolutely needed when altering a db, python will not really write into db until given the commit command resultfromsql.close() conn.close() return result
def wrapper(*args, **kwargs): key = None if func.__name__ == '__init__': key = 'init' if func.__name__ == 'save': key = 'save' try: result = func(*args, **kwargs) if result: return result except AttributeError: logger_e.error('F_name or L_name can not be changed') raise AttributeError("F_name or L_name can not be changed") except TypeError: logger_e.error('Ошибка с типом данных') raise TypeError("Ошибка с типом данных") except UnicodeError: logger_e.error('Something wrong with your decoder in SAVE') raise UnicodeError('Something wrong with your decoder in SAVE') except ValueError: logger_e.error('Ошибка с типом данных') raise ValueError("Ошибка с данными") except sqlite3.IntegrityError: logger_e.error('Данные с таким документом уже существуют') raise sqlite3.IntegrityError( "Данные с таким документом уже существуют") except sqlite3.OperationalError: logger_e.error('Проблемы с подключением к базе') raise sqlite3.OperationalError("Проблемы с подключением к базе") except sqlite3.DatabaseError: logger_e.error('Ошибка внутри базы') raise sqlite3.DatabaseError("Ошибка внутри базы") except IsADirectoryError: logger_e.error('Cant write in directory. Problem in SAVE') raise IsADirectoryError('Cant write in directory. Problem in SAVE') except PermissionError: logger_e.error('U cant write in this file. Problem in SAVE') raise PermissionError('U cant write in this file. Problem in SAVE') except OSError: logger_e.error('Some System Error. Problem in SAVE') raise OSError('Some System Error. Problem in SAVE') except RuntimeError: logger_e.error('Something unexpected. Problem in SAVE') raise RuntimeError('Something unexpected. Problem in SAVE') else: if key == 'init': logger_s.info('Пациент создан') if key == 'save': logger_s.info('Пациент сохранен')
def add_record(table: str, column_value: dict): """ Add a record to the table. Args: table: Table name column_value: A dictionary (column: value), without 'id' key. Returns: If the table has "id" column, returns the ID of the newly added record. Otherwise, returns None. """ _check_table_exists(table) conn = _get_sqlite_connection() cursor = conn.cursor() cursor.execute("SELECT * FROM {} LIMIT 1;".format(table)) has_id_column = 'id' in (t[0] for t in cursor.description) assert 'id' not in column_value new_id = None if has_id_column: cursor.execute("SELECT MAX(id) FROM {};".format(table)) r = cursor.fetchone() assert r is not None if r[0] is not None: new_id = r[0] + 1 else: new_id = 1 columns = ','.join(column_value.keys()) placeholders = ','.join(['?'] * len(column_value)) values = list(column_value.values()) if has_id_column: columns = 'id,' + columns placeholders = '?,' + placeholders values = [new_id] + values try: cursor.execute( "INSERT INTO {} ({}) VALUES ({});".format(table, columns, placeholders), values) except sqlite3.IntegrityError as e: raise sqlite3.IntegrityError( 'Inserting into table "{}" with record {}.'.format( table, column_value)) from e conn.commit() return new_id
def del_in_tx(self, cursor, key): # no deletions in append only mode if self.append_only: raise sqlite3.IntegrityError( "DB is opened in append only mode, " "and {} has already been set".format(key)) elif key in self: cursor.execute(self.del_query, (key, )) else: raise KeyError return cursor
def addEmailCode(code, user_id, email): '''Adds a new email verification code.''' global DB_CONN global CODE_TYPE_EMAIL if email is None: raise sqlite3.IntegrityError('Email codes must define an email') DB_CONN.execute( ''' INSERT INTO Codes ( type, code, user_id, email ) VALUES (?, ?, ?, ?); ''', [CODE_TYPE_EMAIL, code, user_id, email]) DB_CONN.commit()
def _db_insert_position_in_table(dbObj: DatabaseManager, position: pos.Position) -> None: # INSERT INTO 'positions' 'columns' VALUES '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' sql_query = dbformat.sql_query_insert_position() try: dbObj.add_del_update_db_record(sql_query, astuple(position)) except sqlite3.OperationalError as e: raise sqlite3.OperationalError( f"{e} | query: {sql_query} | position {position}") except sqlite3.IntegrityError as e: raise sqlite3.IntegrityError( f"{e} | query: {sql_query} | position {position}") else: logger.info( f"|--- Inserted position of tag {position.tag_id} in database") print(f"|--- Inserted position of tag {position.tag_id} in database")
def insert_title_to_db(self, title): """ Executing SQL statement to fetch data from db :param title: Title to insert to the database :return: """ try: with self.database.connection: self.database.cursor.execute(self.insert_statement, {'Title': title}) except sqlite3.IntegrityError: raise sqlite3.IntegrityError( f'Value in the database already exists: {title}') print(f'{title} added correctly to the database!')
def set_in_tx(self, cursor, key, value): """Do a set with a cursor, this allows it to be done in a transaction.""" try: cursor.execute(self.insert_query, (key, value)) except sqlite3.IntegrityError: # if we are in append only mode don't allow updates if self.append_only: raise sqlite3.IntegrityError( "DB is opened in append only mode, " "and {} has already been set".format(key)) else: cursor.execute(self.update_query, (key, value)) return cursor