def copytable(self, dbsrc, dbdes, tablename):
listcolumns = DBFuncBase().getListColumnsTable(tablename)
numberscolumns = len(listcolumns)
# build query insert
request = 'INSERT INTO ' + tablename + '('
request += ', '.join('`{0}`'.format(w) for w in listcolumns) + ') VALUES '
request += '(' + ', '.join(['?'] * numberscolumns) + ')'
# query
querylite = QSqlQuery(dbdes)
query = QSqlQuery(dbsrc)
query.exec_("SELECT * FROM "+tablename)
while query.next():
querylite.prepare(request)
for indcol in range(query.record().count()):
querylite.bindValue(indcol, query.value(indcol))
if not querylite.exec_():
qDebug(tablename+10*' '+querylite.lastError().text())
listparam = list(querylite.boundValues().values())
for i in range(len(listparam)):
qDebug(10*' '+ str(i) + ' ' + str(listparam[i]))
def arrayCardToSql(self, operation, arraydata, columnnamekey, request, listcolumns):
# one card
numberscolumns = len(listcolumns)
queryoperation = QSqlQuery()
queryoperation.prepare(request)
for column in range(numberscolumns):
# first column : primary key
if listcolumns[column] == columnnamekey and operation == 'INSERT':
queryoperation.bindValue(column, None)
else:
queryoperation.bindValue(column, arraydata[listcolumns[column]])
if operation == 'UPDATE':
# primary key for update
queryoperation.bindValue(column + 1, arraydata[columnnamekey])
if not queryoperation.exec_():
qDebug(request + ' ' + queryoperation.lastError().text())
qDebug(','.join(list(queryoperation.boundValues().values())))
return False
queryoperation.clear
return True
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()
