Setting foreign_keys on sqlite is failing

[iban-rodriguez]

Hello!

We are trying to activate foreign_keys option on sqlite through a PRAGMA query but it is not being activated. The following code should print Set foreign_keys: (1,) but it always print Set foreign_keys: (0,). It is probably due to not using the same connection for all queries but generating a new one for each of them. Is there a way to overcome this issue?

import asyncio
from databases import Database

async def set_foreign_key(database):
    result = await database.fetch_one("PRAGMA foreign_keys;")
    print("Initial foreign_keys: {}".format(result))
    await database.execute("PRAGMA foreign_keys = ON;")
    result = await database.fetch_one("PRAGMA foreign_keys;")
    print("Set foreign_keys: {}".format(result))

loop = asyncio.get_event_loop()
database = Database("sqlite:///:memory:")
loop.run_until_complete(set_foreign_key(database))
loop.close()

[mjwestcott]

Databases passes kwargs on to aiosqlite, which passes them on to sqlite3, so I believe the following works:

import sqlite3

class Connection(sqlite3.Connection):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.execute('pragma foreign_keys=1')

db = databases.Database('sqlite:///foo.db', factory=Connection)

See the factory param in https://docs.python.org/2/library/sqlite3.html#sqlite3.connect for more details.

It would be great if this library had a connection pool for sqlite, but I assume it's missing because it's not in aiosqlite (though it has been discussed: https://github.com/omnilib/aiosqlite/issues/7#issuecomment-405745758)