def query(self, q_string, echo=None, to_numpy=True):
"""
Executes a SQL query string against the database.
Echoes the result back if echo=True or self.echo=True.
:param q_string: SQL query string
:param echo: echo back result, overwrites self.echo
:return: SQL result set or Cursor object
"""
words = q_string.upper().split(" ")
if "DROP" in words:
raise UserWarning("DROP queries should be performed manually.")
# Define cursor object
try:
self.cursor = self.connection.cursor()
except AttributeError:
raise Error("Connection to DB has not been established.")
# Execute query
try:
self.cursor.execute(q_string)
if self.auto_commit:
self.connection.commit()
except Error as e:
err_str = 'Error in query:\n"""\n%s\n"""' % q_string
raise Error(err_str) from e
# Fetch or return cursor
echo = self.echo if echo is None else echo
if echo and "SELECT" in words:
if to_numpy:
return np.array(self.cursor.fetchall())
else:
return self.cursor.fetchall()
def login(self, idno, password):
sql_login = """
SELECT type, lastname, firstname, middlename
FROM USERS
WHERE idno = ? AND password = ?;
"""
cur = self.connection.cursor()
cur.execute(sql_login, (idno, password))
row = cur.fetchone()
if row is not None:
if row[0] == 'A':
# we have an admin
return Admin(idno, row[1], row[2], row[3])
elif row[0] == 'S':
# we have a student
cur = self.connection.cursor()
cur.execute("SELECT unitlimit FROM STUDENTS WHERE idno = ?",
(idno, ))
row1 = cur.fetchone()
if row1 is not None:
return Student(idno, row[1], row[2], row[3], row1[0])
else:
raise Error("Database error")
else:
raise Error("Invalid user type")
else:
return None
def update(self, user):
try:
birthDate = datetime.datetime.strptime(user.birthDate, '%d/%m/%y')
except (ValueError, AttributeError) as e:
print('Error: {}'.format(e))
return False
try:
if user.name == '' or user.lastName == '' or user.email == '':
raise Error('empty fields')
#print('Error: empty fields')
return False
except Error as e:
print('Error: {}'.format(e))
return False
cursor = self.connection.cursor()
dataUser = (user.name, user.lastName, birthDate, user.email, user.id)
sql = """ UPDATE {} SET name=?, lastName=?, birthDate=?, email=? WHERE id=?;""".format(
self.table)
try:
cursor.execute(sql, dataUser)
if cursor.rowcount == 0:
raise Error(' no found row, no updated3')
#print('Error: no found row, no updated')
return False
except Error as e:
print('Error: {}'.format(e))
return False
return True
def create_tables(self):
"""
Creates tables for database
:return: None
"""
sql_portfolio = """create table if not exists portfolio (
id integer primary key,
stock text not null);"""
sql_transaction = """create table if not exists transactions (
id integer primary key,
stock_id integer not null,
date text not null,
number_of_shares real not null,
price_per_share real not null,
transaction_type text not null,
FOREIGN KEY (stock_id) REFERENCES portfolio (id));
"""
try:
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_portfolio)
dataset.execute(sql_transaction)
con.close()
except Error:
print(Error.with_traceback())
finally:
con.close()
def delete_stock(self, stock):
"""
Deletes a stock and all transactions
:param stock: Stock object
:return: None
"""
if not isinstance(stock, Stock):
raise ValueError("Argument is not type Stock")
if len(stock.transactions) != 0:
for x in stock.transactions:
self.delete_transactions(x)
sql_statement = """delete from portfolio
where id = ?"""
argument_list = []
argument_list.append(stock.stock_id)
try:
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_statement, argument_list)
con.commit()
except Error:
print(Error.with_traceback())
finally:
con.close()
def __check_table(self):
for table in self.connection.cursor().execute(
'SELECT name FROM sqlite_master WHERE type="table"').fetchall(
):
if table[0] == self.table:
return True
raise Error(f"Table with name {self.table} does not exists")
def execute_JIRA_RestAPI(url):
# execute the call to JIRA
# input : url for the rest API
# return : object (from JIRA response)
debug_print(app.logger, 'start jira query' + datetime.now().ctime())
# Base encode email and api token
#cred = "Basic " + base64.b64encode(b'scohenofir:TigerXO123!').decode("utf-8")
# #c2NvaGVub2ZpcjpUaWdlclhPMTIzIQ==
cred = "Basic " + 'c2NvaGVub2ZpcjoxMjNUaWdlclhPIQ=='
# used a service to convert string to base64 string is scohenofir:pwd
print(cred)
# Set header parameters
headers = {
"Accept": "application/json",
"Content-Type": "application/json",
"Authorization": cred
}
# Send request and get response
response = requests.request("GET", url, headers=headers)
if response.status_code != 200:
raise Error(str(response.status_code) + " :: " + response.text)
# Decode Json string to Python
json_str = json.loads(response.text)
obj = to_object(json_str)
debug_print(app.logger, 'end jira query' + datetime.now().ctime())
return obj
def create(self, user):
try:
birthDate = datetime.datetime.strptime(user.birthDate, '%d/%m/%y')
except (ValueError, AttributeError) as e:
print('Error: {}'.format(e))
return False
try:
if user.name == '' or user.lastName == '' or user.email == '':
raise Error('empty fields')
#print('Error: empty fields')
return False
except Error as e:
print('Error: {}'.format(e))
raise Exception('error')
cursor = self.connection.cursor()
dataUser = (user.name, user.lastName, birthDate, user.email)
sql = """ INSERT INTO {} (name, lastName, birthDate, email) VALUES(?,?,?,?);""".format(
self.table)
try:
cursor.execute(sql, dataUser)
except Error as e:
print('Error: {}'.format(e))
return False
#self.connection.commit()
return True
def connection_verification(connection):
"""
This function check if the connection is already opened
:param connection:
:return:
"""
if not connection:
raise Error("The connection is closed ")
def test_create_database_raises_error(unit_under_test, mocker):
mocker.patch('os.path.isfile', return_value=False)
mock_sql = mocker.patch('sql_handler.sql_handler.sqlite3')
mock_sql.connect.side_effect = Error('Test')
with pytest.raises(Error, match='Test'):
unit_under_test.connect()
assert unit_under_test.connected is False
def get_primary_key(self, *fields: TableField):
result = self.get_matching_elements(*fields)
if len(result) == 1:
return result[0][self.primary_key_column_name]
elif len(result) > 1:
raise Error("Query matches multiple elements")
else:
return
def connect_SQL(self):
""" Connect to SQL database into _nbaPlayer.db, returning successful connection and
acquire registered data types. Check existence of file, otherwise set new file """
try:
self.__db_connect = sqlite3.connect(
self.__DBFILE, detect_types=sqlite3.PARSE_DECLTYPES)
self.cur = self.__db_connect.cursor()
except Error as e:
raise Error("ERROR:", str(e))
def check_if_product_prices_are_sorted_accordingly(self):
product_prices = self.get_plp_final_product_prices()
if len(product_prices) == 1:
print("Only one product exists in the PLP")
elif len(product_prices) >= 1:
for i in range(len(product_prices) - 1):
if not product_prices[i] <= product_prices[i + 1]:
return Error(
"product prices are not sorted and earlier price:{} is > next price:{}"
.format(product_prices[i], product_prices[i + 1]))
return True
def endTransaction(self, state):
cursor = self.conecction.cursor()
try:
if not state:
raise Error('problem query')
sql = """COMMIT;"""
cursor.execute(sql)
return True
except Error as e:
print('Error: {}'.format(e))
sql = """ROLLBACK;"""
cursor.execute(sql)
return False
def batch_update(self, table, search_data, update_data):
if len(search_data) != len(update_data):
raise Error('search and update data lengths must match')
statement_fmt = 'UPDATE {} SET {} WHERE {}'
with sqlite3.connect(self.dbfile) as conn:
c = conn.cursor()
for i in range(len(search_data)):
vals = [update_data[i][k] for k in update_data[i].keys()]
vals += [search_data[i][k] for k in search_data[i].keys()]
statement = statement_fmt.format(table,
','.join(['{}=?'.format(k) for k in update_data[i].keys()]),
' AND '.join(['{}=?'.format(k) for k in search_data[i].keys()]))
c.execute(statement, (*vals,))
def connect(self):
"""
Attempts to connect to the SQLite database
:return: None
"""
try:
self._connection = sqlite3.connect(self.db_file_path)
self._connected = True
# Enable foreign key support
self.query("PRAGMA foreign_keys = ON;")
except Error as e:
raise Error("Could not connect to: %s" % self.db_file_path) from e
return self
def delete(self, idUser):
cursor = self.connection.cursor()
sql = """ DELETE FROM {} WHERE id = '{}';""".format(self.table, idUser)
try:
cursor.execute(sql)
if cursor.rowcount == 0:
raise Error('no found row, no deleted')
#print('Error: no found row, no deleted')
return False
except Error as e:
print('Error: {}'.format(e))
return False
#self.connection.commit()
return True
def get_user(self, idno):
cur = self.connection.cursor()
cur.execute(
"SELECT type, lastname, firstname, middlename FROM USERS WHERE idno = ?",
(idno, ))
row = cur.fetchone()
if row is not None:
if row[0] == 'A':
# we have an admin
return User(idno, UserType.ADMIN, row[1], row[2], row[3])
elif row[0] == 'S':
# we have a student
return User(idno, UserType.STUDENT, row[1], row[2], row[3])
else:
raise Error("Invalid user type")
else:
return None
def delete_transactions(self, tran):
"""
Deletes all transaction of a given stock
:param tran: Transaction from a given stock
:return: None
"""
if not isinstance(tran, Transaction):
raise ValueError("Argument is not type Transaction")
try:
list_of_arguments = [tran.symbol]
sql_command = """delete from transactions
where stock_id = (select id from portfolio where stock = ?)"""
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_command, list_of_arguments)
con.commit()
except Error:
print(Error.with_traceback())
finally:
con.close()
def delete_individual_transaction_using_primary_key(self, primary_key):
"""
Deletes individual transaction given an transaction primary key
:param primary_key: integer primary key of transaction
:return: None
"""
if not isinstance(primary_key, int):
raise ValueError("Wrong argument. Argument must be an integer")
sql_statement = """delete from transactions where id = ?"""
argument_list = [primary_key]
try:
conn = self.__init_connection()
dataset = conn.cursor()
dataset.execute(sql_statement, argument_list)
conn.commit()
except Error:
print(Error.with_traceback())
finally:
conn.close()
def get_stock(self, symbol):
"""
Returns a stock object containing the information from the database.
:param symbol: String of stock ticker symbol
:return: Stock object
"""
try:
if not isinstance(symbol, str):
raise ValueError("Parameter is not type string")
stock_to_return = Stock(symbol)
try:
tran_list = self.get_transactions(symbol)
except NoResultsException.NoResultsException:
tran_list = []
stock_id = self.get_stock_id(symbol)
stock_to_return.transactions = tran_list
stock_to_return.stock_id = stock_id
return stock_to_return
except Error:
print(Error.with_traceback())
def get_all_stocks(self):
"""
Gets All stocks from the database with no transaction data
:return: List of stock objects
"""
sql_statement = """select id, stock from portfolio"""
list_to_return = []
try:
con = self.__init_connection()
dataset = con.cursor()
dataset.execute(sql_statement)
con.commit()
for x in dataset:
item = Stock(x[1])
item.stock_id = x[0]
list_to_return.append(item)
return list_to_return
except Error:
print(Error.with_traceback())
finally:
con.close()
def __init__(self, db_file: str, init_script=None):
"""Create a database connection to a SQLite database and create the default tables form the SQL script in
init_db.sql.
Args:
db_file (str): The filename of the SQLite database file.
init_script (Optional[str]): Optional SQL script filename that will be run when the method is called.
"""
if db_file is None:
raise Error("Database filepath and/or filename hasn't been set.")
self._db_file = db_file
with DatabaseManager(self._db_file) as db_manager:
if init_script is not None:
queries_ = self.parse_sql_file(init_script)
for query in queries_:
try:
db_manager.execute(query)
except Error as error:
print(
"Command could not be executed, skipping it: {0}".
format(error))
def test_delete_record_throw_exception(self, mockSqlite3):
mockSqlite3.connect = MagicMock(side_effect=Error("foo"))
with self.assertRaises(Error):
RecordsHandler("invalid.xlm")
def create_table(self, table_schema):
if not isinstance(table_schema, SSTableSchema):
raise Error('invalid table schema')
return self.execute(str(table_schema))
def parse_and_load_course_branch_item(course_data_path, conn, course_zip_name):
"""take all of the course branch item content and create vocabulary
"""
content_path = os.path.join(course_data_path, "course_branch_item_content")
course_slug = course_zip_name.replace("_", "-")
sql_select_course_id = (
"SELECT DISTINCT course_branch_items.course_branch_id, " +
"course_item_id, course_branch_module_name, " +
"course_branch_lesson_name, course_branch_item_name FROM " +
"course_branch_modules, course_branch_lessons, course_branch_items, " +
"course_branches, courses WHERE course_slug = (?) " +
"AND courses.course_id == course_branches.course_id " +
"AND course_branches.course_branch_id == course_branch_items.course_branch_id "
+
"AND course_branch_items.course_lesson_id == course_branch_lessons.course_lesson_id "
+
"AND course_branch_lessons.course_module_id == course_branch_modules.course_module_id"
)
c = conn.cursor()
c.execute(sql_select_course_id, (course_slug, ))
# module name > lesson name > item name > to processed vocabulary (list of words)
course_vocabulary = {}
rows = c.fetchmany()
while rows:
for row in rows:
(
course_branch_id,
course_item_id,
course_branch_module_name,
course_branch_lesson_name,
course_branch_item_name,
) = row
# load the raw json file for branch item
course_branch_item_path = os.path.join(
content_path, "{}-{}.json".format(course_branch_id,
course_item_id))
with open(course_branch_item_path, "r") as cbif:
# attempt to load the json file, otherwise continue
try:
raw_cbi = json.load(cbif)
except Exception as e:
print(e)
continue
try:
if raw_cbi["message"] == "" and raw_cbi[
"statusCode"] == 204 and raw_cbi[
"reason"] == "ignore assesments":
continue
except KeyError:
pass
try:
if raw_cbi["message"] == "" and raw_cbi[
"statusCode"] == 404:
continue
except KeyError:
pass
try:
if raw_cbi["message"] == None and raw_cbi[
"errorCode"] == "Not Authorized":
continue
except KeyError:
pass
try:
if raw_cbi["message"].startswith(
"No item ItemId("
) and raw_cbi["errorCode"] == None:
continue
except KeyError:
pass
normalized_processed_text = None
try:
# try to get the definition value of the item
definition_raw_html = raw_cbi["linked"][
"openCourseAssets.v1"][0]["definition"]["value"]
definition_text = " ".join(
BeautifulSoup(definition_raw_html,
"html.parser").stripped_strings)
normalized_processed_text = preprocess_string(
definition_text)
update_course_vocabulary(course_vocabulary,
course_branch_module_name,
course_branch_lesson_name,
course_branch_item_name,
normalized_processed_text)
continue
except KeyError:
pass
try:
# check if the branch item is a video with subtitles, get subtitles
subtitles_lookup = raw_cbi["linked"]["onDemandVideos.v1"][
0]["subtitlesTxt"]
if not subtitles_lookup.keys():
continue # no subtitles for the video
subtitle_filepath = course_branch_item_path + ".subtitles.txt"
with open(subtitle_filepath, "r") as subfp:
subtitle_raw_text = "".join(subfp.readlines())
normalized_processed_text = preprocess_string(
subtitle_raw_text)
update_course_vocabulary(course_vocabulary,
course_branch_module_name,
course_branch_lesson_name,
course_branch_item_name,
normalized_processed_text)
continue
except KeyError:
pass
raise Error("unhandled cbi")
rows = c.fetchmany()
# save the course_vocabulary to disk
vocab_filepath = os.path.join(course_data_path, "..",
"vocabulary.{}.json".format(course_slug))
with open(vocab_filepath, "w") as vocab_file:
json.dump(course_vocabulary, vocab_file)
def drop_table(self, table_schema):
if not isinstance(table_schema, SSTableSchema):
raise Error('invalid table schema')
return self.execute('DROP TABLE {}'.format(table_schema.name))
#prompt user to enter the person ID and if the person id is equal to -1 the system will quit
pers_id = input("Person ID: ")
if pers_id == "-1":
sys.exit()
try:
cur = connection.cursor()
cur.execute("SELECT first_name,last_name,per.age,name,pet.age,breed,dead "\
"FROM person per "\
"INNER JOIN person_pet pp ON per.id = pp.person_id "\
"INNER JOIN pet pet ON pet.id = pp.pet_id "\
"WHERE per.id = ?",pers_id)
rows = cur.fetchall()
for row in rows:
if row[6]:
tense1 = 'had'
tense2 = 'was'
else:
tense1 = 'has'
tense2 = 'is'
print("{} {},Age {}".format(row[0],row[1],row[2]))
print("{} {} a {} named {} that {} {} years old".format(
row[0],tense1,row[5],row[3],tense2,row[4]))
except Error as e:
raise Error(e)
