Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Tue, Nov 25, 2014 at 4:31 PM, Frank Millman  wrote:
>
> "Dennis Lee Bieber"  wrote in message
> news:lrr67al6ppa852agu9rq2dstqtue17i...@4ax.com...
>> We must have a different impression of what a "schema" consists of. As
>> I learned it, the "schema" basically came down to the statements defining
>> the form of the data, the rules (triggers) on it, and the relationships
>> between data items. http://en.wikipedia.org/wiki/Database_schema
>>
>
> I also find it confusing. The same word is used by different RDBMS's to mean
> different things.

They're not entirely different; in both cases, your database schema
is, broadly speaking, the structure of your tables and the
relationships between them. Consider the "schema" to be the design
document that lays out your data. Then you have two derivative forms
of it: one is what's usually called DDL, and is executable statements;
the other derives from the concept that one schema represents one
logical dataset, so another schema would represent a different logical
dataset, so you have named schemas.

Since SQLite3 doesn't have the concept of named schemas, it uses
"schema" solely in the sense of DDL. The SQL spec (and therefore
PostgreSQL) uses it in both senses.

> I find that schemas gives me the advantages of both without the
> disadvantages.

Yep. So, use a real database, with better compliance to the spec :)
What you have is similar to what I've done in a number of different
database systems; schemas to separate related sets of tables into
namespaces, and other schemas to store global/shared data, all inside
a single database.

ChrisA
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Frank Millman]

"Dennis Lee Bieber"  wrote in message 
news:lrr67al6ppa852agu9rq2dstqtue17i...@4ax.com...
> On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" 
> declaimed the following:
>
>>
>>Originally I supported two databases - PostgreSQL and Sql Server. They 
>>both
>>have a concept called 'schemas', which handles my requirement elegantly -
>>each company's data is stored in its own schema.
>>
>
> We must have a different impression of what a "schema" consists of. As
> I learned it, the "schema" basically came down to the statements defining
> the form of the data, the rules (triggers) on it, and the relationships
> between data items. http://en.wikipedia.org/wiki/Database_schema
>

I also find it confusing. The same word is used by different RDBMS's to mean 
different things.

When using sqlite3 in interactive mode, you can enter '.tables' to list all 
tables in the database, and you can enter '.schema {table_name}' to view the 
original DDL statement used to create the table.

>From the PostgreSQL docs -

"""
A database contains one or more named schemas, which in turn contain tables. 
Schemas also contain other kinds of named objects, including data types, 
functions, and operators. The same object name can be used in different 
schemas without conflict; for example, both schema1 and myschema can contain 
tables named mytable. Unlike databases, schemas are not rigidly separated: a 
user can access objects in any of the schemas in the database he is 
connected to, if he has privileges to do so.
"""

>
> For a client-server database system, with ability to restrict access
> based upon user accounts/host address, there are a number of ways to
> address the separation.
>
> Either each user account (company) gets its own database in the server
> -- possibly using the same pre-packaged DML to define the tables/etc --  
> and
> one uses the access control system to prevent an account from even knowing
> there are other databases out there... Or... there is one set of tables
> (one database) containing data from all the companies -- wherein each 
> table
> has a field identifying the company, and ALL accesses to the data is via
> views that incorporate a select clause restricting the data to the current
> logged in company.
>

I find that schemas gives me the advantages of both without the 
disadvantages.

The disadvantage of using separate databases is that I want the flexibility 
to allow one company to access data from another company, under controlled 
conditions, specified by various parameters and permissions. It might be 
possible to accesss a remote database with some systems, I don't know, but 
schemas make it easy.

The disadvantage of using a single database, with a column specifying the 
company, is that I want the flexibility to manage different companies, with 
different data requirements, within the same system. A common database would 
result in a mish-mash of different tables, some of which only relate to one 
company or group of companies,  and other tables relate to others. Using 
schemas, I create one schema per company, which contains only the data 
relating to that company.

Frank



