def initializeTables(path, db, driver): # map python datatypes to sql datatypes type_map = {} if driver == 'QSQLITE': type_map = {int: 'INTEGER', float: 'REAL', str: 'TEXT'} elif driver == 'QMYSQL': type_map = {int: 'INT', float: 'FLOAT', str: 'TEXT'} # metadata # get the field names from the metadata file in this directory fields = ['id ' + type_map[int], 'run_id ' + type_map[int]] f_list = glob.glob(os.path.join(path, '*.metadata')) for f in f_list: # open the metadata file with open(f, 'rU') as src: for line in src: # skip empty lines if line == '\n': pass elif 'GROUP\t' not in line: data = line.split('\t') data = [x for x in data if len(x) > 1] for d in data: l, r = guts.evaluateEq(d) r = guts.smartEval(r) fields.append(str(str(l) + ' ' + type_map[type(r)])) fields = list(set(fields)) field_str = ','.join(fields) q = QtSql.QSqlQuery(db) q.exec_(str('create table if not exists metadata(' + field_str + ');')) # groupdata # group fields and datatypes are going to be more restricted q = QtSql.QSqlQuery(db) q.exec_( str('create table if not exists groupdata(' + 'id ' + type_map[int] + ',run_id ' + type_map[int] + ',group ' + type_map[str] + ',units ' + type_map[int] + ',activation_type ' + type_map[str] + ',error_computation_type ' + type_map[str] + ');')) # errordata q = QtSql.QSqlQuery(db) q.exec_( str('create table if not exists errordata(' + 'id ' + type_map[int] + ',run_id ' + type_map[int] + ',run_trial ' + type_map[int] + ',trial ' + type_map[int] + ',error ' + type_map[float] + ');')) # activationdata # noisedata q = QtSql.QSqlQuery(db) q.exec_( str('create table if not exists noisedata(' + 'id ' + type_map[int] + ',run_id ' + type_map[int] + ',noise_type ' + type_map[str] + ',noise_object ' + type_map[str] + ',noise_amount ' + type_map[float] + ');'))
def initializeTestTables(path, db, driver): # map python datatypes to sql datatypes type_map = {} if driver == 'QSQLITE': type_map = {int: 'INTEGER', float: 'REAL', str: 'TEXT'} elif driver == 'QMYSQL': type_map = {int: 'INT', float: 'FLOAT', str: 'TEXT'} # testdata fields = ['run_id ' + type_map[int], 'run_trial ' + type_map[int]] if os.path.isfile(os.path.join(path, 'test_headers')): with open(os.path.join(path, 'test_headers'), 'rU') as src: for line in src: # skip empty lines if line == '\n': pass else: fields.append(line) field_str = ','.join(fields) # we want to build a table for each test unique_test_names = [] f_list = glob.glob(os.path.join(path, '*.test')) for f in f_list: trash, f = os.path.split(f) f_split = f.split('_') test_name = f_split[0] if test_name not in unique_test_names: q = QtSql.QSqlQuery(db) q.exec_( str('create table if not exists ' + test_name + '(' + field_str + ');')) unique_test_names.append(test_name) split_fields = [x.split() for x in fields] return [x for x, y in split_fields] # returns only field names
def testTableCreationAndDestruction(self): #Test table creation and destruction query = QtSql.QSqlQuery() query.exec_( "CREATE TABLE dummy(id int primary key, dummyfield varchar(20))") query.exec_("DROP TABLE dummy") query.clear()
def main(): db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("allsortsDB.db") if not db.open(): print "Cannot establish a databse connection" sys.exit(1) db.transaction() q = QtSql.QSqlQuery() q.exec_("drop table if exists Cars") check_error(q, db) q.exec_("create table Cars(Id Int, Name text, Price int)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(1,'Audi ',52642)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(2,'Mercedes ',57127)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(3,'Skoda ',9000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(4,'Volvo ',29000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(5,'Bentley ' ,350000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(6,'Citroen ',21000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(7,'Hummer ',41400)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(8,'Volkswagen ',21600)") check_error(q, db) db.commit() db.close()
def addRecordTags(self, tagId, recordIds): if not recordIds: return False # Remove records that already have this tag currentIndex = self.index(0, enum.kTags_TagId) match = self.match(currentIndex, QtCore.Qt.DisplayRole, tagId, 1, QtCore.Qt.MatchExactly) if match: existingRecordsForTag = self.index(match[0].row(), enum.kTags_RecordIds).data() recordIds = set(recordIds) - existingRecordsForTag query = QtSql.QSqlQuery() query.prepare(""" INSERT INTO recordtags (recordid, tagid) VALUES (?, ?) """) query.addBindValue(list(recordIds)) query.addBindValue([tagId] * len(recordIds)) if not query.execBatch(): raise Exception(query.lastError().text()) self.tagsChanged.emit() return self.select()
def __init__(self, files, parent=None): super(ImportModel, self).__init__(parent=parent) self._headers = [] self._root = TreeItem() self._checksums = [] self._checksumsSaved = None self.__currentTimestamp = None # Import all record checksums query = QtSql.QSqlQuery(""" SELECT checksum FROM records r INNER JOIN accounts a ON a.id=r.accountid AND a.userid=%d """ % db.userId) if query.lastError().isValid(): raise Exception(query.lastError().text()) while query.next(): self._checksums.append(query.value(0)) self._checksumsSaved = self._checksums[:] for item in self.readFiles(files): self._root.appendChild(item) self._root.setRecordsImported(self._checksums) self._root.setDuplicateRecords() self._numColumns = self._root.maxColumns() self._headers = range(self._numColumns)
def delete_btn_clicked(self): query = QtSql.QSqlQuery() inds = self.tableView.selectionModel().selectedRows() print(inds) if inds: num_records = len(inds) ret = QMessageBox.critical(self.mem_win, "Confirm Delete", "Are you sure you want to delete " + str(num_records) + " records? Please note all related lifts performed by this member will also be deleted. This action cannot be undone.", QMessageBox.Ok|QMessageBox.Cancel) if ret == QMessageBox.Cancel: return elif ret == QMessageBox.Ok: progress = QProgressDialog("Deleting Records...", "Abort Delete", 0, num_records, self.mem_win) counter = 0 for ind in inds: progress.setValue(counter) if progress.wasCanceled(): QMessageBox.information(self.mem_win, "Operation Cancelled", str(counter) + " records imported!", QMessageBox.Ok) return index = ind.row() db_id = self.data[index][0] print(db_id) tmp_mem = self.session.query(Member).filter(Member.id == db_id).first() print(tmp_mem) try: self.session.delete(tmp_mem) self.session.commit() except: self.session.rollback() counter += 1 progress.setValue(num_records) self.refresh_table() else: QMessageBox.critical(self.mem_win, "No selection", "Please make a select at least one row to delete!", QMessageBox.Ok)
def deleteRecord(self): #print "delete REcord" index = self.view.currentIndex() if not index.isValid(): return #QtSql.QSqlDatabase.database().transaction() record = self.model.record(index.row()) id = int(record.value(ID)) table = self.model.tableName() query = QtSql.QSqlQuery() if table == "actions": query.exec_("SELECT COUNT(*) FROM logs " "WHERE actionid = {}".format(id)) elif table == "categories": query.exec_("SELECT COUNT(*) FROM assets " "WHERE categoryid = {}".format(id)) count = 0 if query.next(): count = int(query.value(0)) if count: QtGui.QMessageBox.information( self, "Delete {}".format(table), "Cannot delete {}<br>" "from the {} table because it is used by " "{} records".format(record.value(NAME), table, count)) #QtSql.QSqlDatabase.database().rollback() return self.model.removeRow(index.row()) self.model.submitAll()
def getRecordCount(db, table): q = QtSql.QSqlQuery(db) q.exec_(str('select COUNT(*) from ' + table)) if q.next(): return str(q.value(0)) else: return '0'
def populateDates(self): """ Set date fields to min and max values """ query = QtSql.QSqlQuery(""" SELECT MIN(r.date), MAX(r.date), MAX(r.insertdate) FROM records r LEFT JOIN accounts a ON a.id=r.accountid LEFT JOIN accountshare acs ON acs.accountid = r.accountid WHERE (a.userid=%(userid)s OR acs.userid=%(userid)s) """ % {'userid': db.userId}) if query.lastError().isValid(): QtGui.QMessageBox.critical(self, 'Database Error', query.lastError().text(), QtGui.QMessageBox.Ok) return if query.next(): startDate = query.value(0) endDate = query.value(1) maxInsertDateTime = query.value(2) self.startDateEdit.setDateRange(startDate, endDate) self.endDateEdit.setDateRange(startDate, endDate) self.maxInsertDate = maxInsertDateTime
def deleteAccount(self): for index in self.view.selectionModel().selectedRows(): accountTypeId = self.model.index( index.row(), enum.kAccountType__AccountTypeId).data() query = QtSql.QSqlQuery( 'SELECT COUNT(*) FROM records WHERE accounttypeid=%s' % accountTypeId) query.next() recordCount = query.value(0) if recordCount: QtGui.QMessageBox.critical( self, 'Account Delete ', 'Cannot delete account, %d records exist for this account' % recordCount) return if index.isValid(): self.model.removeRows(index.row(), 1, QtCore.QModelIndex()) self.view.clearSelection() self.enableCommit(True)
def __init__(self, in_fileName, in_connectionName="default"): self.fileName = in_fileName self.connectionName = in_connectionName self.SQL_connection = QtSql.QSqlDatabase.addDatabase( "QSQLITE", self.connectionName) self.SQL_connection.setDatabaseName(self.fileName) if self.SQL_connection.open(): self.query_create = QtSql.QSqlQuery(self.SQL_connection) self.query_select = QtSql.QSqlQuery(self.SQL_connection) self.query_insert = QtSql.QSqlQuery(self.SQL_connection) self.query_delete = QtSql.QSqlQuery(self.SQL_connection) self.query_temp = QtSql.QSqlQuery(self.SQL_connection) else: self.error = self.SQL_connection.lastError()
def _on_join(self, c, e): try: ch = e.target nick = e.source.nick if nick == c.get_nickname(): self.channels[ch] = Channel() self.connection.send_raw("NAMES" + (ch)) #self.connection.send_raw("PRIVMSG %s :%s" % ("#aeolus", "yo!")) elif "aeolus" in ch : #print nick,"has joined", ch query = QtSql.QSqlQuery(self.db) query.prepare("SELECT faction, IFNULL(dominant,-1) FROM galacticwar.accounts LEFT join galacticwar.domination on galacticwar.accounts.faction = galacticwar.domination.slave WHERE galacticwar.accounts.uid = (SELECT id FROM faf_lobby.login WHERE login = ? )") query.addBindValue(nick) query.exec_() if query.size() > 0: query.first() if int(query.value(1)) != -1: faction = int(query.value(1)) else: faction = int(query.value(0)) if faction == 0 : channel = "#UEF" elif faction == 1 : channel = "#Aeon" elif faction == 2 : channel = "#Cybran" elif faction == 3 : channel = "#Seraphim" self.connection.privmsg('chanserv', 'INVITE %s %s' % (channel, nick)) self.channels[ch].add_user(nick) except: pass
def main(): db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("test.db") if not db.open(): print "cannot establish a database connection" sys.exit(1) db.transaction() q = QtSql.QSqlQuery() q.exec_("DROP TABLE IF EXISTS Cars") check_error(q, db) q.exec_("CREATE TABLE Cars(Id INT," "Name TEXT, Price INT)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(1,'Audi',52642)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(2,'Mercedes',57127)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(3,'Skoda',9000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(4,'Volvo',29000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(5,'Bentley',350000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(6,'Citroen',21000)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(7,'Hummer',41400)") check_error(q, db) q.exec_("INSERT INTO Cars VALUES(8,'Volkswagen',21600)") check_error(q, db) db.commit() db.close()
def populateDatabase(): '''Create 'favorites' table of favorite food''' query = QtSql.QSqlQuery() if not query.exec_("""CREATE TABLE favorites ( id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL, category VARCHAR(40) NOT NULL, number INTEGER NOT NULL, shortdesc VARCHAR(20) NOT NULL, longdesc VARCHAR(80))"""): print "Failed to create table" return False print "Populating table..." categories = ("Apples", "Chocolate chip cookies", "Favra beans") numbers = (1, 2, 3) shortDescs = ("Crispy", "Yummy", "Clarice?") longDescs = ("Healthy and tasty", "Never not good...", "") query.prepare( """INSERT INTO favorites (category, number, shortdesc, longdesc) VALUES (:category, :number, :shortdesc, :longdesc)""") for category, number, shortDesc, longDesc in zip(categories, numbers, shortDescs, longDescs): print category, number, shortDesc, longDesc query.bindValue(":category", category) query.bindValue(":number", number) query.bindValue(":shortdesc", shortDesc) query.bindValue(":longdesc", longDesc) if not query.exec_(): print "Failed to populate table" return False QtGui.QApplication.processEvents() #is this needed? return True
def createTourney(self, name, player, minPlayers, maxPlayers): print "we do this" query = QtSql.QSqlQuery(self.db) queryStr = ("INSERT INTO hopin_tournament (`host`) VALUE ( %i )" % player.getId()) query.exec_(queryStr) uuid = query.lastInsertId() uuid = 1 tourney = tournament(uuid, name, player, minPlayers, maxPlayers, self) self.tourney.append(tourney) jsonToSend = {} jsonToSend["command"] = "social" jsonToSend["autojoin"] = re.sub(r'\W+', '_', name) player.getLobbyThread().sendJSON(jsonToSend) for p in self.parent.players.getAllPlayers(): print p jsonToSend = {} jsonToSend["command"] = "tournament_info" jsonToSend["state"] = tourney.getState() jsonToSend["uid"] = tourney.getid() jsonToSend["title"] = tourney.getName() jsonToSend["host"] = player.getLogin() jsonToSend["min_players"] = tourney.minPlayers jsonToSend["max_players"] = tourney.maxPlayers print jsonToSend p.getLobbyThread().sendJSON(jsonToSend)
def __init__(self, model, strQuery, params={}): self._model = model self._strQuery = strQuery self._params = params self._query = QtSql.QSqlQuery() self._prepare()
def createUuid(self, playerId): query = QtSql.QSqlQuery(self.db) queryStr = ("INSERT INTO game_stats (`host`) VALUE ( %i )" % playerId) query.exec_(queryStr) uuid = query.lastInsertId() return uuid
def getColumns(db, varDict, usrVars, queryString): columns = [] for v in usrVars: # make an empty list in 'columns' that will become this variable's numpy array columns.append([]) q = QtSql.QSqlQuery(db) q.setForwardOnly(True) q.exec_(queryString) while q.next(): for i, v in enumerate(usrVars): if varDict[v][0] == "<type 'int'>": columns[i].append(int(q.value(i))) elif varDict[v][0] == "<type 'float'>": columns[i].append(float(q.value(i))) elif varDict[v][0] == "<type 'unicode'>": columns[i].append(str(q.value(i))) # convert columns to numpy arrays, paying attention to type for i, v in enumerate(usrVars): if varDict[v][0] == "<type 'int'>": columns[i] = np.array(columns[i], dtype=np.int32) elif varDict[v][0] == "<type 'float'>": columns[i] = np.array(columns[i], dtype=np.float64) elif varDict[v][0] == "<type 'unicode'>": columns[i] = np.array(columns[i], dtype=np.object) return columns
def printTable(tableName): '''Print out all contents of a table (header names, and each row)''' #print "Printing contents of table '{}'".format(tableName) query = QtSql.QSqlQuery() if not query.exec_("""SELECT * FROM {}""".format(tableName)): print "Failure to execute SELECT in displayTable" return False record = query.record() #display fields numFields = record.count( ) #; print "There are {} fields".format(numFields); fieldNames = () print "\nFields:" for fieldNum in range(numFields): fieldNames = fieldNames + (record.fieldName(fieldNum), ) print fieldNames #display values print "Values:" while query.next(): values = [] for field in range(numFields): values.append(query.value(field)) print values QtGui.QApplication.processEvents() return True
def switchAccount(self, index): """ Account changed by user, populate all fields """ if index == -1: return model = self.accountCombo.model() self.sortCode.setText( model.index(index, enum.kAccounts_SortCode).data()) self.accountNo.setText( model.index(index, enum.kAccounts_AccountNo).data()) realAccountName = model.index(index, enum.kAccounts_AccountTypeId).data() self.accountType.setCurrentIndex( self.accountType.findText(realAccountName)) # Set the filter on accountshare table accountId = model.index(index, enum.kAccounts_Id).data() self.accountShareView.model().changedAccount(accountId) self._allowAccountShareEdit = True query = QtSql.QSqlQuery( 'SELECT count(*) FROM records WHERE accountid=%s' % accountId) query.next() self._accountRecordCount = query.value(0) self.setButtonsEnabled()
def remRow(self): index = self.view.currentIndex() #how to upull row from index? if not index.isValid(): return rowNum = index.row() #print "Trying to remove row ", rowNum QtSql.QSqlDatabase.database().transaction() #begin transaction record = self.model.record(index.row()) rowID = int(record.value(ID)) #dialog to ask user if they are sure msg = "<font color=red>Delete</font> <b>row {}</b><br>from {}?".format( rowNum, self.tableName) if (QtGui.QMessageBox.question( self, "Delete row", msg, QtGui.QMessageBox.Yes | QtGui.QMessageBox.No) == QtGui.QMessageBox.No): QtSql.QSqlDatabase.database().rollback() return #If user has verified they want row deleted, do it query = QtSql.QSqlQuery() query.exec_("DELETE FROM {} WHERE id = {}".format( self.tableName, rowID)) self.model.removeRow(index.row()) self.model.submitAll() #commits change to database QtSql.QSqlDatabase.database().commit() #commit transaction
def getFields(db, table): fieldList = [] q = QtSql.QSqlQuery(db) q.exec_('select * from ' + table + ' LIMIT 0,0') rec = q.record() for i in range(rec.count()): fieldList.append((rec.fieldName(i), str(rec.field(i).type()))) return fieldList
def setDesc(self, desc): query = QtSql.QSqlQuery(self.db) query.prepare( "UPDATE game_featuredMods SET description = ? WHERE gamemod = ?") query.addBindValue(desc) query.addBindValue(self.gameTypeName) query.exec_() self.desc = desc
def updateBalance(self, amount, uid): try: query = QtSql.QSqlQuery(self.db) query.prepare("UPDATE faf_lobby.bet SET amount = ? WHERE userid = ?") query.addBindValue(amount) query.addBindValue(uid) query.exec_() except: pass
def clear_btn_clicked(self): query = QtSql.QSqlQuery() query.exec_("DROP TABLE IF EXISTS simpsons") sql = """CREATE TABLE simpsons ( simpsons_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20) )""" query.exec_(sql) self.refresh_table()
def send_dictionary(self): words = list() query = QtSql.QSqlQuery(self.db) query.exec_(GET_WORDS_QUERY) while query.next(): words.append(str(query.value(0))) #print(words[0]) self.__send_dictionary__.emit(words)
def delete_btn_clicked(self): query = QtSql.QSqlQuery() db_id = self.get_current_id() sql = "DELETE FROM simpsons WHERE simpsons_id = '%d'" % (db_id) try: query.exec_(sql) self.db.commit() except: self.db.rollback() self.refresh_table()
def createModel(self): self.model = QtSql.QSqlQueryModel() query = QtSql.QSqlQuery() query.exec_("Select * from Cars") self.model.setQuery(query) self.model.removeColumn(0) self.model.setHeaderData(0, QtCore.Qt.Horizontal, "Name") self.model.setHeaderData(1, QtCore.Qt.Horizontal, "Price")
def createUuidGW(self, playerId, planetuid): query = QtSql.QSqlQuery(self.db) query.prepare( "INSERT INTO galacticwar.game_stats (`host`, `planetuid`) VALUE (?,?)" ) query.addBindValue(playerId) query.addBindValue(planetuid) if not query.exec_(): self.log.error(query.lastError()) uuid = query.lastInsertId() return uuid