PyCharm 2019.3 Help

Configuring database connections

Remember password

You can select the following options for storing your password:

  • Never: password is prompted each time you establish a connection with a database.

  • Until restart: password is saved only for the current PyCharm run. If you exit PyCharm and open it again, you must provide the password again.

  • For session: password is saved only for the current connection session with a database (until you disconnect from it). You can terminate the connection by pressing the Disconnect icon (the                     Disconnect icon) or Ctrl+F2.

  • Forever: password is saved in the PyCharm storage. You do not need to provide the password next time you open PyCharm.

Close a database connection

PyCharm connects to databases automatically. The names of the data sources with open database connections are shown in the Database tool window in bold.

  • To close a database connection, select a data source and сlick the Disconnect button the Disconnect button on the toolbar. Alternatively, select a data source and press Ctrl+F2.

    Close a database connection

Assign a color to a data source

To have better differentiation between production and test databases, you can assign a color to a data source.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select the data source for which you want to assign a color.

  3. Click the Choose color icon (The Choose color icon) and select a color.

  4. Click OK.

Assign a color to a data source

Using JDBC drivers

Create a connection to a database with a JDBC driver

If you cannot find a name of a database vendor in the list of data sources, download a JDBC driver for the database management system (DBMS), and create a connection in PyCharm. With the JDBC driver, you can connect to DBMS and start working.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Driver and Data Source.

  3. Click the User Driver link.

  4. In the Driver files pane, click the Add icon and select Custom JARs.

  5. Navigate to the JAR file of the JDBC driver, select it, and click OK.

  6. In the Class field, specify the value that you want to use for the driver.

  7. Click Apply.

  8. Return to the created data source connection.

  9. Specify database connection details. Alternatively, paste the JDBC URL in the URL field.

    To delete a password, right-click the Password field and select Set empty.

  10. To ensure that the connection to the data source is successful, click Test Connection.

Add a JDBC driver to an existing connection

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. In the Data Sources and Drivers dialog, select a data source where you want to change a driver, and click the Driver link in data source settings.

  3. Click the provided driver entry, and click Remove (the Remove button).

    To revert changes, click Reset that is in the upper-right part of the window. Alternatively, click the Add button (the Add button) and select Provided Driver | <database_name> | <driver_version>

  4. In the Driver files pane, click the Add icon (The Add icon) and select Custom JARs.

  5. Navigate to the JAR file of the JDBC driver, select it, and click OK.

  6. In the Class field, specify the value that you want to use for the driver.

  7. Click Apply.

Add an icon to a user driver

You can change the default icon of a user driver.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon (The Data Source Properties icon) and select the driver that you created. To create a user driver, see Add a JDBC driver to an existing connection.

  2. Click the Options tab, scroll down to the Other section.

  3. From the Icon list, select an icon that you want to assign to the driver.

  4. Click OK.

Add an icon to the user driver

Configuring connection options

Enable single connection mode

Single connection mode means that the data source and all consoles use one and the same connection. This mode allows you to see the temporary objects in the database tree, or use the same transaction in different consoles. For more information about consoles, read Database Console.

When you apply single connection mode, you must close all existing opened connections. PyCharm displays a notification if you have opened connections. To close opened connections for the selected data source, click Yes. To close opened connections for all modified data sources, click Yes for all.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select the data source where you want to enable single connection mode.

  3. Click the Options tab.

  4. Select Single connection mode.

  5. Click Apply.

Enable the single connection mode

Enable single database mode

When you connect to a data source, PyCharm can retrieve and display you all the databases that the data source has. But in some cases (for example, with certain settings of PgBouncer), you can or are allowed to work only with a certain database. With the Single database mode enabled, you see in the database tree view only the database that you specified in the connection settings, not all the databases that were received from the data source.

Consider using this setting, for PostgreSQL, Azure SQL Database, Greenplum, Amazon Redshift.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select the data source where you want to enable the single connection mode.

  3. Click the Options tab.

  4. Select Single database mode.

  5. Click Apply.

Enable the single database mode

Enable read-only mode for a connection

  1. Navigate to File | Data sources Shift+Enter.

  2. In the Data Sources list, select the necessary data source.

  3. Click the Options tab.

  4. Select the Read-only checkbox.

Enable read-only mode for a connection

Keep the connection alive

You can keep the connection to a database alive by running a keep-alive query after the specified period. You can define the custom query in the driver settings for unsupported databases.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select a data source that you want to modify.

  3. On the Options tab, select the Run keep-alive query each N seconds checkbox, where N is a number of seconds after which PyCharm runs a keep-alive query again.

Disconnect from a database in a specified period

You can specify a period in seconds after which PyCharm terminates the connection.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select a data source that you want to modify and click the Options tab.

  3. On the Options tab, select the Auto-disconnect after N seconds checkbox, where N is a number of seconds after which PyCharm terminates the connection.

Set a time zone for a session

When you start interacting with a database, you begin a database session. You can assign a time zone in which this session will operate.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select a data source that you want to modify and click the Options tab.

  3. In the Time zone field, start typing the time zone that you want to use.

  4. Apply settings and click OK.

Select a time zone in the Time zone field

Run a predefined SQL query as you establish a connection

You can run a predefined SQL query each time you establish a connection.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select a data source that you want to modify.

  3. On the Options tab, in the Startup script field, specify the SQL query that you plan to run on a connection to a database.

Run a predefined SQL query as you establish a connection

Fetch more fields for MongoDB introspection

