python类IntegrityError()的实例源码

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_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_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_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_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_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_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_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_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_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 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 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_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 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()