def readSQL(sql_text, params=None, named=False, check_unique=False):
if params is None:
params = []
query = QSqlQuery(db_connection())
query.setForwardOnly(True)
if not query.prepare(sql_text):
logging.error(
f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' | '{params}'"
)
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec_():
logging.error(
f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' | '{params}'"
)
return None
if query.next():
res = readSQLrecord(query, named=named)
if check_unique and query.next():
logging.warning(
g_tr('DB', "More than 1 record matched SQL query ") +
f"'{sql_text}' | '{params}'")
return None # More then one record in result when only one expected
return res
else:
return None
def setValue(self, key, value):
set_query = QSqlQuery(self.db)
set_query.prepare("INSERT OR REPLACE INTO settings(id, name, value) "
"VALUES((SELECT id FROM settings WHERE name=:key), :key, :value)")
set_query.bindValue(":key", key)
set_query.bindValue(":value", value)
if not set_query.exec_():
logging.fatal(f"Failed to set settings key='{key}' to value='{value}'")
self.db.commit()
def executeSQL(db, sql_text, params = [], forward_only = True):
query = QSqlQuery(db)
query.setForwardOnly(forward_only)
if not query.prepare(sql_text):
logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec_():
logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
return query
def getValue(self, key):
get_query = QSqlQuery(self.db)
get_query.setForwardOnly(True)
get_query.prepare("SELECT value FROM settings WHERE name=:key")
value = None
get_query.bindValue(":key", key)
if not get_query.exec_():
logging.fatal(f"Failed to get settings for key='{key}'")
return value
if get_query.next():
value = get_query.value(0)
return value
def deregisterStudent(self, id):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("face.db")
if self.db.open() is False:
print("Failed opening db")
query = QSqlQuery(self.db)
query.prepare("DELETE FROM Student WHERE id = :id")
query.bindValue(":id", int(id))
query.exec_()
path = 'dataset'
imagePaths = [os.path.join(path, f) for f in os.listdir(path)]
[os.remove(x) for x in imagePaths if ('/' + str(id) + ".") in x]
self.getThreadSignal(9, "Requested Record removed completely. Kindly retrain.")
def readSQL(db, sql_text, params = []):
query = QSqlQuery(db)
query.setForwardOnly(True)
if not query.prepare(sql_text):
logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
for param in params:
query.bindValue(param[0], param[1])
if not query.exec_():
logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'")
return None
if query.next():
return readSQLrecord(query)
else:
return None
def save_torrent(self, torrent):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'insert into torrents(id, forum, title) values(:id, :f, :t)')
query.bindValue(':id', torrent[0])
query.bindValue(':f', torrent[1])
query.bindValue(':t', torrent[2])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def get_forum(self, id):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare("select * from forums where id=:id;")
query.bindValue(':id', id)
query.exec_()
forum: Dict[str, Optional[Any]] = {}
while query.next():
forum['id'] = query.value('id')
forum['category'] = query.value('category')
forum['title'] = query.value('title')
return forum
finally:
db.close()
def __init__(self):
super().__init__()
self.table = QTableView()
self.model = QSqlQueryModel()
self.table.setModel(self.model)
query = QSqlQuery(db=db)
query.prepare("SELECT Name, Composer, Album.Title FROM Track "
"INNER JOIN Album ON Track.AlbumId = Album.AlbumId "
"WHERE Album.Title LIKE '%' || :album_title || '%' ")
query.bindValue(":album_title", "Sinatra")
query.exec_()
self.model.setQuery(query)
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(self.table)
def get_category(self, id):
category: Dict[str, Optional[Any]] = {}
if id is None:
category['id'] = id
category['title'] = '<UNKNOWN>'
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare("select * from categories where id=:id;")
query.bindValue(':id', id)
query.exec_()
while query.next():
category['id'] = query.value('id')
category['title'] = query.value('title')
return category
finally:
db.close()
def registerStudent(self, id, rollno, name, stclass, section):
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("face.db")
if self.db.open() is False:
print("Failed opening db")
query = QSqlQuery(self.db)
query.prepare("Insert Into Student (id, rollno, name, class, section)"
"Values (:id, :rollno, :name, :class, :section)")
query.bindValue(":id", id)
query.bindValue(":rollno", rollno)
query.bindValue(":name", name)
query.bindValue(":class", stclass)
query.bindValue(":section", section)
query.exec_()
# take photos for training
self.startCapture(id)
self.db.close()
def create_password() -> str:
"""Prompt for a password to be created and return it."""
dialogue = CreatePassword()
dialogue.exec_()
if dialogue.result() != QDialog.Accepted:
sys.exit()
password = dialogue.new_password
# upsert
query = QSqlQuery(QSqlDatabase.database())
query.prepare("""
insert into credentials (id, password)
values (0, :password)
on conflict (id)
do update set password=:password
""")
query.bindValue(":password", password)
query.exec_()
query.finish()
return password
def update_user(self, user):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'update users set name=:name, registered=:reg, nation=:nation where id=:id'
)
query.bindValue(':id', user['id'])
query.bindValue(':name', user['name'])
query.bindValue(':reg', user['registered'])
query.bindValue(':nation', user['nation'])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def insert_user(self, user):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'insert into users(id, name, registered, nation) values(:id, :name, :reg, :nation)'
)
query.bindValue(':id', user['id'])
query.bindValue(':name', user['name'])
query.bindValue(':reg', user['registered'])
query.bindValue(':nation', user['nation'])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
def update_rss(self, f, d, ls):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'update forums_rss set last_scanned=:ls, delta=:d where id=:id'
)
query.bindValue(':ls', str(ls))
query.bindValue(':d', d)
query.bindValue(':id', f)
query.exec_()
db.close()
return True
except Exception as ex:
return False
finally:
db.close()
def insert_torrent(self, torrent):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare(
'update torrents set forum=:f, title=:t, seed=:seed, leech=:leech, published=:pub, last_modified=:lm, '
'hash=:hash, downloads=:d, last_checked=:lc, user_id=:user where id=:id'
)
query.bindValue(':id', torrent['id'])
query.bindValue(':f', torrent['forum'])
query.bindValue(':t', torrent['title'])
query.bindValue(':seed', torrent['seed'])
query.bindValue(':leech', torrent['leech'])
query.bindValue(':pub', str(torrent['published']))
query.bindValue(':lm', str(torrent['last_modified']))
query.bindValue(':hash', torrent['hash'])
query.bindValue(':d', torrent['downloads'])
query.bindValue(':lc', str(torrent['last_checked']))
query.bindValue(':user', torrent['user_id'])
return query.exec_()
except Exception as ex:
return False
finally:
db.close()
class MainWindow(QMainWindow):
def __init__(self):
super().__init__()
container = QWidget()
layout_search = QHBoxLayout()
self.track = QLineEdit()
self.track.setPlaceholderText("Track name...")
self.track.textChanged.connect(self.update_query)
self.composer = QLineEdit()
self.composer.setPlaceholderText("Artist name...")
self.composer.textChanged.connect(self.update_query)
self.album = QLineEdit()
self.album.setPlaceholderText("Album name...")
self.album.textChanged.connect(self.update_query)
layout_search.addWidget(self.track)
layout_search.addWidget(self.composer)
layout_search.addWidget(self.album)
layout_view = QVBoxLayout()
layout_view.addLayout(layout_search)
self.table = QTableView()
layout_view.addWidget(self.table)
container.setLayout(layout_view)
self.model = QSqlQueryModel()
self.table.setModel(self.model)
self.query = QSqlQuery(db=db)
self.query.prepare(
"SELECT Name, Composer, Album.Title FROM Track "
"INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE "
"Track.Name LIKE '%' || :track_name || '%' AND "
"Track.Composer LIKE '%' || :track_composer || '%' AND "
"Album.Title LIKE '%' || :album_title || '%'"
)
self.update_query()
self.setMinimumSize(QSize(1024, 600))
self.setCentralWidget(container)
def update_query(self, s=None):
# Get the text values from the widgets.
track_name = self.track.text()
track_composer = self.composer.text()
album_title = self.album.text()
self.query.bindValue(":track_name", track_name)
self.query.bindValue(":track_composer", track_composer)
self.query.bindValue(":album_title", album_title)
self.query.exec_()
self.model.setQuery(self.query)
def run(self):
self.inform.emit(7, "Testing...")
# Initialize and start realtime video capture
cam = cv2.VideoCapture(0)
# set video width
cam.set(3, 640)
# set video height
cam.set(4, 480)
# Define min window size to be recognized as a face
# minW = 0.1*cam.get(3)
# minH = 0.1*cam.get(4)
i = 0
# font = cv2.FONT_HERSHEY_SIMPLEX
self.db = QSqlDatabase.addDatabase("QSQLITE")
n = 0
while True:
ret, img = cam.read()
cv2.imwrite("temp" + str(n) + ".jpg", img)
gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY)
faces = self.detector.detectMultiScale(gray, 1.4, 8)
for (x, y, w, h) in faces:
cv2.rectangle(img, (x, y), (x + w, y + h), (0, 255, 0), 2)
id, confidence = self.recognizer.predict(gray[y:y + h,
x:x + w])
# print(id, confidence)
# Check if confidence is less then 100 ==> "0" is perfect match
if (confidence > 40):
self.db.setDatabaseName("face.db")
if not self.db.open():
self.inform.emit(
99, "Critical Error: Database failed to open.")
else:
cDate = QDate().currentDate().toString('yyyy-MM-dd')
# query = QSqlQuery(self.db)
qstr = "SELECT * FROM Attendance WHERE id = " + str(
id
) + " AND date = '" + cDate + "' AND lecture_no = " + str(
self.lectureNo)
query = QSqlQuery(qstr)
query.next()
if not query.value(0):
query.prepare(
"INSERT INTO Attendance (id, date, lecture_no)"
"Values (:id, :date, :lectureNo)")
query.bindValue(":id", id)
query.bindValue(":date", str(cDate))
query.bindValue(":lectureNo", str(self.lectureNo))
query.exec_()
self.inform.emit(
8, "Attendance marked for ID = " + str(id) +
" for Lecture = " + str(self.lectureNo))
else:
self.inform.emit(
8, "Attendance for ID = " + str(id) +
" for Lecture = " + str(self.lectureNo) +
" is already marked.")
i += 1
break
# self.db.close()
# count.append(id)
if n == 0:
n = 1
else:
n = 0
if i > 0:
break
cam.release()
cv2.destroyAllWindows()
)
query = QSqlQuery()
if 'good' not in tables:
if query.exec_(
"create table good(id integer primary key autoincrement, goodname text, goodcount integer) "
):
query.finish()
query.prepare("insert into good values (null, ?, ?)")
query.addBindValue('FlashDrive')
query.addBindValue(10)
if not query.exec_():
print("Error with insert record flashdrive",
e.lastError().text())
query.finish()
query.prepare("insert into good values (null, ?, ?)")
query.bindValue(0, 'Paper for printer')
query.bindValue(1, 3)
if not query.exec_():
print("Error with insert record paper", e.lastError().text())
query.finish()
query.prepare("insert into good values (null, :name, :count)")
query.bindValue(':name', 'Cartridge for printer')
query.bindValue(':count', 8)
if not query.exec_():
print("error with insert record cartridge",
e.lastError().text())
query.finish()
query.prepare("insert into good values (null, :name, :count)")
list1 = ['Cd', 'Dvd', 'Scanner']
list2 = [9, 4, 7]
query.bindValue(':name', list1)
