There is a difference between key signatures and chords. While the key signatures obey the circle of fifths, chords within a key signature also have different structural relationships. For example, I connects to vi by their common tones, and IV usually leads to V even though the circle of fifths suggests this move should be mitigated by I (which would be ridiculous in most situations!).
In tonal music (e.g. of the common practice period, or the vast majority of popular music), you will rarely hear a key that has a diminished tonic triad. On the other hand, diminished chords in tonal music are quite common. They mostly arise as vii° in major or (harmonic or melodic) minor keys, or as ii° in minor keys.
Why diminished chords?
The main way vii° comes about is as a dominant substitute. If every tonic were I (in root position) and every dominant V (in root position), the music would get boring quickly. Therefore, composers elongate phrases by moving through inverted chords like I⁶ and V⁶, as well as substitutes like vii°⁶ and its seventh-chord variants.
Another reason to use diminished chords is to highlight its dissonant quality. This can be used to colour sad or dark moments, or as a point of climax. Another typical place that attracts dissonance is right before a cadence, although usually V⁷ is used instead of its diminished cousin vii°⁷.
Of course you are right that because diminished chords are not as stable as major and minor triads, they play more of a background role. But this doesn't mean they are unimportant!¹
The development section of a lot of Bach works not only uses intensification of texture, expansion or contraction of range, etc, but also by harmonic intensification, which often prominently features diminished triads and seventh chords. Already the C major prelude from WTC book I has a diminished chord around its climax² (as well as in other places in the piece), but it's easier to find examples in bigger works as well (especially works in the minor mode).
An example of a more expressive use of diminished chords is the opening chorus of Gluck's Orfeo ed Euridice, which uses the highly expressive quality of scale degree 6 and its harmonisation as vii°⁷ to create a particularly dark and sad tone.
Another example is the 𝒇𝒇𝒇 descending cadenza in Rachmaninov's Prelude in C# minor; it features the chords vii°⁷ i and vii°⁷/iv iv, which again has a prominent role for diminished harmonies. This seems to be a fairly common device for the climax of a minor piece.
¹I would actually argue that the minor mode, especially in its melodic variant used in most classical music, gains its true sadness not from the minor triad, but from the highly emotive lowered 6th scale degree with its harmonisation as vii°⁷, cf. the Gluck example.
²In the original manuscript, it is followed by a harmony that is unusually ambiguous for the time. In new editions, somehow an extra measure is added, that dramatically weakens the harmonic climax.