fix `--help` in magics

[edublancas]

I realized our magics behave funky when passing --help.

Typically, CLIs will print documentation when passing --help, but this is what happens in JupySQL:

%load_ext SQL
%sql --help

Output:

Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

And with %sqlplot:

%sqlplot --help

Output:

UsageError: the following arguments are required: -t/--table, -c/--column

This is non-standard behavior: CLIs typically display the documentation and finish execution when the only argument is --help; but this isn't happening here. I believe the problem is in IPython, and the argument parser isn't finishing execution when finding --help.

I added some tests in a temporary branch.

[mehtamohit013]

Updated outputs:

In [1]: %load_ext sql
   ...: %sql --help
UsageError: No active connection.

To fix it:

Pass a valid connection string:
    Example: %sql postgresql://username:password@hostname/dbname

OR

Set the environment variable $DATABASE_URL

Documentation: https://jupysql.ploomber.io/en/latest/connecting.html
If you need help solving this issue, send us a message: https://ploomber.io/community

In [1]: %load_ext sql
   ...: %sql duckdb://

In [2]: %sql --help
Running query in 'duckdb://'

[edublancas]

it should generate something similar to what a typical CLI returns. for example, this is what ploomber-engine --help returns:

Usage: ploomber-engine [OPTIONS] INPUT_PATH OUTPUT_PATH

  Execute my-notebook.ipynb, store results in output.ipynb:

  $ ploomber-engine my-notebook.ipynb output.ipynb

  Pass parameters:

  $ ploomber-engine my-notebook.ipynb output.ipynb -p key value -p x 42

  Display print statements:

  $ ploomber-engine my-notebook.ipynb output.ipynb --log-output

  Store a plot with cell's runtime:

  $ ploomber-engine my-notebook.ipynb output.ipynb --profile-runtime

  Store a plot with cell's memory usage:

  $ ploomber-engine my-notebook.ipynb output.ipynb --profile-memory

  Remove cells before execution:

  $ ploomber-engine my-notebook.ipynb output.ipynb --remove-tagged-cells
  remove

Options:
  --log-output                    Display notebook's stdout
  --profile-runtime               Profile cell's runtime
  --profile-memory                Profile cell's memory usage
  --progress-bar / --no-progress-bar
                                  Display a progress bar
  -p, --parameters TEXT...
  --debug-later                   Serialize traceback for later debugging
  --remove-tagged-cells TEXT      Remove cells with this tag before execution
  --cwd TEXT                      Working directory to run notebook in.
  --save-profiling-data           Save profiling data to a file (requires
                                  --profile-runtime and/or --profile-memory)
  --help                          Show this message and exit.

I think we can use the sqlmagic object and inspect it to determine which options it takes, then we can generate the text description. check out the argparse model, it automatically generates the help response so we might be able to re-use that logic

[mehtamohit013]

So, I tried running %sql?, it return the correct documentation:

Docstring:
::

  %execute [-l] [-x CLOSE] [-c CREATOR] [-s SECTION] [-p] [-P] [-n]
               [--append] [-a CONNECTION_ARGUMENTS] [-f FILE] [-S SAVE]
               [-w WITH_] [-N] [-A ALIAS] [--interact INTERACT]
               [line ...]

Runs SQL statement against a database, specified by
SQLAlchemy connect string.

If no database connection has been established, first word
should be a SQLAlchemy connection string, or the user@db name
of an established connection.

Examples::

  %%sql postgresql://me:mypw@localhost/mydb
  SELECT * FROM mytable

  %%sql me@mydb
  DELETE FROM mytable

  %%sql
  DROP TABLE mytable

SQLAlchemy connect string syntax examples:

  postgresql://me:mypw@localhost/mydb
  sqlite://
  mysql+pymysql://me:mypw@localhost/mydb

positional arguments:
  line                  sql

options:
  -l, --connections     list active connections
  -x CLOSE, --close CLOSE
                        close a session by name
  -c CREATOR, --creator CREATOR
                        specify creator function for new connection
  -s SECTION, --section SECTION
                        section of dsn_file to be used for generating a
                        connection string
  -p, --persist         create a table name in the database from the named
                        DataFrame
  -P, --persist-replace
                        replace the DataFrame if it exists, otherwise perform
                        --persist
  -n, --no-index        Do not store Data Frame index when persisting
  --append              create, or append to, a table name in the database
                        from the named DataFrame
  -a CONNECTION_ARGUMENTS, --connection_arguments CONNECTION_ARGUMENTS
                        specify dictionary of connection arguments to pass to
                        SQL driver
  -f FILE, --file FILE  Run SQL from file at this path
  -S SAVE, --save SAVE  Save this query for later use
  -w WITH_, --with WITH_
                        Use a saved query
  -N, --no-execute      Do not execute query (use it with --save)
  -A ALIAS, --alias ALIAS
                        Assign an alias to the connection
  --interact INTERACT   Interactive mode
File: ~/ploomber/jupysql/src/sql/magic.py

[mehtamohit013]

@edublancas Do you want me to add an argument %sql --help which gives the same output as %sql?

Btw, %magic_command? is the standard one. Magics like %time doesn't support --help, but do support ?

Also, there is mention of same in Ipython docs

[edublancas]

Ok, I forgot that the ? is the standard IPython, let's do this then:

ensure that all magics have proper docstrings: %sql, %sqlplot and %sqlcmd. then document in our quickstart how to get this documentation (via ?), and also document in each of the API sections that document each magic the ? command.