Connect to a Vertica database

The following tutorial demonstrates a quick and easy way to connect to a Vertica database using VerticaPy. For a full exploration of the connection possibilities in VerticaPy, including auto-connections, see Connection.

Requirements

Before connecting to a database, you must satisfy the following requirements:

• Have access to a machine with Vertica version 9 or later installed

• Install Python 3.9 or later on your machine

• Install VerticaPy on your machine

For more information about these installations, see Getting Started.

Connect to a DB

To connect to a database for the first time, use the `new_connection() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.connection.new_connection.html>`__ function, replacing the configuration values with the credentials for your database:

import verticapy as vp
vp.new_connection({"host": "12.345.67.89",
                   "port": "5433",
                   "database": "testdb",
                   "password": "XxX",
                   "user": "dbadmin"},
                   name = "Vertica_Connection")

The connection is saved to the VerticaPy connection file under the name specified in the name parameter. To reconnect to the database using this connection, run the `connect() <https://www.vertica.com/python/documentation/1.0.x/html/api/verticapy.connection.connect.html>`__ function with the name of the connection as the argument value:

vp.connect("Vertica_Connection")

To view all available connections, use the available_connections() function:

vp.available_connections()

['Vertica_New_Connection', 'Vertica_Connection']

If you need to confirm the parameters for a given function, you can view the function’s page in the API reference guide or use the help function:

help(vp.new_connection)

Help on function new_connection in module verticapy.connection.write:

new_connection(conn_info: dict, name: str = 'vertica_connection', auto: bool = True, overwrite: bool = True) -> None
    Saves the new connection in the VerticaPy
    connection file. The information is saved
    as plaintext in the local machine.
    The function
    :py:func:`~verticapy.connection.get_connection_file`
    returns the associated connection file
    path. If you want a temporary connection,
    you can use the
    :py:func:`~verticapy.connection.set_connection`
    function.

    Parameters
    ----------
    conn_info: dict
        ``dictionnary`` containing
        the information to set up
        the connection.

         - database:
            Database Name.
         - host:
            Server ID.
         - password:
            User Password.
         - port:
            Database Port (optional, default: 5433).
         - user:
            User ID (optional, default: dbadmin).

        ...

         - env:
            ``bool`` to indicate whether the user and
            password are replaced by the associated
            environment variables. If ``True``, VerticaPy
            reads the associated environment variables
            instead of writing and directly using the
            username and password.
            For example:
            ``{'user': 'ENV_USER', 'password': 'ENV_PASSWORD'}``

            This works only for the user and password.
            The real values of the other variables are
            stored plaintext in the VerticaPy connection
            file. Using the environment variables hides
            the username and password in cases where the
            local machine is shared.

    name: str, optional
        Name of the connection.
    auto: bool, optional
        If set to True, the connection
        will become the new auto-connection.
    overwrite: bool, optional
        If set to ``True`` and the connection
        already exists, the existing connection
        will be overwritten.

    Examples
    --------
    Create a new connection to VerticaPy:

    .. note::

        If no errors are raised, the new connection was
        successful.

    .. code-block:: python

        from verticapy.connection import new_connection

        conn_info = {
            "host": "10.211.55.14",
            "port": "5433",
            "database": "testdb",
            "password": "XxX",
            "user": "dbadmin",
        }

        new_connection(conn_info, name = "VerticaDSN")

    .. seealso::

        | :py:func:`~verticapy.connection.get_connection_file` :
            Gets the VerticaPy connection file.
        | :py:func:`~verticapy.connection.set_connection` :
            Sets the VerticaPy connection.

For an interactive start guide, you can use the help_start() function:

vp.help_start()

📢 Welcome to the VerticaPy help module.

This module can help you connect to Vertica, create a Virtual DataFrame, load your data, and more. - [Enter 0] Overview of the library - [Enter 1] Load an example dataset - [Enter 2] View an example of data analysis with VerticaPy - [Enter 3] Contribute on GitHub - [Enter 4] View the SQL code generated by a vDataFrame and the time elapsed for the query - [Enter 5] Load your own dataset into Vertica - [Enter 6] Write SQL queries in Jupyter - [Enter -1] Exit

In the next tutorial, we will use this Vertica connection to explore one of the core features of VerticaPy: the Virtual DataFrame.