I'd like to connect iPhoto on my MBP to my photo stream and my wife's photo stream. We each have our own Apple ID and iCloud account. Can iPhoto connect to multiple iCloud photo streams?
Currently it can't. Photo Stream settings are detected via the iCloud preference pane and is global to your machine. Until it can support multiple iCloud accounts, iPhoto can only connect to one Photo Stream at a time. Since it's only a temporary store of the last photos taken I am assuming Apple will most likely not make this possible.
I normally move my photos from the Photo Stream to Flickr to achieve this since you can have multiple Flickr accounts.
Look into Dropbox with photo upload if you want to merge photos from multiple devices into one collective stream. From there you can use the Mac client and Folder Actions to automate importing, processing and other tasks as needed.
Family Sharing is not a good option because you manually need to select the photos you share.
If you want you and your wife to have access to all the same photos, the solution is to use the same iCloud account for photo sharing for both of you. This also means you only need to pay for iCloud storage once.
What makes this feasible is that you can then setup your wife with a separate iCloud account for Notes, Email, Calendar, etc under Mail, Contacts, and Calendar. So, you can just share Photos.
Note, though, that you will want to turn off her iCloud access to Passbook, Safari, and Keychain, as you probably don't want to share those. I leave on Backup, so that all Backups are going to the primary account (mine), and we don't need to pay for more storage for her.
Finally, this meant that we needed to use Find My Phone for location sharing rather than Find My Friends.
Obviously, Apple should add an option to Family Sharing to share 100% of photos. But until they do, this is a workable scenario.