Path: csiph.com!usenet.pasdenom.info!dedibox.gegeweb.org!gegeweb.eu!nntpfeed.proxad.net!proxad.net!feeder1-1.proxad.net!hq-usenetpeers.eweka.nl!hq-usenetpeers.eweka.nl!bcyclone02.am1.xlned.com!bcyclone02.am1.xlned.com!newsfeed.xs4all.nl!newsfeed2a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail Return-Path: X-Original-To: python-list@python.org Delivered-To: python-list@mail.python.org X-Spam-Status: OK 0.000 X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'subject:Python': 0.06; '(b)': 0.07; '(so': 0.07; 'postgresql': 0.07; 'suddenly': 0.07; 'string': 0.09; '%s",': 0.09; 'back-end': 0.09; 'key.': 0.09; 'subject:2.7': 0.09; 'cc:addr:python-list': 0.11; 'python': 0.11; '%r.': 0.16; '%s,': 0.16; '(assuming': 0.16; '23,': 0.16; 'detected': 0.16; 'escapes': 0.16; 'from:addr:rosuav': 0.16; 'from:name:chris angelico': 0.16; 'invisible': 0.16; 'keys.': 0.16; 'naming': 0.16; 'newlines': 0.16; 'non-ascii': 0.16; 'quoted': 0.16; 'sqlite': 0.16; 'do,': 0.16; 'wrote:': 0.18; 'seems': 0.21; 'appears': 0.22; 'import': 0.22; 'cc:addr:python.org': 0.22; 'error': 0.23; '(a)': 0.24; "aren't": 0.24; 'byte': 0.24; 'setup,': 0.24; 'string,': 0.24; 'why.': 0.24; 'cc:2**0': 0.24; "i've": 0.25; 'logging': 0.26; 'switch': 0.26; 'header:In-Reply-To:1': 0.27; 'rest': 0.29; '(c)': 0.29; 'characters': 0.30; 'message-id:@mail.gmail.com': 0.30; "i'm": 0.30; 'code': 0.31; 'too.': 0.31; 'informative': 0.31; 'keys': 0.31; 'stuff': 0.32; "i'd": 0.34; 'problem': 0.35; 'connection': 0.35; 'something': 0.35; 'case,': 0.35; 'but': 0.35; 'received:google.com': 0.35; 'there': 0.35; 'done,': 0.36; 'possible': 0.36; 'being': 0.38; 'sometimes': 0.38; 'checks': 0.38; 'nov': 0.38; 'same.': 0.38; 'pm,': 0.38; 'is.': 0.60; 'first': 0.61; 'back': 0.62; "you'll": 0.62; 'become': 0.64; 'more': 0.64; 'different': 0.65; 'foreign': 0.74; 'integrity': 0.74; 'as:': 0.81; 'same,': 0.91; 'suspicious': 0.91; 'to:none': 0.92 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:cc :content-type:content-transfer-encoding; bh=uiJ2vfItsS0yEzfKg3bTAwRregyBRNFlbjt0UPVlLeE=; b=vK8crpEtRX6PODCN2cNcEiDIaB3rdpCenX7O05fHIoteUdiIr55BMiQBFGBDPfUVnz cruSkxsYemrtDF1KtXRmvSQf42fxaC0725ALKPiSr0KlcEjTGHQoB1Q8UMyzkcElV4H0 TL+HSwOssbs1lAfKZxT+x3EYbI16VAwQ3fUEJL6GzC6/49L3qlLirFIXZHOhtjNKihYG Dh7SiK+Jza8RLn51bW3sK3iE5Mj8uUkRI35//UYUxAyw9zV4TYOv2iuooYKFe7uujlay STLREx8vUetRlZ8Hd0aZGfPrGF+fb8vJmwY44S97RB5VWmTbkXqlBLpHpvWCjh7VSC2x O1wQ== MIME-Version: 1.0 X-Received: by 10.107.17.79 with SMTP id z76mr11624401ioi.27.1416709330865; Sat, 22 Nov 2014 18:22:10 -0800 (PST) In-Reply-To: References: Date: Sun, 23 Nov 2014 13:22:10 +1100 Subject: Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert From: Chris Angelico Cc: "python-list@python.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-BeenThere: python-list@python.org X-Mailman-Version: 2.1.15 Precedence: list List-Id: General discussion list for the Python programming language List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Newsgroups: comp.lang.python Message-ID: Lines: 37 NNTP-Posting-Host: 2001:888:2000:d::a6 X-Trace: 1416709340 news.xs4all.nl 2892 [2001:888:2000:d::a6]:49934 X-Complaints-To: abuse@xs4all.nl X-Received-Body-CRC: 2006538164 X-Received-Bytes: 5760 Xref: csiph.com comp.lang.python:81314 On Sun, Nov 23, 2014 at 1:11 PM, llanitedave wrote: > logging.info("Related borehole_id is %s, of_borehole is %s", relatedbh, r= unfields[1]) > > In this case, the displayed data from both is identical -- the logging li= ne 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 s= ome 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