def loadDefaultData(self, table, data): query = QSqlQuery() insertList= "', '".join(data.tableCols) insertList= "'" + insertList + "'" colCount= len(data.tableCols) quest= "" for i in range(0, colCount): quest = quest + "?," quest= quest[:-1] sql= "INSERT INTO " + table + "(" + insertList + ") VALUES (" + quest + ");" # self.updateStatus(sql) status= query.prepare(sql) if status == False: self.updateStatus("Could not prepare material property database table " + table) self.updateStatus("Database error message: " + query.lastError().text()) for row in data.propDict: for prop in data.tableCols: propval= data.propDict[row][prop] if data.tableColSQLTypes[prop] == 'TEXT': query.addBindValue(QVariant(QString(propval))) # self.updateStatus("Setting TEXT property " + prop + " to value " + str(propval) + " in row " + str(row)) elif data.tableColSQLTypes[prop] == 'real': if (propval == '-'): propreal= -999.9 else: propreal= float(propval) query.addBindValue(QVariant(propreal)) # self.updateStatus("Setting real property " + prop + " to value " + str(propreal) + " in row " + str(row)) status= query.exec_() if status == False: self.updateStatus("Could not load property database table " + table + " with " + str(row)) self.updateStatus("Database error message: " + query.lastError().text())
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 get_info(self, image_date): query = QSqlQuery(self.db) query.setForwardOnly(True) query.prepare('SELECT copyright_info FROM copyright WHERE image_date = ?;') query.addBindValue(image_date) if not query.exec_(): self.error.emit('Error getting copyright info', query.lastError().text()) return '' query.first() copyright_info = query.value(0).toString() return copyright_info
def CadastrarCliente(cliente): conn = ConexaoSQL db = conn.getConexao() db.open() query = QSqlQuery() query.prepare( "INSERT INTO Cliente(Nome, CPF, Endereco, Email, Telefone) " "VALUES (?, ?, ?, ?, ?)") query.addBindValue(cliente.Nome) query.addBindValue(cliente.CPF) query.addBindValue(cliente.Endereco) query.addBindValue(cliente.Email) query.addBindValue(cliente.Telefone) query.exec_() db.commit()
def recuperar_datos(self): query = QSqlQuery() sql = "select nombre,telefono,celular,direccion,CP,RFC from clientes where cliente_id=?" query.prepare(sql) query.addBindValue(self.id) query.exec_() if query.size() < 1: print "Error en el cliente no existe?" return g = lambda x:query.value(x).toString() query.next() self.nombre = g(0) #self.apellidos = g(1) self.telefono = g(1) self.celular = g(2) self.direccion = g(3) self.CP = g(4) self.RFC = g(5)
def execute(self, *args): """ @type query: str @type params: list """ cursor = self.connection.cursor() query = QSqlQuery() query.prepare(args[0]) if len(args) == 1: text = args[0] elif len(args) == 2: text = args[0] % args[1] for value in args[1]: query.addBindValue(value); query.exec_() # cursor.execute(args[0], args[1]) statusWindow = self.applicationWindow.mainWindow.txtStatus statusWindow.append("%s;" % text) return query
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 CadastrarAluguel(aluguel): conn = ConexaoSQL db = conn.getConexao() db.open() query = QSqlQuery() query.prepare("UPDATE Veiculo SET Alugado = 'Sim' WHERE CodigoVeic = "+aluguel.CodigoVeic) query.exec_() db.commit() query.prepare("INSERT INTO Aluguel(DataAluguel, DataPrazo, DataDevolucao, ValorAluguel, " "ValorMulta, KmEntrada, KmSaida, CodigoCli, CodigoVeic) " "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)") query.addBindValue(aluguel.DataAluguel) query.addBindValue(aluguel.DataPrazo) query.addBindValue(aluguel.DataDevolucao) query.addBindValue(aluguel.ValorAluguel) query.addBindValue(aluguel.ValorMulta) query.addBindValue(aluguel.KmEntrada) query.addBindValue(aluguel.KmSaida) query.addBindValue(aluguel.CodigoCli) query.addBindValue(aluguel.CodigoVeic) query.exec_() db.commit()
class LandmarkToolbox(QDockWidget, Ui_DockWidget): landmarkMessage = pyqtSignal(unicode, int) def __init__(self, iface): QDockWidget.__init__(self) self.setupUi(self) self.iface = iface self.canvas = self.iface.mapCanvas() self.geoCrs = QgsCoordinateReferenceSystem(4326) self.btnAddPhoto.setIcon(QIcon(':/icons/camera.svg')) self.txtPhotoComment.setPlaceholderText(self.tr('Comment')) self.cmbLayers.setFilters(QgsMapLayerProxyModel.VectorLayer) self.db = QSqlDatabase.addDatabase('QPSQL') self.landmarkId = None self.photoId = None self.highlight = None self.model = QStandardItemModel() self.lstPhotos.setModel(self.model) self.btnUpdateLandmark.clicked.connect(self.saveLandmark) self.btnDeleteLandmark.clicked.connect(self.deleteLandmark) self.btnAddPhoto.clicked.connect(self.addPhoto) self.btnUpdatePhoto.clicked.connect(self.savePhoto) self.btnDeletePhoto.clicked.connect(self.removePhoto) self.lstPhotos.selectionModel().selectionChanged.connect( self.photoSelected) self.lstPhotos.doubleClicked.connect(self.showPhoto) self._enableOrDisableButtons() self.ToggleToolbox() def ToggleToolbox(self): layer_list = self.canvas.layers() if not layer_list: self.hide() return elif len(layer_list) == 0: self.hide() return self.setVisible(not self.isVisible()) def getLandmarkID(self): #ランドマークがなかった時の処理 return self.landmarkId def openDatabase(self): if self.db.isValid(): settings = QSettings('MatsueGkukan', 'Gkukandb') dbHostName = settings.value('hostname') dbDatabaseName = settings.value('databasename') dbUserName = settings.value('username') dbPassword = settings.value('dbpassword') self.db.setHostName(dbHostName) self.db.setDatabaseName(dbDatabaseName) self.db.setUserName(dbUserName) self.db.setPassword(dbPassword) if not self.db.open(): self.GKukanMusiumMessage.emit( self.tr('Can not open GKukanMusium database'), QgsMessageBar.WARNING) return False self.query = QSqlQuery(self.db) return True else: settings = QSettings('MatsueGkukan', 'Gkukandb') dbHostName = settings.value('hostname') dbDatabaseName = settings.value('databasename') dbUserName = settings.value('username') dbPassword = settings.value('dbpassword') self.db.removeDatabase(dbDatabaseName) del self.db self.db = None self.db = QSqlDatabase.addDatabase('QPSQL') self.db.setHostName(dbHostName) self.db.setDatabaseName(dbDatabaseName) self.db.setUserName(dbUserName) self.db.setPassword(dbPassword) if not self.db.open(): self.GKukanMusiumMessage.emit( self.tr('Can not open GKukanMusium database'), QgsMessageBar.WARNING) return False self.query = QSqlQuery(self.db) return True return False def GetPhotoFolderPath(self): if not self.openDatabase(): return False if self.query.exec_(u'select * from m_folder'): self.query.first() self.folderpath = self.query.value(2) self.thumbpath = os.path.join(self.folderpath, 'thumb') ret = self.folderpath else: ret = '' self.db.close() return ret def landmarkSelected(self, infos): self.info = infos[0] ft = self.info[1] self.landmarkId = ft['id'] self.leLandmarkTitle.setText(ft['title'] if ft['title'] else '') self.spnLandmarkClass.setValue( ft['icon_type'] if ft['icon_type'] != None else 0) self._highlightLandmark() self.populatePhotos() self._enableOrDisableButtons() def populatePhotos(self, index=0): self.model.clear() QApplication.setOverrideCursor(Qt.WaitCursor) # photos is a list of tuples (id, title, imagepath) photos = self._photosOfLandmark() for i in photos: tp = os.path.join(self.thumbpath, str(i[0])) + '.png' img = self.thumbnailPhoto(i[2], tp) icon = QIcon(img) title = i[1] if i[1] else '<unnamed photo> %s' % i[0] item = QStandardItem(title) item.setIcon(icon) item.setData(i[0], Qt.UserRole) item.setToolTip(title) self.model.appendRow(item) lastIdx = self.model.indexFromItem(item) idx = self.model.createIndex(0, 0) if self.model.rowCount() > 0: if index == -1: idx = lastIdx elif index > 0: idx = self.model.createIndex(index, 0) self.lstPhotos.selectionModel().select(idx, QItemSelectionModel.Select) else: self._clearForm() QApplication.restoreOverrideCursor() def thumbnailPhoto(self, imagePath, tp): if os.path.exists(tp): return QPixmap(tp) else: if os.path.exists(os.path.dirname(tp)) == False: os.mkdir(os.path.dirname(tp)) pixmap = QPixmap(imagePath).scaled(800, 600).scaled( 75, 50, Qt.IgnoreAspectRatio, Qt.SmoothTransformation) a = pixmap.save(tp, 'PNG') return pixmap def showPhoto(self, index): if not self.openDatabase(): return item = self.lstPhotos.model().itemFromIndex(index) self.query.prepare( 'SELECT filename ,ST_X(geom),ST_Y(geom),geom FROM t_photo WHERE p_id=?;' ) self.query.addBindValue(item.data(Qt.UserRole)) if self.query.exec_(): self.query.first() path = os.path.join(self.folderpath, self.query.value(0)) if self.query.value( 3) <> '010100000000000000000000000000000000000000': lon = self.query.value(1) lat = self.query.value(2) point = self._transformPoint(QgsPoint(lon, lat)) self.canvas.freeze(True) self.canvas.setCenter(point) self.canvas.freeze(False) self.canvas.refresh() dlg = ViewPhotoDialog(path) dlg.exec_() else: a = self.query.lastError().text() self.db.close() def saveLandmark(self): layer = self.info[0] fid = self.info[1].id() idxTitle = layer.fieldNameIndex('title') idxClassification = layer.fieldNameIndex('icon_type') attrs = {idxTitle: self.leLandmarkTitle.text(),\ idxClassification: self.spnLandmarkClass.value() } layer.dataProvider().changeAttributeValues({fid: attrs}) layer.reload() layer.triggerRepaint() self.landmarkMessage.emit(self.tr('Landmark updated.'), QgsMessageBar.INFO) def deleteLandmark(self): layer = self.info[0] fid = self.info[1].id() layer.dataProvider().deleteFeatures([fid]) layer.reload() layer.triggerRepaint() self._clearAllFields() self.landmarkMessage.emit(self.tr('Landmark deleted.'), QgsMessageBar.INFO) def addPhoto(self): if self.landmarkId is not None: settings = QSettings('MatsueGkukan', 'Gkukandb') lastDir = settings.value('lastPhotoDir', '.') fileName = QFileDialog.getOpenFileName(self, self.tr('Select photo'), lastDir, self._createFilter()) if fileName == '': return settings.setValue('lastPhotoDir', QFileInfo(fileName).absoluteDir().absolutePath()) projectPath = self.GetPhotoFolderPath() + os.sep photoPath = os.path.basename(fileName) photoDate = self._photoDate(fileName).toString('yyyy-MM-dd') if not self.openDatabase(): return self.query.prepare( 'INSERT INTO t_photo("cdate", "filename", "landmark_id",lon,lat,angle,geomtype,geom) VALUES(?, ?, ?,?,?,?,?,?);' ) self.query.addBindValue(photoDate) self.query.addBindValue(photoPath) self.query.addBindValue(self.landmarkId) self.query.addBindValue(0) self.query.addBindValue(0) self.query.addBindValue(0) self.query.addBindValue(0) self.query.addBindValue( '010100000000000000000000000000000000000000') if self.query.exec_(): self._copyPhotoToFolder(fileName, self.landmarkId) self.populatePhotos(-1) else: a = self.query.lastError().text() self.db.close() else: self.landmarkMessage.emit( self.tr('Select landmark before adding a photo.'), QgsMessageBar.WARNING) def savePhoto(self): if not self.openDatabase(): return self.query.prepare( 'UPDATE t_photo SET film_no=?, keywords=?, keyword1=?, keyword2=?, keyword3=?, notes=?, mdate=?, registrant=?, comment=?, reference=?, angle=? WHERE p_id=?;' ) self.query.addBindValue(self.lePhotoTitle.text()) self.query.addBindValue(self.leKeywords.text()) self.query.addBindValue(self.leKeyword1.text()) self.query.addBindValue(self.leKeyword2.text()) self.query.addBindValue(self.leKeyword3.text()) self.query.addBindValue(self.txtPhotoComment.toPlainText()) self.query.addBindValue( self.edPhotoDate.dateTime().toString('yyyy-MM-dd')) self.query.addBindValue(self.leRegistrant.text()) self.query.addBindValue(self.leComment.text()) self.query.addBindValue(self.lerRference.text()) self.query.addBindValue(self.spnPhotoAngle.value()) self.query.addBindValue(self.photoId) if self.query.exec_(): self.landmarkMessage.emit(self.tr('Photo updated.'), QgsMessageBar.INFO) self.populatePhotos(self.lstPhotos.currentIndex().row()) else: a = self.query.lastError().text() self.db.close() def removePhoto(self): if not self.openDatabase(): return self.query.prepare('DELETE FROM t_photo WHERE "p_id"=?;') self.query.addBindValue(self.photoId) if self.query.exec_(): self.db.close() self._removePhotofromFolder() self.populatePhotos() def photoSelected(self, current, previous): if not self.openDatabase(): return idx = current.indexes()[0] item = self.lstPhotos.model().itemFromIndex(idx) self.photoId = item.data(Qt.UserRole) self.query.prepare( 'SELECT film_no, filename, keywords, keyword1, keyword2, keyword3, notes, mdate, registrant, comment, reference, angle FROM t_photo WHERE p_id=?;' ) self.query.addBindValue(self.photoId) if self.query.exec_(): self.query.first() self.filename = self.query.value(1) self.lePhotoTitle.setText( self.query.value(0) if self.query.value(0) else '') self.txtPhotoComment.setPlainText( self.query.value(6) if self.query.value(6) else '') self.leKeywords.setText( self.query.value(2) if self.query.value(2) else '') self.leKeyword1.setText( self.query.value(3) if self.query.value(3) else '') self.leKeyword2.setText( self.query.value(4) if self.query.value(4) else '') self.leKeyword3.setText( self.query.value(5) if self.query.value(5) else '') self.leRegistrant.setText( self.query.value(8) if self.query.value(8) else '') self.leComment.setText( self.query.value(9) if self.query.value(9) else '') self.lerRference.setText( self.query.value(10) if self.query.value(10) else '') self.spnPhotoAngle.setValue( int(self.query.value(11)) if self.query.value(11) else 0) self.edPhotoDate.setDateTime( self.query.value(7) if self.query.value(7) else QDateTime. currentDateTime()) self._enableOrDisableButtons() self.db.close() def _photosOfLandmark(self): projectPath = self.GetPhotoFolderPath() if not self.openDatabase(): return photos = [] self.query.prepare( 'SELECT "p_id", "keywords", "filename" FROM t_photo WHERE "landmark_id"=? ORDER BY "p_id";' ) self.query.addBindValue(self.landmarkId) if self.query.exec_(): while self.query.next(): photos.append((self.query.value(0), self.query.value(1), os.path.join(projectPath, self.query.value(2)))) self.db.close() return photos def _createFilter(self): formats = '' for f in QImageReader.supportedImageFormats(): f = unicode(f) if f == 'svg': continue formats += '*.{} *.{} '.format(f.lower(), f.upper()) return self.tr('Image files (%s);;All files (*.*)' % formats[:-1]) def _clearForm(self): self.lePhotoTitle.clear() self.txtPhotoComment.clear() self.leKeyword1.clear() self.leKeyword2.clear() self.leKeyword3.clear() self.leRegistrant.clear() self.leComment.clear() self.lerRference.clear() self.photoId = None self.landmarkId = None def _enableOrDisableButtons(self): if self.landmarkId is None: self.btnAddPhoto.setEnabled(False) else: self.btnAddPhoto.setEnabled(True) if self.photoId is None: self.btnDeletePhoto.setEnabled(False) self.btnUpdatePhoto.setEnabled(False) else: self.btnDeletePhoto.setEnabled(True) self.btnUpdatePhoto.setEnabled(True) def _highlightLandmark(self): self._clearHighlight() self.highlight = QgsHighlight(self.canvas, self.info[1].geometry(), self.info[0]) settings = QSettings() color = QColor( settings.value('/Map/highlight/color', QGis.DEFAULT_HIGHLIGHT_COLOR.name())) alpha = settings.value('/Map/highlight/colorAlpha', QGis.DEFAULT_HIGHLIGHT_COLOR.alpha(), type=int) buffer = settings.value('/Map/highlight/buffer', QGis.DEFAULT_HIGHLIGHT_BUFFER_MM, type=float) minWidth = settings.value('/Map/highlight/minWidth', QGis.DEFAULT_HIGHLIGHT_MIN_WIDTH_MM, type=float) self.highlight.setColor(color) color.setAlpha(alpha) self.highlight.setFillColor(color) self.highlight.setBuffer(buffer) self.highlight.setMinWidth(minWidth) self.highlight.show() def _photoDate(self, path): with open(path, 'rb') as imgFile: tags = exifread.process_file(imgFile, details=False) if 'EXIF GPS GPSDate' in tags: return QDateTime.fromString(tags['EXIF GPS GPSDate'].values, 'yyyy:MM:dd') else: return QDateTime.currentDateTime() def _clearHighlight(self): if hasattr(self, 'highlight'): del self.highlight self.highlight = None def _clearAllFields(self): self.leLandmarkTitle.clear() self.spnLandmarkClass.clear() self._clearHighlight() self._clearForm() self.model.clear() def _copyPhotoToFolder(self, path, landmark_id): projectPath = self.GetPhotoFolderPath() dst = os.path.join(projectPath, os.path.basename(path)) shutil.copy2(path, dst) def _removePhotofromFolder(self, path, landmark_id): projectPath = self.GetPhotoFolderPath() dst = os.path.join(projectPath, path) os.remove(dst) def _transformPoint(self, pnt): crsDest = self.canvas.mapSettings().destinationCrs() xform = QgsCoordinateTransform(self.geoCrs, crsDest) p2 = xform.transform(pnt) return p2
class Query(object): """a more pythonic interface to QSqlQuery. We could instead use the python sqlite3 module but then we would either have to do more programming for the model/view tables, or we would have two connections to the same database. For selecting queries we fill a list with ALL records. Every record is a list of all fields. q.records[0][1] is record 0, field 1. For select, we also convert to python data types - as far as we need them""" dbhandle = None localServerName = m18ncE('kajongg name for local game server', 'Local Game') def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False): """we take a list of sql statements. Only the last one is allowed to be a select statement. Do prepared queries by passing a single query statement in cmdList and the parameters in args. If args is a list of lists, execute the prepared query for every sublist. If dbHandle is passed, use that for db access. Else if the default dbHandle (Query.dbhandle) is defined, use it.""" # pylint: disable=R0912 # pylint says too many branches silent |= not Debug.sql self.dbHandle = dbHandle or Query.dbhandle assert self.dbHandle preparedQuery = not isinstance(cmdList, list) and bool(args) self.query = QSqlQuery(self.dbHandle) self.msg = None self.records = [] if not isinstance(cmdList, list): cmdList = list([cmdList]) self.cmdList = cmdList for cmd in cmdList: retryCount = 0 while retryCount < 100: self.lastError = None if preparedQuery: self.query.prepare(cmd) if not isinstance(args[0], list): args = list([args]) for dataSet in args: if not silent: logDebug('%s %s' % (cmd, dataSet)) for value in dataSet: self.query.addBindValue(QVariant(value)) self.success = self.query.exec_() if not self.success: break else: if not silent: logDebug(cmd) self.success = self.query.exec_(cmd) if self.success or self.query.lastError().number() not in (5, 6): # 5: database locked, 6: table locked. Where can we get symbols for this? break time.sleep(0.1) retryCount += 1 if not self.success: self.lastError = unicode(self.query.lastError().text()) self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError) if mayFail: if not silent: logDebug(self.msg) else: logError(self.msg) return self.records = None self.fields = None if self.query.isSelect(): self.retrieveRecords() def rowcount(self): """how many rows were affected?""" return self.query.numRowsAffected() def retrieveRecords(self): """get all records from SQL into a python list""" record = self.query.record() self.fields = [record.field(x) for x in range(record.count())] self.records = [] while self.query.next(): self.records.append([self.__convertField(x) for x in range(record.count())]) def __convertField(self, idx): """convert a QSqlQuery field into a python value""" field = self.fields[idx] name = str(field.name()) valType = field.type() if valType == QVariant.String: value = unicode(self.query.value(idx).toString()) elif valType == QVariant.Double: value = self.query.value(idx).toDouble()[0] elif valType == QVariant.Int: value = unicode(self.query.value(idx).toString()) if '.' in value: # rule.limits is defined as integer in older versions # but we save floats anyway. Sqlite3 lets us do a lot # of illegal things... value = self.query.value(idx).toDouble()[0] else: value = self.query.value(idx).toInt()[0] elif valType == QVariant.UInt: value = self.query.value(idx).toUInt()[0] elif valType == QVariant.LongLong: value = self.query.value(idx).toLongLong()[0] elif valType == QVariant.ULongLong: value = self.query.value(idx).toULongLong()[0] elif valType == QVariant.Invalid: value = None else: raise Exception('Query: variant type %s not implemented for field %s ' % \ (QVariant.typeToName(valType), name)) return value @staticmethod def tableHasField(dbhandle, table, field): """does the table contain a column named field?""" query = QSqlQuery(dbhandle) query.exec_('select * from %s' % table) record = query.record() for idx in range(record.count()): if record.fieldName(idx) == field: return True schema = {} schema['player'] = """ id INTEGER PRIMARY KEY, name TEXT unique""" schema['game'] = """ id integer primary key, seed text, autoplay integer default 0, starttime text default current_timestamp, endtime text, ruleset integer references usedruleset(id), p0 integer constraint fk_p0 references player(id), p1 integer constraint fk_p1 references player(id), p2 integer constraint fk_p2 references player(id), p3 integer constraint fk_p3 references player(id)""" schema['score'] = """ game integer constraint fk_game references game(id), hand integer, data text, manualrules text, rotated integer, notrotated integer, player integer constraint fk_player references player(id), scoretime text, won integer, penalty integer default 0, prevailing text, wind text, points integer, payments integer, balance integer""" schema['ruleset'] = """ id integer primary key, name text unique, hash text, lastused text, description text""" schema['rule'] = """ ruleset integer, list integer, position integer, name text, definition text, points text, doubles text, limits text, parameter text, primary key(ruleset,list,position), unique (ruleset,name)""" schema['usedruleset'] = """ id integer primary key, name text, hash text, lastused text, description text""" schema['usedrule'] = """ ruleset integer, list integer, position integer, name text, definition text, points text, doubles integer, limits integer, parameter text, primary key(ruleset,list,position), unique (ruleset,name)""" schema['server'] = """ url text, lastname text, lasttime text, lastruleset integer, primary key(url)""" schema['passwords'] = """ url text, player integer, password text""" schema['general'] = """ ident text""" @staticmethod def createTable(dbhandle, table): """create a single table using the predefined schema""" if table not in dbhandle.driver().tables(QSql.Tables): Query("create table %s(%s)" % (table, Query.schema[table]), dbHandle=dbhandle) @staticmethod def createTables(dbhandle): """creates empty tables""" for table in ['player', 'game', 'score', 'ruleset', 'rule', 'usedruleset', 'usedrule']: Query.createTable(dbhandle, table) Query.createIndex(dbhandle, 'idxgame', 'score(game)') if InternalParameters.isServer: Query('ALTER TABLE player add password text', dbHandle=dbhandle) else: Query.createTable(dbhandle, 'passwords') Query.createTable(dbhandle, 'server') @staticmethod def createIndex(dbhandle, name, cmd): """only try to create it if it does not yet exist. Do not use create if not exists because we want debug output only if we really create the index""" if not Query("select 1 from sqlite_master where type='index' and name='%s'" % name, dbHandle=dbhandle, silent=True).records: Query("create index %s on %s" % (name, cmd), dbHandle=dbhandle) @staticmethod def cleanPlayerTable(dbhandle): """remove now unneeded columns host, password and make names unique""" playerCounts = IntDict() names = {} keep = {} for nameId, name in Query('select id,name from player', dbHandle=dbhandle).records: playerCounts[name] += 1 names[int(nameId)] = name for name, counter in defaultdict.items(playerCounts): nameIds = [x[0] for x in names.items() if x[1] == name] keepId = nameIds[0] keep[keepId] = name if counter > 1: for nameId in nameIds[1:]: Query('update score set player=%d where player=%d' % (keepId, nameId), dbHandle=dbhandle) Query('update game set p0=%d where p0=%d' % (keepId, nameId), dbHandle=dbhandle) Query('update game set p1=%d where p1=%d' % (keepId, nameId), dbHandle=dbhandle) Query('update game set p2=%d where p2=%d' % (keepId, nameId), dbHandle=dbhandle) Query('update game set p3=%d where p3=%d' % (keepId, nameId), dbHandle=dbhandle) Query('delete from player where id=%d' % nameId, dbHandle=dbhandle) Query('drop table player', dbHandle=dbhandle) Query.createTable(dbhandle, 'player') for nameId, name in keep.items(): Query('insert into player(id,name) values(?,?)', list([nameId, name]), dbHandle=dbhandle) @staticmethod def removeGameServer(dbhandle): """drops column server from table game. Sqlite3 cannot drop columns""" Query('create table gameback(%s)' % Query.schema['game'], dbHandle=dbhandle) Query('insert into gameback ' 'select id,seed,autoplay,starttime,endtime,ruleset,p0,p1,p2,p3 from game', dbHandle=dbhandle) Query('drop table game', dbHandle=dbhandle) Query('create table game(%s)' % Query.schema['game'], dbHandle=dbhandle) Query('insert into game ' 'select id,seed,autoplay,starttime,endtime,ruleset,p0,p1,p2,p3 from gameback', dbHandle=dbhandle) Query('drop table gameback', dbHandle=dbhandle) @staticmethod def upgradeDb(dbhandle): """upgrade any version to current schema""" # TODO: scan rulesets and usedrulesets for unfinished games # for regex. Warn before removing such rulesets and setting those # unfinished games to finished. Alternative is to downgrade kajongg. Query.createIndex(dbhandle, 'idxgame', 'score(game)') if not Query.tableHasField(dbhandle, 'game', 'autoplay'): Query('ALTER TABLE game add autoplay integer default 0', dbHandle=dbhandle) if not Query.tableHasField(dbhandle, 'score', 'penalty'): Query('ALTER TABLE score add penalty integer default 0', dbHandle=dbhandle) Query("UPDATE score SET penalty=1 WHERE manualrules LIKE " "'False Naming%' OR manualrules LIKE 'False Decl%'", dbHandle=dbhandle) if Query.tableHasField(dbhandle, 'player', 'host'): Query.cleanPlayerTable(dbhandle) if InternalParameters.isServer: if not Query.tableHasField(dbhandle, 'player', 'password'): Query('ALTER TABLE player add password text', dbHandle=dbhandle) else: Query.createTable(dbhandle, 'passwords') if not Query.tableHasField(dbhandle, 'server', 'lastruleset'): Query('alter table server add lastruleset integer', dbHandle=dbhandle) if Query.tableHasField(dbhandle, 'game', 'server'): Query.removeGameServer(dbhandle) if not Query.tableHasField(dbhandle, 'score', 'notrotated'): Query('ALTER TABLE score add notrotated integer default 0', dbHandle=dbhandle)
class Query(object): """a more pythonic interface to QSqlQuery. We could instead use the python sqlite3 module but then we would either have to do more programming for the model/view tables, or we would have two connections to the same database. For selecting queries we fill a list with ALL records. Every record is a list of all fields. q.records[0][1] is record 0, field 1. For select, we also convert to python data types - as far as we need them""" localServerName = m18ncE('kajongg name for local game server', 'Local Game') def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False): """we take a list of sql statements. Only the last one is allowed to be a select statement. Do prepared queries by passing a single query statement in cmdList and the parameters in args. If args is a list of lists, execute the prepared query for every sublist. If dbHandle is passed, use that for db access. Else if the default dbHandle (DBHandle.default) is defined, use it.""" # pylint: disable=R0912 # pylint says too many branches silent |= not Debug.sql self.dbHandle = dbHandle or DBHandle.default preparedQuery = not isinstance(cmdList, list) and bool(args) self.query = QSqlQuery(self.dbHandle) self.msg = None self.records = [] if not isinstance(cmdList, list): cmdList = list([cmdList]) self.cmdList = cmdList for cmd in cmdList: retryCount = 0 while retryCount < 100: self.lastError = None if preparedQuery: self.query.prepare(cmd) if not isinstance(args[0], list): args = list([args]) for dataSet in args: if not silent: _, utf8Args = xToUtf8(u'', dataSet) logDebug("{cmd} [{args}]".format(cmd=cmd, args=", ".join(utf8Args))) for value in dataSet: self.query.addBindValue(QVariant(value)) self.success = self.query.exec_() if not self.success: break else: if not silent: logDebug('%s %s' % (self.dbHandle.name, cmd)) self.success = self.query.exec_(cmd) if self.success or self.query.lastError().number() not in (5, 6): # 5: database locked, 6: table locked. Where can we get symbols for this? break time.sleep(0.1) retryCount += 1 if not self.success: self.lastError = unicode(self.query.lastError().text()) self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError) if mayFail: if not silent: logDebug(self.msg) else: logException(self.msg) return self.records = None self.fields = None if self.query.isSelect(): self.retrieveRecords() def rowcount(self): """how many rows were affected?""" return self.query.numRowsAffected() def retrieveRecords(self): """get all records from SQL into a python list""" record = self.query.record() self.fields = [record.field(x) for x in range(record.count())] self.records = [] while self.query.next(): self.records.append([self.__convertField(x) for x in range(record.count())]) def __convertField(self, idx): """convert a QSqlQuery field into a python value""" result = self.query.value(idx).toPyObject() if isinstance(result, QString): result = unicode(result) if isinstance(result, long) and -sys.maxint -1 <= result <= sys.maxint: result = int(result) return result
class Query(object): """a more pythonic interface to QSqlQuery. We could instead use the python sqlite3 module but then we would either have to do more programming for the model/view tables, or we would have two connections to the same database. For selecting queries we fill a list with ALL records. Every record is a list of all fields. q.records[0][1] is record 0, field 1. For select, we also convert to python data types - as far as we need them""" localServerName = m18ncE('kajongg name for local game server', 'Local Game') def __init__(self, cmdList, args=None, dbHandle=None, silent=False, mayFail=False): """we take a list of sql statements. Only the last one is allowed to be a select statement. Do prepared queries by passing a single query statement in cmdList and the parameters in args. If args is a list of lists, execute the prepared query for every sublist. If dbHandle is passed, use that for db access. Else if the default dbHandle (DBHandle.default) is defined, use it.""" # pylint: disable=R0912 # pylint says too many branches silent |= not Debug.sql self.dbHandle = dbHandle or DBHandle.default preparedQuery = not isinstance(cmdList, list) and bool(args) self.query = QSqlQuery(self.dbHandle) self.msg = None self.records = [] if not isinstance(cmdList, list): cmdList = list([cmdList]) self.cmdList = cmdList for cmd in cmdList: retryCount = 0 while retryCount < 100: self.lastError = None if preparedQuery: self.query.prepare(cmd) if not isinstance(args[0], list): args = list([args]) for dataSet in args: if not silent: _, utf8Args = xToUtf8(u'', dataSet) logDebug("{cmd} [{args}]".format( cmd=cmd, args=", ".join(utf8Args))) for value in dataSet: self.query.addBindValue(QVariant(value)) self.success = self.query.exec_() if not self.success: break else: if not silent: logDebug('%s %s' % (self.dbHandle.name, cmd)) self.success = self.query.exec_(cmd) if self.success or self.query.lastError().number() not in (5, 6): # 5: database locked, 6: table locked. Where can we get symbols for this? break time.sleep(0.1) retryCount += 1 if not self.success: self.lastError = unicode(self.query.lastError().text()) self.msg = 'ERROR in %s: %s' % (self.dbHandle.databaseName(), self.lastError) if mayFail: if not silent: logDebug(self.msg) else: logException(self.msg) return self.records = None self.fields = None if self.query.isSelect(): self.retrieveRecords() def rowcount(self): """how many rows were affected?""" return self.query.numRowsAffected() def retrieveRecords(self): """get all records from SQL into a python list""" record = self.query.record() self.fields = [record.field(x) for x in range(record.count())] self.records = [] while self.query.next(): self.records.append( [self.__convertField(x) for x in range(record.count())]) def __convertField(self, idx): """convert a QSqlQuery field into a python value""" result = self.query.value(idx).toPyObject() if isinstance(result, QString): result = unicode(result) if isinstance(result, long) and -sys.maxint - 1 <= result <= sys.maxint: result = int(result) return result
class DataProcessor: def __init__(self, source): self.layer = None self.dataFile = None regex = re.compile("dbname='(.+)'") r = regex.search(source) self.source = r.groups()[0] #QPSQL #self.db = QSqlDatabase.addDatabase('QPSQL') self.db = QSqlDatabase.addDatabase('QSQLITE') encoding = locale.getdefaultlocale()[1] if encoding is None: self.csvEncoding = 'utf-8' else: self.csvEncoding = encoding.lower() def setLayer(self, layer): self.layer = layer def setDataFile(self, fileName): self.dataFile = fileName def exportData(self): landmarks = self._exportLayer() if landmarks is None: return False photos = self._exportPhotos() if photos is None: return False zf = zipfile.ZipFile(self.dataFile, 'w') zf.write(landmarks, 'landmarks.csv') zf.write(photos, 'photos.csv') zf.close() return True def importData(self): if not self._importLayer(): return False if not self._importPhotos(): return False return True def _openDatabase(self): self.db.setDatabaseName(self.source) if not self.db.open(): return False self.query = QSqlQuery() return True def _exportLayer(self): fileName = self._tempFileName() err = QgsVectorFileWriter.writeAsVectorFormat( self.layer, fileName, self.csvEncoding, self.layer.crs(), 'CSV', layerOptions=['GEOMETRY=AS_XY']) if err != QgsVectorFileWriter.NoError: return None return fileName def _importLayer(self): ids = [] titles = [] types = [] angles = [] x = [] y = [] result = False zf = zipfile.ZipFile(self.dataFile, 'r') fh = StringIO.StringIO(zf.read('landmarks.csv')) reader = UnicodeReader(fh, dialect='excel', quotechar='"', encoding=self.csvEncoding) reader.next() for r in reader: x.append(float(r[0]) if r[0] else None) y.append(float(r[1]) if r[1] else None) ids.append(int(r[2]) if r[2] else None) titles.append(r[3]) types.append(int(r[4]) if r[4] else None) angles.append(float(r[5]) if r[5] else None) if not self._openDatabase(): return False self.query.prepare( 'INSERT OR REPLACE INTO landmark("id", "title", "icon_type", "label_angle", "the_geom") VALUES(?, ?, ?, ?, MakePoint(?, ?, 4326));' ) self.query.addBindValue(ids) self.query.addBindValue(titles) self.query.addBindValue(types) self.query.addBindValue(angles) self.query.addBindValue(x) self.query.addBindValue(y) if self.query.execBatch(): result = True self.db.close() return result def _exportPhotos(self): if not self._openDatabase(): return None result = None fileName = self._tempFileName() self.query.prepare( 'SELECT "id", "angle", "date", "title", "comment", "imagepath", "landmark_id" FROM photo;' ) if self.query.exec_(): result = fileName with open(fileName, 'wb') as f: writer = UnicodeWriter(f, dialect='excel', quotechar='"', encoding=self.csvEncoding) writer.writerow(('id', 'angle', 'date', 'title', 'comment', 'imagepath', 'landmark_id')) while self.query.next(): pid = unicode(self.query.value( 0)) if self.query.value(0) != None else '' angle = unicode( self.query.value(1)) if self.query.value(1) else '' date = self.query.value(2) if self.query.value(2) else '' title = self.query.value(3) if self.query.value(3) else '' comment = self.query.value(4) if self.query.value( 4) else '' imgpath = self.query.value(5) if self.query.value( 5) else '' landmarkid = unicode(self.query.value( 6)) if self.query.value(6) != None else '' writer.writerow((pid, angle, date, title, comment, imgpath, landmarkid)) self.db.close() return result def _importPhotos(self): pids = [] angles = [] dates = [] titles = [] comments = [] paths = [] ids = [] result = False zf = zipfile.ZipFile(self.dataFile, 'r') fh = StringIO.StringIO(zf.read('photos.csv')) reader = UnicodeReader(fh, dialect='excel', quotechar='"', encoding=self.csvEncoding) reader.next() for r in reader: pids.append(int(r[0]) if r[0] else None) angles.append(float(r[1]) if r[1] else None) dates.append(r[2]) titles.append(r[3]) comments.append(r[4]) paths.append(r[5]) ids.append(int(r[6]) if r[6] else None) if not self._openDatabase(): return False self.query.prepare( 'INSERT OR REPLACE INTO photo("id", "angle", "date", "title", "comment", "imagepath", "landmark_id") VALUES(?, ?, ?, ?, ?, ?, ?);' ) self.query.addBindValue(pids) self.query.addBindValue(angles) self.query.addBindValue(dates) self.query.addBindValue(titles) self.query.addBindValue(comments) self.query.addBindValue(paths) self.query.addBindValue(ids) if self.query.execBatch(): result = True self.db.close() return result def _tempDirectory(self): tmp = unicode(os.path.join(QDir.tempPath(), 'landmark')) if not os.path.exists(tmp): QDir().mkpath(tmp) return os.path.abspath(tmp) def _tempFileName(self): tmpDir = self._tempDirectory() fName = os.path.join(tmpDir, str(uuid.uuid4()).replace('-', '') + '.csv') return fName
def efectuar_venta(self): print "Efectuando Venta" query = QSqlQuery() sql = "insert into ventas values(null,?,?,?,?,?,?,?)" query.prepare(sql) query.addBindValue(self.cliente_id) query.addBindValue((self.fecha)) query.addBindValue(self.hora) query.addBindValue(self.notas) query.addBindValue(self.total) query.addBindValue(self.pagado) query.addBindValue(self.usuario_id) query.exec_() sql = "insert into detalle_ventas values" for i in self.productos.keys(): sql += """(%d,"%s",%d,%d),""" % (self.id, i, self.productos[i].cantidad, self.productos[i].descuento) query.exec_(sql[:-1]) self.es_nueva = False