Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
MIME-Version: 1.0
In-Reply-To: <ea4e3af8-f173-4706-ba4e-4eca9d701e40@googlegroups.com>
References: <ca987fb8-157d-4612-bafa-5da2da2920e0@googlegroups.com> <mailman.16153.1416709340.18130.python-list@python.org> <5d474a87-e2a5-43d3-b07c-4a1194880b4d@googlegroups.com> <54716987.7040301@gmail.com> <mailman.16159.1416721303.18130.python-list@python.org> <ea4e3af8-f173-4706-ba4e-4eca9d701e40@googlegroups.com>
Date: Sun, 23 Nov 2014 17:32:16 +1100
Subject: Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert
From: Chris Angelico <rosuav@gmail.com>
Cc: "python-list@python.org" <python-list@python.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.16161.1416724339.18130.python-list@python.org>
Lines: 149
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:81335

On Sun, Nov 23, 2014 at 5:08 PM, llanitedave
<llanitedave@birdandflower.com> wrote:
> The application was working "correctly" earlier (meaning that I could ent=
er and retrieve data with it; being a strictly user application it didn't a=
llow deletes from the GUI), and then I discovered (while cleaning up the us=
er documentation) that I'd neglected to include a couple of relatively impo=
rtant database fields.  Because of SQLite's limited ALTER TABLE capabilitie=
s, that mean I had to recreate the tables to add the fields, and in doing s=
o noticed that the table in question didn't even have the foreign key const=
raint defined.  So ever since I defined that constraint, it hasn't let me s=
ave any records on that table from Python.  Although, as I said, when enter=
ing 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.
>

Entirely possible. I never did track down the actual cause of the
SQLite3 issues my students were having; though I suspect it's not
purely a Python API issue. I tried to demonstrate the concept of
foreign keys using the sqlite3 command line tool, and did a sequence
of commands which ought to have failed, but didn't. Let's see if I can
recreate this:

rosuav@sikorsky:~$ sqlite3
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (val text primary key);
sqlite> create table bar (val text references foo on delete set null);
sqlite> insert into foo values ('asdf');
sqlite> insert into bar values ('asdf');
sqlite> insert into bar values ('qwer');
sqlite> select * from foo;
asdf
sqlite> select * from bar;
asdf
qwer
sqlite> delete from foo;
sqlite> select * from foo;
sqlite> select * from bar;
asdf
qwer

So the foreign key is being completely ignored. If I do the same
commands in PostgreSQL, I get errors at appropriate places:

rosuav@sikorsky:~$ psql
psql (9.3.5)
Type "help" for help.

rosuav=3D> create table foo (val text primary key);
CREATE TABLE
rosuav=3D> create table bar (val text references foo on delete set null);
CREATE TABLE
rosuav=3D> insert into foo values ('asdf');
INSERT 0 1
rosuav=3D> insert into bar values ('asdf');
INSERT 0 1
rosuav=3D> insert into bar values ('qwer');
ERROR:  insert or update on table "bar" violates foreign key
constraint "bar_val_fkey"
DETAIL:  Key (val)=3D(qwer) is not present in table "foo".
rosuav=3D> select * from foo;
 val
------
 asdf
(1 row)

rosuav=3D> select * from bar;
 val
------
 asdf
(1 row)

rosuav=3D> delete from foo;
DELETE 1
rosuav=3D> select * from foo;
 val
-----
(0 rows)

rosuav=3D> select * from bar;
 val
-----

(1 row)


PostgreSQL is a lot more chatty, but what's significant here is that
it won't let me insert into the referring table when there's no row in
the referent. Also, when I delete the referred-to row, the referring
row's key gets correctly set to NULL (like I specified in the
constraint definition).

I don't know if there's a way to tell SQLite "hey, I want you to
actually take notice of foreign keys, tyvm", as there's nothing
obvious in the .help command output; but even if there is, I don't
know why that isn't the default. Maybe there can be a way to say
"ignore foreign key constraints for efficiency", but frankly, I'd
rather have constraints actually checked - if you want to cheat them
away, actually drop the constraints, don't have the database silently
ignore them.

> 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 re=
ally 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 o=
f 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.

Cool. There are several GUI toolkits for Python, and I know multiple
of them do support Py3; I can't say which is the best, as I don't do
my GUI programming in Python generally. But definitely try to use
Python 3 if you can; and try to use PostgreSQL if you can, too.
SQLite3 may be the light-weight option, but as you're seeing, it does
sometimes take shortcuts; switching to a more full-featured database
may be worth doing permanently, or at least for development (think of
it like turning on a bunch of assertions).

> Anyway, if I can't get this thing straightened out, I may have to just re=
move 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 m=
e some directions that I needed some extra learning in.

My pleasure! Databasing is well worth studying up on; the better laid
out your table structure, the easier your coding will be - and more
importantly, the easier your data debugging will be. A quick error
message about a foreign key violation can save you hours or weeks of
headaches down the track when you discover that, for the past year,
you had two customers with account number 142857... and then find that
there are many such pairs of duplicates, because your
application-level code had a concurrency/race issue, and the database
wasn't protecting you... This sounds contrived, but it's not
unfeasible; I've seen some crazy problems in Pastel Accounting, which
(back in the 1990s) used a non-SQL BTrieve back-end with terrible data
integrity checking. My dad and I spent many hours wrestling with
strange issues, and one of my favourite solutions was "Let's just
import that into DB2 real quick, so we can do SQL queries on it". When
your acchistl.dat file (that's Accounting, Invoice History, Lines - as
opposed to acchisth.dat, which is invoice headers) is 256MB, you don't
want to step manually through it.

Good luck with the project. If you need help, you know where to find us!

ChrisA