On Saturday, November 22, 2014 10:32:30 PM UTC-8, Chris Angelico wrote:
> On Sun, Nov 23, 2014 at 5:08 PM, llanitedave wrote:
> > 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.
> >
>
> 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=> create table foo (val text primary key);
> CREATE TABLE
> rosuav=> create table bar (val text references foo on delete set null);
> CREATE TABLE
> rosuav=> insert into foo values ('asdf');
> INSERT 0 1
> rosuav=> insert into bar values ('asdf');
> INSERT 0 1
> rosuav=> insert into bar values ('qwer');
> ERROR: insert or update on table "bar" violates foreign key
> constraint "bar_val_fkey"
> DETAIL: Key (val)=(qwer) is not present in table "foo".
> rosuav=> select * from foo;
> val
> ------
> asdf
> (1 row)
>
> rosuav=> select * from bar;
> val
> ------
> asdf
> (1 row)
>
> rosuav=> delete from foo;
> DELETE 1
> rosuav=> select * from foo;
> val
> -----
> (0 rows)
>
> rosuav=> 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
> > 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.
>
> 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
> > 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.
>
> 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
And thanks again, Chris! I'm not too worried about SQLite's limitations on this
particular application, it's just an embedded single-user tool. Workarounds
should be fairly straightforward, as long as I know my own limitations!
For the Django version, it's going to be multi-user and I wouldn't really be
comfortable using anything but Postgres. But yeah, I'll need help, because I'm
a slow learner!
--
https://mail.python.org/mailman/listinfo/python-list
