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 start_game(self):
para = self.merkozesek.currentItem().text().rsplit("\t")
p1_model = QSqlQueryModel()
p1_query = QSqlQuery(
f"select player_id from torna_resztvevok where player_name='{para[2]}'"
)
p1_model.setQuery(p1_query)
p2_model = QSqlQueryModel()
p2_query = QSqlQuery(
f"select player_id from torna_resztvevok where player_name='{para[3]}'"
)
p2_model.setQuery(p2_query)
params = []
params.append(para[2])
params.append(para[3])
params.append(int(para[0]))
self.match_id = int(para[0])
params.append(int(p1_model.record(0).value(0)))
params.append(int(p2_model.record(0).value(0)))
params.append(para[4])
params.append(int(para[6]))
params.append(int(para[5]))
params.append(0) # hc1
params.append(0) # hc2
params.append(0) #bets_of...
self.parent.new_game_window.params = params
self.parent.new_game_window.refresh()
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 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 __init__(self):
QMainWindow.__init__(self)
Ui_MainWindow.__init__(self)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.ui.dateEdit.setDate(QtCore.QDate.currentDate())
self.ui.dateEdit.setMinimumDate(QtCore.QDate.currentDate())
self.ui.dateEdit.setMaximumDate(QtCore.QDate(8181, 12, 31))
self.ui.AddBtn.clicked.connect(self.save)
self.ui.lineEdit.returnPressed.connect(self.save)
self.db = QSqlDatabase.addDatabase("QSQLITE")
self.db.setDatabaseName("store")
dateMe = QtCore.QDate.currentDate()
self.db.open()
self.que = QSqlQuery("CREATE Table urgent (priority integer, "
"task varchar(256), date datetime);")
if self.que.exec_:
print("Executed successful.")
self.que = QSqlQuery("CREATE Table priority (priority integer, "
"task varchar(256), date datetime);")
if self.que.exec_:
print("Executed successful.")
self.que = QSqlQuery("CREATE Table dueDate (priority integer, "
"task varchar(256), date datetime);")
if self.que.exec_:
print("Executed successful.")
self.load("priority")
self.load("urgent")
self.load("dueDate")
self.ui.priority.itemDoubleClicked.connect(self.editPriority)
self.ui.urgent.itemDoubleClicked.connect(self.editUrgent)
self.ui.dueDate.itemDoubleClicked.connect(self.editDueDate)
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 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 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 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 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 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 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_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')
self.db.setDatabaseName('DataBaseName.sqlite3')
# self.db.setDatabaseName(':memory:')
self.db.open()
self.query = QSqlQuery()
# Criando a tabela.
self.create_table()
def create_customer(title: str, first_name: str, last_name: str,
company_name: str, address: str) -> None:
query_text = f"""
INSERT INTO public.customers(title, first_name, last_name, company_name, address)
VALUES ('{title}', '{first_name}', '{last_name}', '{company_name}', '{address}')
;"""
query = QSqlQuery()
if not query.exec_(query_text):
raise RuntimeError(
f"Query {query_text} failed with:\n{query.lastError().text()}")
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 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 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 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 del_table(self):
query = QSqlQuery(self.db)
for key in self.tb_name.keys():
# 不修改保温箱跟冷藏车的记录
tb_sn = 'LOGS_' + key
del_sql = f'''delete FROM {tb_sn} where (LOGS_TIME LIKE '%:[!03][0-9]:00' OR LOGS_TIME LIKE '%:[03][1-9]:00')
AND LOGS_TIME NOT LIKE '2019/7/3 %' and LOGS_TIME NOT LIKE '2020/4/21 %'
'''
print('删除数据：' + del_sql)
value = query.exec_(del_sql)
return value
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 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 __init__(self, parent_view):
super().__init__(parent_view)
self._view = parent_view
self._amount_delegate = None
self._data = []
self._row_count = 0
self._query = QSqlQuery(db_connection())
self._begin = 0
self._end = 0
self._account = 0
self._text_filter = ''
self.prepareData()
def __init__(self, parent_view):
super().__init__(parent_view)
self._view = parent_view
self._amount_delegate = None
self._data = []
self._row_count = 0
self._table_name = 'all_operations'
self._query = QSqlQuery(db_connection())
self._begin = 0
self._end = 0
self._account = 0
self.prepareData()
def load_spatialite():
queries = (
"SELECT load_extension('mod_spatialite')",
"SELECT InitSpatialMetadata(1)",
)
q = QSqlQuery()
for query in queries:
if not q.exec_(query):
print(
f"Error: cannot load the Spatialite extension ({q.lastError().text()})"
)
return False
return True
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_()
