Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Setting foreign_keys on sqlite is failing #169

Open
iban-rodriguez opened this issue Jan 8, 2020 · 1 comment
Open

Setting foreign_keys on sqlite is failing #169

iban-rodriguez opened this issue Jan 8, 2020 · 1 comment

Comments

@iban-rodriguez
Copy link

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
Copy link

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: omnilib/aiosqlite#7 (comment))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants