PyCharm 2019.3 Help

Data sources and drivers dialog

To access the Data Sources and Drivers dialog (Shift+Enter), perform one of the following actions:

  • In the Database tool window (View | Tool Windows | Database), click the Data Source Properties button the Data Source Properties button.

  • In the Database tool window (View | Tool Windows | Database), click the Add button the Add button and navigate to Data Source | <data_source_vendor>.

Overview

In the Data Sources and Drivers dialog, you can manage your data sources and database drivers.

the Data Sources and Drivers dialog

Data sources and drivers

When you select an item from the list of data sources and drivers, settings of the item appear in the right-hand part of the dialog.

Toolbar

Item

Shortcut

Description

the Add buttonAlt+Insert

Create a data source or a driver.

Android SQLite to create a data source for an SQLite database located on an Android device or emulator. An Android application module and Android SDK is required and must be defined in PyCharm.

See .

the Remove iconDelete

Remove the selected item or items from the list.

the Duplicate iconCtrl+D

Create a copy of the selected data source or driver.

the Go to Driver iconCtrl+B

Navigate to the driver settings that are associated with the selected data source.

import

Move the selected data source to the global or project level. For more information about global and project levels, see Managing data sources.

Context menu

Item

Shortcut

Description

the Add button Add

Alt+Insert

