Run queries
Available only in PyCharm Professional: download to try or compare editions
To run SQL code, press CtrlEnter
To cancel a running statement, press CtrlF2
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.
note
The Database Tools and SQL plugin is available only in PyCharm Professional.
Press CtrlAlt0S to open settings and then select Plugins.
Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.
You can run your code by using shortcuts, toolbar buttons and icons, a context menu, and run/debug configurations. You can run a query from query consoles, scratch files, and user files.
tip
A database or query console does not differ from other SQL files like user files and scratches. The only difference is that the query console is already connected to a data source, so you can type and run your code right away. You can use a query console in the same way as you use a terminal. Type, press CtrlEnter, and run your code.
For more information about where and how to view query results, refer to Query results.
note
Before PyCharm 2024.1, you had to attach your to a session manually using the <session> list in the top right corner of the editor.
Starting with PyCharm 2024.1, you have to attach your query consoles to a database or a schema using the corresponding list in the top right corner of the editor. The IDE manages sessions automatically.
You can relate to a query console as to a terminal where you type and run your code.
In the Database tool window (View | Tool Windows | Database) , click the data source.
Press F4 to open a query console. For more information about working with consoles, refer to Work with consoles.
Type or paste the statement that you want to execute.
Click the Execute button () on the toolbar. Alternatively, press CtrlEnter.
If you have several statements, select whether you want to execute all statements or a single statement. The suggestion list always contains an item for running all the statements.
In PyCharm, you can open and run a file. The limitation for the file size is 20 MB. When you open a file that is larger than 20 MB, you see only first 2.5 MB of the file. The file should be associated with the SQL file type. For more information about file type associations, refer to the File type associations topic.
Open the Project tool window (View | Tool Windows | Project) and double-click an SQL file.
For more information about attaching directories and files in PyCharm, refer to User files.
Click the statement that you want to execute.
Also, you can select (highlight) the fragment of code that you want to execute. It can be a subquery or a group of statements. PyCharm executes only the selection.
Click the Execute button () on the toolbar. Alternatively, press CtrlEnter.
To customize query execution settings, click the Customize link. Alternatively, open settings by pressing CtrlAlt0S and navigate to Tools | Database | Query Execution. .
(Optional) If the SQL file is not attached to a data source, select the data source that you want to attach it to in the <data source> list.
In the Statements window, press Enter to run the selected statement. You can switch between other entries to run another set of statements. Statements that you are going to execute are highlighted in a query editor.
tip
In the Statements window, you can click Customize to define whether you want to see the chooser or always run the statement under the caret.
For another example of running script files, refer to the following video at youtube.com.
A stored procedure is a set of SQL statements with an assigned name. You can execute stored procedures in PostgreSQL, Microsoft SQL Server, Oracle, and MySQL.
Right-click a stored function that you want to execute and select Run Function.
In the Execute Routine window, type all the necessary parameter values, and click OK.
If necessary, select the Run from checkbox and select the file or console to run the stored function from.
(Optional) If the file is not connected to a data source, select a data source from the list of data sources on the toolbar. Then select the connection session from the Sessions list.
For more information about connection sessions, refer to Sessions.
Right-click a
SELECT
statement.Select Execute to File and select the output format.
In the Export Data dialog, specify the extractor that you want to use and other settings.
For more information about the Export Data dialog, refer to the Export data topic.
To cancel running statements, click Cancel Running Statements on the toolbar.
The progress indicator on the gutter shows a red square while the request is being canceled.
note
If you click the Deactivate button () on the toolbar of Database tool window, it will stop the query in PyCharm, but the query will continue its execution in the database.
If you click the Cancel Running Statements button two times, PyCharm displays a warning where you can terminate the remote process or wait a bit longer. The warning is displayed to ensure safety as deactivation might be potentially dangerous for some processes happening simultaneously.
Also, the IDE displays a warning after 10 seconds of idle canceling.
You can interrupt not only the execution of a request, but also the creation of a connection. This is especially relevant for the very first query in the console, which also creates a connection.
You can stop the process of creating a connection. To do that, click the Cancel Running Statements button () on the toolbar while the connection is being created. The connection will be stopped, and you will see the Connection canceled message.
In order to run SQL scripts, treat the usages of the database objects as links, and provide you with correct code completion, PyCharm must resolve the database objects in your code to a certain context. By default, IDE uses one of the following as a starting point to resolve the database objects to: <schema> selector, or the default database (if none of the above is set).
Depending on whether your code is a single script or it has multiple unrelated SQL statements, select one of the following resolve modes to resolve the database objects properly:
Playground: default mode for query consoles. All the database objects are resolved to the same context.
Use this mode if your code is a set of unconnected SQL statements which are not meant to be run as a single script.
Script: default mode for local SQL files. The
USE
SQL statement (SET search_path TO
for PostgreSQL) overrides the default context that the database objects are resolved to.Use this mode if your code is meant to be run as a single script.
note
By default, the <schema> selector is not available for local SQL files. For the <schema> selector to appear, attach a session to your SQL file. To do that, click the <session> button (
<session>
) and select the session. Alternatively, right-click the file in the Project tool window and select Switch session.
In the following video, the public
schema is selected as default for the query console. The schema contains the actor
table, whereas the information_schema
schema does not contain it. When the resolve mode is Playground, the database object is resolved to default schema in both SELECT
statements, because all the statements are considered unrelated.
Once the resolve mode is changed to Script, the second SELECT
statement is considered related to the SET search_path TO information_schema
one. The actor
table is no longer resolved in that statement.
Depending on the database vendor, the question mark ?
is treated as a parameter in SQL statements. You can specify what other characters and their sequences should be treated as parameters, and in which places, in the Tools | Database | Query Execution | User Parameters settings page CtrlAlt0S.
The patterns for SQL parameters are specified by using regular expressions.
If you have parameters in your statement, PyCharm will prompt you to specify the values of the parameters before you execute the statement.
To execute a parameterized statement, click the Execute button () on the toolbar and enter values in the second column. Alternatively, to open the Parameters dialog, click the View Parameters button ().
In PyCharm, the replacement of a parameter with a value is straightforward. To use an array as the value, specify your array in the corresponding field.
In the following example, the ?
parameter can be replaced with 206, 'John', 'Smith'
value:
INSERT INTO actor (actor_id, first_name, last_name, last_update)
VALUES (?, '01.02.2023 13:34');
For further information on parameters, refer to the database documentation.
If you have parameters with a specific parameter syntax in your scripts, specify the pattern using a regular expression.
For the following example, the %{2}(\w+_\d+)
pattern must be set in the Tools | Database | Query Execution | User Parameters settings page CtrlAlt0S:
SELECT title, description, release_year FROM actor
JOIN film_actor fa ON actor.actor_id = fa.actor_id
JOIN film f ON f.film_id = fa.film_id
WHERE last_name = '%%winnerpl_2';
In the IDE code editor, you can preview the structure of tables you have in your SQL statements by using the Quick Documentation feature.
To preview a table structure in an SQL statement, select the table and press Ctrl0Q.
The Quick Documentation popup that appears contains the CREATE
DDL script among other information about the table.
For more information about the feature, refer to Quick Documentation.
PyCharm stores history of all statements that you have run in a query console.
To open the Query History dialog, the Browse Query History button () on the toolbar, or press CtrlAlt0E.
In the Query History dialog, you can perform the following operations:
To filter information, start typing your search query.
To paste the query from the Query History dialog to the console, double-click the query in the left pane of the Query History dialog.
To delete a record from history, select the record and press Delete.
Usually, when you run a query, you receive results in a table format. PyCharm displays the results in a data editor. For each statement, the data editor with results appears in a separate tab in the Services tool window.
For more information about viewing query results, refer to Query results.
Thanks for your feedback!