Test in VSCode a postgreSQL SQL expression in Python’s interactive notebook (= Jupyter Notebook, iPython) in a venv with package manager conda

Sharing this since it took me too long to set this up from the scattered guides and hints.

• Change to a conda environment of your choice. For example, make a virtual environment called venv310 (same name in the whole answer) with conda create -n venv310 python=3.10 and activate it with: conda activate venv310.

If I open VSCode from a terminal that has activated the venv310, VSCode falls back to the base env as soon as I run code with Shift+Enter:

(base) … $ /bin/python3
Python 3.10.6 (main, Nov 14 2022, 16:10:14) [GCC 11.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import create_engine

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'sqlalchemy'

• Without having the venv310 at work here, it is already clear from this error that the mistakenly taken base environment would need the sqlalchemy module, so that we need to install in the venv310:

conda install sqlalchemy

• And from a later error it turns out that you also need to install the dialect "psycopg2":

    raise exc.NoSuchModuleError(
sqlalchemy.exc.NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:postgres.psycopg2

conda install psycopg2

• If you run magic like '%load_ext sql' instead, you will see:

>>> %load_ext sql
  File "<stdin>", line 1
    %load_ext sql
    ^
SyntaxError: invalid syntax

• Anyway, the main problem is that the virtual environment does not work. Thus, install VSCode/VSCodium extension "Python for VSCode". See Activating Anaconda Environment in VsCode.
• Restart VSCode.
• Click Yes if you see this:

We noticed you're using a conda environment. If you are experiencing issues with this environment in the integrated terminal, we recommend that you let the Python extension change "terminal.integrated.inheritEnv" to false in your user settings.

Source: Python (Extension) Yes No More Info

• Press Ctrl+Shift+P → "Python: Select interpreter" → choose venv310 (this is what I could already do before, but now, when running some code, it did not fall back to the base env). See Activating Anaconda Environment in VsCode - Stack Overflow.

• Open the interactive Window with Ctrl+Shift+P → "Jupyter: Create Interactive Window"

I started the interactive window just with a right-click on the %load_ext sql line that I had in the normal Python code editor. Or you press Shift+Enter in that line.

See How do I find/excute Python Interactive Mode in Visual Studio Code? - Stack Overflow.

Then it shows:

Running cells with 'Python 3.10.6 ('venv310')' requires ipykernel package.

Click Install.

• Not sure but I think I clicked Install, and it still showed in the iPython history:

Running cells with 'Python 3.10.6 ('venv310')' requires ipykernel package.
Run the following command to install 'ipykernel' into the Python environment.
Command: 'conda install -n venv310 ipykernel --update-deps --force-reinstall'

# %% Python 3.10.8 (main, Nov 24 2022, 14:13:03) [GCC 11.2.0]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.7.0 -- An enhanced Interactive Python. Type '?' for help.

Thus, if you see this as well, run conda install -n venv310 ipykernel --update-deps --force-reinstall in the venv310.

• Run %load_ext sql in the iPython terminal, either by typing it in the terminal, or by running the magic from the Python editor (check right-click to see how to run in interactive window).

If you see:

Output exceeds the size limit. Open the full output data in a text editor
---------------------------------------------------------------------------
ModuleNotFoundError                     Traceback (most recent call last)
Cell In[2], line 1
----> 1 get_ipython().run_line_magic('load_ext', 'sql')

File ~/anaconda3/envs/venv310/lib/python3.10/site-packages/IPython/core/interactiveshell.py:2364, in InteractiveShell.run_line_magic(self, magic_name, line, _stack_depth)
   2362     kwargs['local_ns'] = self.get_local_scope(stack_depth)
   2363 with self.builtin_trap:
-> 2364     result = fn(*args, **kwargs)
   2365 return result

File ~/anaconda3/envs/venv310/lib/python3.10/site-packages/IPython/core/magics/extension.py:33, in ExtensionMagics.load_ext(self, module_str)
    31 if not module_str:
    32  raise UsageError('Missing module name.')
---> 33 res = self.shell.extension_manager.load_extension(module_str)
    35 if res == 'already loaded':
    36  print("The %s extension is already loaded. To reload it, use:" % module_str)

File ~/anaconda3/envs/venv310/lib/python3.10/site-packages/IPython/core/extensions.py:76, in ExtensionManager.load_extension(self, module_str)
    69 """Load an IPython extension by its module name.
    70
    71 Returns the string "already loaded" if the extension is already loaded,
    72 "no load function" if the module doesn't have a load_ipython_extension
    73 function, or None if it succeeded.
    74 """
...
File <frozen importlib._bootstrap>:1027, in _find_and_load(name, import_)

File <frozen importlib._bootstrap>:1004, in _find_and_load_unlocked(name, import_)

ModuleNotFoundError: No module named 'sql'

or if you run the following magic and see one of these:

# %%
%sql postgresql+psycopg2://localhost/&server=postgres?user=postgres&port=5432&password=postgres&database=test_db
UsageError: Line magic function `%sql` not found.

# %%
%%sql select * FROM x;
UsageError: Cell magic `%%sql` not found.

# %%
result = %sql select * FROM x;
UsageError: Line magic function `%sql` not found.

then you need to run in the venv310:

conda install -c conda-forge ipython-sql

(and not the pip installer pipenv install ipython-sql like it is written in the ipython-sql guide)

See UsageError: Line magic function %sql not found - Stack Overflow

• Back in VSCode, you might see:

But that does not harm.

• Run again:

%load_ext sql

• And then: %sql postgresql+psycopg2://localhost/&server=postgres?user=postgres&port=5432&password=postgres&database=test_db

(See: What is the format for the PostgreSQL connection string / URL? - Stack Overflow)

• And now, you can see the SQL output in the iPython console:

%sql select * FROM x;

with the output afterwards. For a displaylimit, see the ipython-sql guide --> "Configuration".