Create a data source or a driver.

    the Remove icon Remove

    Delete

    Remove the selected item or items from the list.

    the Duplicate iconDuplicate

    Ctrl+D

    Create a copy of the selected data source or driver.

    the Go to Driver icon Go to Driver

    Ctrl+B

    Navigate to the driver settings that are associated with the selected data source.

    the Make Global or Move to Project icon Make Global or Move to Project

    Move the selected data source to the global or project level. For more information about global and project levels, see Managing data sources.

    Change Driver

    Associate a data source with a driver.

    Reset Changes

    Ctrl+Alt+Z

    Revert changes for the selected item.

    Load Sources

    Load source code of database objects for the selected category of schemas.

    Show Driver Usages

    Alt+F7

    Show data sources that use the selected driver.

    the Back icon the Forward icon

    Switch between recently-used items.

    Data source settings

    Settings pane of a data source includes the following tabs: General, Options, SSH/SSL, Schemas, Advanced.

    General

    The General tab includes settings that you need to specify for a database connection.

    The set of fields and controls on the tab depends on the option that is selected in the Connection type list.

    Item

    Description

    Connection type

    A connection type that you want to use for the database connection.

    • URL only This option, generally, is for editing the database connection URL directly.

      When you select this option, only the following fields are available: User, Password and URL.

      You should edit the URL right in the field. Your username and password, if necessary, are specified in the corresponding fields, or within the URL in the format appropriate for the JDBC driver that you are using.

    • When using any other of the options (the options are DBMS-specific), PyCharm forms the database connection URL automatically using the info in the fields above the URL field. In all such cases, normally, you don't need to edit the URL (though you can if you want).

    Driver

    The driver from the Drivers list that you want to use for the connection.

    File

    The path to the database file.

    Use the Open icon (The Open icon) to select an existing database file.

    The Save icon (the Save button) creates a copy of a database file and changes a path to the copy location.

    Path

    The path to the database file or folder.

    Use the Open icon (The Open icon) to select an existing database file or a folder.

    Host

    The hostname (domain name) or the IP address of the computer where the database is located. If the database is on your local computer, specify localhost or 127.0.0.1.

    If you are using SSH, the database host must be accessible by the specified domain name or IP address from the computer on which the SSH proxy runs. See SSH/SSL tab.

    Port

    The database port number.

    Database

    The name of the target database or schema.

    User

    The name of your database user account.

    Password

    The password for the database user.

    Save

    Save settings for the password field. 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.

    Create database

    Adds an argument to the URL to create a database. This option is available only in some database management systems (for example, in the Apache Derby (Embedded)).

    URL

    The URL that PyCharm will use to connect to the database. The user interface for specifying the URL is different depending on which option is selected in the Connection type list.

    Test Connection

    Verifies that the database connection settings are correct and PyCharm can communicate with the database. To copy the communication information, click the Copy to Clipboard icon the Copy to Clipboard icon.

    Options

    The Options tab includes settings of the database connection.

    The set of fields and controls on the tab depends on the option that is selected in the Connection type list.

    Item

    Description

    Read-only

    Set the read-only status. Select the checkbox to protect the data source from accidental data modifications.

    If the checkbox is selected, you cannot modify the data in the Data editor.

    Data modifications might be possible in the console if the driver does not support the read-only status.

    Transaction control

    Set the isolation level for database transactions and the mode of how the transactions are committed.

    • Auto: the current transaction is committed automatically when you submit your local changes to the database server.

    • Manual: changes submitted to the database server are accumulated in a transaction that can either be committed or rolled back.

    Switch schema

    Define a mode that PyCharm uses to switch schemas.

    • Automatic: switch schemas automatically or manually. For PostgreSQL and Amazon Redshift, use this option to save a configured search path between IDE restarts.

    • Manual: switch schemas manually only.

    • Disable: do not switch schemas.

    Single connection mode

    Enable the 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.

    Run keep-alive query each N seconds

    Run a keep-alive query to keep the connection alive. You can define the custom query in the driver settings for unsupported databases.

    Auto-disconnect after N seconds

    Disconnect from the database after the specified number of seconds.

    Single database mode

    In the database tree view, show and enable only the database that you specified in the connection settings.

    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. In the database tree view with the Single database mode enabled, you see only the database that you specified in the connection settings.

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

    Startup script

    Run an SQL query each time you establish a connection.

    Note: if the Single connection mode checkbox is cleared, each new console creates a new connection.

    Auto sync

    Synchronize the actual state of the database automatically. The state of the database means the database tree view that you see in the Database tool window (View | Tool Windows | Database).

    If the Auto sync checkbox is selected, the view of the data source is automatically updated:

    • When you click OK in the Data Sources and Drivers dialog.

    • When you run DDL SQL statements in the database consoles associated with the data source.

    If the Auto sync checkbox is cleared, the view of the data source in the Database tool window is synchronized with the actual state of the database only when you click the Refresh button the Refresh button or press Ctrl+F5).

    Load sources for

    Load source code of views, procedures, packages and other database objects for the selected category of schemas: all schemas (All schemas), all schemas excluding system schemas (All excl. system schemas), or disable the feature (None).

    To change this setting for several data sources simultaneously, select the corresponding data sources. Right-click the selection and navigate to Load Sources and select an option.

    Warn when editing an outdated DDL

    Display a notification if the cached DDL differs from the actual DDL in the database.

    Every time you open source code, PyCharm displays you a version that is loaded during the indexing process. But if someone changes the source code from another workstation, the indexed version becomes outdated.

    If your version is older than the one in the database, you see the following notification: Cached object differs from the object in the database.

    Introspect using JDBC metadata

    Switch to the JDBC-based introspector.

    To retrieve information about database objects (DB metadata), PyCharm uses the following introspectors:

    • A native introspector (might be unavailable for certain DBMS). The native introspector uses DBMS-specific tables and views as a source of metadata. It can retrieve DBMS-specific details and produce a more precise picture of database objects.

    • A JDBC-based introspector (available for all the DBMS). The JDBC-based introspector uses the metadata provided by the JDBC driver. It can retrieve only standard information about database objects and their properties.

    Consider using the JDBC-based intorspector when the native introspector fails or is not available.

    The native introspector can fail, when your database server version is older than the minimum version supported by PyCharm.

    You can try to switch to the JDBC-based introspector to fix problems with retrieving the database structure information from your database. For example, when the schemas that exist in your database or database objects below the schema level are not shown in the Database tool window.

    Use pre-introspected objects for system catalogs that are not introspected

    Toggles the usage of pre-introspected objects for system catalogs. For more information about introspection and system catalogs, see Pre-introspected objects from system catalogs.

    Code style

    Select a code style that you want to use for schemas. For more information about code style customization, read Code styles for SQL.

    SSH/SSL

    The SSH/SSL tab includes settings for the SSH or SSL connection.

    SSH

    Item

    Description

    Use SSH tunnel

    Enable settings for configuring the SSH connection.

    Copy from

    Copy SSH settings from the existing data source.

    Click the link and select the corresponding data source.

    Proxy host

    The hostname (domain name) or IP address of the SSH proxy server. The SSH proxy server host must be accessible by the specified hostname or IP address from your local computer.

    Proxy port

    The port of the SSH proxy server that accepts SSH connections.

    Local port

    The local port for the SSH connection. The default value is <Dynamic>.

    Proxy user

    The name of the SSH proxy user.

    Auth type

    The user authentication type that is used by the SSH proxy.

    • Password: to access the host with a password.

    • 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).

    Proxy password

    The password for the SSH proxy user.

    See also, Remember password.

    Private key file

    The path to the private key file.

    Passphrase

    The passphrase for the private key if the key is locked with the passphrase.

    Remember

    Select this checkbox if you want PyCharm to remember the password or the passphrase.

    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.

    SSL

    Item

    Description

    Use SSL tunnel

    Enable settings for configuring the SSL connection.

    Copy from

    Copy SSH settings from the existing data source.

    Click the link and select the corresponding data source.

    CA file

    Specify the path to the SSL Certificate Authority (CA) certificate file. The certificate file must be the same certificate as used by the server.

    Client certificate file

    Specify the path to your (client) public key certificate file.

    Client key file

    Specify the path to your (client) private key file.

    Schemas

    Select the databases and schemas to be shown in the Database tool window.

    Item

    Description

    the Refresh button

    Refresh the list of the databases and schemas.

    the Expand All button

    Expand all nodes in all trees.

    the Collapse All button

    Collapse all nodes in all trees.

    the Filter field

    Filter the contents in trees. Only the databases and schemas whose names contain the specified text are shown.

    Pattern

    Define a pattern to select the necessary databases and schemas. To get the info about the syntax to be used, place the cursor into the field and press Ctrl+Q.

    • @: the current database or schema.

    • *: every database or schema. You can list schemas after *:.

    Consider the following examples:

    • *:*: all schemas in all databases.

    • @:*: all schemas from the current database

    • @:@: only the current schema

    • *:dbo|@:@|db1:s1,s2,s3: the dbo schema from all databases, the current schema, schemas s1,s2,s3 from the db1 database.

    Object filter

    Limit the set of tables and other database objects that are shown in the Database tool window for each data source. For example, if you limit the resolve scope to table:actor.*, then only the actor table will be accessible in the Database tool window (View | Tool Windows | Database).

    Limit the resolve scope to the actor table

    All other tables will be out of the scope. PyCharm do not resolve objects that are not displayed in the Database tool window.

    Object is not resolved

    Use the following pattern when you compose an expression for the Object filter field.

    <type>:[-]<pattern>, where:

    <type> might be an aggregate, collation, event, fdw, ftable, mview, operator, package, role, routine, sequence, synonym, table, user, view, vtable.

    <pattern> is a regular expression.To exclude an item, prepend with - (minus). For more information about regular expressions, see Class Patterns at JavaTM 2 Platform Standard Edition 5.0 API Specification.

    Advanced

    The Advanced tab includes the database connection properties, options and environment variables for the JVM database driver.

    The table on the Advanced tab displays a set of connection options that are passed to the database driver as key and value pairs.

    When you select a cell in the Name column, the description of the corresponding option is shown underneath the table.

    To add a row, start editing the values in the last row, where <user defined> and <value> are shown. A new row is added to the table automatically.

    Item

    Description

    VM Options

    JVM options for the database driver.

    For certain Oracle Database versions (for example, Oracle version 9), there might be connection problems when you and your database server are in different time zones. You can specify the time offset for your timezone in the VM Option field (for example, -Duser.timezone=UTC+03:00). Alternatively, set the variable oracle.jdbc.timezoneAsRegion to false in the table on the Advanced tab.

    VM Environment

    Environment variables for the database driver JVM.

    For example, encoding-related issues in Oracle database can be solved by setting the NLS_LANG variable (like NLS_LANG=Russian_CIS.CL8MSWIN1251). For more information, see Oracle NLS_LANG FAQ.

    For additional information, refer to your DBMS documentation.

    DDL data source settings

    A DDL data source is defined by its name and can reference one or more DDL files and another data source (a parent data source).

    Item

    Description

    Name

    The data source name.

    DDL Files

    List of files that contain the necessary DDL definitions.

    • the Add button Alt+Insert. Add a DDL SQL file or files to the data source definition. In the dialog that opens, select the necessary file or files.

    • the Remove button (Alt+Delete). Remove the selected file or files from the list.

    • the Up button (Alt+Up). Move the selected file one line up in the list.

    • the Down button (Alt+Down). Move the selected file one line down in the list.

    Extend

    Optionally, you can select another data source as a parent in the Extend list. As a result, the data source that you are editing inherits all DDL definitions from its parent.

    If you do not want to use the parent data source, select <none>.

    Driver settings

    Settings pane of a driver includes the following tabs: General, Options, Advanced.

    Settings

    The General tab includes settings for driver files, URL templates, and driver classes.

    Item

    Description

    Class

    The fully qualified name of the driver class to be used.

    JDBC drivers

    PyCharm uses JDBC drivers to interact with a database. You can download and use a driver from the PyCharm driver repository or specify the driver that you store on your computer.

    To download and use the latest driver version, click the Download ver. N link.

    You can also specify and use a driver with a particular version number. Click the ver. <version_number> link and select the driver version that you want to use. The selected version is downloaded and applied automatically.

    You can use the driver that is already available on your computer. In that case, click the Add button the Add button and select the driver files (with the JAR extension).

    URL templates

    The templates used to construct the database URL. The text in curly brackets represents variables. Consider the following examples:

    • {host}: the domain name or IP address of the database host.

    • {port}: the database port number.

    • {database}: the name of the database or schema.

    Optional fragments are in square brackets, for example, [:{port}].

    Template names correspond to the names of the options in the URL options list.

    Options

    Item

    Description

    Transaction control

    Set the isolation level for database transactions and the mode of how the transactions are committed.

    • Auto The current transaction is committed automatically when you submit your local changes to the database server.

    • Manual The changes submitted to the database server are accumulated in a transaction that can either be committed or rolled back.

    Switch schema

    Define a mode that PyCharm uses to switch schemas.

    • Automatic: switch schemas automatically or manually.

    • Manual: switch schemas manually only.

    • Disable: do not switch schemas.

    Startup script

    An SQL query that will run each time you establish a connection.

    Note: if the Single connection mode checkbox is cleared, each new console creates a new connection.

    Keep-alive query

    A keep-alive query that will run to keep the connection alive.

    Auto sync

    The default setting for the auto sync option.

    Load sources for

    Load source code of database objects for the selected category of schemas.

    Send application info

    When connecting to a database server, PyCharm sends the info about itself if this checkbox is selected.

    Dialect

    The SQL dialect associated with the corresponding data sources.

    Code style

    Selects a code style that you want to use for the data sources that use this driver. For more information about code style customization, read Code styles for SQL.

    Format synced sources

    Apply the code style to source code of database objects after synchronization.

    Advanced

    The Advanced tab includes JVM options, environment variables, and options that can be passed to the database driver as key-value pairs.

    To start editing a value in the table, double-click the corresponding Value field.

    To add a row, start editing the values in the last row, where <user defined> and <value> are shown. A new row will be added to the table automatically.

    Item

    Description

    VM Options

    The default options for the JVM in which the database driver runs. The driver is started as a separate process in the JVM.

    VM environment

    Environment variables for the database driver JVM.

    For example, encoding-related issues in Oracle database can be solved by setting the NLS_LANG variable (like NLS_LANG=Russian_CIS.CL8MSWIN1251). For more information, see Oracle NLS_LANG FAQ.

    Problems

    If potential problems are detected, there is a number to the right of Problems. In that case, if you click Problems, you see the list of problems as well as controls for fixing them.

    Last modified: 10 December 2019