-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Tue, Nov 25, 2014 at 5:44 AM, Dennis Lee Bieber
 wrote:
> On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" 
> declaimed the following:
>
>>I will explain why such a feature would have been useful for me.
>>
>>My accounting system handles multiple companies. I needed to figure out a
>>way to keep each one's data separate from the others.
>>
>>Originally I supported two databases - PostgreSQL and Sql Server. They both
>>have a concept called 'schemas', which handles my requirement elegantly -
>>each company's data is stored in its own schema.
>>
>
> We must have a different impression of what a "schema" consists of. As
> I learned it, the "schema" basically came down to the statements defining
> the form of the data, the rules (triggers) on it, and the relationships
> between data items. http://en.wikipedia.org/wiki/Database_schema
>
> By that definition, SQLite3 does support "schemas" (it's all the DDL
> statements used to create the database less the data itself -- which are
> DML). So does Firebird, MySQL, Access/JET

The sense here is one of qualified names. In DB2 and PostgreSQL, you
can create a database, and then within that, create schemas. You
choose a database when you connect, and then you can write code like
this:

select customer_name, invoice_date from accts.invoices join
crm.customers on (customer_id = crm.customers.id) where blah blah

The table names are qualified with their schema names. You can also
have a "default schema", which is the one used for unqualified table
names, which lets you pick one from a group of related data sets: for
example, Frank's accounting system would probably make use of that,
such that the code needn't care after startup. It'll just select a
default schema (or, in PostgreSQL, alter the search_path), and then
most of the code will just use unqualified names. Any data that's not
company-specific (maybe the application stores UI config data in the
database too) can be stored in a dedicated schema and referenced by
qualified name.

Technically, a table can be referred to as
database_name.schema_name.table_name (and then you can add
.column_name after that, but I have NEVER seen anyone use the full
four-parter!), but in PostgreSQL, the database_name, if present, must
be the one that you connected to. In MySQL, there these things that
are kinda databases and kinda schemas, are referred to by both names,
and in terms of data separation, primarily act like schemas. In
SQLite3, there are basically no schemas, AIUI, but you can use
databases to do something similar - with the originally-mentioned
limitation.

ChrisA
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Frank Millman]

"Dennis Lee Bieber"  wrote in message 
news:8d747a5biq4rc559tvgju088508bp0o...@4ax.com...
> On Sun, 23 Nov 2014 10:21:48 +0200, "Frank Millman" 
> declaimed the following:
>
>>
>>
>>The default is for sqlite3 to ignore foreign key contraints.
>>
[...]
>>
>>Unfortunately it has a limitation, which they acknowledge but they say is
>>unlikely to be addressed. You can access more than one database 
>>concurrently
>>by using the 'attach' command, and qualifying a remote tablename as
>>{database} dot {tablename}. You can then include the remote table in any 
>>sql
>>command. However, it will not enforce foreign key constraints across
>>databases.
>>
> Seems acceptable-- as such enforcement would require it to
> automatically reattach (likely in both directions) any/all attached
> databases when a change is made. Imagine the thrashing if one has some
> master database in a read-only mode for most users, and each user has some
> child database referencing some field in the master... And the owner of 
> the
> master database deletes a record...
>
> Are they suddenly going to have to have update capability on every
> user's database to check/enforce multi-db references?

I will explain why such a feature would have been useful for me.

My accounting system handles multiple companies. I needed to figure out a 
way to keep each one's data separate from the others.

Originally I supported two databases - PostgreSQL and Sql Server. They both 
have a concept called 'schemas', which handles my requirement elegantly - 
each company's data is stored in its own schema.

When I decided to add sqlite3 as a third option, I found that they did not 
have 'schemas'. However, I could get very close by creating multiple 
databases and using 'attach' to access them from one central database.

Overall I have been very impressed with how 'complete' sqlite3 is. The 
limitation mentioned above is one of the few compromises I have had to make. 
The sql syntax used by PostgreSQL and sqlite3 is virtually identical. I 
found more variations between PostgreSQL and Sql Server.

