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)