コード例 #1
0
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()))
コード例 #2
0
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())
コード例 #3
0
 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
コード例 #4
0
ファイル: part1.py プロジェクト: LiangZhang0103/pyqt5
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