def regenerateAllChannelThumbnails(self, refresh=False):
query = QSqlQuery("""SELECT channel_id FROM channels""")
query.exec()
channel_ids = []
while query.next():
channel_ids.append(query.value(0))
self.generateChannelThumbnails(channel_ids, refresh)
def insertPlaylist(self, playlist):
'''
Insert playlist into db
'''
playlist_id = playlist["id"]
playlist_title = playlist["snippet"]["title"]
description = playlist["snippet"]["description"]
thumbnails = playlist["snippet"]["thumbnails"]
if not self.playlistInDb(playlist_id):
query = QSqlQuery()
query.prepare("""INSERT INTO playlists VALUES (?,?,?)""")
for value in (playlist_id, playlist_title, description):
query.addBindValue(value)
query.exec()
for size, entry in thumbnails.items():
width = entry["width"]
height = entry["height"]
url = entry["url"]
query = QSqlQuery()
query.prepare(
"""INSERT INTO playlist_thumbnails VALUES (?,?,?,?,?)""")
for value in (playlist_id, size, width, height, url):
query.addBindValue(value)
query.exec()
def getAllParents(self, tagSet, yt_tags=False):
parent_table = "yt_tags_parent" if yt_tags else "tags_parent"
# Build directed graph of child to parent relationships
query = QSqlQuery(f"SELECT parent_tag, child_tag FROM {parent_table}")
query.exec()
directedGraph = {}
while query.next():
pTag = query.value(0)
cTag = query.value(1)
directedGraph.setdefault(pTag, set())
directedGraph.setdefault(cTag, set())
directedGraph[cTag].add(pTag)
def recursiveGetParents(tag, directedGraph):
newTagSet = set()
newTagSet.add(tag)
for newTag in directedGraph.get(tag, set()):
newTagSet = newTagSet | recursiveGetParents(
newTag, directedGraph)
return newTagSet
newTagSet = set()
for tag in tagSet:
newTagSet = newTagSet | recursiveGetParents(tag, directedGraph)
return newTagSet
def regenerateAllVideoThumbnails(self, refresh=False):
query = QSqlQuery("""SELECT video_id FROM videos""")
query.exec()
video_ids = []
while query.next():
video_ids.append(query.value(0))
self.generateVideoThumbnails(video_ids, refresh)
def db_update_parameter(self, parameter, value):
query = QSqlQuery(self.db)
qrytxt = ('UPDATE tblParameters '
'SET VALUE = \'{v}\' '
'WHERE PARAMETER = \'{p}\'') \
.format(v=value, p=parameter)
query.exec(qrytxt)
def insert_row(self):
record = QtSql.QSqlRecord()
record.append(QtSql.QSqlField('id', QtCore.QVariant.Int))
record.append(QtSql.QSqlField('rfid_id', QtCore.QVariant.String))
record.append(QtSql.QSqlField('description', QtCore.QVariant.String))
record.setValue('id', 0)
if self.onEvents:
record.append(QtSql.QSqlField('date', QtCore.QVariant.DateTime))
record.setValue('date', QDateTime.currentDateTime())
record.setValue('rfid_id', self.label_2.text())
else:
if self.rfidIDField.text().strip(" ") == "":
QMessageBox.warning(
self, 'Error!',
"Need to fill ID of marker field to insert new marker.")
return
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM markers WHERE rfid_id = :rfid_id")
query.bindValue(":rfid_id", self.rfidIDField.text().strip(" "))
query.exec()
found = query.next()
if found:
QMessageBox.warning(self, 'Error!',
"Marker with such id already exists.")
return
record.setValue('rfid_id', self.rfidIDField.text().strip(" "))
self.tableModel.insertRecord(-1, record)
self.tableModel.select()
def register_control(self): # 注册实现方法
user = self.lineEdit.text() # 获取输入的信息
pwd1 = self.lineEdit_2.text()
pwd2 = self.lineEdit_3.text()
code = self.lineEdit_4.text()
print(user + pwd1 + pwd2 + code + self.content)
if (pwd1 == pwd2) & (code == self.content): # 必须加两个括号来进行if判断
self.db = QSqlDatabase.addDatabase('QMYSQL') # 连接数据库
self.db.setHostName('localhost')
self.db.setDatabaseName('bigpeng')
self.db.setUserName('root')
self.db.setPassword('')
if not self.db.open(): # 判断数据库打开与否
QMessageBox.critical(None, ('无法打开数据库'), ('无法建立连接，需要配置'),
QMessageBox.Cancel)
return False
query = QSqlQuery()
query.exec('select * from df_user') # 查询所有
count = query.size() # 获得总数据条目数
sql = "insert into df_user values(%d,'%s','',0,'%s','','','*****@*****.**',0,0,'','','',0,'','')" % (
count + 1, pwd2, user)
flag = query.exec(sql) # 判断是否插入成功
if flag == True:
main_login.show()
self.close()
def updateData(self, list, queryModel):
"""
修改产出库信息
:param list:
:return:
"""
self.outNO.setText(list[1])
self.productID.addItem(list[2])
# 获取所有的productID
AddOutStorage.AddOutStorage.get_product_NO(self)
self.outStorageNo.setText(list[3])
self.outTechState.setText(list[4])
self.isReturn.setItemText(0, list[5])
self.createTime.setDateTime(datetime.strptime(list[7], "%Y-%m-%d %H:%M:%S"))
self.createTime.setDisplayFormat("yyyy-MM-dd hh:mm:ss")
self.remark.setText(list[10])
global out_count
out_count = int(list[11])
self.outCount.setRange(0, out_count)
self.outCount.setValue(out_count)
db = openDB()
query = QSqlQuery()
sql = "SELECT * FROM T_Out_Base where OutNO='%s'" % self.outNO.text()
query.exec(sql)
if query.next():
self.outDate.setDateTime(datetime.strptime(query.value(2), "%Y-%m-%d"))
self.outDate.setDisplayFormat("yyyy-MM-dd")
self.usedID.setText(str(query.value(3)))
self.usedDepartmentNO.setText(str(query.value(4)))
self.outRecorderPersonNO.setText(str(query.value(5)))
self.outReason.setText(query.value(6))
db.close()
def updateData(self, list, queryModel):
"""
修改入库信息
:param list:
:return:
"""
self.productID.addItem(list[1])
# 获取所有的productID
AddInStorage.get_product_NO(self)
self.inStorageNo.setText(list[2])
self.inTechState.setText(list[4])
self.isUsed.setItemText(0, list[5])
self.updateID.setText(getCurrentUserId())
self.createTime.setDateTime(
datetime.strptime(list[7], "%Y-%m-%d %H:%M:%S"))
self.createTime.setDisplayFormat("yyyy-MM-dd hh:mm:ss")
self.updateTime.setDateTime(QDateTime.currentDateTime())
self.updateTime.setDisplayFormat("yyyy-MM-dd hh:mm:ss")
self.remark.setText(list[10])
self.inNO.setText(str(list[11]))
global in_count
in_count = int(list[12])
self.inCount.setRange(0, in_count)
self.inCount.setValue(in_count)
db = openDB()
query = QSqlQuery()
sql = "SELECT * FROM T_In_Base where InNO='%s'" % self.inNO.text()
query.exec(sql)
if query.next():
self.outNO.setText(query.value(2))
self.inDate.setDateTime(
datetime.strptime(query.value(3), "%Y-%m-%d"))
self.inDate.setDisplayFormat("yyyy-MM-dd")
self.inRecorderPerson.setText(query.value(4))
db.close()
def Login_Control(self):
self.db = QSqlDatabase.addDatabase('QMYSQL')
self.db.setHostName('localhost')
self.db.setDatabaseName('bigpeng')
self.db.setUserName('root')
self.db.setPassword('')
if not self.db.open():
QMessageBox.critical(None, ('无法打开数据库'), ('无法建立连接，需要配置'),
QMessageBox.Cancel)
return False
query = QSqlQuery()
user = self.lineEdit.text() # 得到用户输入的数据
pwd = self.lineEdit_2.text()
print(user + '=get=' + pwd)
query.exec("select * from df_user WHERE username='******'" %
user) # 由于django采用的登录密码放入数据库采用sha256加密，不能破解
while (query.next()):
print(query.value(4))
if pwd == query.value(4): # 因此不在比对密码，直接比对账号
print(user, pwd)
main_login.lists.clear() # 清空密码存储,确保以后放入的数据在第一位和第二位
self.lists.append(user)
self.lists.append(pwd)
print(self.lists[0] + 'list')
if (self.checkBox.isChecked() == True) | (
self.checkBox_2.isChecked() == True): # 复选框选中，记录账号和密码
self.save_user()
main.show() # 打开新窗口
main.label_4.setText('欢迎您：%s' % main_login.lists[0]) #
self.close() # 关闭当前登录窗口
else:
self.lineEdit_2.setText('密码错误') # 提示输入错误类型信息
else:
self.lineEdit.setText('账号错误')
def createDB():
global searchHistoryDb
# 添加数据库
searchHistoryDb = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
searchHistoryDb.setDatabaseName('./searchHistory.db')
# 判断是否打开
if not searchHistoryDb.open():
QMessageBox.critical(
None, ("Cannot open database"),
("Unable to establish a database connection. \n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\n"
"Click Cancel to exit."), QMessageBox.Cancel)
return False
# 声明数据库查询对象
query = QSqlQuery()
# 创建表
query.exec(
"create table musicLike(id int primary key,name vchar, createdTime TimeStamp NOT NULL DEFAULT (datetime('now', 'localtime')))"
) #name vchar, sex vchar, age int, deparment vchar
#添加记录
#,datetime #(datetime('now','localtime'))
return True
def addmwID(self):
try:
self.mwID.clear()
# 给mwID加上项目
db = openDB()
query = QSqlQuery()
productID = self.productID.currentText()
# print(productID)
mwid_list = ["该产品暂无相应的维保方式"]
if not self.mwID.hasFocus():
sql = "SELECT MaintenanceWayID From MaintenanceWay where ProductID = '%s'" % (
productID)
# print(sql)
query.exec(sql)
while query.next():
mwid_list.append(query.value(0))
# print(mwid_list)
if len(mwid_list) > 1:
mwid_list[0] = ""
for i in mwid_list:
self.mwID.addItem(i)
# for i in range(1,len(mwid_list)):
# self.mwID.addItem(str(mwid_list[i]))
else:
for i in mwid_list:
self.mwID.addItem(i)
except Exception as e:
print(e)
def __init__(self, table, mainModel, parent=None):
super(ClerkDialog, self).__init__(parent)
self.setupUi(self)
self.table = table
self.mainModel = mainModel
# 关联待写
self.model = QSqlTableModel(self)
self.model.setTable(table)
self.model.setSort(ID, Qt.AscendingOrder)
self.model.setHeaderData(ID, Qt.Horizontal, "Order")
self.model.setHeaderData(NAME, Qt.Horizontal, "Name")
self.model.setHeaderData(GENDER, Qt.Horizontal, "Gender")
self.model.setHeaderData(DEPARTMENT, Qt.Horizontal, "Department")
self.model.select()
self.tableView.setModel(self.model)
self.tableView.setSelectionMode(QTableView.SingleSelection)
self.tableView.setSelectionBehavior(QTableView.SelectRows)
self.tableView.setColumnHidden(ID, True)
self.tableView.setColumnHidden(GENDER, True)
self.tableView.setEditTriggers(
QAbstractItemView.NoEditTriggers) # 不允许编辑
# self.tableView.verticalHeader(False)
self.tableView.resizeColumnsToContents()
items = [] # 获取所有部门名称
query = QSqlQuery()
query.exec("SELECT dpt_name FROM departments{0}".format(createmoth))
while query.next(): # 找到第一条记录，每次后移一条记录
items.append(query.value("dpt_name"))
self.clerk_dpt_comboBox.addItems(items)
def setData(self, list):
"""
hsj 设置单个产品查询结果
:param list: 单个查询结果
:return:
"""
self.ID.setText(list[0])
self.outNO.setText(list[1])
self.productID.setText(list[2])
self.outStorageNo.setText(list[3])
self.outTechState.setText(list[4])
self.isReturn.setItemText(0, list[5])
self.createTime.setDateTime(
datetime.strptime(list[7], "%Y-%m-%d %H:%M:%S"))
self.createTime.setDisplayFormat("yyyy-MM-dd hh:mm:ss")
self.updateID.setText(list[8])
self.updateTime.setDateTime(
datetime.strptime(list[9], "%Y-%m-%d %H:%M:%S"))
self.updateTime.setDisplayFormat("yyyy-MM-dd hh:mm:ss")
self.remark.setText(list[10])
self.outCount.setRange(0, int(list[11]))
self.outCount.setValue(int(list[11]))
db = openDB()
query = QSqlQuery()
sql = "SELECT * FROM T_Out_Base where OutNO='%s'" % self.outNO.text()
query.exec(sql)
if query.next():
self.outDate.setDate(datetime.strptime(query.value(2), "%Y-%m-%d"))
self.outDate.setDisplayFormat("yyyy-MM-dd")
self.outReason.setText(query.value(6))
self.usedID.setText(str(query.value(3)))
self.usedDepartmentNO.setText(str(query.value(4)))
self.outRecorderPersonNO.setText(str(query.value(5)))
db.close()
def intersections_popup(self):
if 'db1' in dir(self):
self.open_sql_for_table_view()
sql_txt = """
SELECT int.ID AS 'Intersection ID', int.X, int.Y, f.name AS 'Type of Feature'
FROM Intersections AS int
LEFT JOIN InstalledFeatures AS inf ON int.ID = inf.intersectionID
LEFT JOIN Features AS f ON inf.FeatureID = f.ID
"""
query = QSqlQuery()
query.exec(sql_txt)
self.model = QSqlQueryModel()
self.model.setQuery(query)
self.tableview.setModel(self.model)
self.tableview.show()
self.db.close()
return
else:
msg = QMessageBox()
msg.setWindowTitle("Database Error")
txt = "There is no connected database"
msg.setText(txt)
msg.setIcon(QMessageBox.Critical)
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
return
def parentTagListBetaUpdate(self):
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
parents = ["tags_parent", "yt_tags_parent"]
sort = ["P.parent_tag", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT P.parent_tag, P.parent_tag || ' (' || IFNULL(ct, 0) || ')', ct
FROM (
SELECT parent_tag
FROM {parents[self.tagType]}
WHERE child_tag = (?)
GROUP BY 1
) P LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
WHERE tag_name = (?)
GROUP BY 1
) C
ON P.parent_tag = C.tag_name
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
query = QSqlQuery()
query.prepare(queryTemplate)
childTag = self.childTag
query.addBindValue(childTag)
query.addBindValue(childTag)
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
self.parentTagListBeta.setModel(model)
self.parentTagListBeta.setModelColumn(1)
self.parentTagListBeta.show()
def __init__(self, *args):
super().__init__(*args)
self.setupUi(self)
self.servM = QSqlQueryModel()
self.userM = QSqlQueryModel()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry = QSqlQuery(self.db)
qry.prepare('select name from services')
qry.exec()
# QMessageBox.information(self, 'Отладка', 'select name from services')
self.servM.setQuery(qry)
# QMessageBox.information(self, 'Отладка', '3')
self.cb_ser_name.setModel(self.servM)
qry.prepare('select FIO from User')
qry.exec()
self.userM.setQuery(qry)
self.cb_fio.setModel(self.userM)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
self.btnOk.clicked.connect(self.bt_ok)
self.btnCancel.clicked.connect(self.bt_cancel)
def tagListUpdate(self):
types = ["tags", "yt_tags"]
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM {types[self.tagType]} T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
WHERE T.tag_name LIKE (?)
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
searchText = self.tagSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{searchText}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
self.tagList.setModel(model)
self.tagList.setModelColumn(1)
self.tagList.show()
self.tagList.selectionModel().selectionChanged.connect(
self.tagListSelectionChanged)
def addTestData(self):
data = [
("Harrison", "Senior Back-End Developer",
"*****@*****.**", "0799204524", 22),
("Joe", "Senior Front-End Developer", "*****@*****.**",
"0799001654", 21),
("Lara", "Project Manager", "*****@*****.**", "0710204524", 25),
("David", "Data Analyst", "*****@*****.**", "0799204500", 24),
("Jane", "Senior Python Developer", "*****@*****.**",
"0799200520", 23),
]
query = QSqlQuery()
query.prepare("""
INSERT INTO employee (
name,
job,
email,
phone,
age
)
VALUES (?, ?, ?, ?, ?)
""")
for name, job, email, phone, age in data:
query.addBindValue(name)
query.addBindValue(job)
query.addBindValue(email)
query.addBindValue(phone)
query.addBindValue(age)
query.exec()
query.finish()
def DateA(self):
text1, ok1 = QInputDialog.getText(self, 'Приход препаратов',
'Введите ID препарата. ')
if ok1:
now = datetime.datetime.now()
text2 = now.strftime("%Y-%m-%d")
text3, ok3 = QInputDialog.getText(
self, 'Приход препаратов',
'Введите количество препаратов в партии. ')
if ok3:
text4, ok4 = QInputDialog.getText(
self, 'Приход препаратов',
'Введите срок годности препаратов в партии.(В формате: Год-месяц-день. Пример:2018-12-17) '
)
if ok4:
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName("db.db")
db.open()
queryAdd = QSqlQuery()
queryAdd.prepare(
"INSERT INTO batch (b_mId, bDateA, bCol, bDateEx ) "
"VALUES (?, ?, ?, ?)")
queryAdd.bindValue(0, text1)
queryAdd.bindValue(1, text2)
queryAdd.bindValue(2, text3)
queryAdd.bindValue(3, text4)
queryAdd.exec()
db.close()
def query(self):
query = QSqlQuery(self.db)
query.exec("select * from user")
while (query.next()):
print(query.value(0))
print(query.value(1))
print(query.value(2))
def parentTagListAlphaUpdate(self):
types = ["tags", "yt_tags"]
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM {types[self.tagType]} T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
WHERE T.tag_name LIKE (?)
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
searchText = self.parentTagSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{searchText}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
pmodel = FilterProxyModel()
pmodel.setSourceModel(model)
filteredSet = self.parentTags
filteredSet.add(self.childTag)
pmodel.setFilteredSet(filteredSet)
self.parentTagListAlpha.setModel(pmodel)
self.parentTagListAlpha.setModelColumn(1)
self.parentTagListAlpha.show()
def Delete(self):
dltxt, dlok = QInputDialog.getText(
self, 'Удаление',
'Введите ID препарата, который вы хотите удалить:')
if dlok:
db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName("db.db")
db.open()
querydl = QSqlQuery()
querydl.prepare("DELETE FROM medications WHERE mId= ( ? )")
querydl.bindValue(0, dltxt)
querydl.exec()
qry = QSqlQuery()
qry = db.exec("SELECT mId AS 'ID',"
"mName AS 'Название',"
"mDescription AS 'Описание',"
"mPrice AS 'Цена' FROM medications")
tabmodel = QSqlQueryModel()
self.ui.tableView_2.setModel(tabmodel)
tabmodel.setQuery(qry)
self.ui.tableView_2.setColumnWidth(2, 310)
header = self.ui.tableView_2.horizontalHeader()
Lheader = self.ui.tableView_2.verticalHeader()
Lheader.setVisible(False)
header.setStretchLastSection(True)
self.ui.tableView_2.show
db.close()
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
## # SQL语句测试1， exec_() 和exec()都可以直接执行不带参数的SQL语句
## query.exec('''UPDATE employee SET Salary=3000 where Gender="女" ''')
## query.exec_('''UPDATE employee SET Salary=4500 where Gender="女" ''')
# SQL语句测试2，执行带参数的SQL语句，只能用 exec_()，不能用exec()
## query.prepare('''UPDATE employee SET Salary=9000 where Gender=:Gender ''')
## query.bindValue(":Gender","男")
## query.exec()
query.exec('''UPDATE employee SET Salary=500+Salary ''')
## query.bindValue(":Gender","男")
## query.exec()
## query.prepare("UPDATE employee SET Department=?, Salary=? where Name=?")
## query.bindValue(0, "技术部")
## query.bindValue(1, 5500)
## query.bindValue(2, "张三")
## query.exec_() #只能用exec_()，而不能用exec()函数
## self.qryModel.query().exec() #不增减记录时更新显示
## 增减记录后的更新显示
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
print("SQL OK")
def updateDistinctIfNeeded(self, force=False):
if (not force and (time.time() - self.distinctLastUpdateTime) < 10
) or self.maxRowId == self.distinctLastRowId:
return
if (self.maxRowId < self.distinctLastRowId):
#the db has been cleared, re-init the values
self.distinctLastRowId = 0
self.distinct = {
'process': [],
'dst_host': [],
'dst_ip': [],
'dst_port': [],
'rule': [],
'node': [],
'protocol': []
}
q = QSqlQuery(self.db)
q.setForwardOnly(True)
for column in self.distinct.keys():
q.exec('SELECT DISTINCT ' + column +
' FROM connections WHERE rowid>' +
str(self.distinctLastRowId) + ' AND rowid<=' +
str(self.maxRowId))
while q.next():
if q.value(0) not in self.distinct[column]:
self.distinct[column].append(q.value(0))
self.distinctLastRowId = self.maxRowId
self.distinctLastUpdateTime = time.time()
def saveNewValue(self):
if self.upperBatteryPercentageLimitSpinBox.value(
) > self.lowerBatteryPercentageLimitSpinBox.value():
self.saveBtn.setEnabled(False)
self.originalUpperBatteryPercentageLimit = self.upperBatteryPercentageLimitSpinBox.value(
)
self.originalLowerBatteryPercentageLimit = self.lowerBatteryPercentageLimitSpinBox.value(
)
self.db.open()
query = QSqlQuery()
statement = f"UPDATE info SET upperBatLimit={self.originalUpperBatteryPercentageLimit}, lowerBatLimit={self.originalLowerBatteryPercentageLimit};"
query.exec(statement)
self.db.close()
elif self.upperBatteryPercentageLimitSpinBox.value(
) == self.lowerBatteryPercentageLimitSpinBox.value():
QMessageBox.critical(
None, "Error",
"The upper battery limit cannot be equal to the lower battery limit"
)
else:
QMessageBox.critical(
None, "Error",
"The upper battery limit cannot be lower than the lower battery limit"
)
def pushButton_3_clicked(self):
"""新建"""
db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName(self.IPaddress)
db.setUserName(self.account)
db.setPassword(self.password)
db.setDatabaseName('project')
db.open()
productnames = db.tables()
addproductname = self.UI.lineEdit_1.text()
if len(addproductname) == 0:
QMessageBox.warning(self, "错误", '产品名不能为空')
elif addproductname in productnames:
QMessageBox.warning(self, "错误", '数据库中已有该产品')
elif addproductname[0].isdigit():
QMessageBox.warning(self, "错误", '产品名开头不能为数字')
else:
exword = 'create table ' + addproductname + '(ID int auto_increment primary key,'
for i in range(self.model.rowCount()):
# print(self.model.index(i, 0).data(), self.model.index(i, 1).data())
exword = exword + ' ' + self.model.index(
i, 0).data() + ' ' + self.model.index(i, 1).data() + ','
exword = exword[:-1] + ')'
query = QSqlQuery()
query.exec(exword)
QMessageBox.information(self, '提示', '创建成功')
self.accept()
db.close()
def __init__(self, tag_number):
super().__init__()
print('processing query...')
qry = QSqlQuery(db)
query = 'SELECT name, ename, startno, starttime FROM name WHERE ecard = %i OR ecard2 = %i' % (
tag_number, tag_number)
qry.prepare(query)
qry.exec()
model = QSqlQueryModel()
model.setQuery(qry)
print(model.rowCount())
mapper = QDataWidgetMapper()
form = QFormLayout()
layout = QVBoxLayout()
first_name = QLineEdit()
start_number = QLineEdit()
form.addRow(QLabel("Startnummer"), start_number)
form.addRow(QLabel("Fornavn"), first_name)
mapper.setModel(model)
mapper.addMapping(first_name, 0)
mapper.addMapping(start_number, 2)
mapper.toFirst()
layout.addLayout(form)
widget = QWidget()
widget.setLayout(layout)
self.setCentralWidget(widget)
#controls = QHBoxLayout()
'''
def _createModel():
oi_get_query = QSqlQuery()
oi_get_query.prepare("""select symbol
,desc
,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2)
,globex_volume
,volume
,open_interest
,change
from oi_reports
where symbol = "EUR"
order by cast(oi_date as int) desc
;
""")
#oi_get_query.addBindValue(symbol)
oi_get_query.exec()
queryModel = QSqlQueryModel()
queryModel.setQuery(oi_get_query)
headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change']
for columnIndex, header in enumerate(headers):
queryModel.setHeaderData(columnIndex, Qt.Horizontal, header)
#queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol')
#queryModel.setHeaderData(1, Qt.Horizontal, 'Description')
#queryModel.setHeaderData(2, Qt.Horizontal, 'Date')
#queryModel.setHeaderData(3, Qt.Horizontal, 'Globex')
#queryModel.setHeaderData(4, Qt.Horizontal, 'Volume')
#queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest')
#queryModel.setHeaderData(6, Qt.Horizontal, 'Change')
return queryModel
def streets_popup(self):
if 'db1' in dir(self):
self.open_sql_for_table_view()
sql_txt = """
SELECT R.name AS 'Road Name', R.ID AS 'Road ID', I1.X AS X1, I1.Y AS Y1, I2.X AS X2, I2.Y AS Y2
FROM Roads as R, Intersections as I1, Intersections as I2
WHERE R.startNodeID = I1.ID
AND R.endNodeID = I2.ID
"""
query = QSqlQuery()
query.exec(sql_txt)
self.model = QSqlQueryModel()
self.model.setQuery(query)
self.tableview.setModel(self.model)
self.tableview.show()
self.db.close()
return
else:
msg = QMessageBox()
msg.setWindowTitle("Database Error")
txt = "There is no connected database"
msg.setText(txt)
msg.setIcon(QMessageBox.Critical)
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
return
def question_types(self) -> List[str]:
question_type_list = [] # type: List[str]
query_command = "SELECT * FROM 'question_type'"
query = QSqlQuery(self.database)
if not query.exec(query_command):
raise ConnectionError(query.lastError().text())
question_type_field_id = query.record().indexOf('name')
while query.next():
question_type_list.append(
query.value(question_type_field_id)
)
return question_type_list
def init_database(self):
success = self.database.open()
if not success:
raise ConnectionError(self.database.lastError().text())
query = QSqlQuery(self.database)
query_command = "CREATE TABLE IF NOT EXISTS 'question_type' ({}{}{})".format(
'id integer primary key,',
'name text,',
'position integer'
)
if not query.exec(query_command):
raise ConnectionError(query.lastError().text())
def createTableAndInit():
# 添加数据库
db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
db.setDatabaseName('./db/database.db')
# 判断是否打开
if not db.open():
return False
# 声明数据库查询对象
query = QSqlQuery()
# 创建表
query.exec("create table student(id int primary key, name vchar, sex vchar, age int, deparment vchar)")
#添加记录
query.exec("insert into student values(1,'张三1','男',20,'计算机')")
query.exec("insert into student values(2,'李四1','男',19,'经管')")
query.exec("insert into student values(3,'王五1','男',22,'机械')")
query.exec("insert into student values(4,'赵六1','男',21,'法律')")
query.exec("insert into student values(5,'小明1','男',20,'英语')")
query.exec("insert into student values(6,'小李1','女',19,'计算机')")
query.exec("insert into student values(7,'小张1','男',20,'机械')")
query.exec("insert into student values(8,'小刚1','男',19,'经管')")
query.exec("insert into student values(9,'张三2','男',21,'计算机')")
query.exec("insert into student values(10,'张三3','女',20,'法律')")
query.exec("insert into student values(11,'王五2','男',19,'经管')")
query.exec("insert into student values(12,'张三4','男',20,'计算机')")
query.exec("insert into student values(13,'小李2','男',20,'机械')")
query.exec("insert into student values(14,'李四2','女',19,'经管')")
query.exec("insert into student values(15,'赵六3','男',21,'英语')")
query.exec("insert into student values(16,'李四2','男',19,'法律')")
query.exec("insert into student values(17,'小张2','女',22,'经管')")
query.exec("insert into student values(18,'李四3','男',21,'英语')")
query.exec("insert into student values(19,'小李3','女',19,'法律')")
query.exec("insert into student values(20,'王五3','女',20,'机械')")
query.exec("insert into student values(21,'张三4','男',22,'计算机')")
query.exec("insert into student values(22,'小李2','男',20,'法律')")
query.exec("insert into student values(23,'张三5','男',19,'经管')")
query.exec("insert into student values(24,'小张3','女',20,'计算机')")
query.exec("insert into student values(25,'李四4','男',22,'英语')")
query.exec("insert into student values(26,'赵六2','男',20,'机械')")
query.exec("insert into student values(27,'小李3','女',19,'英语')")
query.exec("insert into student values(28,'王五4','男',21,'经管')")
return True
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
def merge(self, out):
query = QSqlQuery(db=self.db)
main_name = out[0]
for current_name in out[1:]:
query.exec('update domains set name="%s" where name="%s"' % (main_name, current_name))
def createDB():
# 添加数据库
db = QSqlDatabase.addDatabase('QSQLITE')
# 设置数据库名称
db.setDatabaseName('./db/database.db')
# 判断是否打开
if not db.open():
QMessageBox.critical(None, ("Cannot open database"),
("Unable to establish a database connection. \n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\n"
"Click Cancel to exit."),
QMessageBox.Cancel)
return False
# 声明数据库查询对象
query = QSqlQuery()
# 创建表
query.exec("create table student(id int primary key, name vchar, sex vchar, age int, deparment vchar)")
#添加记录
query.exec("insert into student values(1,'张三1','男',20,'计算机')")
query.exec("insert into student values(2,'李四1','男',19,'经管')")
query.exec("insert into student values(3,'王五1','男',22,'机械')")
query.exec("insert into student values(4,'赵六1','男',21,'法律')")
query.exec("insert into student values(5,'小明1','男',20,'英语')")
query.exec("insert into student values(6,'小李1','女',19,'计算机')")
query.exec("insert into student values(7,'小张1','男',20,'机械')")
query.exec("insert into student values(8,'小刚1','男',19,'经管')")
query.exec("insert into student values(9,'张三2','男',21,'计算机')")
query.exec("insert into student values(10,'张三3','女',20,'法律')")
query.exec("insert into student values(11,'王五2','男',19,'经管')")
query.exec("insert into student values(12,'张三4','男',20,'计算机')")
query.exec("insert into student values(13,'小李2','男',20,'机械')")
query.exec("insert into student values(14,'李四2','女',19,'经管')")
query.exec("insert into student values(15,'赵六3','男',21,'英语')")
query.exec("insert into student values(16,'李四2','男',19,'法律')")
query.exec("insert into student values(17,'小张2','女',22,'经管')")
query.exec("insert into student values(18,'李四3','男',21,'英语')")
query.exec("insert into student values(19,'小李3','女',19,'法律')")
query.exec("insert into student values(20,'王五3','女',20,'机械')")
query.exec("insert into student values(21,'张三4','男',22,'计算机')")
query.exec("insert into student values(22,'小李2','男',20,'法律')")
query.exec("insert into student values(23,'张三5','男',19,'经管')")
query.exec("insert into student values(24,'小张3','女',20,'计算机')")
query.exec("insert into student values(25,'李四4','男',22,'英语')")
query.exec("insert into student values(26,'赵六2','男',20,'机械')")
query.exec("insert into student values(27,'小李3','女',19,'英语')")
query.exec("insert into student values(28,'王五4','男',21,'经管')")
# 关闭数据库
db.close()
return True
