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
