def update(self):
if not os.path.isfile("mtg.db"):
raise OperationalError(
"mtg database file does not exist, run buildDatabase.exe to recreate the database"
)
print("Comparing online database to local database...")
self.numberOfCards = self.getLastId()
self.numberOfSets = self.readSetCounter()
onlineSets = self.readSetsFromOnline()
localSets = self.readSetsFromFile()
sets = self.compareSetLists(onlineSets, localSets)
connection = self.createConnection()
memory = connection.cursor()
cards = []
if len(sets) > 0:
print("Missing sets:")
for s in sets:
print(s[1])
print()
else:
print("Database is already up to date")
input("Press enter to continue:")
sys.exit()
for i, set in enumerate(sets):
try:
print("searching for", set[1])
cards = Card.where(set=set[0]).all()
self.insertSet(set, memory)
except OperationalError:
raise OperationalError("Error accessing database")
print("gathered cards for ", set[1], "set")
for card in cards:
cardProperties = self.getCardProperties(
card, self.numberOfCards, set[0])
self.insertCard(cardProperties, memory)
self.numberOfCards += 1 # increment the id used for each card
connection.commit()
self.numberOfSets += 1 # increment the self.numberOfSets used to keep track of which set of the total we are on
print(set[1], "complete,", (i + 1), "of", len(sets))
print(self.numberOfCards)
self.setNewId(self.numberOfCards)
self.writeSetCounter(self.numberOfSets)
# this is used to space out the requests since it will throw an error if the program grabs all the card at once
if self.numberOfSets % 10 == 0:
time.sleep(30)
def _fetch(self, index):
if self.connection.detect_types and index < len(self._row_cast_map):
converter = self._row_cast_map[index]
if converter is not None:
return converter(self._stmt.column_blob(index))
coltype = self._stmt.column_type(index)
if coltype == SQLITE_NULL:
return None
elif coltype == SQLITE_INTEGER:
return self._stmt.column_int(index)
elif coltype == SQLITE_FLOAT:
return self._stmt.column_double(index)
elif coltype == SQLITE_TEXT:
text = self._stmt.column_text(index)
text_factory = self.connection.text_factory
if text_factory is str:
try:
return text.decode()
except UnicodeDecodeError:
raise OperationalError("Could not decode to UTF-8 column {!r} with text {}".format(self.description[index][0], str(text)[1:]))
else:
return text_factory(text)
elif coltype == SQLITE_BLOB:
return self._stmt.column_blob(index)
else:
assert False
def test_add_holiday_returns_negative_one_on_exception_thrown(connection):
connection.commit.side_effect = OperationalError()
holiday = Holiday(connection)
actual = holiday.add_holiday(datetime.now())
assert actual is -1
def get_rows(self, table_name, param='1=1', fetch_last=False):
"""
Returns rows from table
:param string table_name: name of table in DB
:param string param: conditional parameter to retreive row data
:param bool fetch_last: returns last item added to table
"""
param = param if type(param) is not list else \
' AND '.join([x for x in param])
sql = """SELECT * FROM {} WHERE {};
""".format(table_name, param)
try:
c = self.conn.cursor().execute(sql)
# Dictionary output option
if self.dict_output:
query = [dict(row) for row in c.fetchall()]
else:
query = c.fetchall()
# Last item in query
if fetch_last:
return query[-1]
return query
# When SQL Query is broken
except OperationalError:
raise (OperationalError("Cannot Execute SQL Query: %s" % sql))
def test_remove_holiday_returns_false_on_exception_throw(connection):
connection.commit.side_effect = OperationalError()
holiday = Holiday(connection)
actual = holiday.remove_holiday(randint(1,999))
assert actual is False
def test_get_row_id_by_date_returns_negative_one_on_exception(connection):
connection.execute.side_effect = OperationalError()
holiday = Holiday(connection)
actual = holiday.get_row_id_by_date(datetime.now())
assert actual is -1
def test_is_holiday_returns_true_when_exception_thrown(connection):
connection.fetchall.side_effect = OperationalError()
holiday = Holiday(connection)
actual = holiday.is_holiday(datetime.now())
assert actual is True
def create_aggregate(self, name, num_params, aggregate_class):
self._check()
self._function_pinboard[aggregate_class] = None
rc = self._db.create_function(name.encode(), num_params, 0, id(aggregate_class), None, _aggregate_step, _aggregate_final)
if rc != SQLITE_OK:
raise OperationalError("Error creating aggregate")
def test_insert_new_day_returns_false_on_failure(connection):
connection.execute.side_effect = OperationalError()
punch = Punch(connection)
actual = punch.insert_new_day()
connection.commit.assert_not_called()
assert actual is False
def run_sql_statement(self, request, params=()):
try:
#print request
self.cursor.execute(request, params)
except OperationalError as o:
raise OperationalError(o.message + " : " + request)
return self.cursor.rowcount
def test_get_most_recent_day_returns_empty_tuple_on_error(connection):
connection.execute.side_effect = OperationalError()
punch = Punch(connection)
actual = punch.get_most_recent_day()
connection.fetchone.assert_not_called()
assert actual is ()
def test_get_punch_by_id_returns_empty_tuple_on_error(connection):
connection.execute.side_effect = OperationalError()
punch = Punch(connection)
actual = punch.get_punch_by_id(1)
connection.execute.assert_called_once()
connection.fetchone.assert_not_called()
assert actual is ()
def run(self, statement, DBNAME):
"""Execute the sql in the database and return the results. The results
are a list of tuples. Each tuple has 4 values
(title, rows, headers, status).
"""
#print self.conn
if not hasattr(self, 'conn') or not self.conn:
self._connect(DBNAME)
# Remove spaces and EOL
statement = statement.strip()
if not statement: # Empty string
yield (None, None, None, None)
# Split the sql into separate queries and run each one.
# Unless it's saving a favorite query, in which case we
# want to save them all together.
if statement.startswith("\\fs"):
components = [statement]
else:
components = sqlparse.split(statement)
#print components
for sql in components:
# Remove spaces, eol and semi-colons.
sql = sql.rstrip(";")
# \G is treated specially since we have to set the expanded output.
if sql.endswith("\\G"):
special.set_expanded_output(True)
sql = sql[:-2].strip()
if not self.conn and not (
sql.startswith(".open") or sql.lower().startswith("use")
or sql.startswith("\\u") or sql.startswith("\\?")
or sql.startswith("\\q") or sql.startswith("help")
or sql.startswith("exit") or sql.startswith("quit")):
_logger.debug(
"Not connected to database. Will not run statement: %s.",
sql)
#yield self.get_result2()
raise OperationalError("Not connected to database.")
# yield ('Not connected to database', None, None, None)
# return
cur = self.conn.cursor() if self.conn else None
try: # Special command
_logger.debug("Trying a dbspecial command. sql: %r", sql)
for result in special.execute(cur, sql):
yield result
except special.CommandNotFound: # Regular SQL
_logger.debug("Regular sql statement. sql: %r", sql)
cur.execute(sql)
yield self.get_result(cur)
def test_update_is_work_day_returns_false_on_failure(connection):
connection.execute.side_effect = OperationalError()
punch = Punch(connection)
# Do not want this test to depend on get_most_recent_day(),
# so it is mocked out, as it is tested separately.
punch.get_most_recent_day = Mock(return_value=(1, ))
actual = punch.update_is_work_day(True)
connection.commit.assert_not_called()
assert actual is False
async def _manage_operational_error(self,
allow_commit: bool = False
) -> AsyncIterator[None]:
"""Close the local database when an operational error is detected
Operational errors have to be treated with care since they usually indicate
that the current transaction has been rolled back. Since parsec has its own
in-memory cache for manifests, this can lead to complicated bugs and possibly
database corruptions (in the sense that a committed manifest might reference a
chunk of data that has not been successfully committed). See issue #1535 for an
example of such problem.
If an operational error is detected we simply close the connection and invalidate
the local database object while raising an FSLocalStorageOperationalError exception.
This way, we force the core to create new workspace storage objects and therefore
discarding any uncommited data.
"""
in_transaction_before = self._conn.in_transaction
# Safe context for operational errors
try:
try:
yield
# Extra checks for end of transaction
finally:
end_of_transaction_detected = (in_transaction_before and
not self._conn.in_transaction)
if not allow_commit and end_of_transaction_detected:
raise OperationalError(
"A forbidden commit/rollback has been detected")
# An operational error has been detected
except OperationalError as exception:
with trio.CancelScope(shield=True):
# Close the sqlite3 connection
try:
await self.run_in_thread(self._conn.close)
# Ignore second operational error (it should not happen though)
except OperationalError:
pass
# Mark the local database as closed
finally:
del self._conn
# Raise the dedicated operational error
raise FSLocalStorageOperationalError from exception
def execute(self, *args, **kwargs):
"""Executes the sql statement, but does not commit. Returns the cursor to commit
@return: DB and cursor instance following sql execution
"""
# Execute the query
cursor = self.connection.cursor()
try:
cursor.execute(*args, **kwargs)
except OperationalError as e:
raise OperationalError('{} when executing: {}'.format(
e.args, args[0]))
return cursor
def createTable():
create_table = """
CREATE TABLE URL_LINKS(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
URL TEXT NOT NULL
);
"""
with sqlite3.connect('url_links.db') as conn:
cursor = conn.cursor()
try:
cursor.execute(create_table)
except OperationalError as e:
if e.message == "table URL_LINKS already exists":
# The table already exists in the database, continue forward
pass
else:
raise OperationalError(
"There was an error creating the database table")
def select_col_names(connection: Connection, table_name):
"""Return column names for chosen table name
:param connection: SQLite Connection object
:param table_name: Name of table
:return: list
"""
if not isinstance(connection, Connection):
raise TypeError(
"Parameter 'connection' must be SQLite Connection object")
cursor = connection.cursor()
try:
cursor.execute(f"PRAGMA table_info({table_name});")
except Error as e:
raise e
fetch_cols = cursor.fetchall()
if not fetch_cols:
raise OperationalError(f"no such table: {table_name}")
else:
return [x[1] for x in fetch_cols]
def __update_subject(self, item_id: int, ignore_none=True, **kwargs):
params = {}
for k, v in kwargs.items():
if k in ['title', 'alt', 'status', 'tag_date', 'original_title', 'aka', 'subtype', 'languages', 'year', 'durations',
'current_season', 'episodes_count', 'seasons_count', 'archived', 'location', 'source'] \
and (not ignore_none or (ignore_none and v is not None)):
params[k] = v
if not params or len(params) == 0:
raise ValueError('No params to update')
cursor = self.__con.cursor()
cursor.execute(
'UPDATE movies SET last_update=DATETIME(\'now\'), %s WHERE id = %d'
% (', '.join(['%s = :%s' % (k, k) for k in params]), item_id),
params)
if cursor.rowcount != 1:
logger.error('Failed to update movie: %d', item_id)
self.__con.rollback()
raise OperationalError('Failed to update')
self.__con.commit()
return True
def _create_table(conn, cur):
# check if table exist
try:
cur.execute("""SELECT name FROM sqlite_master WHERE type='table' AND name='contacts'""")
if not cur.fetchone():
raise OperationalError("Table does not exist")
except OperationalError as e:
self.log.error(e)
cur.execute('''CREATE TABLE contacts
(
id INTEGER,
first_name character(128) NOT NULL,
last_name character(128),
phone_number character(1),
email_address VARCHAR NOT NULL,
CONSTRAINT contacts_pkey PRIMARY KEY (id)
)
''')
conn.commit()
LOG.debug('contacts table exist')
def _set_error(rc, errmsg=None):
throw()
if rc == SQLITE_OK:
return
if errmsg is None:
errmsg = sqlite3_errstr(rc).decode()
if rc in (
SQLITE_INTERNAL,
SQLITE_NOTFOUND):
raise InteralError(errmsg)
elif rc == SQLITE_NOMEM:
raise MemoryError()
elif rc in (
SQLITE_ERROR,
SQLITE_PERM,
SQLITE_ABORT,
SQLITE_BUSY,
SQLITE_LOCKED,
SQLITE_READONLY,
SQLITE_INTERRUPT,
SQLITE_IOERR,
SQLITE_FULL,
SQLITE_CANTOPEN,
SQLITE_PROTOCOL,
SQLITE_EMPTY,
SQLITE_SCHEMA,
):
raise OperationalError(errmsg)
elif rc == SQLITE_CORRUPT:
raise DatabaseError(errmsg)
elif rc == SQLITE_TOOBIG:
raise DataError(errmsg)
elif rc in (SQLITE_CONSTRAINT, SQLITE_MISMATCH):
raise IntegrityError(errmsg)
elif rc == SQLITE_MISUSE:
raise ProgrammingError(errmsg)
else:
raise DatabaseError(errmsg)
def _iterdecompress(self, cid, desc="response_cache/_iterdecompress"):
"""Iteratively decompress chunks retrieved from database by `context_identity`"""
decompressor = decompressobj()
with self.sqltransactions.concurrent(desc) as (_, execute):
query = """SELECT `mimetype` FROM `response_cache`
WHERE `context_identity` == ? LIMIT 1"""
mimetype, = execute(query, [cid]).fetchone() or [None]
if mimetype is None:
raise EOFError("No chunks found in response_cache")
else:
yield mimetype
query = """SELECT `chunk`,`mimetype` FROM `response_cache`
WHERE `context_identity` == ? ORDER BY `i` ASC"""
for chunk, _mimetype in execute(query, [cid]):
if mimetype != _mimetype:
raise OperationalError("Stored mimetypes of chunks differ")
decompressed_chunk = decompressor.decompress(chunk)
if decompressed_chunk:
yield decompressed_chunk.decode()
decompressed_chunk = decompressor.flush()
if decompressed_chunk:
yield decompressed_chunk.decode()
def readSetsFromOnline(self):
try:
setObjects = Set.where().all()
except OperationalError:
raise OperationalError("Error in connection to database")
sets = []
setInfo = []
for set in setObjects:
setInfo = [
set.code,
set.name.replace(u"\u2014", "-").replace(u"\u2022", "*")
]
if set.block is None:
setInfo.append("")
else:
setInfo.append(set.block)
sets.append(setInfo)
return sets
def run(self, statement, BUCKET_NAME):
"""Execute the sql in the database and return the results. The results
are a list of tuples. Each tuple has 4 values
(title, rows, headers, status).
"""
#print self.conn
if not hasattr(self, 'bucket') or not self.bucket:
self._connect(BUCKET_NAME)
# Remove spaces and EOL
statement = statement.strip()
if not statement: # Empty string
yield (None, None, None, None)
# Split the sql into separate queries and run each one.
# Unless it's saving a favorite query, in which case we
# want to save them all together.
if statement.startswith("\\fs"):
components = [statement]
else:
components = sqlparse.split(statement)
#print components
for sql in components:
# Remove spaces, eol and semi-colons.
sql = sql.rstrip(";")
# \G is treated specially since we have to set the expanded output.
if sql.endswith("\\G"):
special.set_expanded_output(True)
sql = sql[:-2].strip()
if not self.bucket and not (
sql.startswith(".open") or sql.lower().startswith("use")
or sql.startswith("\\u") or sql.startswith("\\?")
or sql.startswith("\\q") or sql.startswith("help")
or sql.startswith("exit") or sql.startswith("quit")):
_logger.debug(
"Not connected to database. Will not run statement: %s.",
sql)
#yield self.get_result2()
raise OperationalError("Not connected to database.")
# yield ('Not connected to database', None, None, None)
# return
cur = self.conn.cursor() if self.conn else None
if 1:
print(statement)
#export LANG=en_US.utf-8
#export LC_ALL=en_US.utf-8
k = Key(self.bucket)
stm = statement.split()
limit = 25
if len(stm) == 2:
kname, limit = stm
else:
kname = stm[0]
k.key = kname
k.open()
gzipped = GzipFile(None, 'rb', fileobj=k)
reader = csv.reader(io.TextIOWrapper(gzipped,
newline="",
encoding="utf-8"),
delimiter='^')
if 1:
data = []
for id, line in enumerate(reader):
if id >= int(limit): break
data.append([id + 1] + line)
try: # Special command
_logger.debug("Trying a dbspecial command. sql: %r", sql)
for result in special.execute(cur, sql):
yield result
except special.CommandNotFound: # Regular SQL
_logger.debug("Regular sql statement. sql: %r", sql)
#print(sql)
#cur.execute(sql)
yield self.get_result2(data)
def execute(self, statement, parameters=None):
"""Trigger exception"""
raise OperationalError('in testing')
def create_function(self, name, num_params, func, *, deterministic=False):
self._check()
self._function_pinboard[func] = None
rc = self._db.create_function(name.encode(), num_params, SQLITE_DETERMINISTIC if deterministic else 0, id(func), _function_callback, None, None)
if rc != SQLITE_OK:
raise OperationalError("Error creating function")
def raise_error(*args, **kwargs):
raise OperationalError('teto')
connection.execute.side_effect = OperationalError()
punch = Punch(connection)
actual = punch.get_punch_by_id(1)
connection.execute.assert_called_once()
connection.fetchone.assert_not_called()
assert actual is ()
@pytest.mark.parametrize(
"error,action,db_column,expected",
[
# Clock in
(None, 'in', 'clock_in', True),
(OperationalError(), 'in', 'clock_in', False),
# Start Lunch
(None, 'start', 'lunch_start', True),
(OperationalError(), 'start', 'lunch_start', False),
# End Lunch
(None, 'end', 'lunch_end', True),
(OperationalError(), 'end', 'lunch_end', False),
# Clock out
(None, 'out', 'clock_out', True),
(OperationalError(), 'out', 'clock_out', False),
])
def test_punch_actions(connection, error, action, db_column, expected):
id_ = 1
def connect(self):
try:
dbURI = "file:{}?mode=ro".format(pathname2url(self._databasePath))
self._connection = connect(dbURI, uri=True)
except OperationalError as oe:
raise OperationalError("Unable to find database")
