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()