Frank



-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Mon, Nov 24, 2014 at 1:22 PM, llanitedave
 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
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

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!
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

On Sunday, November 23, 2014 12:22:30 AM UTC-8, Frank Millman wrote:
> "Chris Angelico"  wrote in message 
> news:captjjmp4y5zowwn5yftjutko4h5jvtqlantwqepa6b35xnd...@mail.gmail.com...
> >
> > 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.
> 
> The default is for sqlite3 to ignore foreign key contraints.
> 
> To enable them, add the following -
> 
> pragma foreign_keys = on;
> 
> It works for me.
> 
> Unfortunately it has a limitation, which they acknowledge but they say is 
> unlikely to be addressed. You can access more than one database concurrently 
> by using the 'attach' command, and qualifying a remote tablename as 
> {database} dot {tablename}. You can then include the remote table in any sql 
> command. However, it will not enforce foreign key constraints across 
> databases.
> 
> Frank Millman

Well in this case the foreign keys ARE on, otherwise it wouldn't be throwing up 
a foreign key  conflict.  I've got one more thing to try, but so far I still 
haven't figured out why it's not accepting the foreign key data.  The only 
thing I can conclude so far is that it chokes if the fields are TEXT.  I've got 
other foreign keys in the database that are more traditional integer fields, 
and they seem to be working fine.
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Christian Gollwitzer]

Am 23.11.14 07:32, schrieb Chris Angelico:

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.


SQLite does foreign keys by running a trigger. You need to activate it

https://www.sqlite.org/foreignkeys.html#fk_enable

Apfelkiste:VecTcl chris$ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA foreign_keys = ON;
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');
Error: foreign key constraint failed
sqlite> select * from foo;
asdf
sqlite> select * from bar;
asdf
sqlite> delete from foo;
sqlite> select * from foo;
sqlite> select * from bar;

sqlite>


Not sure, if this helps the OP, though.

Christian

--
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Sun, Nov 23, 2014 at 8:40 PM, Jussi Piitulainen
 wrote:
> Chris Angelico writes:
>> On Sun, Nov 23, 2014 at 7:21 PM, Frank Millman wrote:
>>
>> > To enable them, add the following -
>> >
>> > pragma foreign_keys = on;
>> >
>> > It works for me.
>>
>> Thanks, I went poking around briefly but didn't find that pragma.
>
> I didn't notice a pointer to the relevant documentation in this thread
> yet. So here, and in the rest of that page:
>
> 

>From that page:

"""Foreign key constraints are disabled by default (for backwards
compatibility), so must be enabled separately for each database
connection. (Note, however, that future releases of SQLite might
change so that foreign key constraints enabled by default. Careful
developers will not make any assumptions about whether or not foreign
keys are enabled by default but will instead enable or disable them as
necessary.)"""

That explains it. Putting the "backwards" into "backwards
compatibility", but understandable, at least. I'm pointing all the
people I know to this page; just enable the things and then you can
actually trust stuff.

ChrisA
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Jussi Piitulainen]

Chris Angelico writes:
> On Sun, Nov 23, 2014 at 7:21 PM, Frank Millman wrote:
>
> > To enable them, add the following -
> >
> > pragma foreign_keys = on;
> >
> > It works for me.
> 
> Thanks, I went poking around briefly but didn't find that pragma.

I didn't notice a pointer to the relevant documentation in this thread
yet. So here, and in the rest of that page:


-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Sun, Nov 23, 2014 at 7:21 PM, Frank Millman  wrote:
> "Chris Angelico"  wrote in message
> news:captjjmp4y5zowwn5yftjutko4h5jvtqlantwqepa6b35xnd...@mail.gmail.com...
>>
>> 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.
>
> The default is for sqlite3 to ignore foreign key contraints.
>
> To enable them, add the following -
>
> pragma foreign_keys = on;
>
> It works for me.

