我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用sqlite3.Error()。
def _query(self, query, *args): con = None data = None try: con = sqlite3.connect( DATABASE ) cur = con.cursor() cur.execute(query, tuple(args)) data = cur.fetchall() if not data: con.commit() except sqlite3.Error as e: self.log.error("Database error: %s" % e) except Exception as e: self.log.error("Exception in _query: %s" % e) finally: if con: con.close() return data
def getColumns(conn, connType, tableName, exampleNameForDB): retval = False if connType == DBTYPE_MYSQL: query = "SHOW COLUMNS FROM {}".format(tableName) else: query = "PRAGMA table_info({})".format(tableName) try: cursor = conn.cursor() cursor.execute(query) retval = cursor.fetchall() cursor.close() except (MySQLdb.ProgrammingError, sqlite3.Error,) as e: print "TABLE {} not found for example {}: {}".format(tableName,exampleNameForDB, e.args) if connType == DBTYPE_SQLITE: retval = [(x[1],) for x in retval] # weird unSELECTable tuple retval return retval
def entry_get_id(self, entry_date): try: cursor = self.con.cursor() params = (entry_date,) cursor.execute(''' SELECT id FROM viewer_entry WHERE date = ?''' , params) data = cursor.fetchone() except sqlite3.Error, e: print 'entry_get_id() Error %s:' % e.args[0] self.close() sys.exit(1) return data
def exchangerate_get_id(self, entry_id, currency): try: cursor = self.con.cursor() params = (entry_id, currency) cursor.execute(''' SELECT id FROM viewer_exchangerate WHERE entry_id = ? AND currency = ?''' , params) data = cursor.fetchone() except sqlite3.Error, e: print 'exchangerate_get_id() Error %s:' % e.args[0] self.close() sys.exit(1) return data
def scanLogEvent(self, instanceId, classification, message, component=None): if component is None: component = "SpiderFoot" qry = "INSERT INTO tbl_scan_log \ (scan_instance_id, generated, component, type, message) \ VALUES (?, ?, ?, ?, ?)" try: self.dbh.execute(qry, ( instanceId, time.time() * 1000, component, classification, message )) self.conn.commit() except sqlite3.Error as e: if "locked" in e.args[0]: # TODO: Do something smarter here to handle locked databases self.sf.fatal("Unable to log event in DB due to lock: " + e.args[0]) else: self.sf.fatal("Unable to log event in DB: " + e.args[0]) return True # Store a scan instance
def scanErrors(self, instanceId, limit=None): qry = "SELECT generated AS generated, component, \ message FROM tbl_scan_log WHERE scan_instance_id = ? \ AND type = 'ERROR' ORDER BY generated DESC" qvars = [instanceId] if limit is not None: qry += " LIMIT ?" qvars.append(limit) try: self.dbh.execute(qry, qvars) return self.dbh.fetchall() except sqlite3.Error as e: self.sf.error("SQL error encountered when fetching scan errors: " + e.args[0]) # Delete a scan instance
def scanInstanceDelete(self, instanceId): qry1 = "DELETE FROM tbl_scan_instance WHERE guid = ?" qry2 = "DELETE FROM tbl_scan_config WHERE scan_instance_id = ?" qry3 = "DELETE FROM tbl_scan_results WHERE scan_instance_id = ?" qry4 = "DELETE FROM tbl_scan_log WHERE scan_instance_id = ?" qvars = [instanceId] try: self.dbh.execute(qry1, qvars) self.dbh.execute(qry2, qvars) self.dbh.execute(qry3, qvars) self.dbh.execute(qry4, qvars) self.conn.commit() except sqlite3.Error as e: self.sf.error("SQL error encountered when deleting scan: " + e.args[0]) # Set the false positive flag for a result
def configSet(self, optMap=dict()): qry = "REPLACE INTO tbl_config (scope, opt, val) VALUES (?, ?, ?)" for opt in optMap.keys(): # Module option if ":" in opt: parts = opt.split(':') qvals = [parts[0], parts[1], optMap[opt]] else: # Global option qvals = ["GLOBAL", opt, optMap[opt]] try: self.dbh.execute(qry, qvals) except sqlite3.Error as e: self.sf.error("SQL error encountered when storing config, aborting: " + e.args[0]) self.conn.commit() # Retreive the config from the database
def configGet(self): qry = "SELECT scope, opt, val FROM tbl_config" try: retval = dict() self.dbh.execute(qry) for [scope, opt, val] in self.dbh.fetchall(): if scope == "GLOBAL": retval[opt] = val else: retval[scope + ":" + opt] = val return retval except sqlite3.Error as e: self.sf.error("SQL error encountered when fetching configuration: " + e.args[0]) # Reset the config to default (clear it from the DB and let the hard-coded # settings in the code take effect.)
def scanConfigSet(self, id, optMap=dict()): qry = "REPLACE INTO tbl_scan_config \ (scan_instance_id, component, opt, val) VALUES (?, ?, ?, ?)" for opt in optMap.keys(): # Module option if ":" in opt: parts = opt.split(':') qvals = [id, parts[0], parts[1], optMap[opt]] else: # Global option qvals = [id, "GLOBAL", opt, optMap[opt]] try: self.dbh.execute(qry, qvals) except sqlite3.Error as e: self.sf.error("SQL error encountered when storing config, aborting: " + e.args[0]) self.conn.commit() # Retreive configuration data for a scan component
def scanInstanceList(self): # SQLite doesn't support OUTER JOINs, so we need a work-around that # does a UNION of scans with results and scans without results to # get a complete listing. qry = "SELECT i.guid, i.name, i.seed_target, ROUND(i.created/1000), \ ROUND(i.started)/1000 as started, ROUND(i.ended)/1000, i.status, COUNT(r.type) \ FROM tbl_scan_instance i, tbl_scan_results r WHERE i.guid = r.scan_instance_id \ AND r.type <> 'ROOT' GROUP BY i.guid \ UNION ALL \ SELECT i.guid, i.name, i.seed_target, ROUND(i.created/1000), \ ROUND(i.started)/1000 as started, ROUND(i.ended)/1000, i.status, '0' \ FROM tbl_scan_instance i WHERE i.guid NOT IN ( \ SELECT distinct scan_instance_id FROM tbl_scan_results WHERE type <> 'ROOT') \ ORDER BY started DESC" try: self.dbh.execute(qry) return self.dbh.fetchall() except sqlite3.Error as e: self.sf.error("SQL error encountered when fetching scan list: " + e.args[0]) # History of data from the scan
def add_vms(self, conn, vms): """ Add vms in the vms table for this tenant. """ tenant_id = self.id vms = [(vm_id, vm_name, tenant_id) for vm_id, vm_name in vms] if vms: try: conn.executemany( "INSERT INTO vms(vm_id, vm_name, tenant_id) VALUES (?, ?, ?)", vms ) conn.commit() except sqlite3.Error as e: logging.error("Error %s when inserting into vms table with vms %s", e, vms) return str(e) return None
def remove_vms(self, conn, vms): """ Remove vms from the vms table for this tenant. """ tenant_id = self.id vms = [(vm_id, tenant_id) for vm_id, _ in vms] try: conn.executemany( "DELETE FROM vms WHERE vm_id = ? AND tenant_id = ?", vms ) conn.commit() except sqlite3.Error as e: logging.error("Error %s when removing from vms table with vms %s", e, vms) return str(e) return None
def replace_vms(self, conn, vms): """ Update vms from the vms table which belong to this tenant. """ tenant_id = self.id vms = [(vm_id, vm_name, tenant_id) for vm_id, vm_name in vms] try: # Delete old VMs conn.execute( "DELETE FROM vms WHERE tenant_id = ?", [tenant_id] ) conn.executemany( "INSERT INTO vms(vm_id, vm_name, tenant_id) VALUES (?, ?, ?)", vms ) conn.commit() except sqlite3.Error as e: logging.error("Error %s when replace vms table with vms %s", e, vms) return str(e) return None
def get_default_datastore(self, conn): """ Get default_datastore url for this tenant Return value: error_msg: return None on success or error info on failure datastore_url: return default_datastore url on success or None on failure """ error_msg, result = auth.get_row_from_tenants_table(conn, self.id) if error_msg: logging.error("Error %s when getting default datastore for tenant_id %s", error_msg, self.id) return str(error_msg), None else: datastore_url = result[auth_data_const.COL_DEFAULT_DATASTORE_URL] logging.debug("auth.data.get_default_datastore: datastore_url=%s", datastore_url) if not datastore_url: # datastore_url read from DB is empty return None, None else: return None, datastore_url
def __connect(self): """ Private function for connecting and setting return type for select ops. Raises a DbConnectionError when fails to connect. """ if self.conn: logging.info("AuthorizationDataManager: Reconnecting to %s on request", self.db_path) self.__close() try: self.conn = sqlite3.connect(self.db_path) except sqlite3.Error as e: logging.error("Failed to connect to DB (%s): %s", self.db_path, e) raise DbConnectionError(self.db_path) # Use return rows as Row instances instead of tuples self.conn.row_factory = sqlite3.Row
def get_row_from_tenants_table(conn, tenant_uuid): """ Get a row from tenants table for a given tenant. Return value: -- error_msg: return None on success or error string -- result: returns a row in tenants table with given tenant_uuid on success, return None on failure """ try: cur = conn.execute( "SELECT * FROM tenants WHERE id=?", (tenant_uuid,) ) except sqlite3.Error as e: logging.error("Error: %s when querying tenants table for tenant %s", e, tenant_uuid) return str(e), None result = cur.fetchone() return None, result
def get_row_from_vms_table(conn, tenant_uuid): """ Get rows from vms table for a given tenant. Return value: -- error_msg: return None on success or error string -- result: returns rows in vms table with given tenant_uuid on success, return None on failure """ try: cur = conn.execute( "SELECT * FROM vms WHERE tenant_id=?", (tenant_uuid,) ) except sqlite3.Error as e: logging.error("Error: %s when querying vms table for tenant %s", e, tenant_uuid) return str(e), None result = cur.fetchall() return None, result
def get_row_from_privileges_table(conn, tenant_uuid): """ Get rows from privileges table for a given tenant Return value: -- error_msg: return None on success or error string -- result: returns rows in privileges table with given tenant_uuid on success, return None on failure """ try: cur = conn.execute( "SELECT * FROM privileges WHERE tenant_id=?", (tenant_uuid,) ) except sqlite3.Error as e: logging.error("Error: %s when querying privileges table for tenant %s", e, tenant_uuid) return str(e), None result = cur.fetchall() return None, result
def __enter__(self): """Context manager __enter__ method. Open DB connection. """ self.connection = None LOGGER.debug('Connecting to DB...') try: self.connection = sqlite3.connect(self.db_name) self.cursor = self.connection.cursor() LOGGER.debug('Connection established.') except sqlite3.Error as e: LOGGER.critical('Could not open DB: {}'.format(self.db_name)) raise return self # end-of-method __enter__
def connect_to_db(self): """ Open a connection to the SQLite DB """ try: self.con = sqlite.connect(self.file) self.cur = self.con.cursor() print 'Connected to', self.file print except sqlite.Error, e: if self.con: self.con.rollback() print 'Error connecting to', self.file print 'Exception follows:' print e print 'Quitting...' sys.exit(1)
def insert_dummy_record(self, exploitability): """ For testing purposes """ if not exploitability: exploitability = 'Exploitable' try: query = "INSERT INTO Crashes(NodeId, Victim, Cpu, EventName, Ip, StackTrace, CrashLabel, Exploitable, FileName) VALUES('Win7', 'Test Victim', 'x86_64', 'Test Event', '10000', 'msvcrt!test+0x414141', 'Example Label', '%s', 'dummy.bmp');" % exploitability self.cur.execute(query) self.con.commit() print 'Inserted dummy record' except sqlite.Error, e: print 'Unable to insert dummy record' print 'Exception follows:' print e
def scanResultEventUnique(self, instanceId, eventType='ALL', filterFp=False): qry = "SELECT DISTINCT data, type, COUNT(*) FROM tbl_scan_results \ WHERE scan_instance_id = ?" qvars = [instanceId] if eventType != "ALL": qry += " AND type = ?" qvars.append(eventType) if filterFp: qry += " AND false_positive <> 1" qry += " GROUP BY type, data ORDER BY COUNT(*)" try: self.dbh.execute(qry, qvars) return self.dbh.fetchall() except sqlite3.Error as e: self.sf.error("SQL error encountered when fetching unique result events: " + e.args[0]) # Get scan logs
def run_query(self, preparedQuery, queryInfo=None): try: if not queryInfo: self.database["cursor"].execute(preparedQuery) else: if (type(queryInfo) == str) or (type(queryInfo) == unicode): queryInfo = (queryInfo,) self.database["cursor"].execute(preparedQuery, queryInfo) result = self.database["cursor"].fetchall() self.database["handle"].commit() if not result: return True else: return result except sqlite3.Error as e: self._error_log("Database Error: %s" % str(e)) return False
def fetchCard(self, cardId): cur = self.conn.cursor() try: cur.execute('SELECT texts.id, texts.name, texts.desc, datas.type, datas.atk, datas.def, datas.level,' ' datas.race, datas.attribute FROM texts ' 'JOIN datas ON texts.id = datas.id ' 'WHERE datas.id=%d' % int(cardId)) except Error as e: print(e) return rows = cur.fetchall() if not rows: print "Rows are empty, dumping deck" return None return rows[0]
def load_into_db(self, table): def callback(response): """ Loads the CSV data contained into the response body and puts it into the appropriate table """ data = itertools.ifilter(lambda x: len(x) == 2, (row.split(',', 1) for row in response.body_as_unicode().split('\r\n')[1:])) cur = self.db_connection.cursor() try: cur.executemany('INSERT INTO %s(pk, data) VALUES (?, ?)' % table, data) except sqlite3.Error: self.db_connection.rollback() raise else: self.db_connection.commit() finally: cur.close() for each in self.finalize_data(table): yield each return callback
def dbuser_get(arg_userid): global dbconn try: dbcursor = dbconn.cursor() dbcursor.execute("SELECT * FROM {tn} WHERE {cn1}='{cv1}'" \ .format(tn=ddl.TBL_USER, cn1=ddl.FLD_USER_ID, cv1=arg_userid)) row = dbcursor.fetchone() if row == None: return None return row except sqlite3.Error as e: app.logger.error("dbuser_update_ts: SELECT {%s} failed, reason: {%s}", arg_userid, repr(e)) return None #======================================== # Update the password in a given user row #========================================
def dbuser_add(arg_userid, arg_password, arg_email): global dbconn if app.debug: app.logger.debug("dbuser_add: arg_userid=%s, arg_email=%s", arg_userid, arg_email) try: dbcursor = dbconn.cursor() stamp = epoch2dbfmt(time.time()) dbcursor.execute("INSERT INTO {tn} ('{cn1}', '{cn2}','{cn3}','{cn4}') VALUES ('{cv1}','{cv2}','{cv3}','{cv4}')" \ .format(tn=ddl.TBL_USER, \ cn1=ddl.FLD_USER_ID, cv1=arg_userid, \ cn2=ddl.FLD_USER_PSWD, cv2=arg_password, \ cn3=ddl.FLD_USER_EMAIL, cv3=arg_email, \ cn4=ddl.FLD_USER_TSTAMP, cv4=stamp)) dbconn.commit() except sqlite3.Error as e: app.logger.error("dbuser_add: INSERT {%s,%s} failed, reason: {%s}", arg_userid, arg_email, repr(e)) return False # Success return True #===================== # Remove a user record #=====================
def dbuser_remove(arg_userid): global dbconn if app.debug: app.logger.debug("dbuser_remove: arg_userid=%s", arg_userid) try: dbcursor = dbconn.cursor() dbcursor.execute("DELETE FROM {tn} WHERE {cn1}='{cv1}'" \ .format(tn=ddl.TBL_USER, \ cn1=ddl.FLD_USER_ID, cv1=arg_userid)) dbconn.commit() except sqlite3.Error as e: app.logger.error("dbuser_remove: DELETE {%s} failed, reason: {%s}", arg_userid, repr(e)) return False # Success return True #======================== # Initialize the database #========================
def dbinit(): global dbconn try: dbcursor = dbconn.cursor() # Delete old database tables dbcursor.execute("DROP TABLE IF EXISTS {tn}".format(tn=ddl.TBL_USER)) # Create the user table dbcursor.execute("CREATE TABLE {tn} ({cn} {ct} PRIMARY KEY)" \ .format(tn=ddl.TBL_USER, cn=ddl.FLD_USER_ID, ct="TEXT")) dbcursor.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}" \ .format(tn=ddl.TBL_USER, cn=ddl.FLD_USER_EMAIL, ct="TEXT")) dbcursor.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}" \ .format(tn=ddl.TBL_USER, cn=ddl.FLD_USER_PSWD, ct="TEXT")) dbcursor.execute("ALTER TABLE {tn} ADD COLUMN '{cn}' {ct}" \ .format(tn=ddl.TBL_USER, cn=ddl.FLD_USER_TSTAMP, ct="TEXT")) except sqlite3.Error, e: oops ("dbinit failed, reason: {%s}", e.args[0]) #============== # Open database #==============
def Execute(self, p_sql): try: v_keep = None if self.v_con is None: self.Open() v_keep = False else: v_keep = True self.v_cur.execute(p_sql) except Spartacus.Database.Exception as exc: raise exc except sqlite3.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc)) finally: if not v_keep: self.Close()
def ExecuteScalar(self, p_sql): try: v_keep = None if self.v_con is None: self.Open() v_keep = False else: v_keep = True self.v_cur.execute(p_sql) r = self.v_cur.fetchone() if r != None: s = r[0] else: s = None return s except Spartacus.Database.Exception as exc: raise exc except sqlite3.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc)) finally: if not v_keep: self.Close()
def InsertBlock(self, p_block, p_tablename, p_fields=None): try: v_columnames = [] if p_fields is None: v_fields = [] for c in p_block.Columns: v_columnames.append(c) v_fields.append(DataField(c)) else: v_fields = p_fields for p in v_fields: v_columnames.append(p.v_name) v_insert = 'begin; ' for r in p_block.Rows: v_insert = v_insert + 'insert into ' + p_tablename + '(' + ','.join(v_columnames) + ') values ' + self.Mogrify(r, v_fields) + '; ' v_insert = v_insert + 'commit;' self.Execute(v_insert) except Spartacus.Database.Exception as exc: raise exc except sqlite3.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc))
def ExecuteScalar(self, p_sql): try: if self.v_con is None: raise Spartacus.Database.Exception('This method should be called in the middle of Open() and Close() calls.') else: self.v_cur.execute(p_sql) r = self.v_cur.fetchone() if r != None: s = r[0] else: s = None return s except Spartacus.Database.Exception as exc: raise exc except sqlite3.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc))
def GetFields(self, p_sql): try: if self.v_con is None: raise Spartacus.Database.Exception('This method should be called in the middle of Open() and Close() calls.') else: v_fields = [] self.v_cur.execute('select * from ( ' + p_sql + ' ) t limit 1') r = self.v_cur.fetchone() if r != None: k = 0 for c in self.v_cur.description: v_fields.append(DataField(c[0], p_type=type(r[k]), p_dbtype=type(r[k]))) k = k + 1 else: k = 0 for c in self.v_cur.description: v_fields.append(DataField(c[0], p_type=type(None), p_dbtype=type(None))) k = k + 1 return v_fields except Spartacus.Database.Exception as exc: raise exc except sqlite3.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc))
def Open(self, p_autocommit=True): try: self.v_con = psycopg2.connect( self.GetConnectionString(), cursor_factory=psycopg2.extras.DictCursor ) self.v_con.autocommit = p_autocommit self.v_cur = self.v_con.cursor() self.v_start = True # PostgreSQL types self.v_cur.execute('select oid, typname from pg_type') self.v_types = dict([(r['oid'], r['typname']) for r in self.v_cur.fetchall()]) if not p_autocommit: self.v_con.commit() self.v_con.notices = DataList() except Spartacus.Database.Exception as exc: raise exc except psycopg2.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc))
def Execute(self, p_sql): try: v_keep = None if self.v_con is None: self.Open() v_keep = False else: v_keep = True self.v_cur.execute(p_sql) except Spartacus.Database.Exception as exc: raise exc except psycopg2.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc)) finally: if not v_keep: self.Close()
def ExecuteScalar(self, p_sql): try: v_keep = None if self.v_con is None: self.Open() v_keep = False else: v_keep = True self.v_cur.execute(p_sql) r = self.v_cur.fetchone() if r != None: s = r[0] else: s = None return s except Spartacus.Database.Exception as exc: raise exc except psycopg2.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc)) finally: if not v_keep: self.Close()
def InsertBlock(self, p_block, p_tablename, p_fields=None): try: v_columnames = [] if p_fields is None: v_fields = [] for c in p_block.Columns: v_columnames.append(c) v_fields.append(DataField(c)) else: v_fields = p_fields for p in v_fields: v_columnames.append(p.v_name) v_values = [] for r in p_block.Rows: v_values.append(self.Mogrify(r, v_fields)) self.Execute('insert into ' + p_tablename + '(' + ','.join(v_columnames) + ') values ' + ','.join(v_values) + '') except Spartacus.Database.Exception as exc: raise exc except psycopg2.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc))
def Execute(self, p_sql): try: v_keep = None if self.v_con is None: self.Open() v_keep = False else: v_keep = True self.v_cur.execute(p_sql) except Spartacus.Database.Exception as exc: raise exc except pymysql.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc)) finally: if not v_keep: self.Close()
def InsertBlock(self, p_block, p_tablename, p_fields=None): try: v_columnames = [] if p_fields is None: v_fields = [] for c in p_block.Columns: v_columnames.append(c) v_fields.append(DataField(c)) else: v_fields = p_fields for p in v_fields: v_columnames.append(p.v_name) v_values = [] for r in p_block.Rows: v_values.append(self.Mogrify(r, v_fields)) self.Execute('insert into ' + p_tablename + '(' + ','.join(v_columnames) + ') values ' + ','.join(v_values) + '') except Spartacus.Database.Exception as exc: raise exc except pymysql.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc))
def ExecuteScalar(self, p_sql): try: v_keep = None if self.v_con is None: self.Open() v_keep = False else: v_keep = True self.v_cur.execute(p_sql) r = self.v_cur.fetchone() if r != None: s = r[0] else: s = None return s except Spartacus.Database.Exception as exc: raise exc except pymysql.Error as exc: raise Spartacus.Database.Exception(str(exc)) except Exception as exc: raise Spartacus.Database.Exception(str(exc)) finally: if not v_keep: self.Close()
