Title
#beginners-need-help
dmb23

dmb23

10/13/2021, 8:34 AM
Hi, I have some metadata in a project stored as a SQLite database file. In handling that more or less clean, I stumbled upon two questions, and would be curious if there are more elegant solutions: - currently I store the data I recieve e.g. in
data/01_raw/my_metadata.db
, but specify this location in the credentials.yml (which is not the first place I would look if future me has to change something). I think it might also be possible to put it in a globals.yml with a TemplatedConfigLoader, but I did not find a way to include it in a catalog.yml where I would search for it when I want to define SQLQueryDataSet(s) - and in that context: the documentation states that it might be possible (at least for a SQLTableDataSet) to provide the connection string not in credentials, but in the load_args. But both classes SQLTableDataSet and SQLQueryDataSet have a check for credentials[con] that raises a DataSetError when it is not found. Is there some way around that which I am missing? (or should I offer to change the documentation accordingly?)
datajoely

datajoely

10/13/2021, 1:34 PM
Hi @User I've prototyped things on my end and can maybe describe how I'd set things up
1:35 PM
The SQL credentials stuff is a bit ham-fisted, it feels even more so when the the database is actually on the local file system to reference things and as such the connection string being in credentials is weird I agree
1:36 PM
Here is how I would set things up, for reference I used this chinook database available here:https://www.sqlitetutorial.net/sqlite-sample-database/
1:37 PM
In
catalog.yml
my SQLite reference is as follows:
yaml
chinook:
  type: pandas.SQLTableDataSet
  table_name: artists
  credentials: chinook
1:37 PM
In
credentials.yml
I declare the
con
key as follows:
yaml
chinook:
  con: sqlite:///${chinook_location}/chinook.db
1:37 PM
In
global.yml
I declare the
chinook_location
as a key:
yaml
chinook_location: 'data/01_raw'
1:38 PM
I then switch to
TemplatedConfigLoader
in
hooks.py
1:39 PM
We push this credentials pattern as we want to guide users to avoid storing sensitive data explicitly in the catalog
1:39 PM
we want push users to make sure secrets are managed properly
1:40 PM
Appreciate it feels a bit like overkill for local unsecured sqlite development, but in general we want to push people to follow software engineering best practice from day 1
1:40 PM
Does that make sense?
dmb23

dmb23

10/13/2021, 1:44 PM
ok, that's what I thought. Makes completely sense that a local SQLite file is not the key target for this system!
datajoely

datajoely

10/13/2021, 1:44 PM
🙂
5:44 PM
@User not quite the same thing but we spoke about SQLite earlier this week