Read-only access to (sqlite) database #136

Closed
bilderbuchi opened this Issue Aug 7, 2015 · 5 comments

Comments

2 participants
@bilderbuchi

I recently started using dataset for a little project. One feature I missed: I was reading from a database, which I (inexperienced that I am) did not want to affect/change/destroy.
sqlite3 offers read-only access, see here:

If uri is true, database is interpreted as a URI. This allows you to specify options. For example, to open a database in read-only mode you can use:

db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)

I did not find a corresponding feature in dataset, and trying to append the mode string to the db path when connecting in dataset did not work (as could be expected, I guess).

Is this something you would find useful and consider adding?

@bilderbuchi bilderbuchi changed the title from Read-only access to sqlite database to Read-only access to (sqlite) database Aug 7, 2015

@pudo

This comment has been minimized.

Show comment
Hide comment
@pudo

pudo Aug 7, 2015

Owner

So dataset uses sqlalchemy to abstract over all database types, and I'm very reluctant to implement any workaround that they don't have. I've googled this a bit and can't see any sign of whether sqlalchemy is aware of sqlite read-only mode. The best I've seen is that you can pass in connect_args, like this: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#compatibility-with-sqlite3-native-date-and-datetime-types - maybe that can be used to pass the read-only flag (https://www.sqlite.org/c3ref/open.html)?

Owner

pudo commented Aug 7, 2015

So dataset uses sqlalchemy to abstract over all database types, and I'm very reluctant to implement any workaround that they don't have. I've googled this a bit and can't see any sign of whether sqlalchemy is aware of sqlite read-only mode. The best I've seen is that you can pass in connect_args, like this: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#compatibility-with-sqlite3-native-date-and-datetime-types - maybe that can be used to pass the read-only flag (https://www.sqlite.org/c3ref/open.html)?

@bilderbuchi

This comment has been minimized.

Show comment
Hide comment
@bilderbuchi

bilderbuchi Aug 7, 2015

So, I tried to do this (among other equally unsuccessful variations), but it does not work:

db = dataset.connect('sqlite:///' + db_file + '?mode=ro', engine_kwargs={'connect_args': {'uri': True}})
# TypeError: 'mode' is an invalid keyword argument for this function

Apparently, I'm not the only one with this problem, a workaround is to do

creator = lambda: sqlite3.connect('file:' + db_file + '?mode=ro', uri=True)
db = dataset.connect('sqlite:///' , engine_kwargs={'creator': creator})
db.tables

# ['itemDataValues',
#  'fulltextItems',
#  'tags',
#  'baseFieldMappingsCombined',
#  ....

So, is this a bug in sqlalchemy? something you can/should fix on your side, or add some syntactic sugar? no idea...

So, I tried to do this (among other equally unsuccessful variations), but it does not work:

db = dataset.connect('sqlite:///' + db_file + '?mode=ro', engine_kwargs={'connect_args': {'uri': True}})
# TypeError: 'mode' is an invalid keyword argument for this function

Apparently, I'm not the only one with this problem, a workaround is to do

creator = lambda: sqlite3.connect('file:' + db_file + '?mode=ro', uri=True)
db = dataset.connect('sqlite:///' , engine_kwargs={'creator': creator})
db.tables

# ['itemDataValues',
#  'fulltextItems',
#  'tags',
#  'baseFieldMappingsCombined',
#  ....

So, is this a bug in sqlalchemy? something you can/should fix on your side, or add some syntactic sugar? no idea...

@bilderbuchi

This comment has been minimized.

Show comment
Hide comment
@bilderbuchi

bilderbuchi Aug 7, 2015

and it works:

table = db['itemDataValues']
table.create_column('foo', sqlalchemy.BIGINT)
# OperationalError: (sqlite3.OperationalError) attempt to write a readonly database [SQL: 'ALTER TABLE "itemDataValues" ADD COLUMN foo BIGINT']

and it works:

table = db['itemDataValues']
table.create_column('foo', sqlalchemy.BIGINT)
# OperationalError: (sqlite3.OperationalError) attempt to write a readonly database [SQL: 'ALTER TABLE "itemDataValues" ADD COLUMN foo BIGINT']
@pudo

This comment has been minimized.

Show comment
Hide comment
@pudo

pudo Aug 7, 2015

Owner

Ok, that's an awful workaround, I'll give you that. But I'd rather not like to start incorporating workarounds for sqlalchemy in this project -- perhaps worth filing a ticket on their tracker?

Owner

pudo commented Aug 7, 2015

Ok, that's an awful workaround, I'll give you that. But I'd rather not like to start incorporating workarounds for sqlalchemy in this project -- perhaps worth filing a ticket on their tracker?

@bilderbuchi

This comment has been minimized.

Show comment
Hide comment
@bilderbuchi

bilderbuchi Aug 7, 2015

Yeah, let's see - I don't have a bitbucket account, and wouldn't even know how to properly formulate it (DB knowledge == machine_epsilon), and the workaround is good enough for me for now. Feel free to close this on your side, and thanks for the help!

Yeah, let's see - I don't have a bitbucket account, and wouldn't even know how to properly formulate it (DB knowledge == machine_epsilon), and the workaround is good enough for me for now. Feel free to close this on your side, and thanks for the help!

@pudo pudo closed this Aug 7, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment