SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

On Saturday, November 22, 2014 9:41:55 PM UTC-8, Chris Angelico wrote:
> On Sun, Nov 23, 2014 at 3:58 PM, Michael Torrie wrote:
> > On 11/22/2014 08:54 PM, llanitedave wrote:
> > 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...
> >
> > Or manually encode it to a UTF-8 byte string (just call .encode() on
> > it).  Sqlite probably only knows about UTF-8 when it comes to unicode.
> 
> Since it was a byte string sent to the database and a Unicode string
> coming back, the solution would be to .decode() the byte string.
> However, I doubt that's the issue; that's being done for you
> implicitly by the lower-level connections. Also, in Python 2:
> 
> >>> u'testbh3' ==  'testbh3'
> True
> 
> So that's not your primary problem. You could try that, but I doubt
> it'll solve anything for you.
> 
> Are you able to switch to Python 3, though? If you are, Unicode issues
> tend to be a lot easier to resolve there.
> 
> ChrisA

You're right.  It ultimately didn't make a difference.  And it makes sense that it wouldn't have, because when I did a query using the same field that got rejected by the foreign key, the query was successful.

The application was working "correctly" earlier (meaning that I could enter and retrieve data with it; being a strictly user application it didn't allow deletes from the GUI), and then I discovered (while cleaning up the user documentation) that I'd neglected to include a couple of relatively important database fields.  Because of SQLite's limited ALTER TABLE capabilities, that mean I had to recreate the tables to add the fields, and in doing so noticed that the table in question didn't even have the foreign key constraint defined.  So ever since I defined that constraint, it hasn't let me save any records on that table from Python.  Although, as I said, when entering the same data through the Sqliteman application, it works fine. That's why I suspected that the problem might be in the Python API for SQLite3.

As for Python3, that's a future possibility.  My next step was to expand the functionality of this particular app, which is intended for use in the field on a tablet or laptop, to a web-app using Django 1.7. WxPython was really a way to get my feet wet on it.  The Django version is using Python 3.4 and Postgresql 9.3.4, and it's still in the early stages -- I broke off of it to correct this mess.

It's in the back of my head to go back to the field version at some point with Python3 and PyQt, but it is not this day.

Anyway, if I can't get this thing straightened out, I may have to just remove the foreign key constraint and rely on application logic to ensure my data integrity.  :(

I do appreciate the help, though Chris.  If nothing else, you've showed me some directions that I needed some extra learning in.