def ApisLogger(db, action, fromTable, primaryKeysWhere):
toTable = fromTable + u"_log"
q = QSqlQuery(db)
q.exec_("PRAGMA table_info({0});".format(fromTable))
q.seek(-1)
fieldNames = []
while (q.next()):
fieldNames.append(str(q.record().value(1)))
fieldNames.pop(0) # pop id
query = QSqlQuery(db)
query.prepare("INSERT INTO {0}({1}) SELECT {1} FROM {2} WHERE {3}".format(
toTable, ", ".join(fieldNames), fromTable, primaryKeysWhere))
res = query.exec_()
if not res:
QMessageBox.information(
None, "SqlError", "{0}, {1}".format(query.lastError().text(),
query.executedQuery()))
import getpass
query.prepare(
"UPDATE {0} SET aktion = '{1}', aktionsdatum = '{2}', aktionsuser = '******' WHERE rowid = (SELECT max(rowid) FROM {0})"
.format(toTable, action,
QDateTime.currentDateTime().toString("yyyy-MM-dd hh:mm:ss"),
getpass.getuser()))
res = query.exec_()
if not res:
QMessageBox.information(
None, "SqlError", "{0}, {1}".format(query.lastError().text(),
query.executedQuery()))
class MusicDB(QObject):
def __init__(self):
super(MusicDB).__init__()
self.filename = "music_library.db"
self.createConnection()
self.model = QSqlTableModel()
self.model.setTable("music")
self.model.setEditStrategy(QSqlTableModel.OnManualSubmit)
self.model.select()
self.model.setHeaderData(1, Qt.Horizontal, "Title")
self.model.setHeaderData(2, Qt.Horizontal, "Album")
self.model.setHeaderData(3, Qt.Horizontal, "Artist")
self.model.setHeaderData(4, Qt.Horizontal, "Release")
self.model.setHeaderData(5, Qt.Horizontal, "Label")
self.model.setHeaderData(6, Qt.Horizontal, "Tk. Pos.")
self.model.setHeaderData(8, Qt.Horizontal, "Rate")
self.model.setHeaderData(9, Qt.Horizontal, "Length")
self.q = QSqlQuery()
def createConnection(self):
self.con = QSqlDatabase.addDatabase("QSQLITE")
# FIXME CHECK IF FILE EXISTS
self.con.setDatabaseName(self.filename)
if not self.con.open():
QMessageBox.critical(None, "QTableView Example - Error!",
"Database Error: %s" % con.lastError().databaseText())
def queryResult(self, res):
if not res:
print (self.q.lastError().text())
print (self.q.executedQuery())
def initializeFromFile(self, filename):
df = pd.read_csv(filename, sep=';')
for i in range(len(df)):
print (i, len(df))
sql = "UPDATE music SET file_id=:file_id WHERE file_name=:file_name;"
self.q.prepare(sql)
self.q.bindValue(":file_id", str(df.loc[i][1]))
self.q.bindValue(":file_name", df.loc[i][0])
self.queryResult(self.q.exec())
def incrementPlayed(self, file_id, rate):
sql = "UPDATE music SET rate=:rate WHERE file_id=:file_id;"
self.q.prepare(sql)
self.q.bindValue(":file_id", file_id)
self.q.bindValue(":rate", rate)
self.queryResult(self.q.exec())
def __getDataSourceFromQuery(self, query: QtSql.QSqlQuery) -> list:
if not query.exec():
errMsg = '报表执行查询失败！语句为\n{}'
raise Exception(errMsg.format(query.executedQuery()))
result = []
self.DataSourceRecord = query.record()
bz = query.first()
while bz:
recs = query.record()
if not self.__FieldTypes:
self.__FieldTypes = {
recs.field(i).name(): getFieldType(self.__db,
recs.field(i).typeID())
for i in range(recs.count())
}
for i in range(recs.count()):
row = {
recs.field(i).name(): recs.field(i).value()
for i in range(recs.count())
}
result.append(row)
bz = query.next()
return result
query.prepare('insert into student values (?, ?, ?)')
query.addBindValue([6, 7, 8])
query.addBindValue(['test5', 'test6', 'test7'])
query.addBindValue([1, 1, 1])
if query.execBatch():
print('inserted')
# 三、executedQuery()返回最后一个执行成功的指令
if query.exec('select id, name, age from student'):
while query.next():
id, name, age = query.value(0), query.value(1), query.value(2)
print(id, name, age)
print(query.executedQuery())
# 执行结果为： select id, name, age from student
# 四、其它
# finish() 终止当前的操作
# isActive() 返回当前是否处于活跃状态
# isNull(int field) 返回当前是否不活跃
# isSelect() 返回是不是一个查询语句
# next() 检索结果中的下一条记录(如果可用)，并将该查询放在检索到的记录上。
# 请注意，结果必须处于活动状态，并且在调用此函数之前， isSelect() 必须返回true, 否则它将不执行任何操作并返回false.
# 指令执行成功则 exec_() 会返回True 并把查询状态设为活跃状态，否则返回false
