def _insert_batch(self, query_str, fields, values): try: with self._lock: q = QSqlQuery(self.db) q.prepare(query_str) q.addBindValue(fields) q.addBindValue(values) if not q.execBatch(): print(query_str) print(q.lastError().driverText()) q.finish() except Exception as e: print("_insert_batch() exception:", e)
def _insert_batch(self, query_str, fields, values): result=True with self._lock: try: q = QSqlQuery(self.db) q.prepare(query_str) q.addBindValue(fields) q.addBindValue(values) if not q.execBatch(): print("_insert_batch() error", query_str) print(q.lastError().driverText()) result=False except Exception as e: print("_insert_batch() exception:", e) finally: q.finish() return result
# 另外对于SQLite, 查询字符串一次智能查询一条语句,如果给出多条语句,则函数返回false 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) # 二、execBatch()操作 # 这个函数是批处理之前准备好的指令,如果函数库不支持批处理他会自己调用exec()来模拟 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
class Query: """A prepared SQL query.""" def __init__(self, querystr, forward_only=True): """Prepare a new SQL query. Args: querystr: String to prepare query from. forward_only: Optimization for queries that will only step forward. Must be false for completion queries. """ self.query = QSqlQuery(QSqlDatabase.database()) log.sql.debug('Preparing SQL query: "{}"'.format(querystr)) ok = self.query.prepare(querystr) self._check_ok('prepare', ok) self.query.setForwardOnly(forward_only) def __iter__(self): if not self.query.isActive(): raise BugError("Cannot iterate inactive query") rec = self.query.record() fields = [rec.fieldName(i) for i in range(rec.count())] rowtype = collections.namedtuple( # type: ignore[misc] 'ResultRow', fields) while self.query.next(): rec = self.query.record() yield rowtype(*[rec.value(i) for i in range(rec.count())]) def _check_ok(self, step, ok): if not ok: query = self.query.lastQuery() error = self.query.lastError() msg = 'Failed to {} query "{}": "{}"'.format( step, query, error.text()) raise_sqlite_error(msg, error) def _bind_values(self, values): for key, val in values.items(): self.query.bindValue(':{}'.format(key), val) if any(val is None for val in self.bound_values().values()): raise BugError("Missing bound values!") def run(self, **values): """Execute the prepared query.""" log.sql.debug('Running SQL query: "{}"'.format(self.query.lastQuery())) self._bind_values(values) log.sql.debug('query bindings: {}'.format(self.bound_values())) ok = self.query.exec_() self._check_ok('exec', ok) return self def run_batch(self, values): """Execute the query in batch mode.""" log.sql.debug('Running SQL query (batch): "{}"'.format( self.query.lastQuery())) self._bind_values(values) db = QSqlDatabase.database() ok = db.transaction() self._check_ok('transaction', ok) ok = self.query.execBatch() try: self._check_ok('execBatch', ok) except Error: # Not checking the return value here, as we're failing anyways... db.rollback() raise ok = db.commit() self._check_ok('commit', ok) def value(self): """Return the result of a single-value query (e.g. an EXISTS).""" if not self.query.next(): raise BugError("No result for single-result query") return self.query.record().value(0) def rows_affected(self): return self.query.numRowsAffected() def bound_values(self): return self.query.boundValues()
class Query: """A prepared SQL query.""" def __init__(self, querystr, forward_only=True): """Prepare a new SQL query. Args: querystr: String to prepare query from. forward_only: Optimization for queries that will only step forward. Must be false for completion queries. """ self.query = QSqlQuery(QSqlDatabase.database()) log.sql.debug('Preparing SQL query: "{}"'.format(querystr)) ok = self.query.prepare(querystr) self._check_ok('prepare', ok) self.query.setForwardOnly(forward_only) def __iter__(self): if not self.query.isActive(): raise BugError("Cannot iterate inactive query") rec = self.query.record() fields = [rec.fieldName(i) for i in range(rec.count())] rowtype = collections.namedtuple('ResultRow', fields) while self.query.next(): rec = self.query.record() yield rowtype(*[rec.value(i) for i in range(rec.count())]) def _check_ok(self, step, ok): if not ok: query = self.query.lastQuery() error = self.query.lastError() msg = 'Failed to {} query "{}": "{}"'.format(step, query, error.text()) raise_sqlite_error(msg, error) def _bind_values(self, values): for key, val in values.items(): self.query.bindValue(':{}'.format(key), val) if any(val is None for val in self.bound_values().values()): raise BugError("Missing bound values!") def run(self, **values): """Execute the prepared query.""" log.sql.debug('Running SQL query: "{}"'.format( self.query.lastQuery())) self._bind_values(values) log.sql.debug('query bindings: {}'.format(self.bound_values())) ok = self.query.exec_() self._check_ok('exec', ok) return self def run_batch(self, values): """Execute the query in batch mode.""" log.sql.debug('Running SQL query (batch): "{}"'.format( self.query.lastQuery())) self._bind_values(values) db = QSqlDatabase.database() ok = db.transaction() self._check_ok('transaction', ok) ok = self.query.execBatch() try: self._check_ok('execBatch', ok) except Error: # Not checking the return value here, as we're failing anyways... db.rollback() raise ok = db.commit() self._check_ok('commit', ok) def value(self): """Return the result of a single-value query (e.g. an EXISTS).""" if not self.query.next(): raise BugError("No result for single-result query") return self.query.record().value(0) def rows_affected(self): return self.query.numRowsAffected() def bound_values(self): return self.query.boundValues()
class Query: """A prepared SQL query.""" def __init__(self, database: Database, querystr: str, forward_only: bool = True) -> None: """Prepare a new SQL query. Args: database: The Database object on which to operate. querystr: String to prepare query from. forward_only: Optimization for queries that will only step forward. Must be false for completion queries. """ self._database = database self.query = QSqlQuery(database.qt_database()) log.sql.vdebug(f'Preparing: {querystr}') # type: ignore[attr-defined] ok = self.query.prepare(querystr) self._check_ok('prepare', ok) self.query.setForwardOnly(forward_only) def __iter__(self) -> Iterator[Any]: if not self.query.isActive(): raise BugError("Cannot iterate inactive query") rec = self.query.record() fields = [rec.fieldName(i) for i in range(rec.count())] rowtype = collections.namedtuple( # type: ignore[misc] 'ResultRow', fields) while self.query.next(): rec = self.query.record() yield rowtype(*[rec.value(i) for i in range(rec.count())]) def _check_ok(self, step: str, ok: bool) -> None: if not ok: query = self.query.lastQuery() error = self.query.lastError() msg = f'Failed to {step} query "{query}": "{error.text()}"' raise_sqlite_error(msg, error) def _bind_values(self, values: Mapping[str, Any]) -> Dict[str, Any]: for key, val in values.items(): self.query.bindValue(f':{key}', val) bound_values = self.bound_values() if None in bound_values.values(): raise BugError("Missing bound values!") return bound_values def run(self, **values: Any) -> 'Query': """Execute the prepared query.""" log.sql.debug(self.query.lastQuery()) bound_values = self._bind_values(values) if bound_values: log.sql.debug(f' {bound_values}') ok = self.query.exec() self._check_ok('exec', ok) return self def run_batch(self, values: Mapping[str, MutableSequence[Any]]) -> None: """Execute the query in batch mode.""" log.sql.debug(f'Running SQL query (batch): "{self.query.lastQuery()}"') self._bind_values(values) db = self._database.qt_database() ok = db.transaction() self._check_ok('transaction', ok) ok = self.query.execBatch() try: self._check_ok('execBatch', ok) except Error: # Not checking the return value here, as we're failing anyways... db.rollback() raise ok = db.commit() self._check_ok('commit', ok) def value(self) -> Any: """Return the result of a single-value query (e.g. an EXISTS).""" if not self.query.next(): raise BugError("No result for single-result query") return self.query.record().value(0) def rows_affected(self) -> int: """Return how many rows were affected by a non-SELECT query.""" assert not self.query.isSelect(), self assert self.query.isActive(), self rows = self.query.numRowsAffected() assert rows != -1 return rows def bound_values(self) -> Dict[str, Any]: return self.query.boundValues()