我们从Python开源项目中，提取了以下50个代码示例，用于说明如何使用psycopg2.IntegrityError()。
def create_profile_hits(cur, feature, gene_id): '''Create profile hit entries for a feature''' detected_domains = parse_domains_detected(feature) for domain in detected_domains: domain['gene_id'] = gene_id cur.execute(""" SELECT gene_id FROM antismash.profile_hits WHERE gene_id = %(gene_id)s AND name = %(name)s AND evalue = %(evalue)s AND bitscore = %(bitscore)s""", domain) ret = cur.fetchone() if ret is None: try: cur.execute(""" INSERT INTO antismash.profile_hits (gene_id, name, evalue, bitscore, seeds) VALUES (%(gene_id)s, %(name)s, %(evalue)s, %(bitscore)s, %(seeds)s)""", domain) except psycopg2.IntegrityError: print(feature) print(domain) raise
def test_error(self): cur = self.conn.cursor() cur.execute("insert into table1 values (%s)", (1, )) self.wait(cur) cur.execute("insert into table1 values (%s)", (1, )) # this should fail self.assertRaises(psycopg2.IntegrityError, self.wait, cur) cur.execute("insert into table1 values (%s); " "insert into table1 values (%s)", (2, 2)) # this should fail as well self.assertRaises(psycopg2.IntegrityError, self.wait, cur) # but this should work cur.execute("insert into table1 values (%s)", (2, )) self.wait(cur) # and the cursor should be usable afterwards cur.execute("insert into table1 values (%s)", (3, )) self.wait(cur) cur.execute("select * from table1 order by id") self.wait(cur) self.assertEqual(cur.fetchall(), [(1, ), (2, ), (3, )]) cur.execute("delete from table1") self.wait(cur)
def test_error(self): cur = self.conn.cursor() cur.execute("insert into table1 values (%s)", (1, )) self.wait(cur) cur.execute("insert into table1 values (%s)", (1, )) # this should fail self.assertRaises(psycopg2.IntegrityError, self.wait, cur) cur.execute("insert into table1 values (%s); " "insert into table1 values (%s)", (2, 2)) # this should fail as well self.assertRaises(psycopg2.IntegrityError, self.wait, cur) # but this should work cur.execute("insert into table1 values (%s)", (2, )) self.wait(cur) # and the cursor should be usable afterwards cur.execute("insert into table1 values (%s)", (3, )) self.wait(cur) cur.execute("select * from table1 order by id") self.wait(cur) self.assertEquals(cur.fetchall(), [(1, ), (2, ), (3, )]) cur.execute("delete from table1") self.wait(cur)
def upsert_edit_project(self,filepath,filename,uuid,version,desc=None,opens_with=None): cursor = self.conn.cursor() matches=re.search(u'(\.[^\.]+)$',filename) file_xtn="" if matches is not None: file_xtn=str(matches.group(1)) else: raise ArgumentError("Filename %s does not appear to have a file extension" % filename) typenum=self.project_type_for_extension(file_xtn,desc=desc,opens_with=opens_with) try: cursor.execute("insert into edit_projects (filename,filepath,type,lastseen,valid) values (%s,%s,%s,now(),true) returning id", (filename,filepath,typenum)) except psycopg2.IntegrityError as e: self.conn.rollback() cursor.execute("update edit_projects set lastseen=now(), valid=true where filename=%s and filepath=%s returning id", (filename,filepath)) result = cursor.fetchone() id = result[0] sqlcmd="update edit_projects set uuid=%s, version=%s where id=%s" cursor.execute(sqlcmd, (uuid,version,id)) self.conn.commit() return id
def log_project_issue(self,filepath,filename,problem="",detail="",desc=None,opens_with=None): cursor = self.conn.cursor() matches=re.search(u'(\.[^\.]+)$',filename) file_xtn = "" if matches is not None: file_xtn=str(matches.group(1)) else: raise ArgumentError("Filename %s does not appear to have a file extension" % filename) typenum=self.project_type_for_extension(file_xtn,desc=desc,opens_with=opens_with) try: cursor.execute("""insert into edit_projects (filename,filepath,type,problem,problem_detail,lastseen,valid) values (%s,%s,%s,%s,%s,now(),false) returning id""", (filename,filepath,typenum,problem,detail)) except psycopg2.IntegrityError as e: print str(e) print traceback.format_exc() self.conn.rollback() cursor.execute("""update edit_projects set lastseen=now(), valid=false, problem=%s, problem_detail=%s where filename=%s and filepath=%s returning id""", (problem,detail,filename,filepath)) #print cursor.mogrify("""update edit_projects set lastseen=now(), valid=false, problem=%s, problem_detail=%s where filename=%s and filepath=%s returning id""", (problem,detail,filename,filepath)) result=cursor.fetchone() id = result[0] self.conn.commit() return id
def __exit__(self, ty, val, tb): end = time.time() # Ignore the problem if we just added a duplicate if ty is None: log('SQL execution in [{}] completed without error. Duration: [{:.3f}]'.format( self.proc, end - self.start), proc=self.proc, v=logging.D) # Handle duplicate entry violations elif (ty is psycopg2.IntegrityError) and self.ignore_duplicates: if (val.pgcode in (errorcodes.UNIQUE_VIOLATION, errorcodes.NOT_NULL_VIOLATION, )): log('SQL execution in [{}] completed. Null or Unique constraint hit [{}]. Duration: [{:.3f}]'.format( self.proc, val.pgerror, end - self.start), proc=self.proc, v=logging.I) return True else: log('Finished SQL execution in [{}] after [{:.3f}] seconds with [{}] error [{}]. Traceback: [{}]'.format( self.proc, end - self.start, ty.__name__, str(val), traceback.format_tb(tb)), proc=self.proc, v=logging.I)
def save_load(jid, load, minions=None): ''' Save the load to the specified jid id ''' with _get_serv(commit=True) as cur: sql = '''INSERT INTO jids (jid, load) VALUES (%s, %s)''' try: cur.execute(sql, (jid, psycopg2.extras.Json(load))) except psycopg2.IntegrityError: # https://github.com/saltstack/salt/issues/22171 # Without this try:except: we get tons of duplicate entry errors # which result in job returns not being stored properly pass
def test_failed_commit(self): # Test that we can recover from a failed commit. # We use a deferred constraint to cause a failure on commit. curs = self.conn.cursor() curs.execute('SET CONSTRAINTS table2__table1_id__fk DEFERRED') curs.execute('INSERT INTO table2 VALUES (2, 42)') # The commit should fail, and move the cursor back to READY state self.assertEqual(self.conn.status, STATUS_BEGIN) self.assertRaises(psycopg2.IntegrityError, self.conn.commit) self.assertEqual(self.conn.status, STATUS_READY) # The connection should be ready to use for the next transaction: curs.execute('SELECT 1') self.assertEqual(curs.fetchone()[0], 1)
def test_missing_column_required(self, db_session, missing_column_required): with pytest.raises(psycopg2.IntegrityError): dallinger.data.ingest_to_model(missing_column_required, dallinger.models.Participant)
def update_users(repository): """Update users of the integration in the database""" if os.environ.get("OVER_HEROKU", False) is not False: # Check if repository exists in database query = r"INSERT INTO Users (repository, created_at) VALUES ('{}', now());" \ "".format(repository) # cursor and conn are bultins, defined in app.py try: cursor.execute(query) conn.commit() except psycopg2.IntegrityError: # If already exists conn.rollback()
def create_user(self, username: str, email: str, password: str): """ Create a new user. This method is called during the registration process. Raises ------ LengthError Raised when password length is less than 8 or greater than 72 characters. Why 72? because bcrypt only works properly till 72. """ if len(password) < 8 or len(password) > 72: raise LengthError("password", "Password length should be between 8 and 72 characters.") if not self._is_valid_email(email): raise ValidationError("Please enter a valid email-id.") with self.conn: with self.conn.cursor() as cur: try: cur.execute( """ INSERT INTO users (user_id, user_name, user_email, user_password_hash, user_timestamp, user_avatar) VALUES (%s, %s, %s, %s, %s, %s) """, (snowflake.generate(), username, email, self._hash_password(password), datetime.utcnow(), self._hash_email(email)) ) return True # For now, this happens when the unique email constraint # is violated. except psycopg2.IntegrityError: print("This email_id already exists. Sorry bruh!") return False # TODO: verify_user is probably better off in `auth.py`
def perform_group_create(self, intent): payload = '{"admins": [], "users": []}' async with self.connection.acquire() as conn: async with conn.cursor() as cur: try: await cur.execute("INSERT INTO groups VALUES (%s, %s);", (intent.name, payload)) except IntegrityError: raise GroupAlreadyExist('Group already exist.')
def perform_pubkey_add(self, intent): async with self.connection.acquire() as conn: async with conn.cursor() as cur: try: await cur.execute("INSERT INTO pubkeys VALUES (%s, %s);", (intent.id, intent.key.decode())) except IntegrityError: raise PubKeyError('Identity `%s` already has a public key' % intent.id)
def perform_privkey_add(self, intent): async with self.connection.acquire() as conn: async with conn.cursor() as cur: try: await cur.execute("INSERT INTO privkeys VALUES (%s, %s);", (intent.hash, intent.cipherkey)) except IntegrityError: raise PrivKeyHashCollision('Hash collision, change your password and retry.')
def copy_table(conn_in, conn_out, table_name, constraint=None): cursor_in = conn_in.cursor() #Check that table exists cursor_in.execute( "SELECT name FROM sqlite_master WHERE type='table' AND name='%s';" % table_name ) if cursor_in.fetchone(): cursor_in.execute( "SELECT * FROM \"%s\"%s;" % (table_name, "" if constraint is None else " WHERE " + constraint) ) with conn_out.cursor() as cursor_out: for record in cursor_in: values = record_to_string(record) try: cursor_out.execute( "INSERT INTO \"%s\" VALUES (%s);" % (table_name, values) ) except psycopg2.IntegrityError as e: conn_out.rollback() if e.pgcode == '23505': sys.stderr.write( "ERROR: GeoPackage seems to be already imported. " "Error message was: '%s'.\n" % e.message ) sys.exit(1) except Exception as e: conn_out.rollback() sys.stderr.write( "ERROR: Input doesn't seem to be a valid GeoPackage. " "Error message was: '%s'.\n" % e.message ) sys.exit(1)
def find_by_id(self, _id, attempt = 0): with self.conn.cursor() as cur: try: cur.execute( """SELECT id FROM page WHERE url = '{}'""".format(_id) ) return cur.fetchone() except (psycopg2.IntegrityError, psycopg2.InternalError) as err: if attempt < 5: return self.find_by_id(_id, attempt + 1) else: raise err
def upsert_file_record(self,filepath,filename,statinfo,mimetype,ignore=None): cursor=self.conn.cursor() self.conn.commit() safe_filepath = filepath.decode('utf-8', 'strict') safe_filename = filename.decode('utf-8', 'strict') try: cursor.execute("insert into files (filename,filepath,last_seen) values (%s,%s,now()) returning id", (safe_filename,safe_filepath)) except psycopg2.IntegrityError as e: self.conn.rollback() cursor.execute("update files set last_seen=now() where filename=%s and filepath=%s returning id,ignore", (safe_filename, safe_filepath)) result=cursor.fetchone() id=result[0] try: if result[1] == True: ignore = True except Exception as e: logging.warning("An error occurred: " + str(e) + " trying to get ignore flag") sqlcmd="update files set mtime={mt}, atime={at}, ctime={ct}, size=%s, owner=%s, gid=%s, mime_type=%s where id=%s".format( mt="(SELECT TIMESTAMP WITH TIME ZONE 'epoch' + "+str(statinfo.st_mtime)+" * INTERVAL '1 second')", at="(SELECT TIMESTAMP WITH TIME ZONE 'epoch' + "+str(statinfo.st_atime)+" * INTERVAL '1 second')", ct="(SELECT TIMESTAMP WITH TIME ZONE 'epoch' + "+str(statinfo.st_ctime)+" * INTERVAL '1 second')", ) cursor.execute(sqlcmd, (statinfo.st_size,statinfo.st_uid,statinfo.st_gid,mimetype,id)) if ignore is not None: cursor.execute("update files set ignore={ign} where id={id}".format( ign=ignore, id=id )) self.conn.commit()
def upsert_prelude_project(self,path=None,filename=None,uuid=None,version=None,nclips=None): cursor=self.conn.cursor() self.conn.commit() #if uuid is None: # raise DataError("You need to pass a valid uuid") try: sqlcmd = """insert into prelude_projects (filepath,filename,uuid,version,clips,lastseen) values (%s,%s,%s,%s,%s,now()) returning id""" cursor.execute(sqlcmd,(path,filename,uuid,version,nclips)) except psycopg2.IntegrityError as e: #if we violate unique keys, try to update on filename self.conn.rollback() try: sqlcmd = """update prelude_projects set filepath=%s, filename=%s, uuid=%s, version=%s, clips=%s, lastseen=now() where filepath=%s and filename=%s returning id""" cursor.execute(sqlcmd,(path,filename,uuid,version,nclips,path,filename)) except psycopg2.IntegrityError as e: #if that causes a violation, try to update on uuid self.conn.rollback() sqlcmd = """update prelude_projects set filepath=%s, filename=%s, uuid=%s, version=%s, clips=%s, lastseen=now() where uuid=%s returning id""" cursor.execute(sqlcmd,(path,filename,uuid,version,nclips,uuid)) self.conn.commit() result=cursor.fetchone() return result[0] #return id of inserted row
def upsert_prelude_clip(self,project_ref=None,asset_name=None,asset_relink_skipped=None,asset_type=None, uuid=None,created_date=None,drop_frame=None,duration=None,file_path=None,frame_rate=None, import_date=None,parent_uuid=None,start_time=None): cursor=self.conn.cursor() self.conn.commit() try: sqlcmd="""insert into prelude_clips (asset_name,asset_relink_skipped,asset_type,class_id,created_date,drop_frame, duration_text,file_path,frame_rate,import_date,project,start_time,parent_id) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) returning id """ cursor.execute(sqlcmd, (asset_name,asset_relink_skipped,asset_type,uuid,created_date,drop_frame,duration,file_path, frame_rate,import_date,parent_uuid,start_time,project_ref)) except psycopg2.IntegrityError as e: #if e.startswith('duplicate key'): self.conn.rollback() sqlcmd = """update prelude_clips set asset_name=%s, asset_relink_skipped=%s, asset_type=%s, created_date=%s, drop_frame=%s, duration_text=%s, frame_rate=%s, import_date=%s, project=%s, start_time=%s, parent_id=%s where class_id=%s and file_path=%s returning id""" cursor.execute(sqlcmd,(asset_name,asset_relink_skipped,asset_type,created_date,drop_frame,duration, frame_rate,import_date,parent_uuid,start_time,project_ref,uuid,file_path)) self.conn.commit() result=cursor.fetchone() return result[0]
def advance_round(teams, services): """Advance the round: update results, truncate the active_flags table and store new flags in the database for each team and service.""" with db_conn.cursor() as cur: # advance the round and clear the flag tables try: cur.execute('SELECT * FROM switch_round()') rnd = cur.fetchone()['switch_round'] except psycopg2.Error as e: logger.critical(("Error while incrementing the round, " "aborting: {}").format(e)) abort() # commit the stored procedure operations (probably not needed) db_conn.commit() logger.info("Round {} started".format(rnd)) # generate and insert the new flags to the database cur = db_conn.cursor() for service in services: for team in teams: inserted = False while not inserted: flag = utils.generate_flag(config['FLAG_PREFIX'], config['FLAG_SUFFIX'], config['FLAG_CHARS'], config['FLAG_LENGTH']) try: cur.execute(( 'INSERT INTO flags (flag, team_id, service_id, round) ' 'VALUES (%s, %s, %s, %s)'), (flag, team.id, service.id, rnd)) except psycopg2.IntegrityError: logger.warning('Duplicate flag, generating a new one') except psycopg2.Error as e: logger.critical(('Error while adding a new flag to the ' 'database, aborting: {}').format(e)) abort() else: inserted = True logger.debug(('New flag just added to the database: {}').format(flag)) db_conn.commit() cur.close()
def refresh_api_credentials(formdata): """returns new expiry if refresh_token/client_id/client_secret correct and valid""" client_id = formdata.get('client_id') client_secret = formdata.get('client_secret') refresh_token = formdata.get('refresh_token') if None in [client_id, client_secret, refresh_token]: return {'error':'invalid_token'} else: db = get_db() cur = db.cursor() cur.execute('SELECT id,userid from api_users WHERE refresh_token = '+app.sqlesc+' AND clientid = (SELECT id FROM api_clients WHERE key = '+app.sqlesc+' AND secret = '+app.sqlesc+')',(refresh_token,client_id,client_secret)) result = cur.fetchall() if len(result) == 0: return {'error':'bad_refresh_token'} elif len(result) != 1: return {'internal_error':'multiple_users_returned'} else: # perform the checking for API key eligibility... set_api_user(result[0][1]) eligible = check_api_eligibility() if eligible: for i in range(100): # try 100 times to insert new uuids; if fails 100 times, something is seriously wrong! try: token = str(uuid.uuid4()) refresh_token = str(uuid.uuid4()) expires_in = 3600 expiry = int(time.time())+expires_in cur.execute('UPDATE api_users SET token = '+app.sqlesc+', refresh_token = '+app.sqlesc+', expiry = '+app.sqlesc+' WHERE id = '+app.sqlesc,(token,refresh_token,expiry,result[0][0])) db.commit() return {'token':token,'refresh_token':refresh_token,'expires_in':expires_in} except psycopg2.IntegrityError: db.rollback() else: return {'error':'no_api_access'} return {'internal_error':'unable_to_generate_new_unique_keys'}
def _get_init_db_session(self): """ ???session,????session???session?? ??????sessionid :raise gen.Return: session_key, ??? """ while True: session_key = get_random_string() try: yield self.db.execute("INSERT INTO session (session_key, session_data) VALUES (%s,%s)", (session_key, Json({}))) self.set_cookie('sessionid', session_key) raise gen.Return(session_key) except IntegrityError: continue
def ensureKey(self, key, cur): try: cur.execute('INSERT INTO cvdata (key) values (%s)', (key, )) except psycopg2.IntegrityError: pass
def execute(self, statement, values=None, commit=1): """ Runs a synchronized database query, ignoring any result rows. Automatically re-opens a troubled connection, and handles errors. """ cursor = None try: cursor = self.cursor() cursor.execute(statement, values) LOGGER.debug("Executed: %s", cursor.query) if commit: try: self.db.commit() except Exception: LOGGER.critical("Failed to commit") except psycopg2.IntegrityError: LOGGER.critical("Database integrity error, throwing away update", exc_info=True) LOGGER.debug("Tried to execute: %s", cursor.query) if commit: self.db.rollback() except Exception: LOGGER.critical("Could not execute statement: %s", cursor.query if cursor else statement, exc_info=True) if commit: self.db.rollback() raise DbError()