Thanks, I went poking around briefly but didn't find that pragma. I
still stand by my view, though, that constraint checking should be
active by default; you have to explicitly create them anyway, so it's
not like you'd unexpectedly lose heaps of performance. And even if it
is costly, the default settings should be for reliability, more than
performance; if you want to cut corners to speed things up, that's
your decision, but that should be something you have to explicitly ask
for.

It'd be nice to have a warning or at least informational: "Foreign key
constraint created, but constraint checking is inactive". Would mean
folks like me, who grew up on IBM's DB2 and now use PostgreSQL,
wouldn't be so confused by "why did that not throw an error".

> Unfortunately it has a limitation, which they acknowledge but they say is
> unlikely to be addressed. You can access more than one database concurrently
> by using the 'attach' command, and qualifying a remote tablename as
> {database} dot {tablename}. You can then include the remote table in any sql
> command. However, it will not enforce foreign key constraints across
> databases.

That's more of an edge case; I wouldn't be too bothered by that.

ChrisA
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Frank Millman]

"Chris Angelico"  wrote in message 
news:captjjmp4y5zowwn5yftjutko4h5jvtqlantwqepa6b35xnd...@mail.gmail.com...
>
> 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.

The default is for sqlite3 to ignore foreign key contraints.

To enable them, add the following -

pragma foreign_keys = on;

It works for me.

Unfortunately it has a limitation, which they acknowledge but they say is 
unlikely to be addressed. You can access more than one database concurrently 
by using the 'attach' command, and qualifying a remote tablename as 
{database} dot {tablename}. You can then include the remote table in any sql 
command. However, it will not enforce foreign key constraints across 
databases.

Frank Millman



-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

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 d

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

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

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

On Saturday, November 22, 2014 9:41:55 PM UTC-8, Chris Angelico wrote:
> On Sun, Nov 23, 2014 at 3:58 PM, Michael Torrie wrote:
> > On 11/22/2014 08:54 PM, llanitedave wrote:
> > 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...
> >
> > Or manually encode it to a UTF-8 byte string (just call .encode() on
> > it).  Sqlite probably only knows about UTF-8 when it comes to unicode.
> 
> Since it was a byte string sent to the database and a Unicode string
> coming back, the solution would be to .decode() the byte string.
> However, I doubt that's the issue; that's being done for you
> implicitly by the lower-level connections. Also, in Python 2:
> 
> >>> u'testbh3' ==  'testbh3'
> True
> 
> So that's not your primary problem. You could try that, but I doubt
> it'll solve anything for you.
> 
> Are you able to switch to Python 3, though? If you are, Unicode issues
> tend to be a lot easier to resolve there.
> 
> ChrisA

You're right.  It ultimately didn't make a difference.  And it makes sense that 
it wouldn't have, because when I did a query using the same field that got 
rejected by the foreign key, the query was successful.

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.

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.

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.
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Sun, Nov 23, 2014 at 3:58 PM, Michael Torrie  wrote:
> On 11/22/2014 08:54 PM, llanitedave wrote:
> 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...
>
> Or manually encode it to a UTF-8 byte string (just call .encode() on
> it).  Sqlite probably only knows about UTF-8 when it comes to unicode.

Since it was a byte string sent to the database and a Unicode string
coming back, the solution would be to .decode() the byte string.
However, I doubt that's the issue; that's being done for you
implicitly by the lower-level connections. Also, in Python 2:

>>> u'testbh3' ==  'testbh3'
True

So that's not your primary problem. You could try that, but I doubt
it'll solve anything for you.

Are you able to switch to Python 3, though? If you are, Unicode issues
tend to be a lot easier to resolve there.

ChrisA
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Michael Torrie]

On 11/22/2014 08:54 PM, llanitedave wrote:
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...

Or manually encode it to a UTF-8 byte string (just call .encode() on
it).  Sqlite probably only knows about UTF-8 when it comes to unicode.

-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[llanitedave]

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...
-- 
https://mail.python.org/mailman/listinfo/python-list

Re: SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

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
-- 
https://mail.python.org/mailman/listinfo/python-list