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)
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 getStatusCode(self):
if self.statusCode == 8:
query = QSqlQuery("SELECT COUNT(*) FROM Student JOIN "
"Attendance ON Student.id = Attendance.id "
"AND Attendance.date = (SELECT date('now')) "
"AND Attendance.lecture_no = 1")
query.next()
self.ocount = query.value(0)
self.oCount_changed.emit()
return self.statusCode
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 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 get_torrent(self):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(
query=
"select * from torrents where (hash is null) and (last_checked is null) order by id desc limit 1;",
db=db)
topic = {}
while query.next():
topic['id'] = query.value('id')
topic['forum'] = query.value('forum')
topic['title'] = query.value('title')
topic['seed'] = query.value('seed')
topic['leech'] = query.value('leech')
topic['published'] = query.value('published')
topic['last_modified'] = query.value('last_modified')
topic['hash'] = query.value('hash')
topic['downloads'] = query.value('downloads')
topic['last_checked'] = query.value('last_checked')
topic['user_id'] = query.value('user_id')
return topic
finally:
db.close()
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 __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 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 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 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 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 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 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 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.nyereshez_kell = torna.value(8)
self.pont_gyozelem = torna.value(11)
self.pont_vereseg = torna.value(13)
self.tablak = []
for cs in range(self.csoportok_szama):
tablasor = []
for sor in range(self.sorok_szama):
tablasor.append(0)
self.tablak.append(tablasor)
query2 = QSqlQuery(
f"select * from torna_tablak where torna_id={self.torna_id}")
query2.exec_()
while query2.next():
self.tablak[query2.value(2)][query2.value(3)] = query2.value(1)
self.nevek = []
for cs in range(self.csoportok_szama):
neveksor = []
for sor in range(self.sorok_szama):
neveksor.append("")
self.nevek.append(neveksor)
query2 = QSqlQuery(
f"select player_id, player_name from torna_resztvevok where torna_id={self.torna_id}"
)
query2.exec_()
while query2.next():
for x in range(self.csoportok_szama):
for y in range(self.sorok_szama):
if query2.value(0) == self.tablak[x][y]:
self.nevek[x][y] = query2.value(1)
self.create_widgets()
self.set_layout()
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 create_merchandise(code: str, description: str, by_metre: bool,
discount_group: str, price: float) -> int:
unit = 'm' if by_metre else 'pc.'
query_text = f"SELECT public.create_merchandise('{code}', '{description}', '{unit}', '{discount_group}', {price})"
query = QSqlQuery(query_text)
if not query.next():
raise RuntimeError(
f"Query {query_text} failed with:\n{query.lastError().text()}")
merchandise_id = query.value(0)
if merchandise_id < 0:
raise RuntimeError(f"Query {query_text} failed on server side")
return merchandise_id
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 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()
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 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 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 LoadHistory(self):
query = QSqlQuery(self.db)
query.exec_("""
select * from history
""")
while query.next():
# bookId, name, epsId, index, url, path
info = QtHistoryData()
info.bookId = query.value(0)
info.name = query.value(1)
info.epsId = query.value(2)
info.picIndex = query.value(3)
info.url = query.value(4)
info.path = query.value(5)
info.tick = query.value(6)
self.history[info.bookId] = info
pass
def get_forum_to_scan(self) -> object:
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(query="select * from forums_to_scan_2 limit 1;",
db=db)
forum = {}
while query.next():
forum['id'] = query.value(0)
forum['title'] = query.value(3)
forum['delta'] = query.value('delta')
return forum
except Exception as ex:
print(str(ex))
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 up_table(self, start_date, end_date):
query = QSqlQuery(self.db)
for sn in self.tb_name.keys():
tb_sn = 'LOGS_' + sn
# one_high最高温度，one_low最低温度，two_high最高湿度,two_low最低湿度
one_high = float(self.tb_name[sn][1])
one_low = float(self.tb_name[sn][2])
two_high = float(self.tb_name[sn][3])
two_low = float(self.tb_name[sn][4])
# 查询出温度大于或者小于范围 1 度的或者湿度大于、小于1%。
sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO
FROM {tb_sn}
where (LOGS_CHONE > {one_high + 1} or LOGS_CHONE < {one_low - 1} or LOGS_CHTWO > {two_high + 1} OR LOGS_CHTWO < {two_low - 1}) and LOGS_TIME between #{start_date}# and #{end_date}#'
'''
if (query.exec_(sql)):
print('修改所有表' + sql)
q = QSqlQuery(self.db)
j = 0
while query.next():
t = query.value(0).toString('yyyy/M/d h:mm:ss')
one = float(query.value(1))
two = float(query.value(2))
# 温度超标
if (one > one_high or one < one_low) and (two < two_high
and two > two_low):
if abs(one_low - one) < abs(one_high - one):
sql = f"update {tb_sn} set LOGS_CHONE = {random.uniform(one_low, one_low + 2)} WHERE LOGS_TIME = #{t}#"
q.exec_(sql)
print(sql)
else:
sql = f"update {tb_sn} set LOGS_CHONE = {random.uniform(one_high - 1, one_high)} WHERE LOGS_TIME = #{t}#"
q.exec_(sql)
print(sql)
# 湿度超标
elif (one < one_high and one > one_low) and (two > two_high
or two < two_low):
if abs(one_low - one) < abs(one_high - one):
sql = f"update {tb_sn} set LOGS_CHTWO = {random.uniform(two_low, two_low + 2)} WHERE LOGS_TIME = #{t}#"
q.exec_(sql)
print(sql)
else:
sql = f"update {tb_sn} set LOGS_CHTWO = {random.uniform(two_high - 1, two_high)} WHERE LOGS_TIME = #{t}#"
q.exec_(sql)
print(sql)
def get_categories(self):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(db=db)
query.prepare("select * from categories;")
query.exec_()
categories: List[Dict[str, None]] = []
while query.next():
category = {
'id': query.value('id'),
'title': query.value('title')
}
categories.append(category)
return categories
finally:
db.close()
def get_new_user(self):
db = self.get_db()
try:
if not db.isOpen():
db.open()
query = QSqlQuery(
query=
'select distinct user_id from torrents as t left join users as u on t.user_id=u.id where u.id is null and user_id > 0 limit 1;',
db=db)
user = {}
while query.next():
user['id'] = query.value('user_id')
# user['name'] = query.value('name')
# user['registered'] = query.value('registered')
# user['nation'] = query.value('nation')
return user
finally:
db.close()
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()
