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