def delete_by_id(clv_id):
try:
db = DbController()
query = "DELETE FROM tbl_clv_prediction where id = %s"
db.execute(query, [clv_id])
except Exception as e:
raise e
def find_by_customer_id(customer_id):
try:
db = DbController()
query = "SELECT * FROM tbl_clv_prediction where customer_id = %s"
return db.execute_select(query, [customer_id])
except Exception as e:
raise e
def clean_table():
try:
db = DbController()
query = "DELETE FROM tbl_clv_prediction"
db.execute(query)
except Exception as e:
raise e
def save(self):
try:
db = DbController()
query = "INSERT INTO tbl_clv_prediction (" \
"id, " \
"customer_id, " \
"max_number_item," \
"max_revenue," \
"total_revenue, " \
"total_orders," \
"days_since_last_order," \
"longest_interval," \
"predicted_clv" \
")" \
" VALUES (NULL, %s, %s, %s, %s, %s, %s, %s, %s)"
data = (self.customer_id,
self.max_number_item,
self.max_revenue,
self.total_revenue,
self.total_orders,
self.days_since_last_order,
self.longest_interval,
self.predicted_clv
)
db.execute(query, data)
self.id = db.last_inserted_id
except Exception as e:
raise e
def __init__(self, id_, title, text, owner, files):
self._id = id_
self.title = title
self.text = text
self.owner = owner
self.files = files
self.__db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'post')
def registration(username, password):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'user')
id_ = db.create(username=username, password=password)
return User(id_, username, password)
except sqlite3.Error as e:
print(e)
return None
def get_user(username):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'user')
data = db.get('username', username)
if not data:
return None
return User(data['Id'], data['username'], data['password'])
except sqlite3.Error as e:
print(e)
return None
def read_total():
response = {}
try:
db_object = DbController()
query = "SELECT COUNT(*) as total FROM tbl_clv_prediction"
data = db_object.execute_select(query)
response["data"] = data[0]
except Exception as e:
response['error'] = '%s' % e
return jsonify(response)
def all_user():
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'user')
users = db.all()
user_list = []
for data in users:
user_list.append(
User(data['Id'], data['username'], data['password']))
return user_list
except sqlite3.Error as e:
print(e)
return None
def verify(self):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'user')
id_ = db.query(
f'SELECT id from user WHERE user_name=\'{self.username}\' and password=\'{self.password}\';'
)
if id_:
return True
except sqlite3.Error as e:
print(e)
return False
def __init__(self):
super().__init__()
self.setupUi(self)
self.controller = DbController()
# запускаем настройки
self.setUp__addItemBtn()
self.setUp__delItemBtn()
self.setUp__listWidget()
class GoodsGuiHandler(QtWidgets.QMainWindow, Ui_MainWindow):
def __init__(self):
super().__init__()
self.setupUi(self)
self.controller = DbController()
# запускаем настройки
self.setUp__addItemBtn()
self.setUp__delItemBtn()
self.setUp__listWidget()
def setUp__addItemBtn(self):
self.addItemBtn.clicked.connect(self.addItemToListEval)
def setUp__delItemBtn(self):
self.delItemBtn.clicked.connect(self.delItemsFromListEval)
def setUp__listWidget(self):
goods = self.controller.getList()
if goods:
self.listWidget.addItems(goods)
###########################################################################
def addItemToListEval(self):
text = self.itemLE.text()
if text:
self.listWidget.addItem(text)
self.controller.addItem(text)
self.itemLE.clear()
def delItemsFromListEval(self):
self.controller.deleteItem(self.listWidget.currentItem().text())
self.listWidget.takeItem(self.listWidget.currentRow())
def __init__(self, **kwargs):
super(MainScreen, self).__init__(**kwargs)
self.controller = DbController()
#подписи над полями
self.add_widget(Label(text='Список товаров', size_hint=(.4, .05), pos_hint={'x':.0, 'y':.95}))
self.add_widget(Label(text='Введите новый товар', size_hint=(.4, .05), pos_hint={'x':.4, 'y':.95}))
self.add_widget(Label(text='Введите количество', size_hint=(.2, .05), pos_hint={'x':.8, 'y':.95}))
# поле ввода товара
self.goodTi = TextInput(multiline=False, size_hint=(.4, .05), pos_hint={'x':.4, 'y':.9})
self.add_widget(self.goodTi)
# кнокпка добавления
self.addGoodBtn = Button(text='Добавить товар', size_hint=(.4, .05), pos_hint={'x':.4, 'y':.85})
self.add_widget(self.addGoodBtn)
self.addGoodBtn.bind(on_press=self.addGoodBtnCallback)
# инпут для ввода количества
self.quantitySpin = FloatInput(size_hint=(.2, .05), pos_hint={'x':.8, 'y':.9})
self.add_widget(self.quantitySpin)
self.list_view = RV(size_hint=(.4, .9), pos_hint={'x':.0, 'y':.05})
self.add_widget(self.list_view)
# кнокпка добавления
self.delGoodBtn = Button(text='Удалить товар', size_hint=(.4, .05), pos_hint={'x':.0, 'y':.0})
self.add_widget(self.delGoodBtn)
self.delGoodBtn.bind(on_press=self.delGoodBtnCallback)
self.popup = Popup(title='Не хватает данных',
content=Label(text='Для добавления в список введите название товара и его количество'),
size_hint=(None, None), size=(600, 200)
)
self.setUp__listWidget()
def all():
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'post')
posts = db.all()
user_list = []
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'p_files')
for data in posts:
file_list = [
i['Id'] for i in db.all(param_name='post_id',
param_value=data['Id'])
]
user_list.append(
Post(data['Id'], data['title'], data['text'],
data['owner'], file_list))
return user_list
except sqlite3.Error as e:
print(e)
return None
def create(title, text, owner, files):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'post')
post_id = db.create(title, text, owner)
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'p_files')
post_files = []
if files:
for i in files:
try:
id_ = db.create(i[0], post_id, i[1], i[2])
post_files.append(id_)
except:
continue
return Post(post_id, title, text, owner, post_files)
except sqlite3.Error as e:
print(e)
return None
class MainScreen(FloatLayout):
def __init__(self, **kwargs):
super(MainScreen, self).__init__(**kwargs)
self.controller = DbController()
#подписи над полями
self.add_widget(Label(text='Список товаров', size_hint=(.4, .05), pos_hint={'x':.0, 'y':.95}))
self.add_widget(Label(text='Введите новый товар', size_hint=(.4, .05), pos_hint={'x':.4, 'y':.95}))
self.add_widget(Label(text='Введите количество', size_hint=(.2, .05), pos_hint={'x':.8, 'y':.95}))
# поле ввода товара
self.goodTi = TextInput(multiline=False, size_hint=(.4, .05), pos_hint={'x':.4, 'y':.9})
self.add_widget(self.goodTi)
# кнокпка добавления
self.addGoodBtn = Button(text='Добавить товар', size_hint=(.4, .05), pos_hint={'x':.4, 'y':.85})
self.add_widget(self.addGoodBtn)
self.addGoodBtn.bind(on_press=self.addGoodBtnCallback)
# инпут для ввода количества
self.quantitySpin = FloatInput(size_hint=(.2, .05), pos_hint={'x':.8, 'y':.9})
self.add_widget(self.quantitySpin)
self.list_view = RV(size_hint=(.4, .9), pos_hint={'x':.0, 'y':.05})
self.add_widget(self.list_view)
# кнокпка добавления
self.delGoodBtn = Button(text='Удалить товар', size_hint=(.4, .05), pos_hint={'x':.0, 'y':.0})
self.add_widget(self.delGoodBtn)
self.delGoodBtn.bind(on_press=self.delGoodBtnCallback)
self.popup = Popup(title='Не хватает данных',
content=Label(text='Для добавления в список введите название товара и его количество'),
size_hint=(None, None), size=(600, 200)
)
self.setUp__listWidget()
def addGoodBtnCallback(self, instance):
if self.goodTi.text and self.quantitySpin.text:
good = self.goodTi.text
quantity = self.quantitySpin.text
self.goodTi.text = ''
self.quantitySpin.text = ''
item = self.controller.addItem(good, quantity)
self.list_view.replaceItemByName(item['item'], item['quantity'])
self.list_view.refresh_from_data()
else:
self.popup.open()
def delGoodBtnCallback(self, instance):
itemLine = self.list_view.delSelectedItem()
itemName = re.sub(r'[,\.0-9+-]+', '', itemLine)
self.controller.deleteItem(itemName)
def setUp__listWidget(self):
goods = self.controller.getList()
print(goods)
if goods:
for good in goods:
self.list_view.addItem(good['item'], good['quantity'])
from flask import Flask, jsonify, request, render_template, redirect, url_for
from arriva_scraper import ArrivaScraper
from data_structure import Journey
from db_controller import DbController
from cacheControl import CacheController
APP = Flask(__name__)
arriva = ArrivaScraper()
db = DbController()
db.createTable()
cacher = CacheController()
@APP.route("/")
def red():
return redirect(url_for('home'))
@APP.route("/home")
def home():
return render_template('homepage.html')
@APP.route("/add")
def add():
source = request.args.get("source")
destination = request.args.get("dest")
date = request.args.get("date")
search = [source, destination, date]
data = arriva.run(search[0], search[1], search[2])
lst_of_journeys = [journey.toDict() for journey in data]
db.saveJourneys(lst_of_journeys)
def create_app():
engine = create_engine("sqlite:///teletweet.db")
# app initiliazation
app = Flask(__name__)
db_controller = DbController(engine.connect())
@app.route('/getTweetsCount', methods=['GET'])
def get_tweets_count():
conn = engine.connect()
count = db_controller.get_tweet_count(conn)
return jsonify(count)
@app.route('/getAllTweets', methods=['GET'])
def get_all_tweets():
conn = engine.connect()
result = db_controller.get_all_tweets(conn)
return jsonify({'tweets': [dict(row) for row in result]})
@app.route('/checkExists/<id>', methods=['GET'])
def check_id_exists(id):
conn = engine.connect()
count = db_controller.get_tweet_of_id(conn, id)
return jsonify(count >= 1)
@app.route('/screenTweet', methods=['POST'])
def screen_tweet():
conn = engine.connect()
try:
return jsonify(util_screen_tweet(conn, db_controller, request))
except Exception as e:
print("oh no " + str(e))
return "200"
@app.route('/addTweet', methods=['POST'])
def add_tweet():
conn = engine.connect()
try:
tweet_data = get_tweet_data(request)
user_data = get_user_data(request)
db_controller.add_tweet(conn, tweet_data, user_data)
except Exception as e:
print("oh no " + str(e))
return "200"
@app.route('/getBlackList', methods=['GET'])
def get_all_blacklisted():
try:
conn = engine.connect()
result = db_controller.get_blacklist(conn)
return jsonify({'blacklist': [dict(row) for row in result]})
except Exception as e:
print("oh no " + str(e))
return "200"
@app.route('/blackListUser/<user>', methods=['GET'])
def blacklist_user(user):
try:
conn = engine.connect()
db_controller.add_blacklist_user(conn, user)
return "succesfully blacklisted %s" % user
except Exception as e:
print("oh no " + str(e))
return "fail to blacklist %s" % user
@app.route('/removeBlackListUser/<user>', methods=['GET'])
def remove_blacklist_user(user):
try:
conn = engine.connect()
db_controller.delete_blacklist_user(conn, user)
return "%s is removed from blacklist" % user
except Exception as e:
print("oh no " + str(e))
return "fail to remove %s from blacklist" % user
@app.route('/check', methods=['GET'])
def health_check():
return "DB service is working"
return app
def search():
controller = DbController()
arguments = dict(request.args)
arguments = dict([(key, arguments[key][0]) for key in arguments])
querry_result = controller.get_journey(**arguments)
return jsonify(querry_result)
from PyQt5.QtWidgets import QMessageBox, QTableWidgetItem
import pickle
from db_controller import DbController
controller = DbController()
def setup(line_edit_obj, table_widget_obj):
global line_edit
global table_widget
line_edit = line_edit_obj
table_widget = table_widget_obj
def create_msg_box(msg_text):
msg_box = QMessageBox()
msg_box.setWindowTitle("Warning")
msg_box.setText(msg_text)
msg_box.exec()
def add_task():
if line_edit.text() == "" or line_edit.text().isspace():
create_msg_box("Please enter a task to be completed")
else:
controller.add_task(line_edit.text())
table_widget.show_items(controller.get_all_tasks())
line_edit.setText("")
def del_task():
class User:
def __init__(self, id_, username, password):
self._id = id_
self.password = password
self.username = username
self.__db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'user')
@staticmethod
def registration(username, password):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'user')
id_ = db.create(username=username, password=password)
return User(id_, username, password)
except sqlite3.Error as e:
print(e)
return None
def verify(self):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'user')
id_ = db.query(
f'SELECT id from user WHERE user_name=\'{self.username}\' and password=\'{self.password}\';'
)
if id_:
return True
except sqlite3.Error as e:
print(e)
return False
@staticmethod
def get_user(username):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'user')
data = db.get('username', username)
if not data:
return None
return User(data['Id'], data['username'], data['password'])
except sqlite3.Error as e:
print(e)
return None
@staticmethod
def all_user():
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'user')
users = db.all()
user_list = []
for data in users:
user_list.append(
User(data['Id'], data['username'], data['password']))
return user_list
except sqlite3.Error as e:
print(e)
return None
def update(self):
try:
self.__db.update(self._id,
username=self.username,
password=self.password)
return True
except sqlite3.Error as e:
print(e)
return False
def delete_user(self):
try:
self.__db.delete(self._id)
return True
except sqlite3.Error as e:
print(e)
return False
class Post:
def __init__(self, id_, title, text, owner, files):
self._id = id_
self.title = title
self.text = text
self.owner = owner
self.files = files
self.__db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'post')
@staticmethod
def create(title, text, owner, files):
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'post')
post_id = db.create(title, text, owner)
db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'p_files')
post_files = []
if files:
for i in files:
try:
id_ = db.create(i[0], post_id, i[1], i[2])
post_files.append(id_)
except:
continue
return Post(post_id, title, text, owner, post_files)
except sqlite3.Error as e:
print(e)
return None
@staticmethod
def all():
try:
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'post')
posts = db.all()
user_list = []
db = DbController(os.path.join(os.getcwd(), '..', 'db'), 'p_files')
for data in posts:
file_list = [
i['Id'] for i in db.all(param_name='post_id',
param_value=data['Id'])
]
user_list.append(
Post(data['Id'], data['title'], data['text'],
data['owner'], file_list))
return user_list
except sqlite3.Error as e:
print(e)
return None
def update(self):
try:
self.__db.update(self._id,
title=self.title,
text=self.text,
owner=self.owner)
return True
except sqlite3.Error as e:
print(e)
return False
def delete(self):
try:
self.__db.delete(self._id)
return True
except sqlite3.Error as e:
print(e)
return False
def __init__(self, id_, username, password):
self._id = id_
self.password = password
self.username = username
self.__db = DbController(os.path.join(os.getcwd(), '..', 'db.sqlite3'),
'user')