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
