def initDatabase(self):
Log.info('初始化数据库', 'Start')
self.database = QtSql.QSqlDatabase.addDatabase('QSQLITE')
self.database.setDatabaseName('data.db')
self.database.open()
query = QSqlQuery()
query.prepare(
"create table if not exists user(id integer primary key autoincrement, userName varchar(64), password varchar(32), isRemember int default(0), loginTime TIMESTAMP default (datetime('now', 'localtime')));"
)
if not query.exec_():
query.lastError()
Log.info('初始化数据库', 'Error')
else:
Log.info('初始化数据库', 'Seccsess')
query.clear()
query.prepare(
'select userName,password,isRemember,MAX(loginTime) from user;')
query.exec_()
while query.next():
acc, pwd, isCheck, time = query.value(0), query.value(
1), query.value(2), query.value(3)
print('acc={},pwd={},isCheck={},time={}\n'.format(
acc, pwd, isCheck, time))
if isCheck == 1:
self.loginAccount.setText(acc)
self.loginPwd.setText(pwd)
self.remeberPwd.setChecked(True)
def db_update_board_swrev(db, value):
query = QSqlQuery(db)
qrytxt = "UPDATE tblSmbParams SET VALUE = {v} WHERE NAME = \'SOFTWARE_REV\'".format(
v=value)
dbLogger.info(qrytxt)
query.exec_(qrytxt)
query.clear()
def db_update_board_id(db, value):
query = QSqlQuery(db)
qrytxt = "UPDATE tblSmbParams SET VALUE = {v} WHERE NAME = \'BOARD_ID\'".format(
v=value)
dbLogger.info(qrytxt)
query.exec_(qrytxt)
query.clear()
def update_components(self, id, tickets):
# TODO: capturar excepciones de error que se pueden dar
query = QSqlQuery()
self.connection.open()
sql = """DELETE FROM customnetwork_component
WHERE customnetwork_id = ?;
"""
query.prepare(sql)
query.addBindValue(id)
ok = query.exec_()
if ok:
for ticket in tickets:
sql = "SELECT id, ticket FROM component WHERE ticket = '{}';"\
.format(ticket)
if query.exec_() and query.next():
component_id = int(query.value(0))
query.clear()
sql = """INSERT into customnetwork_component
VALUES (?, ?);
"""
query.prepare(sql)
query.addBindValue(component_id)
query.exec_()
else:
comp_id = self._insert_component_table(query, ticket)
ok = self.\
_insert_customernetwork_component_table(query,
id,
comp_id)
self.model.select()
self.connection.close()
return ok
def insertMsg(self, userName, isSelf, chatWith, messageFrom,
messageContext, createTime):
try:
query = QSqlQuery()
sql = "insert into message(userName, isSelf, chatWith, messageFrom , messageContext,createTime) values('{}',{},'{}','{}','{}',{});".format(
userName, isSelf, chatWith, messageFrom, messageContext,
createTime)
print(
"userName={},isSelf={},chatWith={},messageFrom={},messageContext={},createTime={}"
.format(userName, isSelf, chatWith, messageFrom,
messageContext, createTime))
query.prepare(sql)
# query.bindValue(userName)
# query.bindValue(isSelf)
# query.bindValue(chatWith)
# query.bindValue(messageFrom)
# query.bindValue(messageContext)
# query.bindValue(createTime)
if not query.exec_():
print("插入出错")
query.lastError()
else:
print("插入成功")
query.clear()
del query
except Exception:
return
def db_software_rev(db):
query = QSqlQuery(db)
qrytxt = "SELECT VALUE from tblSmbParams WHERE NAME= \'SOFTWARE_REV\'"
query.exec_(qrytxt)
if query.first():
id = query.value(0)
query.clear()
return id
def db_fetch_board_id(db):
query = QSqlQuery(db)
qrytxt = "SELECT VALUE from tblSmbParams WHERE NAME= \'BOARD_ID\'"
query.exec_(qrytxt)
if query.first():
id = query.value(0)
query.clear()
return id
def getSort(self):
query = QSqlQuery(self.db)
query.prepare("SELECT sort FROM sections WHERE name=?")
query.addBindValue(self.name)
query.exec_()
if query.first():
data = query.record().value(0)
if data:
self.sort = bool(data)
else:
self.sort = False
query.clear()
return self.sort
def getSort(self):
query = QSqlQuery(self.db)
query.prepare("SELECT sort FROM sections WHERE name=?")
query.addBindValue(self.name)
query.exec_()
if query.first():
data = query.record().value(0)
if data:
self.sort = bool(data)
else:
self.sort = False
query.clear()
return self.sort
def _get_media_pixmap(self, _id, option: 'QStyleOptionViewItem',
size: QSize) -> QPixmap:
global PIXMAP_CACHE_MISS, PIXMAP_CACHE_HITS
pix = QPixmapCache.find(str(_id))
if pix is not None:
PIXMAP_CACHE_HITS += 1
return pix
PIXMAP_CACHE_MISS += 1
text_url_query = QSqlQuery()
text_url_query.prepare(
'select url from MediaLocations where media_id = ? AND url LIKE "data:,%" LIMIT 1;'
)
text_url_query.bindValue(0, _id)
if not text_url_query.exec():
raise RuntimeError
if text_url_query.first():
text = text_url_query.value(0).split('data:,', 1)[1]
pix = QPixmap(size)
painter = QPainter(pix)
c = option.palette.base().color()
painter.fillRect(pix.rect(), c)
# painter.setFont( QFont("Arial") );
c = option.palette.text().color()
painter.setPen(c)
painter.drawText(
pix.rect().marginsRemoved(QMargins(5, 5, 5, 5)),
# Qt.TextWrapAnywhere | Qt.TextJustificationForced,
Qt.TextWordWrap,
text)
else:
thumb_query = QSqlQuery()
thumb_query.prepare(
'select thumbnail from Thumbnails where media_id = ?;')
thumb_query.bindValue(0, _id)
if not thumb_query.exec():
raise RuntimeError
if not thumb_query.first():
raise KeyError
pxb = thumb_query.value(0)
if len(pxb) < 10:
raise ValueError(len(pxb))
assert isinstance(pxb, QByteArray), type(pxb)
# pxd = marshal.loads(zlib.decompress(pxb.data()))['jpeg']
pxd = pxb
pix = QPixmap()
pix.loadFromData(pxd)
pix = pix.scaled(size, Qt.KeepAspectRatioByExpanding)
thumb_query.clear()
QPixmapCache.insert(str(_id), pix)
text_url_query.clear()
# print(f'PIXMAP_CACHE_HITS/TOTAL = {PIXMAP_CACHE_HITS/(PIXMAP_CACHE_HITS+PIXMAP_CACHE_MISS):.0%}')
return pix
class DbData():
def __init__(self):
self.createConnection()
self.query = QSqlQuery()
return None
def createConnection(self):
self.con = QSqlDatabase.addDatabase('QODBC')
# self.con.setConnectOptions('CharSet="cp1251"')
self.con.setDatabaseName('farmadb_mdb')
if not self.con.open():
QMessageBox.critical(None, "Cannot open database", "Cannot open database.", QMessageBox.Cancel)
return False
return True
def queryTypeProd(self):
# self.query = QSqlQuery()
self.query.exec("SELECT TypeData FROM PType")
strType = []
while self.query.next():
strType = strType + [self.query.value(0)]
return strType
def queryNamePrep(self, sqlstr):
# self.query = QSqlQuery()
strsql = "SELECT Id FROM PType WHERE TypeData="+"'"+sqlstr+"'"
self.query.exec(strsql)
self.query.next()
idT = self.query.value(0)
sqlstr1 = "SELECT PName FROM Preparation WHERE TypeId="+str(idT)
self.query.exec(sqlstr1)
strName = ['']
while self.query.next():
strName = strName + [self.query.value(0)]
return strName
def queryNameSign(self, sqlstr):
# self.query = QSqlQuery()
self.query.clear()
strsql = "SELECT Id FROM Preparation WHERE PName="+"'"+sqlstr+"'"
self.query.exec(strsql)
self.query.next()
idP = self.query.value(0)
# print(self.query.lastError().text().encode('utf-8').decode('cp1251'))
sqlstr1 = "SELECT SigName FROM Significat WHERE PNameId="+str(idP)
self.query.exec(sqlstr1)
strName = ['']
while self.query.next():
strName = strName + [self.query.value(0)]
return strName
def queryIdPrepSing(self, strName, strSign):
# Отримуємо ID препарата
self.query.clear()
sqlPrep = "SELECT Id FROM Preparation WHERE PName='"+strName+"'"
self.query.exec(sqlPrep)
self.query.next()
idPrep = self.query.value(0)
# Отримуємо ID показника
self.query.clear()
sqlSign = "SELECT Id FROM Significat WHERE (PNameId=" + str(idPrep) +" AND SigName='" + strSign+"')"
# print(sqlSign.encode('utf-8').decode('cp1251'))
self.query.exec(sqlSign)
self.query.next()
idSign = self.query.value(0)
resultData = (idPrep, idSign)
return resultData
def queryGetYear(self):
self.query.clear()
self.query.exec("SELECT YData FROM Yeartable ORDER BY YData DESC")
strYear = []
while self.query.next():
strYear = strYear + [self.query.value(0)]
return strYear
def queryGetCountRec(self):
pass
def queryGetDataPrep(self, nameId, signId, yearId, typeProg):
self.query.clear()
if typeProg:
sqlstr = "SELECT ReadyData FROM Pdata WHERE ((PNameId="+str(nameId)+")"+" AND (SNameId="+str(signId)+") AND (YearId='"+str(yearId)+"'))"
else:
sqlstr = "SELECT PrepData FROM Pdata WHERE ((PNameId="+str(nameId)+")"+" AND (SNameId="+str(signId)+") AND (YearId='"+str(yearId)+"'))"
self.query.exec(sqlstr)
# self.query.next()
getData = []
while self.query.next():
tmpData = self.query.value(0)
if tmpData != '':
getData = getData + [float(tmpData)]
self.query.clear()
sqlcount = "SELECT COUNT(*) FROM Pdata WHERE ((PNameId="+str(nameId)+")" \
" AND (SNameId="+str(signId)+") AND (YearId='"+str(yearId)+"'))"
self.query.exec(sqlcount)
self.query.next()
countData = int(self.query.value(0))
return (countData, getData)
def queryGetDataSign(self, nameId, signId):
'''
:param nameId: індекс препарата
:param signId: назва показника
:return SMax, SMin, OnlyReady, верхня межа, нижня межа, тип готова, проміжна:
'''
self.query.clear()
sqlstr = "SELECT SMax, SMin, OnlyReady FROM Significat WHERE ((PNameId={0}) AND (Id={1}))".format(nameId, str(signId))
self.query.exec(sqlstr)
self.query.next()
getData = (float(self.query.value(0)), float(self.query.value(1)), bool(self.query.value(2)))
self.query.clear()
return getData
def __del__(self):
self.con.close()
return None
