def build(model_type='select'):
# Дополнительный путь к SQL драйверу
QApplication.setLibraryPaths(['./platforms', './plugins'])
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('db25.db3')
if not db.open():
print('БД не существует')
sys.exit(-1)
if model_type == 'query':
return QSqlQueryModel(), db
else:
model = MyTableModel(None, db)
# model = MyTableModel(None, db, model_type)
if 'mytable' not in db.tables():
query = QSqlQuery()
query.exec_(
"create table mytable(id text primary key, title text, podsborka text, izdelie text)")
model.setTable('mytable')
model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.select()
model.setHeaderData(0, Qt.Horizontal, "id")
model.setHeaderData(1, Qt.Horizontal, "title")
model.setHeaderData(2, Qt.Horizontal, "podsborka")
model.setHeaderData(3, Qt.Horizontal, "izdelie")
return model
def createTable():
if table_name in QSqlDatabase.database().tables():
return
query = QSqlQuery()
if not query.exec_("""
CREATE TABLE IF NOT EXISTS 'Conversations' (
'author' TEXT NOT NULL,
'recipient' TEXT NOT NULL,
'timestamp' TEXT NOT NULL,
'message' TEXT NOT NULL,
FOREIGN KEY('author') REFERENCES Contacts ( name ),
FOREIGN KEY('recipient') REFERENCES Contacts ( name )
)
"""):
logging.error("Failed to query database")
# This adds the first message from the Bot
# and further development is required to make it interactive.
query.exec_("""
INSERT INTO Conversations VALUES(
'machine', 'Me', '2019-01-07T14:36:06', 'Hello!'
)
""")
logging.info(query)
def accept(self):
query = QSqlQuery(
f"update torna_match set match_status=1 where match_id={self.match_id}"
)
query.exec_()
#self.match_id
super().accept()
def build():
# Дополнительный путь к SQL драйверу
QApplication.setLibraryPaths(
['./platforms', './plugins',
QApplication.libraryPaths()[2]])
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('db_lib.db3')
if not db.open():
print('БД не существует')
sys.exit(-1)
model = MyTableModel(None, db)
if 'mytable' not in db.tables():
query = QSqlQuery()
query.exec_(
"create table mytable(id integer primary key autoincrement, author text, title text, year text)"
)
model.setTable('mytable')
model.setEditStrategy(QSqlTableModel.OnFieldChange)
model.select()
model.setHeaderData(0, Qt.Horizontal, "id")
model.setHeaderData(1, Qt.Horizontal, "author")
model.setHeaderData(2, Qt.Horizontal, "title")
model.setHeaderData(3, Qt.Horizontal, "year")
return model
def ins_tb(self, start_date, end_date):
q_list = []
for key in self.tb_name.keys():
tb_sn = 'LOGS_' + key
query = QSqlQuery(self.db)
sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO, LOGS_CHTHREE, LOGS_CHFOUR, BAT_DC_STATE FROM {tb_sn}
where LOGS_TIME > #{start_date}# AND LOGS_TIME < #{end_date}#
ORDER BY LOGS_TIME;
'''
query.exec_(sql)
# q_list.append(query)
# print(sql)
# return q_list
# 如果这个时间段没有数据，就insert数据
if not query.next():
# print(self.tb_name[sn][0], self.f_dt(start_time), self.f_dt(end_time))
# self.myinsert(tb_sn, start_time, end_time)
print(f'{self.tb_name[key][0]}没有数据,开始写入数据……')
self.myinsert(tb_sn, start_date, end_date)
else:
print(
tb_sn, self.tb_name[key][0],
query.value('LOGS_TIME').toPython().strftime(
'%Y/%#m/%d %H:%M'))
def __init__(self, db_path):
self.tb_name = {}
self.db = ''
# 连接数据库
self.db = QSqlDatabase.addDatabase("QODBC")
self.db.setDatabaseName(
"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};FIL={MS Access};DBQ="
+ db_path)
# db.setUserName('sa')
self.db.setPassword('20121110')
# 根据ok的值判断数据库是否连接成功
# self.ok = db.open()
if self.db.open():
query = QSqlQuery(self.db)
query.exec_(
"SELECT LOGGER_SN, LOGGER_NAME, CHONE_HIGH , CHONE_LOW, CHTWO_HIGH, CHTWO_LOW FROM TO_LOGGER_INFO"
)
while query.next():
# if self.tb_name[query.value(0)]!='H201403057' and self.tb_name[query.value(0)]!='HT20143221':
self.tb_name[query.value(0)] = (query.value(1), query.value(2),
query.value(3), query.value(4),
query.value(5))
del (self.tb_name['H201403057'])
del (self.tb_name['HT20143221'])
def write_tables(self):
tabla_rekordok = []
for cs in range(len(self.csoportok)):
for sor in range(len(self.csoportok[cs])):
if self.csoportok[cs][sor]._get_player_id() != 0:
tabla_rekord = []
tabla_rekord.append(self.torna_id)
tabla_rekord.append(
self.csoportok[cs][sor]._get_player_id())
tabla_rekord.append(
self.csoportok[cs][sor]._get_csoport_number())
tabla_rekord.append(
self.csoportok[cs][sor]._get_csoport_sor())
tabla_rekordok.append(tabla_rekord)
insertDataQuery = QSqlQuery()
insertDataQuery.prepare("""
insert into torna_tablak (
torna_id,
player_id,
csoport_number,
csoport_sor
)
values (?, ?, ?, ?)
""")
for x in range(len(tabla_rekordok)):
for i in range(len(tabla_rekordok[x])):
insertDataQuery.addBindValue(tabla_rekordok[x][i])
insertDataQuery.exec_()
query = QSqlQuery(
f"update torna_settings set aktiv=1 where torna_id={self.torna_id}"
)
query.exec_()
def importSteamLibrary(self):
apiKey, ok = QInputDialog.getText(self, "Import Steam Library", "Enter Steam API Key:")
if ok and not (apiKey.isspace() or apiKey == ""):
steamID, ok = QInputDialog.getText(self, "Import Steam Library", "Enter Steam User ID:")
if ok and not (steamID.isspace() or steamID == ""):
try:
games = getSteamLibrary(apiKey, steamID)
except (PermissionError, ValueError) as e:
msgBox = QMessageBox(QMessageBox.Critical, "Error", "An error occured.")
msgBox.setInformativeText(str(e))
msgBox.exec_()
else:
if "Steam" not in self.allPlatforms:
self.allPlatforms.add("Steam")
self.allRegions.add("Steam")
self.filterDock.updatePlatforms(sorted(self.allPlatforms, key=str.lower))
self.filterDock.updateRegions(sorted(self.allRegions, key=str.lower))
self.gamesTableView.addData(games)
else: # Only add games not already in collection
existingGames = []
query = QSqlQuery()
query.exec_("SELECT Name from games WHERE Region='Steam'")
while query.next():
existingGames.append(query.value(0))
for game in games:
if game["name"] not in existingGames:
self.gamesTableView.addData(game)
self.overview.updateData(self.gamesTableView)
self.randomizer.updateLists(self.gamesTableView.ownedItems(),
sorted(self.allPlatforms, key=str.lower),
sorted(self.allGenres, key=str.lower))
self.search()
def add_resztvevo(self, item):
new_item = QListWidgetItem(item)
new_item.setData(Qt.UserRole, item.data(Qt.UserRole))
self.current_players.addItem(new_item)
query = QSqlQuery(
f"insert into torna_resztvevok (player_id, player_name, torna_id) values ({new_item.data(Qt.UserRole)}, '{new_item.text()}', {self.torna_id})"
)
query.exec_()
def remove_resztvevo(self, item):
self.current_players.takeItem(
self.current_players.row(self.current_players.selectedItems()[0]))
# print(item.data(Qt.UserRole), item.text())
query = QSqlQuery(
f"delete from torna_resztvevok where player_id={item.data(Qt.UserRole)} and torna_id={self.torna_id}"
)
query.exec_()
def select_table(self):
query = QSqlQuery(self.db)
sql = f'''SELECT LOGGER_SN, LOGGER_NAME, CHONE_HIGH , CHONE_LOW, CHTWO_HIGH, CHTWO_LOW
FROM TO_LOGGER_INFO
where LOGGER_SN in {str(tuple(self.tb_name.keys()))} ORDER BY CHONE_HIGH,LOGGER_NAME '''
query.exec_(sql)
# print('显示表' + sql)
# print(str(tuple(self.tb_name.keys())))
return query
def get_password() -> str:
"""Return the password from the database."""
query = QSqlQuery(QSqlDatabase.database())
query.exec_("select password from credentials")
if query.next():
password = query.value(0)
query.finish()
return password
def load_data(self, tornaid):
self.clear()
query = QSqlQuery(
f"select * from torna_resztvevok where torna_id={tornaid}")
query.exec_()
while query.next():
item = QListWidgetItem(query.value(1))
item.setData(1, query.value(0))
self.addItem(item)
def select(self, query):
if self.db.open():
res = []
sql_query = QSqlQuery()
sql_query.exec_(query)
while sql_query.next():
res.append(sql_query.value(0))
self.db.close()
return res
def up_data(self, tb, high, logs_time, x):
query = QSqlQuery(self.db)
if x == 'one':
sql = f"update {tb} set LOGS_CHONE={high} where LOGS_TIME = #{logs_time}#"
# print('修改详细温度：' + sql)
query.exec_(sql)
elif x == 'two':
sql = f"update {tb} set LOGS_CHTWO={high} where LOGS_TIME = #{logs_time}#"
# print('修改详细湿度：' + sql)
query.exec_(sql)
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 load(self, lst):
query = QSqlQuery()
query.exec_("SELECT task from " + lst + ";")
while query.next():
print(query.value(0))
if lst == "priority":
self.ui.priority.addItem(query.value(0))
elif lst == "urgent":
self.ui.urgent.addItem(query.value(0))
elif lst == "dueDate":
self.ui.dueDate.addItem(query.value(0))
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 show_data(self, SN, start_date, end_date):
tb_sn = 'LOGS_' + SN
query = QSqlQuery(self.db)
sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO
FROM {tb_sn}
where LOGS_TIME between #{start_date}# and #{end_date}#
order by LOGS_TIME
'''
query.exec_(sql)
print('显示超标数据' + sql)
return query
def show_errdata(self, SN, start_date, end_date, one_high, one_low,
two_high, two_low):
tb_sn = 'LOGS_' + SN
query = QSqlQuery(self.db)
sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO
FROM {tb_sn}
where LOGS_TIME between #{start_date}# and #{end_date}#
and (LOGS_CHONE >= {one_high} OR LOGS_CHONE <= {one_low} OR LOGS_CHTWO >= {two_high} OR LOGS_CHTWO <={two_low})
'''
query.exec_(sql)
print('显示超标数据' + sql)
return query
def torna_valasztas(self, i):
self.torna_id = self.tournaments.itemData(i)
torna = QSqlQuery(
f"select * from torna_settings where torna_id={self.torna_id}")
torna.exec_()
while torna.next():
self.csoportok_szama = torna.value(3)
self.sorok_szama = torna.value(4)
self.variant = torna.value(5)
self.sets = torna.value(7)
self.legsperset = torna.value(8)
self.create_widgets()
self.set_layout()
def uj_ember(self):
ujember, ok = QInputDialog.getText(
self, "Új versenyző",
'<html style="font-size: 15px;">Írd be a versenyző nevét!</html>')
if ok and len(ujember):
item = QListWidgetItem(ujember)
item.setData(Qt.UserRole, self.first_new_id)
self.current_players.addItem(item)
self.first_new_id += 1
query = QSqlQuery(
f"insert into torna_resztvevok (player_id, player_name, torna_id) values ({item.data(Qt.UserRole)}, '{item.text()}', {self.torna_id})"
)
query.exec_()
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 change_data(self, szum=None):
self.data = None
self.data = szum
self.name1 = self.data[1]
self.name2 = self.data[3]
match_id = self.data[6]
p1_id = self.data[0]
p2_id = self.data[2]
# Végeredmény
self.won1 = 0
self.won2 = 0
self.avg1 = self.avg2 = 0
eredmenyek_model = QSqlQueryModel()
eredmenyek_query = QSqlQuery(f"select * from matches where match_id={match_id}", db=db)
eredmenyek_model.setQuery(eredmenyek_query)
for x in range(1, self.data[8] + 1):
l1 = l2 = 0
for i in range(eredmenyek_model.rowCount()): # csak set-eket összesítünk
if eredmenyek_model.record(i).value(2) == x:
if eredmenyek_model.record(i).value(3) == p1_id:
l1 += 1
else:
l2 += 1
# print("Set: ", x, "L1: ", l1, "L2: ", l2)
if self.data[8] == 1:
self.won1 = l1
self.won2 = l2
else:
if l1 > l2:
self.won1 += 1
else:
self.won2 += 1
# Átlagok
db1 = db2 = sum1 = sum2 = 0
for x in range(1, self.data[8] + 1):
for leg in range(1, self.data[7][x - 1] + 1):
query = QSqlQuery(f"select max(round_number) as maxround, sum(points) as sumpont from dobas where leg_id={leg} and set_id={x} and match_id={match_id} and player_id={p1_id}")
query.exec_()
while query.next():
db1 += query.value(0)
sum1 += query.value(1)
query2 = QSqlQuery(f"select max(round_number) as maxround, sum(points) as sumpont from dobas where leg_id={leg} and set_id={x} and match_id={match_id} and player_id={p2_id}")
query2.exec_()
while query2.next():
db2 += query2.value(0)
sum2 += query2.value(1)
self.avg1 = round(sum1 / db1 * 3, 2)
self.avg2 = round(sum2 / db2 * 3, 2)
self.update()
def get_adatok(self, para):
print(para)
# self.adatok[0] : p1_id
# self.adatok[1] : name1
# self.adatok[2] : p2_id
# self.adatok[3] : name2
# self.adatok[4] : start_score1
# self.adatok[5] : start_score2
# self.adatok[6] : match
# self.adatok[7] : legs
# self.adatok[8] : sets
# self.adatok[9] : dátum
self.adatok = []
name1_id = int(para[0])
self.adatok.append(name1_id)
query_name1 = QSqlQuery(f"select player_name from players where player_id={name1_id}", db=db)
query_name1.exec_()
while query_name1.next():
name1 = query_name1.value(0)
self.adatok.append(name1)
name2_id = int(para[1])
self.adatok.append(name2_id)
query_name2 = QSqlQuery(f"select player_name from players where player_id={name2_id}", db=db)
query_name2.exec_()
while query_name2.next():
name2 = query_name2.value(0)
self.adatok.append(name2)
start_score1 = int(para[2])
start_score2 = int(para[3])
match = int(para[7])
setek = int(para[5])
self.adatok.append(start_score1)
self.adatok.append(start_score2)
self.adatok.append(match)
# Kell a max set-number, ezt beállítani a sets változóba
# Ciklussal minden set-ben megnézni a max leg-numbert, és ezeket append-elni a legs[]-hez
# Végül leg, set sorrendben append-elni az adatokhoz
legs = []
sets = 0
query2 = QSqlQuery(
f"select max(set_id) as max_set from matches where match_id={match}", db=db)
query2.exec_()
while query2.next():
sets = int(query2.value(0))
for i in range(1, sets + 1):
query = QSqlQuery(f"select max(leg_id) as max_leg from matches where match_id={match} and set_id={i}", db=db)
query.exec_()
while query.next():
legs.append(int(query.value(0)))
# sets.append(int(query.value(1)))
self.adatok.append(legs)
self.adatok.append(sets)
datum = para[6][:16]
self.adatok.append(datum)
print(self.adatok)
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 show_current_players(self):
query = QSqlQuery("select max(player_id) from torna_resztvevok")
query.exec_()
while query.next():
self.first_new_id = int(query.value(0)) + 1
print(self.first_new_id)
self.add_new = QPushButton("Új")
self.add_new.clicked.connect(self.uj_ember)
self.current_players = QListWidget()
self.current_players.setFixedHeight(470)
self.current_players.setFixedWidth(150)
self.current_players.setSortingEnabled(True)
self.gomb_nev_layout.addWidget(self.add_new)
self.current_players.itemDoubleClicked.connect(self.remove_resztvevo)
self.gomb_nev_layout.addWidget(self.current_players)
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 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 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
