SqlMagic Config File Locations

[psychemedia]

Currently, the database connection file defaults to ~/.jupysql/connections.ini, whereas magic settings are found in a pyproject.toml "in the current or parent directories" (docs).

I am trying to set up a clean environment for students to work in and would like to be able to hide the pyproject.toml file.

Ideally, it would be useful if the ~/.jupysql/ path were also a checked location for the SqlMagic confg settings.

[edublancas]

Yes, I think it makes sense to have a user-level configuration.

This seems relevant to decide what this location should be: psf/black#1899

I can ask someone from our team to work on this as soon as they finish their existing PRs, but it might take a couple of weeks (we'd highly appreciate it if you give us a hand)

When is your class starting?

[psychemedia]

Early students are dribbling in now, but we may end up using a legacy environment which uses ipython-sql for all manner of reasons (a planned migration straight from classic notebook to JupyterLab is blocked by most of the extensions we want to use not being available in JupyterLab 4. And we don't want to have to migrate twice (once to JL3, then to JL4).

I'm not sure how much help I can provide code-wise (I've not really engaged with tests, ever!)?

I do note that the file is loaded via:

jupysql/src/sql/magic.py

Line 677 in a71ea40

file_path = util.find_path_from_root("pyproject.toml")

which leads to:

jupysql/src/sql/util.py

Lines 377 to 390 in a71ea40

	def find_path_from_root(file_name):
	"""
	Recursively finds an absolute path to file_name starting
	from current to root directory
	"""
	current = Path().resolve()
	while not (current / file_name).exists():
	if current == current.parent:
	return None
	current = current.parent
	display.message(f"Found {file_name} from '{current}'")
	return str(Path(current, file_name))

Currently, it looks like the connections file and path are picked up literally, so some thinking about how that path is declared may need rethinking.

jupysql/src/sql/magic.py

Lines 132 to 139 in 4011175

	dsn_filename = Unicode(
	default_value=str(Path("~/.jupysql/connections.ini").expanduser()),
	config=True,
	help="Path to DSN file. "
	"When the first argument is of the form [section], "
	"a sqlalchemy connection string is formed from the "
	"matching section in the DSN file.",
	)

The black repo you pointed to seems to use a find_project_toml() routine here.

[edublancas]

we need to modify this line:

jupysql/src/sql/magic.py

Line 677 in a71ea40

file_path = util.find_path_from_root("pyproject.toml")

and replace it with some searching logic:

• if ~/.jupysql/config.toml exists, use it
• otherwise, look for a pyproject.toml in the current directory or parent directories

edge case: if both ~/.jupysql/config.toml and pyproject.toml, use pyproject.toml (?) - to allow users override settings

[psychemedia]

In the edge case, and in case of conflict, should the ~/.jupysql/config.toml be prioritised for the jupysql settings, but the "root" pyproject.toml prioritised for the other settings?

[edublancas]

@marshallwhiteorg: let's see how other projects are tackling this (see my comment: #880 (comment)) and implement something similar

[marshallwhiteorg]

Sounds good, I'll implement this similar to black.

If pyproject.toml exists then that will be used, otherwise we'll look for ~/.jupysql/config.

[edublancas]

any updates @marshallwhiteorg ?