PyCharm 2024.2 Help

Configure connection

Enable the Database Tools and SQL plugin

This functionality relies on the Database Tools and SQL plugin, which is bundled and enabled in PyCharm by default. If the relevant features are not available, make sure that you did not disable the plugin.

  1. Press Ctrl+Alt+S to open settings and then select Plugins.

  2. Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.

When you create a data source, PyCharm connects to a database automatically to receive database objects. Then the connection closes. Names of data sources that interact with a database are shown in the Database tool window with a little green circle.

If you want to close a database connection, select a data source and click the Deactivate button (the Deactivate button) on the toolbar. Alternatively, select a data source and press Ctrl+F2.

Close a database connection

Connection options

Set a time zone for a connection

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  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

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. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Options tab, select the Run keep-alive query each checkbox and type a number of seconds after which PyCharm must run 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. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

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

Set a predefined query to run as you establish a connection

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

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

    Run a predefined query as you establish a connection

Refresh the database state

If someone changed the remote database data or view, the local view of the database might differ from the actual state of the database.

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Options tab, select the Auto sync checkbox.

    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 icon (the Refresh button) in the toolbar or press Ctrl+F5.

    Refresh the database state

Filter objects with the object filter

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Schemas tab, type filtering options in the Object filter field.

    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, refer to Class Patterns at JavaTM 2 Platform Standard Edition 5.0 API Specification.

    The Object Filter field in data source options

Filter databases and schemas

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Schemas tab, type filtering options in the Schema pattern field.

    • @: 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.

Connection modes

Enable read-only mode for a connection

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Options tab, select the Read-only checkbox.

    Enable read-only mode for a connection

Single session mode

Single session 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 query consoles. For more information about query consoles, refer to Query consoles.

When you apply single session 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. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Options tab, select Single session mode.

  4. Click Apply.

    Enable the single session mode

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. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. On the Data Sources tab, select a data source that you want to modify.

  3. On the Options tab, select Single database mode.

  4. Click Apply.

    Enable the single database mode

Transaction mode

A commit ends a transaction and makes all changes available to other users. In PyCharm, you can select how you want to commit transactions: automatically or manually.

How changes are committed to a database

The data editor accumulates all the changes that you made. When you press the Submit button (the Submit button), PyCharm submits these changes and processes them according to the selected commit mode:

  • In the Tx:Auto mode, PyCharm commits all the changes automatically once you click the Submit button (The Submit button) .

  • In the Tx:Manual mode, changes are stored in a transaction to the database. When you press Submit and Commit (the Submit and Commit button), transactions are committed to the database. When you commit the transaction, the Submit and Commit icon (the Submit and Commit button) disables (grays out). It means that all the transactions have been committed.

If the commit mode is set to Tx:Auto, each change of values, rows, or columns is implicitly committed and cannot be rolled back. In the Tx:Auto commit mode, Submit and Commit (The Submit and Commit button) and Roll Back (The Rollback button) buttons are disabled and hidden.

If the commit mode is set to Tx:Manual, you can explicitly submit or roll back the submitted changes by clicking Submit (The Submit button) or Roll Back (The Rollback button) buttons. To commit changes to a database, click the Submit and Commit (The Commit button).

The Tx switch can also be used for selecting the isolation level for the transactions.

Change the transaction level

Select the default transaction mode for a data source

  1. Open data source properties. You can open data source properties by using one of the following options:

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

    • Press Shift+Enter.

  2. In the list of created data sources, click the data source that you want to modify.

  3. Click the Options tab.

  4. From the Transaction control list, select the default transaction mode for the selected data source.

    The selected transaction control is applied for new query consoles.

    Default transaction mode for a data source

Submit changes automatically

By default, changes are not submitted to a database unless you press the Submit icon The Submit icon, or press Ctrl+Enter. You can enable the Submit changes immediately option to submit your changes automatically.

  1. In settings Ctrl+Alt+S, navigate to Tools | Database | Data Editor and Viewer.

  2. Select Submit changes immediately in the Data Modification.

  3. Click OK.

    Submit changes immediately
Last modified: 11 October 2024