def db_load_by_name(self, keywordGroup, keyword): """Load a record. Raise a RecordNotFound exception if record is not found in database.""" # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: keywordGroup = keywordGroup.encode(TransanaGlobal.encoding) keyword = keyword.encode(TransanaGlobal.encoding) db = DBInterface.get_db() query = """ SELECT * FROM Keywords2 WHERE KeywordGroup = %s AND Keyword = %s """ c = db.cursor() c.execute(query, (keywordGroup, keyword)) n = c.rowcount if (n != 1): c.close() self.clear() raise RecordNotFoundError, (keywordGroup + ':' + keyword, n) else: r = DBInterface.fetch_named(c) self._load_row(r) c.close()
def db_save(self): """ Saves ClipKeyword record to Database """ # NOTE: This routine, at present, is ONLY used by the Database Import routine. # Therefore, it does no checking for duplicate records. If you want to # use it for other purposes, you probably have to make it smarter! # If we're using Unicode ... if 'unicode' in wx.PlatformInfo: # ... encode the text fields for this object keywordGroup = self.keywordGroup.encode(TransanaGlobal.encoding) keyword = self.keyword.encode(TransanaGlobal.encoding) # If we're not using Unicode ... else: # ... no encoding is needed keywordGroup = self.keywordGroup keyword = self.keyword # Get a Database Cursor dbCursor = DBInterface.get_db().cursor() # Create the Insert Query SQLText = """ INSERT INTO ClipKeywords2 (EpisodeNum, ClipNum, SnapshotNum, KeywordGroup, Keyword, Example) VALUES (%s, %s, %s, %s, %s, %s) """ # Adjust the query for sqlite if needed SQLText = DBInterface.FixQuery(SQLText) # Prepare the Data Values for the query values = (self.episodeNum, self.clipNum, self.snapshotNum, keywordGroup, keyword, self.example) # Execute the Query dbCursor.execute(SQLText, values) # Close the Database Cursor dbCursor.close()
def lock_record(self): """Lock a record. If the lock is unable to be obtained, a RecordLockedError exception is raised with the username of the lock holder passed.""" if self.number == 0: # no record or new record not yet in database loaded return tablename = self._table() numname = self._num() db = DBInterface.get_db() c = db.cursor() lq = self._get_db_fields(('RecordLock', 'LockTime'), c) if (lq[1] == None) or (lq[0] == "") or ((DBInterface.ServerDateTime() - lq[1]).days > 1): # Lock the record self._set_db_fields( ('RecordLock', 'LockTime'), (DBInterface.get_username(), str(DBInterface.ServerDateTime())[:-3]), c) c.close() # Indicate that the object was successfully locked self._isLocked = True if DEBUG: print "DataObject.lock_record(): Record '%s' locked by '%s'" % (self.number, DBInterface.get_username()) else: # We just raise an exception here since GUI code isn't appropriate. c.close() if DEBUG: print "DataObject.lock_record(): Record %s locked by %s raises exception" % (self.id, lq[0]) raise RecordLockedError, lq[0] # Pass name of person
def get_shop_nearby(longitude, latitude): '''get all shop nearby Args: longitude: latitude: Returns: result: [[shopId, shopName, ownerName, address, shopPhone, longitude, latitude, starRating, description, account, password], [shopId, shopName, ownerName, address, shopPhone, longitude, latitude, starRating, description, account, password], ...] ''' search_range = 0.03 sql = "SELECT * FROM shop WHERE longitude < %s AND longitude > %s AND latitude < %s AND latitude > %s" location = (float(longitude)+search_range, float(longitude)-search_range, float(latitude)+search_range, float(latitude)-search_range) result = DBInterface.query_for_all(sql, location) while len(result) < 5: search_range = float(search_range + 0.03) location = (float(longitude)+search_range, float(longitude)-search_range, float(latitude)+search_range, float(latitude)-search_range) result = DBInterface.query_for_all(sql, location) return result
def _db_start_save(self): """Return 0 if creating new record, 1 if updating an existing one.""" tname = type(self).__name__ if (self.keywordGroup == ""): if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode(_("Cannot save a %s with a blank Keyword Group."), 'utf8') % tname else: prompt = _("Cannot save a %s with a blank Keyword Group.") % tname raise SaveError, prompt elif (self.keyword == ""): if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode(_("Cannot save a %s with a blank Keyword."), 'utf8') % tname else: prompt = _("Cannot save a %s with a blank Keyword.") % tname raise SaveError, prompt else: # Verify record lock is still good db = DBInterface.get_db() if ((self.originalKeywordGroup == None) and \ (self.originalKeyword == None)) or \ ((self.record_lock == DBInterface.get_username()) and ((DBInterface.ServerDateTime() - self.lock_time).days <= 1)): c = db.cursor() # If record num is 0, this is a NEW record and needs to be # INSERTed. Otherwise, it is an existing record to be UPDATEd. if (self.originalKeywordGroup == None) or \ (self.originalKeyword == None): # no record loaded? return 0 else: return 1 else: raise SaveError, _("Record lock no longer valid.")
def lock_record(self): """Lock a record. If the lock is unable to be obtained, a RecordLockedError exception is raised with the username of the lock holder passed.""" if (self.originalKeywordGroup == None) or \ (self.originalKeyword == None): # no record loaded? return tablename = self._table() db = DBInterface.get_db() c = db.cursor() lq = self._get_db_fields(('RecordLock', 'LockTime'), c) if (lq[1] == None) or (lq[0] == "") or ((DBInterface.ServerDateTime() - lq[1]).days > 1): (EpisodeClipLockCount, LockName) = self.checkEpisodesClipsSnapshotsForLocks() if EpisodeClipLockCount == 0: # Lock the record self._set_db_fields( ('RecordLock', 'LockTime'), (DBInterface.get_username(), str(DBInterface.ServerDateTime())[:-3]), c) c.close() else: c.close() raise RecordLockedError, LockName else: # We just raise an exception here since GUI code isn't appropriate. c.close() raise RecordLockedError, lq[0] # Pass name of person
def _load_row(self, r): self.number = r['NoteNum'] self.id = r['NoteID'] self.comment = '' self.series_num = r['SeriesNum'] self.episode_num = r['EpisodeNum'] self.collection_num = r['CollectNum'] self.clip_num = r['ClipNum'] self.transcript_num = r['TranscriptNum'] self.snapshot_num = r['SnapshotNum'] self.document_num = r['DocumentNum'] self.quote_num = r['QuoteNum'] self.author = r['NoteTaker'] # self.text = r['NoteText'] # Okay, this isn't so straight-forward any more. # With MySQL for Python 0.9.x, r['NoteText'] is of type str. # With MySQL for Python 1.2.0, r['NoteText'] is of type array. It could then either be a # character string (typecode == 'c') or a unicode string (typecode == 'u'), which then # need to be interpreted differently. if type(r['NoteText']).__name__ == 'array': if r['NoteText'].typecode == 'u': self.text = r['NoteText'].tounicode() else: self.text = r['NoteText'].tostring() else: self.text = r['NoteText'] # If we're in Unicode mode, we need to encode the data from the database appropriately. # (unicode(var, TransanaGlobal.encoding) doesn't work, as the strings are already unicode, yet aren't decoded.) if 'unicode' in wx.PlatformInfo: self.id = DBInterface.ProcessDBDataForUTF8Encoding(self.id) self.comment = DBInterface.ProcessDBDataForUTF8Encoding( self.comment) self.author = DBInterface.ProcessDBDataForUTF8Encoding(self.author) self.text = DBInterface.ProcessDBDataForUTF8Encoding(self.text)
def lock_record(self): """ Override the DataObject Lock Method """ # If we're using the single-user version of Transana, we just need to ... if not TransanaConstants.singleUserVersion: # ... confirm that the document has not been altered by another user since it was loaded. # To do this, first pull the LastSaveTime for this record from the database. query = """ SELECT LastSaveTime FROM Documents2 WHERE DocumentNum = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor tempDBCursor = DBInterface.get_db().cursor() # Execute the Lock query tempDBCursor.execute(query, (self.number, )) # Get the query results rows = tempDBCursor.fetchall() # If we get exactly one row (and we should) ... if len(rows) == 1: # ... get the LastSaveTime newLastSaveTime = rows[0] # Otherwise ... else: # ... raise an exception raise RecordNotFoundError, (self.id, len(rows)) # Close the database cursor tempDBCursor.close() # if the LastSaveTime has changed, some other user has altered the record since we loaded it. if newLastSaveTime != self.lastsavetime: # ... so we need to re-load it! self.db_load_by_num(self.number) # ... lock the Transcript Record DataObject.DataObject.lock_record(self)
def lock_record(self): """Lock a record. If the lock is unable to be obtained, a RecordLockedError exception is raised with the username of the lock holder passed.""" if (self.originalKeywordGroup == None) or \ (self.originalKeyword == None): # no record loaded? return tablename = self._table() db = DBInterface.get_db() c = db.cursor() lq = self._get_db_fields(('RecordLock', 'LockTime'), c) if (lq[1] == None) or (lq[0] == "") or ( (DBInterface.ServerDateTime() - lq[1]).days > 1): (EpisodeClipLockCount, LockName) = self.checkEpisodesClipsSnapshotsForLocks() if EpisodeClipLockCount == 0: # Lock the record self._set_db_fields(('RecordLock', 'LockTime'), (DBInterface.get_username(), str(DBInterface.ServerDateTime())[:-3]), c) c.close() else: c.close() raise RecordLockedError, LockName else: # We just raise an exception here since GUI code isn't appropriate. c.close() raise RecordLockedError, lq[0] # Pass name of person
def add_shop(shopName, ownerName, address, shopPhone, longitude, latitude, description, account, password): '''add shop when shop register Args: shopName: ownerName: address: shopPhone: longtitude: latitude: description: account: password: Returns: shopId: ''' # m = hashlib.md5() # m.update(password) # password = m.digest() # print m.digest() sql = "INSERT INTO shop (shopName, ownerName, address, shopPhone, longitude, latitude, description, account, password) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)" param = (shopName, ownerName, address, shopPhone, longitude, latitude, description, account, password) DBInterface.execute_sql(sql, param) shopId = get_shopId_by_account(account) return shopId
def db_load_by_name(self, series, episode): """Load a record by ID / Name.""" # If we're in Unicode mode, we need to encode the parameters so that the query will work right. if 'unicode' in wx.PlatformInfo: series = series.encode(TransanaGlobal.encoding) episode = episode.encode(TransanaGlobal.encoding) # Get a database connection db = DBInterface.get_db() # Craft a query to get Episode data query = """SELECT * FROM Episodes2 a, Series2 b WHERE EpisodeID = %s AND a.SeriesNum = b.SeriesNum AND b.SeriesID = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (episode, series)) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one record returned and check later n = 1 # If not sqlite ... else: # ... we can use rowcount to know how much data was returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Episode object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (episode, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # if sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the cursor ... c.close() # ... clear the Episode object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (episode, 0) # Load the data into the Episode object self._load_row(r) # Load Additional Media Files, which aren't handled in the "old" code self.load_additional_vids() # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def set_shopPhone(shopId, shopPhone): '''set shop phone Args: shopPhone: ''' sql = "UPDATE shop SET shopPhone=%s WHERE shopId=%s" param = (shopPhone, shopId) DBInterface.execute_sql(sql, param)
def db_delete(self, use_transactions=1): """Delete this object record from the database. Raises RecordLockedError exception if record is locked and unable to be deleted.""" # Assume success result = 1 try: # Initialize delete operation, begin transaction if necessary. (db, c) = self._db_start_delete(use_transactions) if (db == None): return # Abort delete without even trying # Delete all Series-based Filter Configurations # Delete Series Keyword Sequence Map records DBInterface.delete_filter_records(5, self.number) # Delete Series Keyword Bar Graph records DBInterface.delete_filter_records(6, self.number) # Delete Series Keyword Percentage Map records DBInterface.delete_filter_records(7, self.number) # Delete Series Report records DBInterface.delete_filter_records(10, self.number) # Delete Series Clip Data Export records DBInterface.delete_filter_records(14, self.number) # Detect, Load, and Delete all Series Notes. notes = self.get_note_nums() for note_num in notes: note = Note.Note(note_num) result = result and note.db_delete(0) del note del notes # Deletes Episodes, which in turn will delete Episode Transcripts, # Episode Notes, and Episode Keywords episodes = self.get_episode_nums() for episode_num in episodes: episode = Episode.Episode(episode_num) # Store the result so we can rollback the transaction on failure result = result and episode.db_delete(0) del episode del episodes # Delete the actual record. self._db_do_delete(use_transactions, c, result) # Cleanup c.close() self.clear() except RecordLockedError, e: # if a sub-record is locked, we may need to unlock the Series record (after rolling back the Transaction) if self.isLocked: # c (the database cursor) only exists if the record lock was obtained! # We must roll back the transaction before we unlock the record. c.execute("ROLLBACK") c.close() self.unlock_record() raise e
def db_load_by_name(self, collectionID, quoteID, collectionParent): """Load a record by ID / Name.""" # If we're in Unicode mode, we need to encode the parameters so that the query will work right. if 'unicode' in wx.PlatformInfo: collectionID = collectionID.encode(TransanaGlobal.encoding) quoteID = quoteID.encode(TransanaGlobal.encoding) # Get a database connection db = DBInterface.get_db() # Craft a query to get Quote data query = """SELECT * FROM Quotes2 a, Collections2 b, QuotePositions2 c WHERE QuoteID = %s AND a.CollectNum = b.CollectNum AND b.CollectID = %s AND b.ParentCollectNum = %s AND c.QuoteNum = a.QuoteNum """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (quoteID, collectionID, collectionParent)) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one record returned and check later n = 1 # If not sqlite ... else: # ... we can use rowcount to know how much data was returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Document object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (quoteID, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # if sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the cursor ... c.close() # ... clear the Document object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (quoteID, 0) # Load the data into the Document object self._load_row(r) # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def db_load_by_name(self, library, episode): """Load a record by ID / Name.""" # If we're in Unicode mode, we need to encode the parameters so that the query will work right. if 'unicode' in wx.PlatformInfo: library = library.encode(TransanaGlobal.encoding) episode = episode.encode(TransanaGlobal.encoding) # Get a database connection db = DBInterface.get_db() # Craft a query to get Episode data query = """SELECT * FROM Episodes2 a, Series2 b WHERE EpisodeID = %s AND a.SeriesNum = b.SeriesNum AND b.SeriesID = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (episode, library)) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one record returned and check later n = 1 # If not sqlite ... else: # ... we can use rowcount to know how much data was returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Episode object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (episode, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # if sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the cursor ... c.close() # ... clear the Episode object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (episode, 0) # Load the data into the Episode object self._load_row(r) # Load Additional Media Files, which aren't handled in the "old" code self.load_additional_vids() # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def set_description(shopId, description): """set shop description Args: shopId: description: """ sql = "UPDATE shop SET description=%s WHERE shopId=%s" param = (description, shopId) DBInterface.execute_sql(sql, param)
def set_starRating(shopId, starRating): """set shop starRating Args: shopId: description: """ sql = "UPDATE shop SET starRating=%s WHERE shopId=%s" param = (float(starRating), int(shopId)) DBInterface.execute_sql(sql, param)
def set_password(shopId, password): """set shop password Args: shopId: password: """ sql = "UPDATE shop SET password=%s WHERE shopId=%s" param = (password, shopId) DBInterface.execute_sql(sql, param)
def getTablesListToRun_CM(**kwargs): logger = kwargs["logging"] logger.info("In func : getTablesListToRun") input_module = kwargs["module"] input_table_list = kwargs["input_table_list"] for table in input_table_list: logger.info("Checking table : {} for run".format(table)) kwargs["table_name"] = table DBInterface.checkPreviousRunForTable_DB(**kwargs) return input_module
def checkEpisodesAndClipsForLocks(self): """ Checks Episodes and Clips to see if a Keyword record is free of related locks """ # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: originalKeywordGroup = self.originalKeywordGroup.encode( TransanaGlobal.encoding) originalKeyword = self.originalKeyword.encode( TransanaGlobal.encoding) else: originalKeywordGroup = self.originalKeywordGroup originalKeyword = self.originalKeyword # query the lock status for Episodes that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Episodes2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.EpisodeNum <> 0 AND b.EpisodeNum = c.EpisodeNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Episodes that contain the record are locked, check the lock status of Clips that contain the Keyword if RecordCount == 0: # query the lock status for Clips that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Clips2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.ClipNum <> 0 AND b.ClipNum = c.ClipNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() if RecordCount != 0: LockName = result[0][0] return (RecordCount, LockName) else: return (RecordCount, None)
def db_load_by_num(self, num): """Load a record by record number.""" # Get a database connection db = DBInterface.get_db() # Craft a query to get Episode data query = """SELECT * FROM Episodes2 a, Series2 b WHERE EpisodeNum = %s AND a.SeriesNum = b.SeriesNum """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (num, )) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # so assume one record for now n = 1 # If not sqlite ... else: # ... we can use rowcount to know the number of records returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Episode object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (num, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # Load the data into the Episode object self._load_row(r) # If sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # .. close the cursor ... c.close() # ... clear the Episode object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # Load Additional Media Files, which aren't handled in the "old" code self.load_additional_vids() # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def set_comment(recordId, starRating, contents, commentTime): """set record's comment. Args: recordId: starRating: contents: commentTime: """ sql = "INSERT INTO comment VALUES (%s, %s, %s, %s)" param = (recordId, starRating, contents, commentTime) DBInterface.execute_sql(sql, param)
def _load_row(self, r): self.number = r['SeriesNum'] self.id = r['SeriesID'] self.comment = r['SeriesComment'] self.owner = r['SeriesOwner'] self.keyword_group = r['DefaultKeywordGroup'] # If we're in Unicode mode, we need to encode the data from the database appropriately. # (unicode(var, TransanaGlobal.encoding) doesn't work, as the strings are already unicode, yet aren't decoded.) if 'unicode' in wx.PlatformInfo: self.id = DBInterface.ProcessDBDataForUTF8Encoding(self.id) self.comment = DBInterface.ProcessDBDataForUTF8Encoding(self.comment) self.owner = DBInterface.ProcessDBDataForUTF8Encoding(self.owner) self.keyword_group = DBInterface.ProcessDBDataForUTF8Encoding(self.keyword_group)
def add_favor(customerId, shopId): """add favor shop of customer Args: customerId: shopId: Returns: """ sql = "INSERT INTO favor (customerId, shopId) VALUES (%s, %s)" param = (customerId, shopId) DBInterface.execute_sql(sql, param) return
def add_record(customerId, shopId, recordTime, journeyTime): """add record Args: customerId: shopId: recordTime: journeyTime: """ sql = "INSERT INTO record (customerId, shopId, recordTime, journeyTime) VALUES (%s, %s, %s, %s)" param = (customerId, shopId, recordTime, journeyTime) DBInterface.execute_sql(sql, param) return
def delete_favor(customerId, shopId): """add favor shop of customer Args: customerId: shopId: Returns: """ sql = "DELETE FROM favor WHERE customerId=%s AND shopId=%s" param = (customerId, shopId) DBInterface.execute_sql(sql, param) return
def OnDelKW(self, evt): """Invoked when the 'Delete Keyword from list' button is activated.""" kw_group = self.kw_group.GetStringSelection() kw_name = self.kw_lb.GetStringSelection() if kw_name == "": return sel = self.kw_lb.GetSelection() # If we found the keyword we're supposed to delete ... if TransanaGlobal.menuWindow.ControlObject.UpdateSnapshotWindows('Detect', evt, kw_group, kw_name): # ... present an error message to the user msg = _("Keyword %s : %s is contained in a Snapshot you are currently editing.\nYou cannot delete it at this time.") msg = unicode(msg, 'utf8') dlg = Dialogs.ErrorDialog(self, msg % (kw_group, kw_name)) dlg.ShowModal() dlg.Destroy() # Signal that we do NOT want to delete the Keyword! result = wx.ID_NO else: msg = _('Are you sure you want to delete Keyword "%s" and all instances of it from Quotes, Clips, and Snapshots?') if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. msg = unicode(msg, 'utf8') dlg = Dialogs.QuestionDialog(self, msg % kw_name) result = dlg.LocalShowModal() dlg.Destroy() if result == wx.ID_YES: try: DBInterface.delete_keyword(self.kw_group.GetStringSelection(), kw_name) self.kw_lb.Delete(sel) self.definition.SetValue('') # We need to update all open Snapshot Windows based on the change in this Keyword TransanaGlobal.menuWindow.ControlObject.UpdateSnapshotWindows('Update', evt, kw_group, kw_name) if not TransanaConstants.singleUserVersion: if TransanaGlobal.chatWindow != None: # We need the UNTRANSLATED Root Node here msgData = "%s >|< %s >|< %s >|< %s" % ('KeywordNode', 'Keywords', self.kw_group.GetStringSelection(), kw_name) TransanaGlobal.chatWindow.SendMessage("DN %s" % msgData) # We need to update the Keyword Visualization no matter what here, when deleting a keyword TransanaGlobal.chatWindow.SendMessage("UKV %s %s %s" % ('None', 0, 0)) except GeneralError, e: # Display the Exception Message, allow "continue" flag to remain true prompt = "%s" data = e.explanation if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode(prompt, 'utf8') errordlg = Dialogs.ErrorDialog(None, prompt % data) errordlg.ShowModal() errordlg.Destroy()
def db_load_by_name(self, name): """Load a record by ID / Name. Raise a RecordNotFound exception if record is not found in database.""" # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: name = name.encode(TransanaGlobal.encoding) # Get the database connection db = DBInterface.get_db() # Define the load query query = """ SELECT * FROM Series2 WHERE SeriesID = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (name, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one row return for now n = 1 # If not sqlite ... else: # ... we can use rowcount n = c.rowcount # If not one row ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current series ... self.clear() # ... and raise an exception raise RecordNotFoundError, (name, n) # If exactly one row, or sqlite ... else: # ... get the query results ... r = DBInterface.fetch_named(c) # If sqlite and no results ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current series ... self.clear() # ... and raise an exception raise RecordNotFoundError, (name, 0) # Load the database results into the Series object self._load_row(r) # Close the database cursor ... c.close()
def db_load_by_num(self, num): """Load a record by record number.""" # Get the database connection db = DBInterface.get_db() # If we're skipping the RTF Text ... if self.skipText: # Define the query to load a Clip Transcript without text query = """SELECT TranscriptNum, TranscriptID, EpisodeNum, SourceTranscriptNum, ClipNum, SortOrder, Transcriber, ClipStart, ClipStop, Comment, MinTranscriptWidth, RecordLock, LockTime, LastSaveTime FROM Transcripts2 WHERE TranscriptNum = %s """ # If we're NOT skipping the RTF Text ... else: # Define the query to load a Clip Transcript with everything query = """SELECT * FROM Transcripts2 WHERE TranscriptNum = %s""" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the Load query c.execute(query, (num, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': n = 1 else: n = c.rowcount # If we don't have exactly ONE record ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current Transcript Object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, n) # If we DO have exactly one record (or use sqlite) ... else: # ... Fetch the query results ... r = DBInterface.fetch_named(c) # If sqlite and not results are found ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current Transcript object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # Load the data into the Transcript Object self._load_row(r) # Close the database cursor c.close()
def checkEpisodesAndClipsForLocks(self): """ Checks Episodes and Clips to see if a Keyword record is free of related locks """ # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: originalKeywordGroup = self.originalKeywordGroup.encode(TransanaGlobal.encoding) originalKeyword = self.originalKeyword.encode(TransanaGlobal.encoding) else: originalKeywordGroup = self.originalKeywordGroup originalKeyword = self.originalKeyword # query the lock status for Episodes that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Episodes2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.EpisodeNum <> 0 AND b.EpisodeNum = c.EpisodeNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Episodes that contain the record are locked, check the lock status of Clips that contain the Keyword if RecordCount == 0: # query the lock status for Clips that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Clips2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.ClipNum <> 0 AND b.ClipNum = c.ClipNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() if RecordCount != 0: LockName = result[0][0] return (RecordCount, LockName) else: return (RecordCount, None)
def fsinit(self): try: global GlobalConfig self.config = ConfigFactory.GetConfigByTable( GetDBTable(self.dbname)) self.config.setDB(self.dbname) GlobalConfig = self.config if (self.dbname is None): self.db = DBInterface(self.config) else: self.db = DBInterface(self.config, self.dbname) except Exception, e: print "Caught exception in FSINIT:", e
def _get_db_fields(self, fieldlist, c=None): """Get the values of fields from the database for the currently loaded record. Use existing cursor if it exists, otherwise create a new one. Return a tuple containing the values obtained.""" # If the object number is 0, there's no record in the database ... if self.number == 0: # ... so just return an empty tuple return () # Get the table name and the name of the Number property tablename = self._table() numname = self._num() # Create a flag tht indicates whether the cursor was passed in close_c = False # If no cursor was passed in ... if (c == None): # ... update the flag ... close_c = True # ... get a database reference ... db = DBInterface.get_db() # ... and create a database cursor c = db.cursor() # Determine the field values needed for the query fields = "" for field in fieldlist: fields = fields + field + ", " fields = fields[:-2] # Formulate the query based on the fields query = "SELECT " + fields + " FROM " + tablename + \ " WHERE " + numname + " = %s" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (self.number, )) # Get query row results qr = c.fetchone() if DEBUG: print "DataObject._get_db_fields():\n", query, qr print # If we created the cursor locally (as flagged) ... if (close_c): # ... close the database cursor c.close() # Return the Query Results return qr
def _load_row(self, r): self.keywordGroup = r['KeywordGroup'] self.keyword = r['Keyword'] self.definition = r['Definition'] if self.definition == None: self.definition = '' # If we're in Unicode mode, we need to encode the data from the database appropriately. # (unicode(var, TransanaGlobal.encoding) doesn't work, as the strings are already unicode, yet aren't decoded.) if 'unicode' in wx.PlatformInfo: self.keywordGroup = DBInterface.ProcessDBDataForUTF8Encoding( self.keywordGroup) self.keyword = DBInterface.ProcessDBDataForUTF8Encoding( self.keyword) self.definition = DBInterface.ProcessDBDataForUTF8Encoding( self.definition)
def refresh_keyword_groups(self): """Refresh the keyword groups listbox.""" self.kw_groups = DBInterface.list_of_keyword_groups() self.kw_group_lb.Clear() self.kw_group_lb.InsertItems(self.kw_groups, 0) if len(self.kw_groups) > 0: self.kw_group_lb.SetSelection(0)
def register_email(self): self.password_info = self.password.get() self.password_entry.delete(0, END) self.email_info = self.email_address.get() self.email_address_entry.delete(0, END) if (isBlank(self.email_info) or isBlank(self.password_info)): self.empty_filler() self.info_regis.configure(text="Failed", justify="center", bg="white", fg="green", font=("calibri", 11)) else: db = DBInterface.DBInterface() log = db.login(self.username_info2, self.password_info) data2 = json.loads(log) if (data2["status"] == True): db.update_email(self.ID_info2, self.email_info2, self.email_info, self.password_info) pub_cmd.publish(client, "update email") self.info_regis.configure(text="Success Changing Email", justify="center", bg="white", fg="green", font=("calibri", 11)) else: self.info_regis.configure(text="Wrong Current Password", justify="center", bg="white", fg="green", font=("calibri", 11))
def get_episode_nums(self): """Get a list of Episode numbers that belong to this Series.""" notelist = [] t = self._prefix() table = self._table() query = """ SELECT EpisodeNum FROM Episodes2 a, %s b WHERE a.%sNum = b.%sNum and %sID = %%s ORDER BY EpisodeID """ % (table, t, t, t) if type(self.id).__name__ == 'unicode': id = self.id.encode(TransanaGlobal.encoding) else: id = self.id # Adjust the query for sqlite if needed c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, (id, )) r = c.fetchall() # return array of tuples with results for tup in r: notelist.append(tup[0]) c.close() return notelist
def _db_start_save(self): """Return 0 if creating new record, 1 if updating an existing one.""" tname = type(self).__name__ # You can save a Clip Transcript with a blank Transcript ID! if (self.id == "") and (tname != 'Transcript'): if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode(_("Cannot save a %s with a blank %s ID"), 'utf8') else: prompt = _("Cannot save a %s with a blank %s ID") raise SaveError, prompt % (tname, tname) else: # Verify record lock is still good if (self.number == 0) or \ ((self.record_lock == DBInterface.get_username()) and ((self.lock_time == None) or ((DBInterface.ServerDateTime() - self.lock_time).days <= 1))): # If record num is 0, this is a NEW record and needs to be # INSERTed. Otherwise, it is an existing record to be UPDATEd. if (self.number == 0): return 0 else: return 1 else: raise SaveError, _("Record lock no longer valid.")
def __init__(self, parent, numRecords = 0): """ Create a Dialog Box to process the Database Conversion. Parameters: parent Parent Window numRecords The number of records that will be updated """ # Remember the total number of records passed in, or obtain that number if needed if numRecords == 0: self.numRecords = DBInterface.CountItemsWithoutPlainText() else: self.numRecords = numRecords # Create a Dialog Box wx.Dialog.__init__(self, parent, -1, _("Plain Text Conversion"), size=(600, 350)) # Add the Main Sizer mainSizer = wx.BoxSizer(wx.VERTICAL) # Add a gauge based on the number of records to be handled self.gauge = wx.Gauge(self, -1, numRecords) mainSizer.Add(self.gauge, 0, wx.EXPAND | wx.LEFT | wx.TOP | wx.RIGHT, 5) # Add a TextCtrl to provide user information self.txtCtrl = wx.TextCtrl(self, -1, "", style=wx.TE_LEFT | wx.TE_MULTILINE) mainSizer.Add(self.txtCtrl, 1, wx.EXPAND | wx.ALL, 5) # Add a hidden RichTextCrtl used for the conversion process self.richTextCtrl = TranscriptEditor_RTC.TranscriptEditor(self) self.richTextCtrl.SetReadOnly(True) self.richTextCtrl.Enable(False) # The user doesn't need to see this. self.richTextCtrl.Show(False) # Even though we add it to the sizer, it won't show up because of the Show(False). mainSizer.Add(self.richTextCtrl, 1, wx.EXPAND | wx.ALL, 5) # Finalize the Dialog layout self.SetSizer(mainSizer) self.SetAutoLayout(True) self.Layout() # Center the Dialog on the Screen TransanaGlobal.CenterOnPrimary(self)
def refresh_keywords(self): """Clear the keyword list and refresh it from the database.""" self._kwlist = [] kwpairs = DBInterface.list_of_keywords(Quote=self.number) for data in kwpairs: tempClipKeyword = ClipKeywordObject.ClipKeyword(data[0], data[1], quoteNum=self.number, example=data[2]) self._kwlist.append(tempClipKeyword)
def refresh_keywords(self): """Clear the keyword list and refresh it from the database.""" self._kwlist = [] kwpairs = DBInterface.list_of_keywords(Episode=self.number) for data in kwpairs: tempClipKeyword = ClipKeywordObject.ClipKeyword(data[0], data[1], episodeNum=self.number, example=data[2]) self._kwlist.append(tempClipKeyword)
def update_customer_info(open_id, customer_name, gender, customer_phone, car_type, plate_number): '''update customer information Args: open_id: customerId customer_name: gender: customer_phone: car_type: plate_number: ''' sql = "UPDATE customer\ SET customerName=%s, gender=%s, customerPhone=%s, carModelId=%s, plateNumber=%s\ WHERE customerId=%s" param = (customer_name, gender, customer_phone, car_type, plate_number, open_id) DBInterface.execute_sql(sql, param)
def lock_record(self): """ Override the DataObject Lock Method """ # If we're using the single-user version of Transana, we just need to ... if not TransanaConstants.singleUserVersion: # ... confirm that the quote has not been altered by another user since it was loaded. # To do this, first pull the LastSaveTime for this record from the database. query = """ SELECT LastSaveTime FROM Quotes2 WHERE QuoteNum = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor tempDBCursor = DBInterface.get_db().cursor() # Execute the Lock query tempDBCursor.execute(query, (self.number, )) # Get the query results rows = tempDBCursor.fetchall() # If we get exactly one row (and we should) ... if len(rows) == 1: # ... get the LastSaveTime newLastSaveTime = rows[0] # Otherwise ... else: # ... raise an exception raise RecordNotFoundError, (self.id, len(rows)) # Close the database cursor tempDBCursor.close() # if the LastSaveTime has changed, some other user has altered the record since we loaded it. if newLastSaveTime != self.lastsavetime: # ... so we need to re-load it! self.db_load_by_num(self.number) # ... lock the Transcript Record DataObject.DataObject.lock_record(self)
def db_save(self): """ Saves ClipKeyword record to Database """ # NOTE: This routine, at present, is ONLY used by the Database Import routine. # Therefore, it does no checking for duplicate records. If you want to # use it for other purposes, you probably have to make it smarter! # If we're using Unicode ... if 'unicode' in wx.PlatformInfo: # ... encode the text fields for this object keywordGroup = self.keywordGroup.encode(TransanaGlobal.encoding) keyword = self.keyword.encode(TransanaGlobal.encoding) # If we're not using Unicode ... else: # ... no encoding is needed keywordGroup = self.keywordGroup keyword = self.keyword # Get a Database Cursor dbCursor = DBInterface.get_db().cursor() # Create the Insert Query SQLText = """ INSERT INTO ClipKeywords2 (DocumentNum, EpisodeNum, QuoteNum, ClipNum, SnapshotNum, KeywordGroup, Keyword, Example) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """ # Adjust the query for sqlite if needed SQLText = DBInterface.FixQuery(SQLText) # Prepare the Data Values for the query values = (self.documentNum, self.episodeNum, self.quoteNum, self.clipNum, self.snapshotNum, keywordGroup, keyword, self.example) # Execute the Query dbCursor.execute(SQLText, values) # Close the Database Cursor dbCursor.close()
def _get_lt(self): # Get the Record Lock Time from the Database lt = self._get_db_fields(('LockTime', )) # If a Lock Time has been specified ... if (len(lt) > 0) and (lt[0] is not None): # ... If we're using sqlite, we get a string and need to convert it to a datetime object if TransanaConstants.DBInstalled in ['sqlite3']: import datetime # Start catching exceptions try: # This conversion *almost* always works tempDate = datetime.datetime.strptime( lt[0], '%Y-%m-%d %H:%M:%S.%f') except ValueError: # Every once in a while, we get an lt[0] value with no SECONDS, requiring this conversion. I don't know why. tempDate = datetime.datetime.strptime( lt[0], '%Y-%m-%d %H:%M') return tempDate # ... If we're using MySQL, we get a MySQL DateTime value else: return lt[0] # If we don't get a Lock Time ... else: # ... return the current Server Time return DBInterface.ServerDateTime()
def update_comment_starRating(shopId): sql = "SELECT AVG(Com.starRating)\ FROM record AS Rec, comment AS Com\ WHERE Rec.shopId=%s AND Com.recordId=Rec.recordId" starRating = DBInterface.query_for_one(sql, shopId)[0] set_starRating(shopId, starRating) return