You can observe collections and fields in the database explorer. PyCharm fetches the first 10 documents from each collection to get information about the fields. You can customize this behavior by specifying the fetch_documents_for_metainfo JDBC parameter on the Advanced tab of the data source properties dialog.

  1. In the Database tool window (View | Tool Windows | Database), right-click MongoDB data source and select Properties.

  2. Click the Advanced tab.

  3. In the parameter table, scroll down till you see fetch_documents_for_metainfo.

  4. Double-click the Value cell of the fetch_documents_for_metainfo parameter.

  5. Specify the desired value.

Configuring SSH and SSL

The following procedure describes the SSL configuration that suits most databases. For some databases, you need to use another approach for a successful connection. You can see configuration examples for Cassandra and Heroku Postgres in the DataGrip documentation.

Connect to a database with SSL

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select a data source profile where you want to change connection settings.

  3. Click the SSH/SSL tab and select the Use SSL checkbox.

  4. In the CA file field, navigate to the CA certificate file (for example, ca.pem).

  5. In the Client certificate file field, navigate to the client certificate file (for example, client-cert.pem).

  6. In the Client key file field, navigate to the client key file (for example, client-key.pem).

  7. To ensure that the connection to the data source is successful, click Test Connection.

Connect to a database with SSL

Connect to a database with SSH

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. Select a data source profile where you want to change connection settings.

  3. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

  4. In Proxy host, Proxy user, Proxy password, and Port fields, specify connection details.

  5. From the Auth type list, select an authentication method:

    • Password: to access the host with a password. Specify the password, and select the Save password checkbox to save the password in PyCharm.

    • Key pair (OpenSSH or PuTTY): to use SSH authentication with a key pair. To apply this authentication method, you must have a private key on the client machine and a public key on the remote server. PyCharm supports private keys that are generated with the OpenSSH utility.

      Specify the path to the file where your private key is stored and type the passphrase (if any) in the corresponding fields. To have PyCharm remember the passphrase, select the Save passphrase checkbox.

    • OpenSSH config and authentication agent: to use SSH keys that are managed by a credentials helper application (for example, Pageant on Windows or ssh-agent on Mac and Linux).

  6. To ensure that the connection to the data source is successful, click Test Connection.

SSH and SSL settings of a data source

Create the SSH tunnel with PuTTY (Windows)

  1. Download and run the latest version of the PuTTY SSH and Telnet client (download the client from https://www.putty.org/).

  2. In the PuTTY Configuration dialog, navigate to Connection | SSH | Auth.

  3. In the Private key file for authentication field, specify the path to your private key file and click Open.

  4. In the command line window, specify the username that you use for the SSH tunnel and press Enter. Do not close the command line window.

  5. In the Database window (View | Tool Windows | Database), click the Data Source Properties icon the Data Source Properties icon on the toolbar.

  6. Select a data source profile where you want to change connection settings.

  7. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

  8. From the Auth type list, select OpenSSH config and authentication agent.

  9. In Proxy host, Proxy user, and Port fields, specify connection details.

  10. To ensure that the connection to the data source is successful, click Test Connection.

Create the SSH tunnel with PuTTY (Windows)

Create the SSH tunnel with Pageant (Windows)

Pageant is an SSH authentication agent for PuTTY, PSCP, PSFTP, and Plink. Pageant stores your private key, and as long as it is running, it provides the unlocked private key to PuTTY or other tools like PyCharm. You can find the Pageant icon in the Windows taskbar.

  1. Download the latest version of Pageant (download the client from https://www.putty.org/).

  2. In the Windows taskbar, right-click the Pageant icon and select Add Key.

  3. In the Select Private Key File dialog, navigate to the private key file (the PPK file) and click Open.

  4. (Optional) Enter the private key passphrase and press Enter.

  5. In the Database window (View | Tool Windows | Database), click the Data Source Properties icon the Data Source Properties icon on the toolbar.

  6. Select a data source profile where you want to change connection settings.

  7. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

  8. From the Auth type list, select OpenSSH config and authentication agent.

  9. In Proxy host, Proxy user, and Port fields, specify connection details.

  10. To ensure that the connection to the data source is successful, click Test Connection.

Create the SSH tunnel with Pageant (Windows)

Create the SSH tunnel with the ssh-agent (macOS and Linux)

Run all commands for ssh-agent in the command line.

  1. Ensure that ssh-agent is running.

    ssh-agent

  2. Add your key to the agent (in the following example, the key path is ~/.ssh/id_rsa).

    ssh-add ~/.ssh/id_rsa

  3. (Optional) On macOS, you can add -K option to the ssh-add command to store passphrases in your keychain. On macOS Sierra and later, you need to create the config file in ~/.ssh/ with the following text:

    Host * UseKeychain yes AddKeysToAgent yes IdentityFile ~/.ssh/id_rsa

    If you have other private keys in the .ssh directory, add an IdentityFile line for each key. For example, if the second key has the id_ed25519 name, add IdentityFile ~/.ssh/id_ed25519 as an additional line for the second private key.

  4. List all added keys.

    ssh-add -L

  5. In the Database window (View | Tool Windows | Database), click the Data Source Properties icon the Data Source Properties icon on the toolbar.

  6. Select a data source profile where you want to change connection settings.

  7. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

  8. From the Auth type list, select OpenSSH config and authentication agent.

  9. In Proxy host, Proxy user, and Port fields, specify connection details.

  10. To ensure that the connection to the data source is successful, click Test Connection.

Create the SSH tunnel with the ssh-agent (macOS and Linux)
Last modified: 5 December 2019