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

Groups > comp.lang.python > #81311 > unrolled thread

SQLite3 in Python 2.7 Rejecting Foreign Key Insert

Back to article view | Back to comp.lang.python

Contents

  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

Page 2 of 2 — ← Prev page 1 [2]

#81390

On Sat, 22 Nov 2014 18:11:10 -0800 (PST), llanitedave
<llanitedave@birdandflower.com> declaimed the following:

>I've built a database in SQLite3 to be embedded into a python application using wxPython 2.8.12 and Python 2.7.6.  I'm using Sqliteman to manage the database directly and make changes to the structure when necessary.
>
>One item that's been bugging me is when I'm inserting records into one particular table:
>
>The parent table is called "borehole", and the 'borehole_id' field is TEXT.  It's essentially a name field, because every borehole name is naturally unique, therefore I thought an autoincrementing integer would be superfluous.
>
	Absent an explicit declaration of an auto-increment integer primary
key, SQLite implicitly creates a "rowid" that gets used internally anyway
-- even if you don't see it in a "select * ..." result set. If you define
an auto-increment integer primary key, the internal rowid is not created. 

	So... You might as well use an explicit primary key, and thereby also
save space in related tables as they only need to store an integer rather
than some string field.

	Also note that SQLite3 defaults to using UTF-8 internally (it can be
set for UTF-16BE/LE and maybe some others)... If one of your text strings
was provided using a non-UTF-8 encoding and the other was (possible if
different programs were used to process the data before insertion) they
might visually display the same but be stored differently... Though that
doesn't seem to explain your test "select".



-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#81416

On Saturday, November 22, 2014 6:11:22 PM UTC-8, llanitedave wrote:
> I've built a database in SQLite3 to be embedded into a python application using wxPython 2.8.12 and Python 2.7.6.  I'm using Sqliteman to manage the database directly and make changes to the structure when necessary.
> 
> One item that's been bugging me is when I'm inserting records into one particular table:
> 
> The parent table is called "borehole", and the 'borehole_id' field is TEXT.  It's essentially a name field, because every borehole name is naturally unique, therefore I thought an autoincrementing integer would be superfluous.
> 
> A related field is called "core_run", and its foreign key field is named "of_borehole" -- of course its type is TEXT NOT NULL as well.  It's described in the "DESCRIBE TABLE" feature of Sqliteman as "FOREIGN KEY(of_borehole) REFERENCES borehole(borehole_id)"
> 
> When I use Sqliteman to manually create a record using INSERT INTO core_run VALUES..., it works properly.  However, when I do the same thing, using the same test data, from inside Python, I get the following SQLite error"
> 
> 'foreign key mismatch - "core_run" referencing "borehole"'
> 
> To make sure the core_run.of_borehole and borehole.borehole_id fields are equivalent, I inserted a logging statement just prior to the database cursor.
> 
> [code]
> # retrieve the borehole id field, check it matches with of_borehole field
> db_cursor.execute("select borehole_id from borehole where borehole_id = ?", (runfields[1],))
> relatedbh = db_cursor.fetchone()[0]
> logging.info("Related borehole_id is %s, of_borehole is %s", relatedbh, runfields[1])
> [/code]
> 
> runfields[1] here is the of_borehole field taken from the applications GUI field.
> 
> 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?
> 
> I feel like I've hit a brick wall here.
> 
> Thanks!

OK, I got it working.  VICTORY!

Here's what I did.

The original CREATE TABLE command for the core_run table defined all the fields and then added the foreign key at the bottom of the definition like so:

"FOREIGN KEY(of_borehole) REFERENCES borehole(borehole_id)"

I recreated the table and put the foreign key reference directly into the field definition:

"of_borehole TEXT NOT NULL REFERENCES borehole,"

This is a valid alternative according to the SQLite3 docs, if you don't explicitly define the field name then it references to the primary key that already exists in the referenced table.

And that's all I had to do.  Strange, but it did the trick.

For the enhanced version of this application I will DEFINITELY use an integer primary key for the boreholes table!

[toc] | [prev] | [next] | [standalone]

#81425

On Mon, Nov 24, 2014 at 1:22 PM, llanitedave
<llanitedave@birdandflower.com> wrote:
> I recreated the table and put the foreign key reference directly into the field definition:
>
> "of_borehole TEXT NOT NULL REFERENCES borehole,"
>
> This is a valid alternative according to the SQLite3 docs, if you don't explicitly define the field name then it references to the primary key that already exists in the referenced table.
>
> And that's all I had to do.  Strange, but it did the trick.

That's also perfectly valid according to the SQL spec. I prefer to put
the foreign key up there where possible (obviously it's not possible
with a multi-column FK, but those aren't common). Strange that the
other form doesn't work.

ChrisA

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.python

csiph-web