def getAll():
db = DatabaseConnection(Friends.connection_string)
data = db.query("SELECT * from friends;")
all_friends = [
Friend(friend[0], friend[1], friend[2]) for friend in data
]
return all_friends
def usernameUnique(self, username):
functionName = 'usernameUnique'
validateUN =[]
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
validateUN = cursor.execute('SELECT cust_id FROM Customers WHERE username = ?', (username,))
if validateUN != 0:
validateUN = 'unique'
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
return validateUN
def load_records(database_file):
"""\
Opens the specified local database file with the bank account records and store the
records in-memory for usage with the aid of the dictionary data structure.
Checks that the argument 'database_file' passed in is of type 'str' else an 'Exception' is raised.
If the specified database file doesn't exist an 'Exception' is raised.
:param database_file: Name of the database file to load records from
:return: Dictionary data structure with the bank account records
"""
if type(database_file) != str:
raise Exception(
"Invalid argument: database_file of type {} should be: <class 'str'>"
.format(type(database_file)))
database = DatabaseConnection(database_file)
records = dict()
result_set = database.sql_statement(database.READ,
"SELECT * FROM accounts")
for record in result_set: # grab each record from the result-set
records[record[4]] = BankAccount(record[0], record[1], record[2],
record[3])
database.close()
return records
def createBooking(self, tool, returnedUser):
functionName = 'createBooking'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
# get IDs
cust_id = UserManager.LoadUserId(self, returnedUser)
tool_id = Tools.getId(tool)
cursor.execute('INSERT INTO Bookings (tool_id, cust_id) VALUES (?, ?)', (tool_id, cust_id))
databaseConnection.commit()
book_id = cursor.lastrowid
returnedBooking = Bookings(book_id, tool, returnedUser)
# Diconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedBooking
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def createTool(self, user, tool_name, tool_cat, tool_desc, price, halfDayPrice):
functionName = 'createTool'
try:
# get ID
cust_id = #'1'
tool_id = #'33'
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute('INSERT INTO Tools (tool_id, cust_id, tool_name, tool_cat, tool_desc, price, half_price) VALUES (?, ?, ?, ?, ?, ?, ?)', (tool_id, cust_id, tool_name, tool_cat, tool_desc, price, halfDayPrice))
databaseConnection.commit()
# create tool object
returnedTool = Tools(tool_id, user, tool_name, tool_cat, tool_desc, price, halfDayPrice)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
AvailabilityChecker.get_availability(cust_id, tool_id, int(0))
return returnedTool
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def Rent(self, tool_id, descriptionToolInput):
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'SELECT book_id FROM bookings WHERE tool_id = ?',
tool_id,
)
numberOfBookings = cursor.fetchone()
previousBooking = int(numberOfBookings) - 1
if previousBooking >= 0:
printNoteStateOfItem = (
'This tool was recently added, please see item description:\n'
+ descriptionToolInput)
else:
cursor.execute(
'SELECT note_in FROM bookings WHERE tool_id = ? AND book_id = ?',
tool_id,
previousBooking,
)
printNoteStateOfItem = cursor.fetchone()
DatabaseConnection.CloseDBConnection(self.databaseFilename)
return printNoteStateOfItem
def book_out(self, tool_id, dateOfBooking, lengthOfBookingInput, Delivery):
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databasefilename)
cursor = databaseConnection.cursor()
#creates list of dates to take the availability out for
booking_dates = [dateOfBooking]
i = 1
while i >= lengthOfBookingInput:
booking_dates.append(dateOfBooking + datetime.timedelta(days=1))
i += 1
#sets availability to 0 (rented) for the number of days
for dateOfBooking in booking_dates:
cursor.execute(
'UPDATE Bookings SET available = 0 WHERE tool_id = ? AND date = ?',
tool_id,
dateOfBooking,
)
#sets delivery status
if Delivery == 1:
for dateOfBooking in booking_dates:
cursor.execute(
'UPDATE Bookings SET Delivery = 1 WHERE tool_id = ? AND date = ?',
tool_id,
dateOfBooking,
)
databaseConnection.commit()
DatabaseConnection.CloseDBConnection(self.databasefilename)
def getData(self, customer_email):
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'SELECT cust_id, F_name, L_name, email FROM customers WHERE email = ?',
customer_email,
)
customer_row = cursor.fetchone()
if (customer_row != None):
customer_id = customer_row[0]
customer_firstname = customer_row[1]
customer_lastname = customer_row[2]
customer_email = customer_row[3]
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
return customer_id, customer_firstname, customer_lastname, customer_email
def getEpisode(self,episodeQueue,threadID):
while True:
episode = episodeQueue.get()
detailedEpisode = self.apiConn.getDetailedEpisodeInfo(episode['id'])
if detailedEpisode:
detailedEpisode['seasonID'] = episode['seasonID']
newdb = DatabaseConnection()
newdb.updateEpisodeInfo(detailedEpisode)
newdb.db.close()
episodeQueue.task_done()
return None
class SystemLog:
def __init__(self):
self.db = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
def insert(self, function_name, msg):
sql = """
insert into tb_sync_log(log_function, log_msg)
values('{}', '{}')
"""
self.db.executeCommit(sql.format(function_name, msg))
def Return(self, noteOnReturn, bookingIdReturnInput):
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'UPDATE bookings SET note_in = ? WHERE booking_id = ?',
noteOnReturn,
bookingIdReturnInput,
)
databaseConnection.commit()
DatabaseConnection.CloseDBConnection(self.databaseFilename)
def __init__(self):
# self.maxRowPerWorker = env.VEN_MAX_ROW_PER_WORKER
self.workerAddress = env.VEN_WORKER_ADDR
self.sinkAddr = env.SINK_ADDR
self.context = zmq.Context()
self.sender = self.context.socket(zmq.PUSH)
self.sender.bind(self.workerAddress)
self.processId = os.getpid()
self.system = platform.system()
self.db = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
self.syslog = SystemLog()
self.outbox = Outbox(self.db)
def getList(self):
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute('SELECT customer_email FROM customers;')
mailing_list = cursor.fetchall()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
#presenting the Customer list back to us
return mailing_list
def loadFutureTools(self, range_start, range_end_days = 42):
functionName = 'loadFutureTools'
# empty list
returnedToolList = []
try:
# calculationg range_end
range_end = range_start + timedelta(days = range_end_days)
userManager = UserManager(self.registeredUser, self.databaseFilename)
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT tool_id, duration FROM Bookings WHERE start_date BETWEEN ? AND ?", (range_start, range_end))
tool_rows = cursor.fetchall()
for tool in tool_rows:
# Read our values from the record
tool_id = tool[0]
cust_id = tool[1]
tool_name = tool[2]
tool_cat = tool[3]
tool_desc = tool[4]
price = tool[5]
halfDayPrice = tool[6]
# get ID
user = userManager.LoadUserId(cust_id)
# create tool
singleTool = Tools(tool_id, user, tool_name, tool_cat, tool_desc, price, halfDayPrice)
returnedToolList.append(singleTool)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedToolList
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def __init__(self):
self.syncDB = DatabaseConnection(env.DB_HOST, env.DB_UNAME,
env.DB_PASSWORD, env.DB_NAME)
# self.statusDB = DatabaseConnection(
# env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME)
self.limitRow = env.LIMIT_PROC_ROW
self.outbox = Outbox(self.syncDB)
self.systemlog = SystemLog()
self.inbox = Inbox(self.syncDB)
self.outbox = Outbox(self.syncDB)
self.clientIdStartFrom = 10
self.updateToZeroHistory = set([])
self.PKFileName = 'pk'
self.nextPriToProcess = dict()
self.PRIFileName = 'pri'
def getTool(self, customer_id):
rental_list = [
'Tool Name', 'Day Price', 'Rental Duration', 'Total Price'
]
grand_total = 0
date = (datetime.date.today().replace(day=1) -
datetime.timedelta(days=1)).strftime("%Y-%m") + "-01"
date_end = datetime.date.today().replace(day=1) - datetime.timedelta(
days=1)
delivery_charge = 5
insurance_charge = 5
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute(
'''SELECT tool_id, price, duration, cust_id, delivery, late_return FROM Bookings WHERE cust_id= ? AND strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)'''
), (
customer_id,
date,
date_end,
)
tool_row = cursor.fetchall()
for booking in tool_row:
#checking if customer with given id borrowed any tools and if it was more than one creating a list
customer_id = booking[0]
tool_id = booking[1]
price = booking[2]
duration = booking[3]
deliveries_charge = booking[4] * delivery_charge
late_charge = booking[5]
total_price = int(price) * int(duration) + int(late_charge)
rental_list.append(tool_id, price, duration, total_price)
#rental lines creation based on retrieved data
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
grand_total = grand_total + total_price + deliveries_charge + insurance_charge
return rental_list, grand_total
def save_to_database(db_file, csv):
conn = sqlite3.connect(db_file)
# i value to start with
i = 266115
for row in csv.itertuples():
comment_id = row[1]
comment_content = row[2]
comment_date = row[3]
comment_original = row[4]
last_edit_date = row[5]
like_count = row[6]
video_id = row[7]
if pd.isnull(comment_content):
translation = None
language = None
else:
translation, language = DetectLanguage.detect_language(comment_content)
data = [[i, comment_id, comment_date, comment_content, comment_original, last_edit_date, like_count, video_id,
translation, language]]
column_names = ["index", "comment_id", "comment_date", "comment_content", "comment_original", "last_edit_date",
"like_count", "video_id", "translation", "language"]
sql_dataframe = pd.DataFrame(data, columns=column_names)
i += 1
if i == 0:
sql_create_comments_table = """ CREATE TABLE IF NOT EXISTS comments (
index INT PRIMARY KEY,
comment_id text,
comment_content text ,
comment_date DATE ,
comment_original text ,
last_edit_date DATE ,
like_count FLOAT ,
video_id text,
language text,
translation text
); """
DatabaseConnection.create_table(conn, sql_create_comments_table)
sql_dataframe.to_sql('comments', conn, if_exists='append', index=False)
else:
sql_dataframe.to_sql('comments', conn, if_exists='append', index=False)
str_i = str(i)
file = open(counter, 'w')
file.write(str_i)
file.close()
class Log:
db = None
host = 'localhost'
username = '******'
password = '******'
database = 'db_ta_monitor'
def __init__(self):
self.rowLimit = env.LOG_ROW_LIMIT
self.db = DatabaseConnection(
env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD, env.DB_NAME)
# ///////////////////////////////////////////
def getUnproceessLog(self):
# query = f"""select * from tb_sync_outbox
# where (status = 'waiting') or
# (status = 'sent' and retry_again_at <= now())
# order by first_time_occur_at asc, priority asc"""
query = f"""select * from tb_sync_outbox
where (status = 'waiting') order by first_time_occur_at asc, priority asc"""
if (env.LOG_ROW_LIMIT > 0):
query += f' limit {env.LOG_ROW_LIMIT}'
result = self.db.executeFetchAll(sql=query)
return result
class BaseConnection(SockJSConnection):
"""Base connection implementation"""
def on_open(self, info):
self.db = DatabaseConnection()
def on_message(self, message):
body = None
if '|' in message:
request, body = message.split('|', 1)
else:
request = message
callback = getattr(self, 'on_' + request)
if body is not None and len(body) > 0:
callback(body)
else:
callback()
@gen.engine
def on_auth(self, message):
user = yield self.db.get_user(message)
self.fetch_user_data(user)
yield self.prepare_dataset()
params = self.estimate_dataset_params()
self.send_dataset(params)
def on_close(self): pass
# These methods should be implemented
def fetch_user_data(self, user): pass
def prepare_dataset(self): pass
def estimate_dataset_params(self): pass
def send_dataset(self, params): pass
def searchFutureBookings(self, registeredUser):
functionName = 'SearchFutureBookings'
# empty list
RetBookingList = []
try:
toolManager = ToolManager(self.databaseFilename, registeredUser)
start_date = datetime.now()
cust_id = #'1'
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT Bookings.book_id, Bookings.tool_id FROM Bookings INNER JOIN Tools ON Bookings.tool_id = Tools.tool_id WHERE Tools.tool_start > ? AND Bookings.cust_id = ?", (start_date, cust_id))
booking_rows = cursor.fetchall()
# crete bookings in the DB
for Booking in booking_rows:
book_id = booking_rows[0]
tool_id = booking_rows[1]
# load the correct tool
tool = toolManager.loadToolId(tool_id)
# create booking
singleBooking = Booking(book_id, tool, registeredUser)
RetBookingList.append(singleBooking)
# Disconnect from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return RetBookingList
except Error as e:
# Catch and display any errors that occur
print(__name__, ':', functionName, ':', e)
raise
def add_delivery_charge(self, book_id):
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
#cursor creation for talking to db
cursor = databaseConnection.cursor()
cursor.execute(
'UPDATE bookings SET delivery = delivery + 1 WHERE book_id = ?',
book_id,
)
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(self.databaseFilename)
def searchToolByCategory(self, search_criteria):
functionName = 'searchToolByCategory'
# empty list
returnedToolList = []
try:
userManager = UserManager(self.registeredUser, self.databaseFilename)
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection(self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute("SELECT tool_id, cust_id, tool_name, tool_cat, price, tool_desc, half_price FROM Tools WHERE tool_cat = ?", (search_criteria))
tool_rows = cursor.fetchall()
for tool in tool_rows:
tool_id = tool[0]
cust_id = tool[1]
tool_name = tool[2]
tool_cat = tool[3]
price = tool[4]
tool_desc = tool[5]
half_price = tool[6]
# get user
user = userManager.LoadUserId(cust_id)
# create tool
single_tool = Tools(tool_id, user, tool_name, tool_cat, price, tool_desc, half_price)
returnedToolList.append(single_tool)
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
return returnedToolList
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def __init__(self, queue, token, i,time):
threading.Thread.__init__(self)
self.queue = queue
self.dbConn = DatabaseConnection()
self.apiConn = APIConnection()
self.apiConn.setToken(token)
self.threadNumber = i
self.episodeThreads = 15
self.lastUpdatedTime = time
def checkIfLate(self, book_id):
return_date = datetime.datetime.today()
charge_ratio = 2
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('SELECT end_date FROM Bookings WHERE book_id = ?', book_id,)
booking_dates = cursor.fetchone()
#late_days = 1
late_days = (return_date - booking_dates).days
#counts how many days overdue
late_charge = charge_ratio * late_days
#adds the ratio for later calculation
DatabaseConnection.CloseDBConnection(databaseConnection)
return late_charge
def load_books(self):
self.books = []
books_data = DC.get_data(self.path)
for book_from_db_id in books_data:
new_book = Book.empty()
new_book.load(self.path + '/' + book_from_db_id)
new_book.book_ID = book_from_db_id
self.books.append(new_book)
def Broken(self, maToolInput, brokenNoteInput):
today = datetime.date.today()
databaseConnection = DatabaseConnection.CreateDBConnection(
self.databaseFilename)
cursor = databaseConnection.cursor()
cursor.execute('SELECT tool_id FROM bookings WHERE book_id = ?',
maToolInput)
tool_id = cursor.fetchone()
cursor.execute(
'UPDATE bookings SET note_out = ? AND available = 0 WHERE tool_id = ? AND date >= ?',
brokenNoteInput,
tool_id,
today,
)
databaseConnection.commit()
DatabaseConnection.CloseDBConnection(self.databaseFilename)
def recvAck(self, data):
conn = DatabaseConnection(env.DB_HOST, env.DB_UNAME, env.DB_PASSWORD,
env.DB_NAME)
obox = conn.executeFetchOne(
f"select * from tb_sync_outbox where outbox_id = {data['query']}")
ack = True
if (obox['data']):
if (obox['data']['msg_type'] == 'INS'):
status = 'need_pk_update'
else:
status = 'arrived'
# ack = self.outbox.update(data={
# 'status': status
# }, where_clause={
# 'outbox_id': data['query']
# })
ack = conn.executeCommit(
f"update tb_sync_outbox set status='{status}' where outbox_id={data['query']}"
)
def __init__(self, dbhost, dbusername, dbpass, dbname, sinkaddr, skey,
ivkey):
self.key = skey
self.iv = ivkey
self.context = zmq.Context()
self.receiver = self.context.socket(zmq.PULL)
self.receiver.bind(sinkaddr)
self.syslog = SystemLog()
self.db = DatabaseConnection(dbhost, dbusername, dbpass, dbname)
self.inbox = Inbox(self.db)
self.outbox = Outbox(self.db)
def main():
db_connection = DatabaseConnection.create_connection(
r"C:\Users\ba051652\OneDrive - Otto-Friedrich-Universität Bamberg\SS 20\Seminar\Shared folder\sentiwordnet\Database\languages.db")
now = datetime.now()
current_time = now.strftime("%H:%M:%S")
print("Start Time =", current_time)
csv_file = read_csv(comments_file)
save_to_database(db_connection, csv_file)
now = datetime.now()
current_time = now.strftime("%H:%M:%S")
print("End Time =", current_time)
class ProductDao(object):
def __init__(self):
self.databaseConnection = DatabaseConnection()
def getMaxProductId(self):
lastProductId = 0
connection = self.databaseConnection.getConnection()
cursor = connection.cursor()
cursor.execute('SELECT MAX(PRODUCTS_ID) FROM CON_PRODUCTS')
for row in cursor:
lastProductId = row[0]
return lastProductId
def insertMany(self, products):
connection = self.databaseConnection.getConnection()
cursor = connection.cursor()
data = []
lastProductId = self.getMaxProductId()
curretDatetime = datetime.datetime.now()
for product in products:
lastProductId += 1
record = (lastProductId, product['FILE_ID'], product['VARIANT_ID'],
product['VARIANT_CORR'], product['PARTNUMBER'],
product['NAME'], product['LONGDESCRIPTION'],
product['CATEGORY_CODE'], product['BRAND'],
product['STATUS'], product['PRICE'],
product['ORIGIN_PRICE'], product['SYNC'],
product['SELLER_ID'], product['PRODUCT_SKU'],
product['PRODUCT_SKU_PARENT'], curretDatetime,
curretDatetime)
data.append(record)
cursor.executemany(
"""
INSERT INTO CON_PRODUCTS (PRODUCTS_ID, FILE_ID, VARIANT_ID, VARIANT_CORR, PARTNUMBER, NAME , LONGDESCRIPTION, CATEGORY_CODE, BRAND, STATUS, PRICE, ORIGIN_PRICE, SYNC, SELLER_ID, PRODUCT_SKU, PRODUCT_SKU_PARENT, CREATE_DATE, LAST_DATE)
VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18)""",
data)
connection.commit()
cursor.close()
connection.close()
def setUpClass(cls):
with open('password.txt') as f:
password = f.readline()
cls.dbcon = DatabaseConnection('dbname=test user=postgres password='******'''
INSERT INTO seasons (season_name, start_date, end_date, total_games)
VALUES (%s, %s, %s, %s);
''', ('Test Season', '2013-05-30', '2014-04-23', 43))
cls.dbcon.cursor.execute(
'''
INSERT INTO contestants (name, notes, games_played, total_winnings)
VALUES (%s, %s, %s, %s);
''', ('John Smith', 'A teacher from Akron, Ohio', 2, 19400))
cls.dbcon.cursor.execute(
'''
INSERT INTO contestants (name, notes, games_played, total_winnings)
VALUES (%s, %s, %s, %s);
''',
('Jane Doe', 'A software engineer from Atlanta, Georgia', 1, 2000))
cls.dbcon.cursor.execute(
'''
INSERT INTO contestants (name, notes, games_played, total_winnings)
VALUES (%s, %s, %s, %s);
''',
('Joe Schmo', 'An accountant from San Diego, California', 1, 1000))
cls.dbcon.cursor.execute(
'''
INSERT INTO games (episode_num, season_id, air_date, notes, contestant1, contestant2, contestant3, winner, score1, score2, score3)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
''', (1, 1, '2013-08-24', 'Tournament of Champions Game 1', 1, 2,
3, 1, 3400, 1400, 1200))
cls.dbcon.cursor.execute(
'''
INSERT INTO clues (game_id, value, daily_double, round, category, clue, response, correct_contestant)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s);
''', (1, 400, 'No', 'Jeopardy', 'State Capitals',
'The capital of Indiana', 'Indianapolis', 1))
print('Connected:', cls.dbcon, "\n")
def markAvailability(self, tool_id):
functionName = 'markAvailability'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('UPDATE Tools SET available = 0 WHERE tool_id = ?', (tool_id))
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def bookOutNotes(self, book_id, brokenNoteInput):
functionName = 'bookOutNotes'
try:
# Connecting to the DB
databaseConnection = DatabaseConnection.CreateDBConnection('SharedPower.db')
cursor = databaseConnection.cursor()
cursor.execute('UPDATE Bookings SET note_out = ? WHERE book_id = ?', (brokenNoteInput, book_id))
databaseConnection.commit()
# Disconnecting from the DB
DatabaseConnection.CloseDBConnection(databaseConnection)
except Error as e:
print(__name__, ':', functionName, ':', e)
raise
def load_records(database_file):
"""\
Opens the specified local database file with the bank account records and store the
records in-memory for usage with the aid of the dictionary data structure.
Checks that the argument 'database_file' passed in is of type 'str' else an 'Exception' is raised.
If the specified database file doesn't exist an 'Exception' is raised.
:param database_file: Name of the database file to load records from
:return: Dictionary data structure with the bank account records
"""
if type(database_file) != str:
raise Exception(
"Invalid argument: database_file of type {} should be: <class 'str'>".format(type(database_file)))
database = DatabaseConnection(database_file)
records = dict()
result_set = database.sql_statement(database.READ, "SELECT * FROM accounts")
for record in result_set: # grab each record from the result-set
records[record[4]] = BankAccount(record[0], record[1], record[2], record[3])
database.close()
return records
for row in reader:
output_data.append(row)
return output_data
if __name__ == '__main__':
args = parse_args()
service_code_file = '../resources/fcc_radio_services_map.csv'
station_class_file = '../resources/fcc_station_class_map.csv'
print '[+] Parsing Data Files'
service_code_data = parse_file(service_code_file)
station_class_data = parse_file(station_class_file)
print '[+] Data Parsed Successfully...'
print '[+] Connecting To Database'
database = DatabaseConnection(args.db, args.host, args.port, args.username, args.password)
database.connect()
print '[+] Database Connection Established...'
print '[+] Inserting Data'
database.insert_services(service_code_data)
database.insert_station_classes(station_class_data)
print '[+] Data Insertion Complete...'
print '[+] Committing Changes and Disconnecting'
database.disconnect()
print '[+] All Changes Persisted, Database Connection Terminated...'
print '[+] Complete'
def on_open(self, info):
self.db = DatabaseConnection()
class processSerie(threading.Thread):
def __init__(self, queue, token, i,time):
threading.Thread.__init__(self)
self.queue = queue
self.dbConn = DatabaseConnection()
self.apiConn = APIConnection()
self.apiConn.setToken(token)
self.threadNumber = i
self.episodeThreads = 15
self.lastUpdatedTime = time
def run(self):
while True:
updatedSerie = self.queue.get()
id = updatedSerie[0]
lastupdated = updatedSerie[1]
if self.lastUpdatedTime < lastupdated:
start = datetime.datetime.now()
serieData = self.apiConn.getSerieData(id)
if serieData:
firstCharsSerieName = serieData['seriesName'][:2] # Duplicates in the API start with ** so these should not be handled and updated.
if firstCharsSerieName != "**":
self.dbConn.updateSerieInfo(serieData)
part1 = (datetime.datetime.now() - start).seconds
episodes = self.apiConn.getUpdatedEpisodes(id)
part2 = ((datetime.datetime.now() - start).seconds) - part1
EpisodeQueue = Queue()
episodeCounter = 0
if episodes:
distinctSeasons = self.apiConn.getDistinctSeasons(episodes)
seasonInfo = self.dbConn.getSeasonInfo(distinctSeasons)
for episode in episodes['data']:
episode['seriesID'] = id
if episode['airedSeason'] and seasonInfo:
episode['airedSeason'] = int(episode['airedSeason'])
episode['seasonID'] = seasonInfo[episode['airedSeason']]
EpisodeQueue.put(episode)
episodeCounter += 1
print "[%s]Starting: %s, %s episodes.\r" % (datetime.datetime.now().strftime('%H:%M:%S'),serieData['seriesName'],episodeCounter)
for i in range(self.episodeThreads):
t = threading.Thread(target=self.getEpisode,args=(EpisodeQueue,i))
t.daemon = True
t.start()
# print "%s: Processing %s S%sE%s - %s" % (self.threadNumber,serieData['seriesName'],episode['airedSeason'],episode['airedEpisodeNumber'],episode['episodeName'].encode('utf-8'))
# detailedEpisode = self.apiConn.getDetailedEpisodeInfo(episode['id'])
# self.db.updateEpisodeInfo(detailedEpisode)
EpisodeQueue.join()
end = (datetime.datetime.now() - start).seconds
print "[%s]Finished: %s, %s episodes.\r" % (datetime.datetime.now().strftime('%H:%M:%S'),serieData['seriesName'],episodeCounter)
self.queue.task_done()
def getEpisode(self,episodeQueue,threadID):
while True:
episode = episodeQueue.get()
detailedEpisode = self.apiConn.getDetailedEpisodeInfo(episode['id'])
if detailedEpisode:
detailedEpisode['seasonID'] = episode['seasonID']
newdb = DatabaseConnection()
newdb.updateEpisodeInfo(detailedEpisode)
newdb.db.close()
episodeQueue.task_done()
return None
for row in reader:
output_data.append(row)
return output_data
if __name__ == '__main__':
args = parse_args()
state, county, file_type = parse_file_name(args.input_file)
print '[+] Parsing Data File'
data = parse_file(args.input_file)
print '[+] Data Parsed Successfully...'
print '[+] Connecting To Database'
database = DatabaseConnection(args.db, args.host, args.port, args.username, args.password)
database.connect()
print '[+] Database Connection Established...'
print '[+] Inserting Data For {0}, {1}'.format(county, state)
if file_type == 'signal':
database.insert_signals(state, county, data)
else:
database.insert_licenses(state, county, data)
print '[+] Data Insertion Complete...'
print '[+] Committing Changes and Disconnecting'
database.disconnect()
print '[+] All Changes Persisted, Database Connection Terminated...'
print '[+] Complete'
