def selectModel(self): conn = self.db.getConnection() model = QSqlQueryModel() query = QSqlQuery(conn) self.buildSqlStatement(query) query.exec_() if query.lastError().driverText(): print("Query error msg:", query.lastError().driverText()) model.setQuery(query) self.ui.tblStats.setModel(model) self.ui.tblStats.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
def DelDownloadDB(self, bookId): query = QSqlQuery(self.db) sql = "delete from download where bookId='{}'".format(bookId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) sql = "delete from download_eps where bookId='{}'".format(bookId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
def DelHistory(self, bookId): query = QSqlQuery(self.db) sql = "delete from history where bookId='{}'".format(bookId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
def AddHistory(self, bookId, name, epsId, index, url, path): tick = int(time.time()) info = self.history.get(bookId) if not info: info = QtHistoryData() self.history[bookId] = info info.bookId = bookId info.name = name info.epsId = epsId info.picIndex = index info.url = url info.path = path info.tick = tick query = QSqlQuery(self.db) sql = "INSERT INTO history(bookId, name, epsId, picIndex, url, path, tick) " \ "VALUES ('{0}', '{1}', {2}, {3}, '{4}', '{5}', {6}) " \ "ON CONFLICT(bookId) DO UPDATE SET name='{1}', epsId={2}, picIndex={3}, url = '{4}', path='{5}', tick={6}".\ format(bookId, name, epsId, index, url, path, tick) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
def get_merchandise_record(merchandise_id: int) -> QSqlRecord: text = f"select * from merchandise_view('{date.today()}') where merchandise_id = '{merchandise_id}'" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") return query.record()
def get_new_offer_number(user_id: int) -> int: text = f"SELECT public.get_new_offer_number({user_id})" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") return query.record().value(0)
def LoadDownload(self, owner): query = QSqlQuery(self.db) suc = query.exec_(""" select * from download """) if not suc: Log.Warn(query.lastError().text()) downloads = {} while query.next(): # bookId, downloadEpsIds, curDownloadEpsId, curConvertEpsId, title, savePath, convertPath info = DownloadInfo(owner) info.bookId = query.value(0) data = json.loads(query.value(1)) info.downloadEpsIds = [int(i) for i in data] info.curDownloadEpsId = query.value(2) info.curConvertEpsId = query.value(3) info.title = query.value(4) info.savePath = query.value(5) info.convertPath = query.value(6) info.status = query.value(7) info.convertStatus = query.value(8) downloads[info.bookId] = info query = QSqlQuery(self.db) suc = query.exec_(""" select * from download_eps """) if not suc: Log.Warn(query.lastError().text()) while query.next(): # bookId, epsId, epsTitle, picCnt, curPreDownloadIndex, curPreConvertId bookId = query.value(0) task = downloads.get(bookId) if not task: continue info = DownloadEpsInfo(task) info.epsId = query.value(1) info.epsTitle = query.value(2) info.picCnt = query.value(3) info.curPreDownloadIndex = query.value(4) info.curPreConvertId = query.value(5) task.epsInfo[info.epsId] = info return downloads
def get_var(key: str) -> str: text = f"SELECT value FROM public.vars WHERE key = '{key}'" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") record = query.record() return record.value("value")
def __init__(self): self.db = QSqlDatabase.addDatabase("QSQLITE", "download") self.db.setDatabaseName("download.db") if not self.db.open(): Log.Warn(self.db.lastError().text()) query = QSqlQuery(self.db) sql = """\ create table if not exists download(\ bookId varchar primary key,\ downloadEpsIds varchar,\ curDownloadEpsId int,\ curConvertEpsId int,\ title varchar,\ savePath varchar,\ convertPath varchar,\ status varchar,\ convertStatus varchar\ )\ """ suc = query.exec_(sql) if not suc: a = query.lastError().text() Log.Warn(a) query = QSqlQuery(self.db) sql = """\ create table if not exists download_eps(\ bookId varchar,\ epsId int,\ epsTitle varchar,\ picCnt int,\ curPreDownloadIndex int,\ curPreConvertId int,\ primary key (bookId,epsId)\ )\ """ suc = query.exec_(sql) if not suc: a = query.lastError().text() Log.Warn(a)
def AddDownloadEpsDB(self, info): assert isinstance(info, DownloadEpsInfo) query = QSqlQuery(self.db) sql = "INSERT INTO download_eps(bookId, epsId, epsTitle, picCnt, curPreDownloadIndex, curPreConvertId) " \ "VALUES ('{0}', {1}, '{2}', {3}, {4}, {5}) " \ "ON CONFLICT(bookId, epsId) DO UPDATE SET epsTitle='{2}', picCnt={3}, curPreDownloadIndex={4}, " \ "curPreConvertId = {5}".\ format(info.parent.bookId, info.epsId, info.epsTitle, info.picCnt, info.curPreDownloadIndex, info.curPreConvertId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
class DataBase: def __init__(self): db_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'data.db') self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(db_path) self.db.open() self.query = QSqlQuery() def select(self, fields: tuple, table: str, condition=None): """ select items from table For example, to execute the sql state `select name from Teacher where tid=1`, use `select(fields=('name',), table='Teacher', condition='tid=1')` params fields: tuple of str, such as ('id', 'name', 'type') table: str, name of target table condition: str, such as 'id=1' returns a list of dict """ state = 'select {fields} from {table}'.format(fields=', '.join(fields), table=table) if condition is not None: state += ' where {con}'.format(con=condition) res = [] self.query.clear() self.query.exec_(state) print(state) while self.query.next(): item = {f: self.query.value(f) for f in fields} res.append(item) return res def insert(self, table: str, kv: dict): state = 'insert into {t} ({f}) values ({v})'.format( t=table, f=', '.join(kv.keys()), v=', '.join(kv.values())) print(state) return self.query.exec_(state) def __add_quote(self, s: str): return '"{}"'.format(s) def last_error(self): return self.query.lastError()
def AddDownloadDB(self, task): assert isinstance(task, DownloadInfo) query = QSqlQuery(self.db) sql = "INSERT INTO download(bookId, downloadEpsIds, curDownloadEpsId, curConvertEpsId, title, " \ "savePath, convertPath, status, convertStatus) " \ "VALUES ('{0}', '{1}', {2}, {3}, '{4}', '{5}', '{6}', '{7}', '{8}') " \ "ON CONFLICT(bookId) DO UPDATE SET downloadEpsIds='{1}', curDownloadEpsId={2}, curConvertEpsId={3}, " \ "title = '{4}', savePath = '{5}', convertPath= '{6}', status = '{7}', convertStatus = '{8}'".\ format(task.bookId, json.dumps(task.downloadEpsIds), task.curDownloadEpsId, task.curConvertEpsId, task.title, task.savePath, task.convertPath, task.status, task.convertStatus) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
def __init__(self, owner): super(self.__class__, self).__init__(owner) Ui_History.__init__(self) self.setupUi(self) self.owner = weakref.ref(owner) self.bookList = QtBookList(self, self.__class__.__name__) QtBookListMenu.__init__(self) self.bookList.InitBook(self.LoadNextPage) self.gridLayout_3.addWidget(self.bookList) self.pageNums = 20 self.lineEdit.setValidator(QtIntLimit(1, 1, self)) self.history = {} self.db = QSqlDatabase.addDatabase("QSQLITE", "history") self.db.setDatabaseName("history.db") if not self.db.open(): Log.Warn(self.db.lastError().text()) query = QSqlQuery(self.db) sql = """\ create table if not exists history(\ bookId varchar primary key,\ name varchar,\ epsId int, \ picIndex int,\ url varchar,\ path varchar,\ tick int\ )\ """ suc = query.exec_(sql) if not suc: a = query.lastError().text() Log.Warn(a) self.LoadHistory()
def prepare_db(self) -> bool: """Create the Database from the DDL. The DDL is a script that provided as QResource Returns: True if creation succesful. """ if not self.database.isValid(): self.log.warning("The Database is not valid, reopen") self.database = QSqlDatabase.database(self.database_name) self.database.open() query = QSqlQuery(self.database) file = QFile(":/data/script.sql") if not file.exists(): self.log.error("Kritischer Fehler beim erzeugen der Datenbank" " {} nicht gefunden".format(file.fileName())) return False if not file.open(QIODevice.ReadOnly): self.log.error( "Kritischer Fehler bei der Initialisierung der Datenbank." "Die Datei {} konnte nicht geöffnet werden".format( file.fileName())) ts = QTextStream(file) line: str = "" cleaned_line: str = "" string_list: list = [] read_line: list = [] self.log.info("File at end: {}".format(ts.atEnd())) while not ts.atEnd(): has_text: bool = False line = "" string_list.clear() while not has_text: read_line = ts.read_line() self.log.info("read Line: {}".format(read_line)) cleaned_line = read_line.strip() string_list = cleaned_line.split("--") cleaned_line = string_list[0] if not cleaned_line.startswith( "--") and not cleaned_line.startswith("DROP"): line += cleaned_line if cleaned_line.endswith(";"): break if cleaned_line.startswith("COMMIT"): has_text = True if not line == "": self.log.info("Line: {}".format(line)) if not query.exec_(line): self.log.error( "Fehler beim Erzeugen der Tabelle {}".format(line)) self.log.error("Datenbank meldet Fehler {}".format( query.lastError())) return False else: self.log.error( "Fehler beim Lesen der Datei zur Datenbank Erzeugung: ") return False file.close() self.log.info("Datenbank erfolgreich erzeugt") return True
def run(self): ## print(getpass.getuser(), self.user_entry.text()) e = WorkWithOCI( "XE", self.user_entry.text(), keyring.get_password(getpass.getuser(), self.user_entry.text())) #print("Name of database:", e.name) # print("Host :", e.host) conn = e.open_db() if conn: self.error_label.setText("Ошибок уже/пока нет") query = QSqlQuery() query2 = QSqlQuery() # Доступность if query.exec_(self.bars): #print("Q1 done!") query.finish() # Create model self.sqm = QSqlQueryModel(parent=self.table) # Сам запрос self.sqm.setQuery( self.table_query.format(self.ru_entry.text().strip(), self.cur_entry.text().strip(), self.balance_entry.text().strip())) if query2.exec_( self.table_query.format( self.ru_entry.text().strip(), self.cur_entry.text().strip(), int(self.balance_entry.text().strip()))): #print("Q2 done!") self.sqm.setQuery(query2) else: self.print_and_label("Ошибка 2-го запроса") print( "2-й запрос (", self.table_query.format( self.ru_entry.text().strip(), self.cur_entry.text().strip(), int(self.balance_entry.text().strip())), ") :", query.lastError().text()) # Задаем заголовки для столбцов модели self.sqm.setHeaderData(0, Qt.Horizontal, "Счет") self.sqm.setHeaderData(1, Qt.Horizontal, "РУ") self.sqm.setHeaderData(2, Qt.Horizontal, "Валюта") self.sqm.setHeaderData(3, Qt.Horizontal, "Остаток") # self.print_and_label(sqm.lastError().text()) # Задаем для таблицы только что созданную модель self.table.setModel(self.sqm) # Not)Скрываем первый столбец, в котором выводится идентификатор # self.table.hideColumn(0) self.table.setColumnWidth(0, 150) self.table.setColumnWidth(1, 60) self.table.setColumnWidth(2, 80) self.table.setColumnWidth(3, 150) # print("sqm.rowCount()", self.sqm.rowCount()) if self.sqm.rowCount() > 0: frequency = 2500 duration = 2000 winsound.Beep(frequency, duration) conn.close() #conn.removeDatabase('qt_sql_default_connection') else: self.print_and_label("Ошибка первого запроса (", self.bars, ") :", query.lastError().text()) else: self.print_and_label("Ошибка открытия базы данных")
class ConnectPostgreSQL: def __init__(self): self.db = QSqlDatabase.addDatabase('QPSQL') self.db.setHostName('localhost') self.db.setPort(5432) self.db.setDatabaseName('database_name') self.db.setUserName('dbuser') self.db.setPassword('123456') self.db.open() self.query = QSqlQuery() # Removendo a tabela. self.drop_table(table='table_name') # Criando a tabela. self.create_table() def create_table(self): sql = '''CREATE TABLE IF NOT EXISTS table_name ( id SERIAL NOT NULL, name VARCHAR(100) NOT NULL, age INTEGER NOT NULL, gender VARCHAR(10) NOT NULL, PRIMARY KEY(id) );''' self.query.exec_(sql) def drop_table(self, table): sql = f'DROP TABLE IF EXISTS {table};' self.query.exec_(sql) def insert_row(self, data): sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)' self.query.prepare(sql) for index, value in enumerate(data): self.query.addBindValue(data[index]) if self.query.exec_(): self.query.clear() return True print(self.query.lastError()) return False def insert_rows(self, data): for row in data: self.insert_row(data=row) def find_by_id(self, rowid): sql = 'SELECT * FROM table_name WHERE id = ?' self.query.prepare(sql) self.query.addBindValue(rowid) self.query.exec_() if self.query.first(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) self.query.clear() return data print(self.query.lastError()) return False def find(self, limit=10): sql = 'SELECT * FROM table_name LIMIT ?' self.query.prepare(sql) self.query.addBindValue(limit) self.query.exec_() result = [] while self.query.next(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) result.append(data) self.query.clear() return result def update_row(self, rowid, name, age, gender): sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?' self.query.prepare(sql) self.query.addBindValue(name) self.query.addBindValue(age) self.query.addBindValue(gender) self.query.addBindValue(rowid) if self.query.exec_(): return True print(self.query.lastError()) return False def remove_row(self, rowid): sql = 'DELETE FROM table_name WHERE id=?' self.query.prepare(sql) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False
class ConnectSQLite: def __init__(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('DataBaseName.sqlite3') # self.db.setDatabaseName(':memory:') self.db.open() self.query = QSqlQuery() # Removendo a tabela. self.drop_table(table='table_name') # Criando a tabela. self.create_table() def create_table(self): sql = '''CREATE TABLE IF NOT EXISTS table_name ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, gender TEXT );''' self.query.exec_(sql) def drop_table(self, table): sql = f'DROP TABLE IF EXISTS {table};' self.query.exec_(sql) def insert_row(self, data): sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)' self.query.prepare(sql) for index, value in enumerate(data): self.query.addBindValue(data[index]) self.query.exec_() def insert_rows(self, data): for row in data: self.insert_row(data=row) def find_by_id(self, rowid): sql = 'SELECT * FROM table_name WHERE id = ?' self.query.prepare(sql) self.query.addBindValue(rowid) self.query.exec_() if self.query.first(): return (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) return False def find(self, limit=10): sql = 'SELECT * FROM table_name LIMIT ?' self.query.prepare(sql) self.query.addBindValue(limit) self.query.exec_() result = [] while self.query.next(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) result.append(data) return result def update_row(self, rowid, name, age, gender): sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?' self.query.prepare(sql) self.query.addBindValue(name) self.query.addBindValue(age) self.query.addBindValue(gender) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False def remove_row(self, rowid): sql = 'DELETE FROM table_name WHERE id=?' self.query.prepare(sql) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False