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
