def delete_project(self, project, row):
"""delete a project from the database & file system if it's empty
"""
self.log.debug("Attempting to delete project '{}' from database...".format(project))
q = QSqlQuery()
count_query = "select count(*) from alleles where project_name = '{}'".format(project)
q.exec_(count_query)
check_error(q, self.mydb, self.log)
alleles = 0
while q.next():
alleles = q.value(0)
if alleles == 0:
delete_query = "delete from projects where project_name = '{}'".format(project)
q.exec_(delete_query)
self.log.debug("\t=> Successfully deleted empty project")
self.log.debug("Attempting to delete project '{}' from file system...".format(project))
project_dir = os.path.join(self.settings["projects_dir"], project)
try:
shutil.rmtree(project_dir)
except Exception as E:
self.log.debug("=> File deletion did not work:")
self.log.error(E)
self.log.exception(E)
self.log.debug("=> Project '{}' successfully deleted from database and file system".format(project))
self.deleted_project.emit()
else:
self.log.debug("\t=> Project contains {} alleles => cannot delete!".format(alleles))
def _load(self):
self.clear()
count = 0
query = QSqlQuery(self.db)
query.prepare("SELECT COUNT(DISTINCT position) "
"FROM treeparam WHERE pageid=?")
query.addBindValue(self.pageId)
query.exec_()
if query.first():
count = query.record().value(0)
if count:
for _ in range(count):
self._params.append([])
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM treeparam WHERE pageid=?")
query.addBindValue(self.pageId)
query.exec_()
while query.next():
record = query.record()
position = record.value('position')
fieldId = record.value('fieldid')
self._params[position].append(self.fields.field(fieldId))
def update_game_properties(self):
attraction = self.ui.comboBox_Game_Attraction.currentText().strip()
if len(attraction) > 0:
attraction_id, _ = attraction.split(" - ", 1)
query = QSqlQuery()
query.prepare('SELECT SUM(point), name '
'FROM (SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor ON '
't_point.id_competitor = t_competitor.id '
'WHERE t_point.id_competitor_take = 1 '
'AND t_point.id_attraction = ' + attraction_id + ' '
'UNION ALL '
'SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor '
'ON t_point.id_competitor_take = t_competitor.id '
'WHERE t_point.id_competitor_take != 1 '
'AND t_point.id_attraction = ' + attraction_id + ') '
'GROUP BY name ORDER BY SUM(point) DESC')
query.setForwardOnly(1)
query.exec_()
points = QTreeWidgetItem()
points.setText(0, "Points:")
while query.next():
point = QTreeWidgetItem()
point.setText(0, str(query.value(0)) + " - "
+ query.value(1))
points.addChild(point)
self.ui.treeWidget_Game_Properties.clear()
self.ui.treeWidget_Game_Properties.addTopLevelItem(points)
self.ui.treeWidget_Game_Properties.expandAll()
def newJob(self, price, number, text, bId):
query = QSqlQuery()
sql = "insert into jobs values((select max(id) from jobs) + 1,'{0}','{1}','{2}','{3}')".format(bId, number,
price, text)
query.exec_(sql)
query.exec_('select hours, price,job,id from jobs order by id desc limit 1')
return self.extractJobs(query)[0]
def execute_query(query, num_columns, log, task, err_type = "Database Error", parent = None):
"""executes a query;
returns data of a SELECT statement as list of lists;
reports errors to log and QMessageBox
(using task and err_type as message building blocks)
usage example:
success, data = db_internal.execute_query(query, 1, self.log,
"generating the IPD submission number",
"IPD submission nr error", self)
"""
log.debug("\tExecuting query {}[...]...".format(query.split("\n")[0][:100]))
data = []
success = False
q = QSqlQuery()
q.exec_(query)
err_msg = error_in_query(q, task, log)
if err_msg:
if parent:
QMessageBox.warning(parent, err_type, err_msg)
else:
data = err_msg
return success, data
success = True
while q.next(): # if query has return-data, return it
row = []
for i in range(num_columns):
row.append(q.value(i))
data.append(row)
q.finish()
if data:
log.debug("\t=> {} records found!".format(len(data)))
return success, data
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
q = QSqlQuery()
query = """SELECT project_name, project_nr,
(sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
local_name,
allele_status, lab_status,
sample_id_int, allele_nr
FROM alleles
order by project_nr
"""
q.exec_(query)
self.check_error(q)
self.model.setQuery(q)
self.model.setHeaderData(1, Qt.Horizontal, "Nr")
self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
self.log.debug("\t=> Done!")
def execute_transaction(queries, mydb, log, task, err_type = "Database Error", parent = None):
"""executes a list of queries in a transaction;
reports errors to log and QMessageBox
(using task and err_type as message building blocks)
"""
log.debug("\tStarting transaction...")
success = False
mydb.transaction()
q = QSqlQuery()
i = 0
for query in queries:
i += 1
log.debug("\t\tQuery #{}: '{}[...]...'".format(i, query.split("\n")[0][:50]))
q.exec_(query)
err_msg = error_in_query(q, task, log)
if err_msg:
if parent:
QMessageBox.warning(parent, err_type, err_msg)
mydb.rollback()
return success
success = True
mydb.commit()
log.debug("\t=> transaction successful")
return success
def show_points(self):
attraction = self.ui.comboBox_Game_Attraction.currentText().strip()
if len(attraction) > 0:
attraction_id, _ = attraction.split(" - ", 1)
query = QSqlQuery()
query.prepare('SELECT SUM(point), name '
'FROM (SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor ON '
't_point.id_competitor = t_competitor.id '
'WHERE t_point.id_competitor_take = 1 '
'AND t_point.id_attraction = ' + attraction_id + ' '
'UNION ALL '
'SELECT t_point.point, t_competitor.name '
'FROM t_point INNER JOIN t_competitor '
'ON t_point.id_competitor_take = t_competitor.id '
'WHERE t_point.id_competitor_take != 1 '
'AND t_point.id_attraction = ' + attraction_id + ') '
'GROUP BY name ORDER BY SUM(point) DESC')
query.setForwardOnly(1)
query.exec_()
points_string = "<h1>Points:<br>"
while query.next():
points_string += str(query.value(0)) + " - " + query.value(1) + "<br>"
points_string += "</h1>"
self.game_window.show_string(points_string)
def save(self):
if self.cleared or self.isNull():
self.remove()
else:
if not self.file:
self.file = str(uuid.uuid1()) + '.jpg'
if self.id_:
query = QSqlQuery(self.db)
query.prepare("UPDATE photos SET file=?, url=? WHERE id=?")
query.addBindValue(self.file)
query.addBindValue(self.url)
query.addBindValue(self.id_)
query.exec_()
else:
query = QSqlQuery(self.db)
query.prepare("INSERT INTO photos (file, url) VALUES (?, ?)")
query.addBindValue(self.file)
query.addBindValue(self.url)
query.exec_()
self.id_ = query.lastInsertId()
if not self.image.isNull():
file_name = self._generateFileName(self.file, True)
self.image.save(file_name)
def insertRecord(self, row, record):
self._updateRecord(record)
record.setNull('id') # remove ID value from record
record.setValue('createdat', record.value('updatedat'))
record.remove(record.indexOf('image_id'))
image = record.value('image')
if image:
query = QSqlQuery(self.database())
query.prepare("INSERT INTO images (image) VALUES (?)")
query.addBindValue(image)
query.exec_()
img_id = query.lastInsertId()
record.setValue('image', img_id)
else:
record.setNull('image')
for i in range(4):
field = "photo%d" % (i + 1)
photo = record.value(field)
if not photo.isNull():
if photo.changed:
photo.save()
photo.image = QtGui.QImage() # free image
record.setValue(field, photo.id_)
else:
record.setNull(field)
return super(CollectionModel, self).insertRecord(row, record)
def anime_selection(self, text):
assert isinstance(text, str)
self.ui.comboBox_Songs_Anime.clear()
self.ui.label_Songs_Anime_Count.setText("(0)")
if len(text) != 0:
text_split = text.split(" ")
anime_words = []
for split in text_split:
if len(split) != 0:
anime_words.append(split)
query_string = 'SELECT t_anime.id, t_anime.name FROM t_anime WHERE'
for i in range(len(anime_words)):
if i == 0:
query_string += " t_anime.name LIKE '%'||?||'%'"
else:
query_string += " AND t_anime.name LIKE '%'||?||'%'"
query = QSqlQuery()
query.prepare(query_string)
for i in range(len(anime_words)):
query.bindValue(i, anime_words[i])
query.setForwardOnly(1)
query.exec_()
query_count = 0
while query.next():
query_count += 1
self.ui.comboBox_Songs_Anime.addItem(str(query.value(0)) + " - " + query.value(1))
self.ui.label_Songs_Anime_Count.setText("(" + str(query_count) + ")")
def update_songs_properties(self):
index = self.ui.tableView_Songs.selectedIndexes()
if len(index) > 0:
assert isinstance(index[0], QModelIndex)
row_id = index[0].sibling(index[0].row(), 0).data()
query = QSqlQuery()
query.prepare('SELECT t_artist.id, t_artist.name '
'FROM t_song '
'INNER JOIN t_song_artist ON t_song.id = t_song_artist.id_song '
'INNER JOIN t_artist ON t_artist.id = t_song_artist.id_artist '
'WHERE t_song.id = ' + str(row_id))
query.setForwardOnly(1)
query.exec_()
songs = QTreeWidgetItem()
songs.setText(0, "Artists:")
while query.next():
song = QTreeWidgetItem()
song.setText(0, str(query.value(0)) + " - "
+ query.value(1))
songs.addChild(song)
self.ui.treeWidget_Songs_Properties.clear()
self.ui.treeWidget_Songs_Properties.addTopLevelItem(songs)
self.ui.treeWidget_Songs_Properties.expandAll()
def artist_selection(self, text):
assert isinstance(text, str)
self.ui.listWidget_Songs_Artist_Main.clear()
if len(text) != 0:
text_split = text.split(" ")
artist_words = []
for split in text_split:
if len(split) != 0:
artist_words.append(split)
query_string = 'SELECT t_artist.id, t_artist.name FROM t_artist WHERE'
for i in range(len(artist_words)):
if i == 0:
query_string += " t_artist.name LIKE '%'||?||'%'"
else:
query_string += " AND t_artist.name LIKE '%'||?||'%'"
query = QSqlQuery()
query.prepare(query_string)
for i in range(len(artist_words)):
query.bindValue(i, artist_words[i])
query.setForwardOnly(1)
query.exec_()
while query.next():
self.ui.listWidget_Songs_Artist_Main.addItem(str(query.value(0)) + " - " + query.value(1))
def removeRow(self, row):
record = super().record(row)
ids = []
for field in ['obverseimg', 'reverseimg', 'edgeimg',
'photo1', 'photo2', 'photo3', 'photo4']:
value = record.value(field)
if value:
ids.append(value)
if ids:
ids_sql = '(' + ','.join('?' * len(ids)) + ')'
query = QSqlQuery(self.database())
query.prepare("DELETE FROM photos WHERE id IN " + ids_sql)
for id_ in ids:
query.addBindValue(id_)
query.exec_()
value = record.value('image')
if value:
query = QSqlQuery(self.database())
query.prepare("DELETE FROM images WHERE id=?")
query.addBindValue(value)
query.exec_()
return super().removeRow(row)
def savePositions(self, pages):
for position, page in enumerate(pages):
query = QSqlQuery(self.db)
query.prepare("UPDATE pages SET position=? WHERE id=?")
query.addBindValue(position)
query.addBindValue(page.id)
query.exec_()
def update(self):
self._begin()
self.db.transaction()
fields = ('emitent', 'signaturetype', 'signature', 'signatureimg',
'address', 'latitude', 'longitude')
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(False))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s %s" % (field, Type.toSql(fieldDesc.type))
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self._updateRecord()
self.collection.settings['Version'] = 7
self.collection.settings.save()
self.db.commit()
self._finish()
def updateEquipment(self, equipmentLayout):
if not self.db.open():
return -1
self.devicesInfo = []
for serie in equipmentLayout.getSelectedSeries():
query = QSqlQuery()
query.prepare("SELECT d.description, " +
"d.brand, " +
"d.model, " +
"i.serie, " +
"i.id " +
"FROM Devices AS d " +
"INNER JOIN Items AS i " +
"ON d.id = i.device_id " +
"WHERE i.serie = ?")
query.addBindValue(serie)
query.exec_()
while query.next():
device = {}
self.devicesInfo.append(device)
device["name"] = str(query.value(0))
device["brand"] = str(query.value(1))
device["model"] = str(query.value(2))
device["serie"] = str(query.value(3))
device["id"] = str(query.value(4))
self.db.close()
def update(self):
self._begin()
self.db.transaction()
fields = ['ruler', 'region']
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(fieldDesc.enabled))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s TEXT" % field
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self.progressDlg.setLabelText(self.tr("Saving..."))
sql = "UPDATE photos SET title=NULL"
QSqlQuery(sql, self.db)
self.collection.settings['Version'] = 4
self.collection.settings.save()
self.db.commit()
self._finish()
def populateCustomers(self):
query = QSqlQuery()
self.ui.customerChooser.clear()
query.exec_('select id, name,address, zipcode from customer')
while query.next():
c = self.extractCustomer(query)
self.ui.customerChooser.addItem(c.name)
def createReference(self):
sections = self.reference.allSections()
progressDlg = Gui.ProgressDialog(self.tr("Updating reference"),
self.tr("Cancel"), len(sections), self.parent())
for columnName in sections:
progressDlg.step()
if progressDlg.wasCanceled():
break
refSection = self.reference.section(columnName)
if isinstance(refSection, CrossReferenceSection):
rel = refSection.model.relationModel(1)
for i in range(rel.rowCount()):
data = rel.data(rel.index(i, rel.fieldIndex('value')))
parentId = rel.data(rel.index(i, rel.fieldIndex('id')))
query = QSqlQuery(self.db)
sql = "SELECT DISTINCT %s FROM coins WHERE %s<>'' AND %s IS NOT NULL AND %s=?" % (columnName, columnName, columnName, refSection.parentName)
query.prepare(sql)
query.addBindValue(data)
query.exec_()
refSection.fillFromQuery(parentId, query)
else:
sql = "SELECT DISTINCT %s FROM coins WHERE %s<>'' AND %s IS NOT NULL" % (columnName, columnName, columnName)
query = QSqlQuery(sql, self.db)
refSection.fillFromQuery(query)
progressDlg.reset()
def add_project_to_db(self):
"""adds all info about a project to the projects table
"""
self.log.debug("Adding new project to database...")
mydate = general.timestamp("%d.%m.%Y")
query = """INSERT INTO projects VALUES
('{}', 'Open', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}');
""".format(self.project_name, mydate, self.user, self.gene, self.pool,
self.title, self.description, self.accession_ID, self.submission_ID)
q = QSqlQuery()
q.exec_(query)
lasterr = q.lastError()
if lasterr.isValid():
self.log.error(lasterr.text())
if lasterr.text().startswith("UNIQUE constraint failed:"):
self.project_btn.setText("Such a project exists already!")
self.project_btn.setStyleSheet(general.btn_style_clickme)
self.submit_btn.setEnabled(False)
self.accession_ID = ""
self.acc_entry.setText(self.accession_ID)
success = False
else:
self.log.debug("=> Added to database successfully")
success = True
return success
def save_lists(self, only_if_changed=False):
if not only_if_changed or self.__lists_changed:
self.db.transaction()
# Remove old values
self.__remove_lists()
for position, param in enumerate(self.columns):
query = QSqlQuery(self.db)
query.prepare("INSERT INTO lists (pageid, fieldid, position,"
" enabled, width, sortorder)"
" VALUES (?, ?, ?, ?, ?, ?)")
query.addBindValue(self.page.id)
query.addBindValue(param.fieldid)
query.addBindValue(position)
query.addBindValue(int(param.enabled))
if not param.enabled:
param.width = None
query.addBindValue(param.width)
query.addBindValue(param.sortorder)
query.exec_()
self.db.commit()
self.__lists_changed = False
def save_filters(self):
self.db.transaction()
# Remove old values
self.__remove_filters()
for fieldId, columnFilters in self.filters.items():
for filter_ in columnFilters.filters():
query = QSqlQuery(self.db)
query.prepare("INSERT INTO filters (pageid, fieldid, value,"
" blank, data, revert) VALUES (?, ?, ?, ?, ?, ?)")
query.addBindValue(self.page.id)
query.addBindValue(fieldId)
query.addBindValue(filter_.value)
if filter_.isBlank():
blank = int(True)
else:
blank = None
query.addBindValue(blank)
if filter_.isData():
data = int(True)
else:
data = None
query.addBindValue(data)
if filter_.isRevert():
revert = int(True)
else:
revert = None
query.addBindValue(revert)
query.exec_()
self.db.commit()
def getImage(self, img_id):
query = QSqlQuery(self.database())
query.prepare("SELECT image FROM images WHERE id=?")
query.addBindValue(img_id)
query.exec_()
if query.first():
return query.record().value(0)
def update(self):
self._begin()
self.db.transaction()
fields = ('category', 'sort_id')
for field in fields:
self._updateRecord()
fieldDesc = getattr(self.collection.fields, field)
fieldDesc.enabled = False
query = QSqlQuery(self.db)
query.prepare("INSERT INTO fields (id, title, enabled)"
" VALUES (?, ?, ?)")
query.addBindValue(fieldDesc.id)
query.addBindValue(fieldDesc.title)
query.addBindValue(int(False))
query.exec_()
sql = "ALTER TABLE coins ADD COLUMN %s %s" % (field, Type.toSql(fieldDesc.type))
QSqlQuery(sql, self.db)
self.collection.fields.userFields.append(fieldDesc)
self._updateRecord()
sql = "UPDATE coins SET sort_id = id"
QSqlQuery(sql, self.db)
self.collection.settings['Version'] = 6
self.collection.settings.save()
self.db.commit()
self._finish()
def create_model(self):
"""creates the table model
"""
q = QSqlQuery(self.query + " " + self.filter)
self.model = QSqlQueryModel()
q.exec_(self.query)
self.model.setQuery(q)
def removePage(self, page):
page.listParam.remove()
page.treeParam.remove()
query = QSqlQuery(self.db)
query.prepare("DELETE FROM pages WHERE id=?")
query.addBindValue(page.id)
query.exec_()
def create_model(self):
"""creates the model as QSqlQueryModel,
using the given query
"""
self.model = QSqlQueryModel()
q = QSqlQuery()
q.exec_(self.query)
self.model.setQuery(q)
def reloadEvent(self):
query = QSqlQuery()
query.prepare("SELECT id, name, place FROM t_event")
query.setForwardOnly(1)
query.exec_()
while query.next():
self.comboBox_Event.addItem(str(query.value(0)) + " - " + query.value(1) + " - " + query.value(2))
pass
def newCustomer(self, name, address, zip):
query = QSqlQuery()
sql = "insert into customer values((select max(id) from customer) + 1,'{0}','{1}','{2}')".format(name, address,
zip)
query.exec_(sql)
query.exec_('select id, name,address, zipcode from customer order by id desc limit 1')
query.next()
return self.extractCustomer(query)
def remove_book_from_collection(book_id, collection):
err = ''
q = QSqlQuery(config.db)
q.prepare(queries.DELETE_BOOK_FROM_COLLECTION)
q.bindValue(0, collection.id)
q.bindValue(1, book_id)
if not q.exec_():
err = q.lastError().text()
config.db.rollback()
else:
config.db.commit()
return err
def empty_rule(self, name=""):
if name == "":
return
qstr = "DELETE FROM connections WHERE rule = ?"
with self._lock:
q = QSqlQuery(qstr, self.db)
q.prepare(qstr)
q.addBindValue(name)
if not q.exec_():
print("db, empty_rule() ERROR: ", qstr)
print(q.lastError().driverText())
def initPlanTable(self):
index = 0
query = QSqlQuery()
query.exec_("""select * from(select *,
(select (capacity - (select count(*) as ReservedSeats from Reservation where planId = tbt.planId)) as Remained from Bus as b where b.id = tbt.busId) as RemainedSeats
from (select id as planId,busId,(select c.name from City as c where c.id == p.originId) as Origin , (select c.name from City as c where c.id == p.destId) as Destination , dptDate , arvDate from Plan as p)
as tbt)as tbt2 where tbt2.RemainedSeats > 0""")
self.planReserveTable.clear()
self.planReserveTable.setHorizontalHeaderLabels([
'Plan Id', 'Bus Id', 'Origin', 'Destination', 'Departure',
'Arrival', 'Remained Seats'
])
while query.next():
self.planReserveTable.setRowCount(index + 1)
for i in range(7):
self.planReserveTable.setItem(
index, i, QTableWidgetItem(str(query.value(i))))
index += 1
def remove(self, qstr):
try:
q = QSqlQuery(qstr, self.db)
if q.exec_():
return True
else:
print("db, remove() ERROR: ", qstr)
print(q.lastError().driverText())
except Exception as e:
print("db, remove exception: ", e)
return False
def update_myrequest(self, id, **kwargs):
query = QSqlQuery(self.db)
keys = list(kwargs.keys())
keysstr = ["%s = ?" % (x, ) for x in keys]
querystr = 'update myrequests set %s where id = ?' % (
", ".join(keysstr), )
query.prepare(querystr)
for k in keys:
query.addBindValue(kwargs[k])
query.addBindValue(id)
if not query.exec_():
logger.error(query.lastError().text())
def modelChanged(self):
filter_ = self.model.filter()
if filter_:
sql_filter = "WHERE %s" % filter_
else:
sql_filter = ""
self.points = []
sql = "SELECT latitude, longitude, id, status FROM coins %s" % sql_filter
query = QSqlQuery(self.model.database())
query.exec_(sql)
while query.next():
record = query.record()
lat = record.value(0)
lng = record.value(1)
if lat and lng:
coin_id = record.value(2)
status = record.value(3)
self.addMarker(lat, lng, coin_id, status)
self.showMarkers()
def set_info(self, image_date, info):
query = QSqlQuery(self.db)
query.prepare(
'INSERT INTO copyright (image_date, copyright_info) VALUES (?, ?);'
)
query.addBindValue(image_date)
query.addBindValue(info)
if not query.exec_():
self.error.emit('Error adding copyright info',
query.lastError().text())
return False
return True
def queryDB(self):
db = QSqlDatabase.addDatabase('QODBC')
# TODO: Add support for trusted connections.
# ("Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;")
db.setDatabaseName(
'DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' %
('.', 'Test', 'sa', 'sql'))
if not db.open():
print('db.open failed')
QMessageBox.critical(None, ("无法打开数据库"),
("无法建立到数据库的连接,这个例子需要SQLite 支持，请检查数据库配置。\n\n"
"点击取消按钮退出应用。"), QMessageBox.Cancel)
return False
query = QSqlQuery()
# query.exec_("create table people(id int primary key, name varchar(20), address varchar(30))")
query.exec_("select * from people")
print(str(query.result()))
db.close()
return True
def add_book_in_collection(book_id, collection):
err = ''
q = QSqlQuery(config.db)
q.prepare(queries.ADD_BOOK_IN_COLLECTION)
q.bindValue(0, collection.id)
q.bindValue(1, book_id)
if not q.exec_():
err = q.lastError().text()
config.db.rollback()
else:
config.db.commit()
return err
def getServingAndNeighbors(self):
self.openConnection()
MAX_NEIGHBORS = 32
dataList = []
condition = ""
# Set query condition for serving cell
if self.timeFilter:
condition = "WHERE time <= '%s'" % (self.timeFilter)
dataList.append([self.timeFilter, "", "", ""])
for neighbor in range(1, MAX_NEIGHBORS):
row = []
queryString = """SELECT cdma_cell_pn_%d, cdma_ecio_%d, cdma_cell_type_%d
FROM cdma
%s
ORDER BY time DESC
LIMIT 1""" % (
neighbor,
neighbor,
neighbor,
condition,
)
query = QSqlQuery()
query.exec_(queryString)
if query.first():
for i in range(3):
row.append("" if str(query.value(i)) ==
"NULL" else query.value(i))
else:
row = ["", "", ""]
if not all(v == "" for v in row):
row.insert(0, "")
dataList.append(row)
if len(dataList) == 0:
dataList.append([self.timeFilter, "", "", ""])
self.closeConnection()
return dataList
def insertRecord(self, row, record):
self._updateRecord(record)
record.setNull('id') # remove ID value from record
record.setValue('createdat', record.value('updatedat'))
for field in [
'obverseimg', 'reverseimg', 'edgeimg', 'photo1', 'photo2',
'photo3', 'photo4'
]:
value = record.value(field)
if value:
query = QSqlQuery(self.database())
query.prepare(
"INSERT INTO photos (title, image) VALUES (?, ?)")
query.addBindValue(record.value(field + '_title'))
query.addBindValue(value)
query.exec_()
img_id = query.lastInsertId()
else:
img_id = None
record.setValue(field, img_id)
record.remove(record.indexOf(field + '_id'))
record.remove(record.indexOf(field + '_title'))
value = record.value('image')
if value:
query = QSqlQuery(self.database())
query.prepare("INSERT INTO images (image) VALUES (?)")
query.addBindValue(value)
query.exec_()
img_id = query.lastInsertId()
else:
img_id = None
record.setValue('image', img_id)
record.remove(record.indexOf('image_id'))
return super(CollectionModel, self).insertRecord(row, record)
def initda():
print(111)
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("./db/database.db")
query = QSqlQuery()
query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
"('2018-05-07', '裂缝','hehe',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
print(222)
query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
"('2018-05-07', '裂缝','123',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
"('2018-05-07', '裂缝','124',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
query.exec_("INSERT INTO 深渊统计 (日期, 图, 角色, 深渊次数, 爆货次数, 灵魂爆数,加百利次数, 晶石, SS1,SS2,SS3,SS4,SS5,SS6,SS7,SS8) VALUES "
"('2018-05-07', '裂缝','453',1,2,3,4,5,'SS1','SS2','SS3','SS4','SS5','','','')")
def btnShow(self):
print('btnShow')
db = QSqlDatabase.addDatabase('QODBC')
# TODO: Add support for trusted connections.
# ("Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;")
db.setDatabaseName(
'DRIVER={SQL Server};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s;' %
('.', 'Test', 'sa', 'sql'))
# 判断是否打开
if not db.open():
QMessageBox.critical(
None, ("Cannot open database"),
("Unable to establish a database connection. \n"
"This example needs SQLite support. Please read "
"the Qt SQL driver documentation for information "
"how to build it.\n\n"
"Click Cancel to exit."), QMessageBox.Cancel)
return False
print('btnShow2')
# 声明数据库查询对象
query = QSqlQuery()
# 创建表
query.exec_(
"create table student(id int primary key, name vchar, sex vchar, age int, deparment vchar)"
)
# 添加记录
query.exec_("insert into student values(1,'张三1','男',20,'计算机')")
query.exec_("insert into student values(2,'李四1','男',19,'经管')")
query.exec_("insert into student values(3,'王五1','男',22,'机械')")
# 关闭数据库
db.close()
return True
def fill_db(self):
query = QSqlQuery(db=self.db)
with open('/home/fenrir/tmp/sha.txt') as book:
text = book.read()
text = text[:int(len(text) / 20)]
num_s = 0
num_w = 0
while text:
ends = '.!?'
endn = [text.find(e) for e in ends if e in text]
if endn:
sentence = text[:min(endn) + 1]
text = text[min(endn) + 1:]
else:
sentence = text
text = ''
q4 = 'insert into sentences values (%s, "%s", %s)' % (
num_s, sentence, 0)
query.exec_(q4)
words = sentence.split(' ')
for word in words:
q5 = 'insert into words values (%s, "%s", %s)' % (
num_w, word, num_s)
query.exec_(q5)
q6 = 'insert into domains values (%s, "%s", %s)' % (
num_w, word, num_w)
query.exec_(q6)
num_w += 1
num_s += 1
def registrar_cliente(**kwargs):
data_formatada = datetime.strptime(kwargs["data_nascimento"], "%d/%m/%Y")
try:
sql_cliente = f"""INSERT INTO cliente
(nome, email, senha, genero, numero_conta, data_nascimento, data_registro)
VALUES
('{kwargs["nome"]}', '{kwargs["email"]}', '{kwargs["senha"]}', '{kwargs["genero"]}',
{kwargs["numero_da_conta"]}, '{data_formatada}', '{datetime.now()}')"""
q = QSqlQuery()
q.exec_(sql_cliente)
print(sql_cliente)
mensagem = q.result().lastError().text()
if mensagem:
print(q.result().lastError().text())
return False, mensagem
else:
sql_conta = f"""INSERT INTO conta
(numero_conta, agencia, tipo_conta, saldo, data_registro)
VALUES
({kwargs["numero_da_conta"]}, '{kwargs["agencia"]}', '{kwargs["tipo_da_conta"]}',
{kwargs["saldo"]}, '{datetime.now()}');"""
qc = QSqlQuery()
qc.exec_(sql_conta)
print(sql_conta)
mensagem = qc.result().lastError().text()
if not mensagem:
return True, mensagem
else:
print(qc.result().lastError().text())
print(2)
return False, ''
except QSqlError as qc:
print(qc.result().lastError().text())
print('erro ao registrar o cliente')
def CadastrarVeiculo(veiculo):
conn = ConexaoSQL
db = conn.getConexao()
db.open()
query = QSqlQuery()
query.prepare("INSERT INTO Veiculo(Modelo, Marca, AnoModelo, Placa, "
"Alugado, Batido, KmAtual, ValorDiaria, Descricao, TipoVeiculo) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
query.addBindValue(veiculo.Modelo)
query.addBindValue(veiculo.Marca)
query.addBindValue(veiculo.AnoModelo)
query.addBindValue(veiculo.Placa)
query.addBindValue(veiculo.Alugado)
query.addBindValue(veiculo.Batido)
query.addBindValue(veiculo.KmAtual)
query.addBindValue(veiculo.ValorDiaria)
query.addBindValue(veiculo.Descricao)
query.addBindValue(veiculo.TipoVeiculo)
query.exec_()
db.commit()
def del_DB(ui, window, db, table, identifier, idvalue, widget_list):
db.open()
#Prepare a query and add all values then execute and commit to DB
query = QSqlQuery(db)
db.transaction()
query.prepare(f"DELETE FROM {table} WHERE {identifier}")
for i in idvalue:
query.addBindValue(i)
try:
query.exec_()
db.commit()
db.close()
print(query.lastQuery())
QtWidgets.QMessageBox.information(
window, f"{table} deleted",
f"{table} has been successfully deleted", QtWidgets.QMessageBox.Ok)
except Exception:
QtWidgets.QMessageBox.critical(window, 'Error', 'Something went wrong',
QtWidgets.QMessageBox.Ok)
finally:
for i in widget_list:
i.clear()
def add_DB(ui, window, db, table, vlist, placeholderstr, widget_list):
db.open()
#Prepare a query and add all values then execute and commit to DB
query = QSqlQuery(db)
db.transaction()
query.prepare(f"INSERT INTO {table} VALUES({placeholderstr})")
for i in vlist:
query.addBindValue(i)
try:
query.exec_()
db.commit()
db.close()
QtWidgets.QMessageBox.information(
window, f"New {table} created",
f"New {table} has been successfully created",
QtWidgets.QMessageBox.Ok)
except Exception:
QtWidgets.QMessageBox.critical(window, 'Error', 'Something went wrong',
QtWidgets.QMessageBox.Ok)
finally:
for i in widget_list:
i.clear()
def find(self, **args):
condition = []
for key, value in args.items():
if key not in ['id', 'classid', 'courseid', 'exam_weight']:
condition.append("{0}='{1}'".format(key, value))
else:
condition.append("{0}={1}".format(key, value))
And = " and ".join(condition)
model = QSqlQuery()
model.exec_('PRAGMA foreign_keys = ON;')
if condition != []:
sql = 'select * from exam where {};'.format(And)
else:
sql = 'select * from exam'
model.exec_(sql)
res = []
while model.next():
#print((model.value(0),model.value(1),model.value(2)))
res.append((model.value(0), model.value(1), model.value(2),
model.value(3), model.value(4), model.value(5),
model.value(6), model.value(7)))
return res
def db_dac_register_data_to_dictionary(db, regname, dac_num):
tablename = 'tbldacRegBits'
query = QSqlQuery(db)
qrytxt = "select {name}, {mask}, {shift}, {value} ,{parent} from {tn} inner join tbldacRegisters on " \
"{parent} = tbldacRegisters.ADDRESS where tbldacRegisters.NAME = '{rn}' and dac_num = {dac_num}" \
.format(name=tablename + ".NAME", mask=tablename + ".MASK", shift=tablename + ".SHIFT",
value=tablename + ".VALUE", parent=tablename + ".FK_PARENT_ID", tn=tablename, rn=regname, dac_num=dac_num)
data_dict = {}
data = []
query.exec_(qrytxt)
while query.next():
data_dict["NAME"] = query.value(0)
data_dict["MASK"] = query.value(1)
data_dict["SHIFT"] = query.value(2)
data_dict["VALUE"] = query.value(3)
data_dict["PK_PAPRENT_ID"] = query.value(4)
data.append(data_dict)
data_dict = {}
return data
def insertCity(self,name):
if name != "" and name not in self.cities.keys():
query = QSqlQuery()
query.prepare("insert into City(name) values(:cityName)")
query.bindValue(":cityName" , name)
if query.exec_() == False:
criticalMessage("Could not insert into City table")
else:
self.initCityTable()
self.cityNameLineEdit.clear()
self.initReservationForm()
else:
criticalMessage("Could not insert into City table")
def log_transferir(dados):
numero_conta = dados['numero_conta_s']
conta_destino = dados['numero_conta_d']
valor = dados['valor']
data = str(datetime.now())
saldo_atual = get_saldo(numero_conta)
saldo = saldo_atual + valor
sql_t = f"""INSERT INTO historico (numero_conta, valor, saldo_anterior, saldo_atual,
operacao, conta_destino, data)
VALUES ({numero_conta},{valor}, {saldo}, {saldo_atual},
'transferir' ,{conta_destino} , '{data}')"""
try:
q = QSqlQuery()
q.exec_(sql_t)
print(sql_t)
except QSqlError as qc:
print(qc.result().lastError().text())
print(err_deposito)
def registrar_funcionario(dados):
data_formatada = datetime.strptime(dados["data_nascimento"], "%d/%m/%Y")
data_atual = datetime.now()
try:
sql = f"""INSERT INTO funcionario (nome, email, senha, cargo, salario, data_nascimento, data_registro)
VALUES('{dados["nome"]}', '{dados["email"]}', '{dados["senha"]}', '{dados["cargo"]}', {dados["salario"]},
'{data_formatada}', '{data_atual}')"""
q = QSqlQuery()
q.exec_(sql)
print(sql)
if q.result().lastError().text():
mensagem = q.result().lastError().text()
print(mensagem)
return False, mensagem
else:
return True, ''
except QSqlError as qc:
print(qc.result().lastError().text())
def _generateWeatherCode(self, weatherCode):
categories = ["Low Cloud Amount", "Visibility Kilometres", "Low Cloud Height", "Weather", "Remarks Mission", "Remarks Weather"]
query = QSqlQuery(self.dbm.db)
pos = 0
help = 0
weatherDescription = ""
for c in weatherCode:
qryStr = "select description from wetter where category = '{0}' and code = '{1}' limit 1".format(categories[pos - help], c)
query.exec_(qryStr)
query.first()
fn = query.value(0)
if pos <= 5:
weatherDescription += categories[pos] + ': ' + fn
if pos < 5:
weatherDescription += '\n'
else:
weatherDescription += '; ' + fn
if pos >= 5:
help += 1
pos += 1
return weatherDescription
def init(self) -> bool:
"""
Initialise Albums table.
It creates the table if it does not exists yet
:return: bool - initialization was successful, else false if initialization were unsuccessful
"""
if self.TABLE_NAME not in self._conn.tables():
query = QSqlQuery(self._conn)
return query.exec_(
"create table %s (id integer primary key autoincrement, name text)"
% self.TABLE_NAME)
return True
def load_spatialite():
queries = (
"SELECT load_extension('mod_spatialite')",
"SELECT InitSpatialMetadata(1)",
)
q = QSqlQuery()
for query in queries:
if not q.exec_(query):
print(
f"Error: cannot load the Spatialite extension ({q.lastError().text()})"
)
return False
return True
def __init__(self, db, parent=None):
super().__init__(parent)
self.db = db
if 'fields' not in self.db.tables():
self.create(self.db)
query = QSqlQuery(self.db)
query.prepare("SELECT * FROM fields")
query.exec_()
self.userFields = []
self.disabledFields = []
while query.next():
record = query.record()
fieldId = record.value('id')
field = self.field(fieldId)
field.title = record.value('title')
field.enabled = bool(record.value('enabled'))
if field.enabled:
self.userFields.append(field)
else:
self.disabledFields.append(field)
def dumpCache(self):
self.db.database(self.connectionName, False)
# just to close the connection I use False as the second parameter
query = QSqlQuery(self.db)
isDeleteSuccessful = query.exec_("drop table cache")
if isDeleteSuccessful:
print("the table has been deleted successfully")
else:
print("the table could not be deleted")
print("error:")
print(query.lastError().text())
return True
def create(db=QSqlDatabase()):
db.transaction()
sql = """CREATE TABLE fields (
id INTEGER NOT NULL PRIMARY KEY,
title TEXT,
enabled INTEGER)"""
QSqlQuery(sql, db)
fields = CollectionFieldsBase()
for field in fields:
query = QSqlQuery(db)
query.prepare("""INSERT INTO fields (id, title, enabled)
VALUES (?, ?, ?)""")
query.addBindValue(field.id)
query.addBindValue(field.title)
enabled = field in fields.userFields
query.addBindValue(int(enabled))
query.exec_()
db.commit()
def submit(self):
self.text = self.textedit.toPlainText()
self.feedbackid = 'f' + str(time.strftime("%g%m%d")) + str(
random.randint(0, 9999)).zfill(4)
now = int(time.time())
timeStruct = time.localtime(now)
self.strTime = time.strftime("%Y/%m/%d %H:%M", timeStruct)
if (len(self.text) < 15):
print(
QMessageBox.warning(self, "警告", "无法提交少于15字的反馈。",
QMessageBox.Yes, QMessageBox.Yes))
return
if (len(self.text) > 200):
print(
QMessageBox.warning(self, "警告", "无法提交大于200字的反馈",
QMessageBox.Yes, QMessageBox.Yes))
return
db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName('./db/myPet.db')
db.open()
query = QSqlQuery()
sql = "select * from feedback where FeedbackId = '%s'" % (
self.feedbackid)
query.exec_(sql)
if (query.next()): # 反馈编号已存在
print(
QMessageBox.warning(self, "警告", "系统错误，请重新提交", QMessageBox.Yes,
QMessageBox.Yes))
return
else:
sql = "insert into feedback values('%s','%s','%s','未读','%s')" % (
self.feedbackid, self.UserId, self.strTime, self.text)
query.exec_(sql)
db.commit()
db.close()
print(
QMessageBox.information(self, "提醒", "您已成功提交反馈信息!",
QMessageBox.Yes, QMessageBox.Yes))
self.textedit.setText("")