[Tutor] sqlite3: turning on foreign key support thru python

[Modulok]

>> How do I tell if it succeeded (short of trying an operation that should be
>> blocked by foreign keys)?  How do I use that cursor object returned by the
>> pragma query to tell if its a '1' (on) or a '0' (off) and verify the state?


The cursor object contains the result set. It's a python generator object. (Or
at least a generator interface.) You have to iterate over it in order to see
the resulting rows which are stored as a tuple. Not all operations return a
result row. (For example, conn.execute('pragma foreign_keys=ON' will return a
cursor object, but it won't generate any result rows, as there were
none returned by the database.)

To see the result of your second command, do something like this::

    rows = conn.execute('pragma foreign_keys')
    for r in rows:
        print r


You'll then see something like this when foreign keys are turned on::

    (1,)

Or this when they're turned off::

    (0,)

Hope that helps.
-Modulok-

On 12/16/11, Monte Milanuk <memilanuk at gmail.com> wrote:
> I'm setting up an sqlite3 database to use as a base for some programming
> stuff I
> want to work on.  Currently using python 2.7, which appears to have a new
> enough
> version of sqlite (just barely) to support foreign keys.
>
> As I understand things, sqlite by default has foreign keys turned off,
> unless
> specifically compiled otherwise or until you turn on foreign keys using
> 'pragma
> foreign_keys=ON'.  And it needs to be turned on for each connection too.
>
> So... just putzing around using the python interactive shell...
>
>
> import sqlite3
> sqlite3.sqlite_version
> '3.6.21'
> conn = sqlite3.connect('contacts.db')
> conn.execute('pragma foreign_keys=ON')
> <sqlite3.Cursor object at 0x00B61860>
> conn.execute('pragma foreign_keys')
> <sqlite3.Cursor object at 0x00B6F020>
>
>
> It appears I am able to successfully import sqlite3, its of a recent enough
> version to support foreign keys (> 3.6.19), I connected it to an existing
> database 'contacts.db', and when I execute the pragma statement to turn on
> foreign key support it returns a cursor object.  Similarly, when I send a
> pragma
> statement to query the status of foreign key support, it returns a cursor
> object.
>
> Now for the stupid question(s):
>
> How do I tell if it succeeded (short of trying an operation that should be
> blocked by foreign keys)?  How do I use that cursor object returned by the
> pragma query to tell if its a '1' (on) or a '0' (off) and verify the state?
>
> TIA,
>
> Monte
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> http://mail.python.org/mailman/listinfo/tutor
>