def addItem(self, data):
hash = createHash(data)
if self.lastAddedHash == hash:
return
self.lastAddedHash = hash
query = QSqlQuery()
prepareQuery(query,
"""
delete from clipboardItems where itemHash = :hash;
""")
query.bindValue(':hash', hash)
executeQuery(query)
text = data.get(mimeText, '')
record = self.record()
record.setValue('itemHash', hash)
record.setValue('itemText', text)
record.setValue('itemData', QByteArray(serializeData(data)))
record.setValue('copyTime', QDateTime.currentDateTime())
if not self.insertRecord(0, record):
raise ValueError('Failed to insert item: ' + self.lastError().text())
self.submitChanges()
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 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 modelChanged(self):
# Fetch all selected records
while self.model().canFetchMore():
self.model().fetchMore()
newCount = self.model().rowCount()
# Show updated coins count
sql = "SELECT count(*) FROM coins"
query = QSqlQuery(sql, self.model().database())
query.first()
totalCount = query.record().value(0)
labelText = self.tr("%d/%d coins") % (newCount, totalCount)
self.listCountLabel.setText(labelText)
# Restore selected row
if self.selectedRowId is not None:
idIndex = self.model().fieldIndex('id')
startIndex = self.model().index(0, idIndex)
indexes = self.proxyModel.match(startIndex, Qt.DisplayRole,
self.selectedRowId, 1, Qt.MatchExactly)
if indexes:
self.selectRow(indexes[0].row())
else:
self.selectedRowId = None
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 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 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 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 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 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 report_month_sales(file, dbase):
for_date = date = datetime.date.today()
query = QSqlQuery("SELECT employee.firstname AS firstname, employee.lastname AS lastname, employee.middlename AS middlename, \
count(orders.id) AS qnt, sum(orders.price) AS price \
FROM employee INNER JOIN orders ON employee.id = orders.employee_id \
WHERE orders.regdate >= \"{}\"\
GROUP BY employee.firstname, employee.lastname, employee.middlename".format(for_date.strftime("%Y.%m.01")), dbase)
if not query.isActive():
return print(query.lastError().text())
with open("reports/emp_sales.html", "r") as f:
html = f.read()
rows = []
qnt_total = 0
price_total = 0
while query.next():
rows.append("<tr><td align='center'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr>".format(
"{} {}. {}.".format(query.value("lastname"), query.value("firstname")[0], query.value("middlename")[0]),
int(query.value("qnt")), int(query.value("price"))))
qnt_total += int(query.value("qnt"))
price_total += int(query.value("price"))
rows.append("<b><tr><td align='right'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr></b>".format(
"Итого:", qnt_total, price_total))
date = datetime.date.today().strftime("%d.%m.%Y")
html = html.format(for_month = for_date.strftime("%m.%Y"), date = date, rows = "\n".join(rows))
tmpfile = "/tmp/report{}.html".format(random.randrange(1000000))
with open(tmpfile, "w") as f:
f.write(html)
subprocess.call(["wkhtmltopdf", tmpfile, file])
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 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 update_song_type(self):
query = QSqlQuery()
query.prepare('SELECT t_song_type.id, t_song_type.name '
'FROM t_song_type')
query.setForwardOnly(1)
query.exec_()
while query.next():
self.ui.comboBox_Songs_Type.addItem(str(query.value(0)) + " - " + query.value(1))
def reloadSerie(self):
query = QSqlQuery()
query.prepare("SELECT id, name FROM t_anime_serie")
query.setForwardOnly(1)
query.exec_()
self.comboBox_Serie.clear()
while query.next():
self.comboBox_Serie.addItem(str(query.value(0)) + ' - ' + query.value(1))
pass
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def gameView(self):
query = QSqlQuery()
query.prepare('SELECT t_attraction.id, t_attraction.name, t_event.name FROM t_attraction '
'INNER JOIN t_event ON t_attraction.id_event = t_event.id')
query.setForwardOnly(1)
query.exec_()
self.comboBox_Game_Attraction.clear()
while query.next():
self.comboBox_Game_Attraction.addItem(str(query.value(0)) + ' - ' + query.value(1) + ' - ' + query.value(2))
pass
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 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 report_day_sales(for_date, file, dbase):
query = QSqlQuery("SELECT shop.name AS name, sum(order_detail.quantity) AS qnt, sum(detail.price * order_detail.quantity) AS price \
FROM shop INNER JOIN orders ON shop.id = orders.shop_id \
INNER JOIN order_detail ON order_detail.order_id = orders.id \
INNER JOIN detail ON detail.id = order_detail.detail_id \
WHERE orders.regdate = \"{}\"\
GROUP BY shop.name".format(for_date.toString("yyyy.MM.dd")), dbase)
if not query.isActive():
return print(query.lastError().text())
with open("reports/goods.html", "r") as f:
html = f.read()
rows = []
qnt_total = 0
price_total = 0
while query.next():
rows.append("<tr><td align='center'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr>".format(
query.value("name"), int(query.value("qnt")), int(query.value("price"))))
qnt_total += int(query.value("qnt"))
price_total += int(query.value("price"))
rows.append("<b><tr><td align='right'>{}</td><td align='center'>{}</td><td align='center'>{}</td></tr></b>".format(
"Итого:", qnt_total, price_total))
date = datetime.date.today().strftime("%d.%m.%Y")
html = html.format(for_day = for_date.toString("dd.MM.yyyy"), date = date, rows = "\n".join(rows))
tmpfile = "/tmp/report{}.html".format(random.randrange(1000000))
with open(tmpfile, "w") as f:
f.write(html)
subprocess.call(["wkhtmltopdf", tmpfile, file])
def update_attractions(self):
query = QSqlQuery()
query.prepare('SELECT t_attraction.id, t_attraction.name '
'FROM t_attraction')
query.setForwardOnly(1)
query.exec_()
self.ui.comboBox_Game_Attraction.clear()
while query.next():
self.ui.comboBox_Game_Attraction.addItem(str(query.value(0)) + " - " + query.value(1))
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def update_attractions_date(self):
self.ui.dateTimeEdit_Attractions_Start.setDate(QDate.currentDate())
self.ui.dateTimeEdit_Attractions_End.setDate(QDate.currentDate())
query = QSqlQuery()
query.prepare('SELECT t_event.id, t_event.name '
'FROM t_event')
query.setForwardOnly(1)
query.exec_()
while query.next():
self.ui.comboBox_Attractions_Event.addItem(str(query.value(0)) + " - " + query.value(1))
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def reloadArtist(self):
query = QSqlQuery()
query.prepare("SELECT id, name FROM t_artist")
query.setForwardOnly(1)
query.exec_()
artistlist = []
while query.next():
artistlist.append(str(query.value(0)) + ' - ' + query.value(1))
self.modelright = QStringListModel()
self.modelright.setStringList(artistlist)
self.listView_ArtistList.setModel(self.modelright)
pass
def __init__(self, collection):
super(CollectionSettings, self).__init__()
self.db = collection.db
if 'settings' not in self.db.tables():
self.create(self.db)
query = QSqlQuery("SELECT * FROM settings", self.db)
while query.next():
record = query.record()
if record.value('title') in self.keys():
self.__setitem__(record.value('title'), record.value('value'))
def execSql(self, query):
print(query)
sqlquery = QSqlQuery(query, self.db)
#self.db.exec(query)
if sqlquery.lastError().number() != -1: #TODO: print these in a dialog box
QMessageBox.critical(self, "Error", 'ERROR OCCURRED WHILE EXECUTING STATEMENT: ' + query + "\n" + \
'Database Text:' + sqlquery.lastError().databaseText() + \
'Databse Driver:' + sqlquery.lastError().driverText())
return None
self.onFilterChanged()
return sqlquery
def __init__(self, collection):
super(CollectionDescription, self).__init__(collection)
self.db = collection.db
if 'description' not in self.db.tables():
self.create(collection)
query = QSqlQuery("SELECT * FROM description", self.db)
query.first()
record = query.record()
self.title = record.value('title')
self.description = record.value('description')
self.author = record.value('author')
def init_database(self):
success = self.database.open()
if not success:
raise ConnectionError(self.database.lastError().text())
query = QSqlQuery(self.database)
query_command = "CREATE TABLE IF NOT EXISTS 'question_type' ({}{}{})".format(
'id integer primary key,',
'name text,',
'position integer'
)
if not query.exec(query_command):
raise ConnectionError(query.lastError().text())
def deleteData(self):
current_row = self.listView.currentIndex()
des_name = self.listView.model().data(current_row,0)
# ถ้าไม่ได้เลือก record ให้ return
if des_name == None:
self.showDialog('กรุณาเลือกปลายทางที่จะลบก่อน')
return
des_name_inUse = False # เก็บ status ของ des_name
# สร้าง sqlmModel ไว้สำหรับ query des_id มาเทียบกันระหว่างสองตาราง
tempFromQueue = QSqlQueryModel()
selectQueue = QSqlQuery()
selectQueue.prepare('SELECT des_id FROM queue')
tempFromDes = QSqlQueryModel()
selectDes = QSqlQuery()
selectDes.prepare('SELECT des_id FROM destination WHERE des_name = ' + f"'{des_name}'")
# เก็บค่าของจากทั้งสองตารางมาตรวจสอบการซ้ำของ des_id ว่ามีการใชงานอยู่จริงหรือไม่
# exec query ถ้าผ่านให้ setQuery
if selectDes.exec() == True and selectQueue.exec() == True:
tempFromDes.setQuery(selectDes)
tempFromQueue.setQuery(selectQueue)
# fetch more หา rowCount ของจริง
while tempFromQueue.canFetchMore():
tempFromQueue.fetchMore()
# เทียบค่า des_id
for i in range(tempFromQueue.rowCount()):
if tempFromQueue.index(i,0).data() == tempFromDes.index(0,0).data():
des_name_inUse = True
break
else:
des_name_inUse = False
# ตรวจสอบว่า des_name ไม่มีการใช้งานในตาราง queue
# หากมีการใช้งาน จะไม่สามารถลบออกจาก destination ได้
if des_name_inUse == True:
self.showDialog(f'ปลายทาง {des_name} มีการใช้งานในตาราง ไม่สามารถลบได้')
return
# ยืนยันการลบด้วย code 1024
confCode = self.confDelete(des_name)
if confCode == 1024:
try:
deleteQuery = QSqlQuery()
deleteQuery.prepare("DELETE FROM destination WHERE des_name = " +
f"('{des_name}')")
print('Query = ' + deleteQuery.lastQuery())
if deleteQuery.exec():
print('DELETE COMPLETE')
self.showDialog('ลบปลายทาง : ' + des_name + ' แล้ว')
self.loadData()
else:
print('DELETE FALSE = ' + deleteQuery.lastError().text())
except(QSqlError) as e:
print(str(time.strftime("%H:%M:%S : ", time.localtime())) +
'ERROR :' + str(e))
else:
print('User decided cancel delete this record.')
def delete_collection(collection):
err = ''
q = QSqlQuery(config.db)
if collection.type == CollectionType.Collection:
q.prepare(queries.DELETE_BOOKS_FROM_COLLECTION)
q.bindValue(0, collection.id)
if not q.exec_():
err = q.lastError().text()
config.db.rollback()
if not err:
q.prepare(queries.DELETE_COLLECTION)
q.bindValue(0, collection.id)
if not q.exec_():
err = q.lastError().text()
config.db.rollback()
else:
config.db.commit()
return err
def update_collection(collection):
err = ''
q = QSqlQuery(config.db)
q.prepare(queries.UPDATE_COLLECTION)
q.bindValue(0, collection.name)
q.bindValue(1, collection.type.value)
q.bindValue(2, collection.criteria)
q.bindValue(3, collection.id)
if not q.exec_():
err = q.lastError().text()
config.db.rollback()
else:
config.db.commit()
return err
class QSqlUpgradeManager(QObject):
'''
Automatically upgrades the database schema based on defined @QSqlDBObject
and their corresponding @QSqlDBProperty decorators. Also automatically
performs data and schema upgrades which are defined by classes which
implement the QSqlDataUpgrade and QSqlSchemaUpgrade abstract classes.
'''
_DBObjects = {}
_schemaUpgrades = []
_dataUpgrades = []
@staticmethod
def RegisterDBObject(name: str, schema: QSqlRecord):
'''
Adds a QSqlDBObject to the upgrade manager. This function is handled by the
QSqlDBObject decorator so you shouldn't have to worry about calling it.
'''
QSqlUpgradeManager._DBObjects[name] = schema
@staticmethod
def RegisterSchemaUpgradeOperation(upgrade: Callable[[QSqlDatabase], None]):
'''
Adds a schema upgrade operation to the upgrade manager. This function is
handled by the QtSqlSchemaUpgrade class so you
shouldn't have to worry about calling it.
'''
QSqlUpgradeManager._schemaUpgrades.append(upgrade)
@staticmethod
def RegisterDataUpgradeOperation(upgrade: Callable[[QSqlDatabase], None]):
"""
Adds a data upgrade operation to the upgrade manager. This function is
handled by the QtSqlDataUpgrade class so you shouldn't have to worry
about calling it.
"""
QSqlUpgradeManager._dataUpgrades.append(upgrade)
def __init__(self, db = QSqlDatabase(), dangerousMode = False):
'''
Constructor: Creates a QSqlUpgradeManager with a connection to db.
If db is invalid the default db connection is used.
If dangerousMode is set to True, then any redundant columns and tables
in the database will be deleted.
'''
self._db = db
self._query = QSqlQuery(db)
self._dangerousMode = dangerousMode
# TODO: Perform schema upgrades first
self._handleDBObjects()
# TODO: Perform data upgrades
def _handleSchemaUpgrades(self):
"""
Runs all new schema upgrades
"""
def _handleDataUpgrades(self):
"""
Runs all new data upgrades
"""
def _handleDBObjects(self):
"""
Compares the existing tables in the database with the registered
objects and adds tables and columns for any changes.
If dangerousMode is set then redundant tables and columns will be
deleted
"""
existingDBModel = self._constructDBModel()
newModel = QSqlUpgradeManager._DBObjects
existingDBTables = set(existingDBModel.keys())
newTables = set(newModel.keys())
# Create new tables
for tableName in newTables.difference(existingDBTables):
schema = newModel[tableName]
self._query.CreateTable(tableName, schema)
qDebug("Created new table " + tableName)
# Update tables
for tableName in existingDBTables.intersection(newTables):
existingSchema = existingDBModel[tableName]
newSchema = newModel[tableName]
if existingSchema != newSchema:
self._handleNewColumns(tableName, existingSchema, newSchema)
# Remove any redundant tables if dangerousMode is on
if self._dangerousMode:
for tableName in existingDBTables.difference(newTables):
self._query.DropTable(tableName)
qDebug("Dropped table " + tableName)
def _handleNewColumns(self, tableName:str, existingSchema: QSqlRecord,
newSchema: QSqlRecord):
"""
Compares the existing schema with the new schema to work out what
changes need to be made.
Note: To modify a table column or insert a new column between existing
columns (as opposed to just appending at the end), a schema upgrade
routine will need to be written.
"""
i = 0
while i < min(existingSchema.count(), newSchema.count()):
if existingSchema.field(i) != newSchema.field(i):
raise UpgradeException("New schema cannot modify field " +
"properties or ordering without a schema" +
" upgrade routine.")
i += 1
if newSchema.count() > existingSchema.count():
while i < newSchema.count():
self._query.AlterTableAddColumn(tableName, newSchema.field(i))
i += 1
elif newSchema.count() < existingSchema.count() and self._dangerousMode:
while i < existingSchema.count():
self._query.AlterTableDropColumn(tableName, existingSchema.field(i))
i += 1
def _constructDBModel(self) -> Mapping[str, QSqlRecord]:
"""
Constructs the existing data model of the database
"""
existingDBModel = {}
for tableName in self._db.tables():
existingDBModel[tableName] = self._db.record(tableName)
return existingDBModel
def createDB():
db = QSqlDatabase.addDatabase('QSQLITE')
db.setDatabaseName('./db/database.db')
if not db.open():
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_("insert into people values(1, 'zhangsan1', 'BeiJing')")
query.exec_("insert into people values(2, 'lisi1', 'TianJing')")
query.exec_("insert into people values(3, 'wangwu1', 'HenNan')")
query.exec_("insert into people values(4, 'lisi2', 'HeBei')")
query.exec_("insert into people values(5, 'wangwu2', 'shanghai')")
return True
def isValid(self, table, rowName, value):
sql = "SELECT " + rowName + " FROM " + table + " WHERE " + rowName + " = :value";
query = QSqlQuery();
query.prepare(sql);
query.bindValue(":value", value);
query.exec_();
query.next();
return value == query.value(0);
def query(self, sql): query = QSqlQuery(sql);
def close(self):
