I'm pretty sure the Picard face palm is a shot from S3 episode "Deja Q". He literally is facepalming at Q.

I think to Picard, Q is just this buffoon with god powers and chooses to use them purely for wind ups. He knows he can’t do jack to stop him but scolds him at every opportunity.

I would have loved one character to just disappear on a massive bender with Q for a while and come back totally fried but just slot back into their spot on the ship

Yeah, whereas Sisko punched Q.

I'm with Sisko on that.

I haven't watched Picard, but I assume the finale is a Qwedding.

Picard doesn't hate him, but Picard understands that Q isn't operating in good faith and is always aimed at undermining humanity.

The problem I would think, as Picard sees it, is there is no reasonable way to do battle with Q or stop him, and so instead of a radical path of eliminating Q's ability to interfere entirely, we're given what appears to be a very tepid, liberal response where we're supposed to work with terrible people who aren't operating in good faith simply because they have power and aren't afraid to use it.

Picard is from a strictly socialist society, and that means at some point, they understood how to deal with people like this, who are operating in bad faith. The new wrinkle is that Q is so powerful, you have to hope that you can just talk him down, because there is no way to remove him from the equation or remove his powers.