SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

On Saturday, November 22, 2014 6:22:32 PM UTC-8, Chris Angelico wrote:
> On Sun, Nov 23, 2014 at 1:11 PM, llanitedave wrote:
> > logging.info("Related borehole_id is %s, of_borehole is %s", relatedbh, runfields[1])
> >
> > In this case, the displayed data from both is identical -- the logging line comes back as:
> > INFO:Related borehole_id is testbh3, of_borehole is testbh3
> >
> > So the data type is the same, and the content appears to be the same.  So why would there be a foreign key mismatch?  Is it possible that there is some invisible code in the string which isn't being detected by the logging command?  Is this just a quirk of the Sqlite3 implementation in Python that demands foreign keys be integers?
> >
> 
> First thing I'd do, if I'm suspicious of invisible stuff in a string,
> is to change those %s markers to %r. You'll get a quoted string (so
> you can see if there's leading/trailing whitespace), any non-ASCII
> characters will be escaped (assuming this is a byte string in Python
> 2, which seems to be the case), and control characters like newlines
> will become escapes too.
> 
> But I've seen a number of very strange and annoying behaviours out of
> SQLite as regards foreign keys. It seems that sometimes integrity
> checks just aren't being done, and I don't know why. Can you switch to
> a PostgreSQL back-end to see if the problem (a) suddenly disappears,
> (b) suddenly appears at a completely different point, or (c) now has a
> more informative error message? Chances are all you need to do is
> change your import and connection setup, and all the rest will work
> just the same.
> 
> By the way, naming convention: I prefer to use "id" only when it's
> actually a numeric ID. For something like this, I'd call it "name",
> since that's what it is. But that won't be affecting your foreign key.
> 
> ChrisA

Well that DID make a difference!  I used the %r marker, and the logger line gave me back:
"INFO:Related borehole_id is u'testbh3', of_borehole is 'testbh3'"

So it looks like I need to change my foreign key string to a unicode string.  I'll be working on that...