Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.python > #81324

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Newsgroups comp.lang.python
Date 2014-11-22 19:54 -0800
References <ca987fb8-157d-4612-bafa-5da2da2920e0@googlegroups.com> <mailman.16153.1416709340.18130.python-list@python.org>
Message-ID <5d474a87-e2a5-43d3-b07c-4a1194880b4d@googlegroups.com> (permalink)
Subject Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert
From llanitedave <llanitedave@birdandflower.com>

Show all headers | View raw


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...

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread


Thread

SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 18:11 -0800
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 13:22 +1100
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 19:54 -0800
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Michael Torrie <torriem@gmail.com> - 2014-11-22 21:58 -0700
      Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 16:41 +1100
        Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:08 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 17:32 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-22 22:46 -0800
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Christian Gollwitzer <auriocus@gmx.de> - 2014-11-23 10:55 +0100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-23 10:21 +0200
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 17:29 -0800
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 19:31 +1100
            Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Jussi Piitulainen <jpiitula@ling.helsinki.fi> - 2014-11-23 11:40 +0200
              Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-23 20:53 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 13:01 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-24 10:05 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-24 13:44 -0500
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 10:17 +1100
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert "Frank Millman" <frank@chagford.com> - 2014-11-25 07:31 +0200
          Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-25 17:40 +1100
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-11-23 11:58 -0500
  Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert llanitedave <llanitedave@birdandflower.com> - 2014-11-23 18:22 -0800
    Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert Chris Angelico <rosuav@gmail.com> - 2014-11-24 15:49 +1100

csiph-web