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 aren't available, make sure that you didn't disable the plugin.
Press Control+Alt+S to open the IDE settings and then select.
Open the Installed tab, find the Database Tools and SQL plugin, and select the checkbox next to the plugin name.
The last selected extractor becomes the default one for newly opened editor tabs. For MongoDB, the default extractor is always JSON.
In the following video, the data is copied using the CSV data extractor, and viewed in the Text editor view mode using the SQL Updates and Pipe-separated data extractors.
Using data extractors, you can export database data as SQL statements and in various formats. When you export to a file, a separate file is created for each individual table or view. For more information about exporting, refer to the Export section.
The list of available data extractors is as follows:
Built-in data extractor
Export file format
Extractors for delimiter-separated values. Use them to create your own format that is based on CSV or any DSV format.
Configure an extractor for delimiter-separated values
You can extend the default functionality and create your own format that is based on CSV or any DSV format. In its settings, you can set separators for rows and headers, define text for NULL values, specify quotation, create new extractors for formats with delimiter-separated values.
From the list of data extractors, select Configure CSV Formats.
In the CSV Formats dialog, click the Add Format icon .
Specify a name of a new format (for example,
Confluence Wiki Markup).
Define settings of the format: set separators for rows and headers, define text for NULL values, specify quotation. Click OK.
When the format is created, you can select it in the drop-down list near the Export Data icon ().
For more information about the CSV Formats dialog, refer to the reference.
Add a custom extractor
In the Project tool window ( ) , navigate to Scratches and Consoles | Extensions | Database Tools and SQL | data | extractors.
Right-click the extractors node and select .
Type the filename (for example,
PHP: the name that is used in the extractor menu.
array: output of the extractor.
groovy: the extension of the script.
Type or paste the code of the extractor in a new file. You can use code of other extractors in the extractors folder to write your extractor. Also, you can try the following extractors:
TSV-Icelandic-Groovy.tsv.groovy on GitHub: copy tab-separated query results and to paste them to Excel as comma-separated.
Dbunit Dataset Export in Groovy on GitHub: an extractor for Dbunit export.
PHP-Array.groovy: an extractor to PHP array.
Select the extractor from the list of extractors.
Consider the following example of adding the PHP-array extractor in PyCharm.
API for custom data extractors
Use the following API to create your custom data extractor.
Database table metadata
A list of all columns in the query result or currently open table editor.
A list of selected columns in the query result or currently open table editor.
Default formatting methods.
Boolean value that indicates whether the table editor is in transposed mode.
You can use the following simple CSV data extractor as a base for your custom data extractors. The full version of this data extractor is available in PyCharm. To locate it, in the Project tool window , navigate to Scratches and Consoles | Extensions | Database Tools and SQL | data | extractors
CSV Formats dialog
To access this dialog, click the Data Extractors list on the toolbar and select Configure CSV Formats.
This dialog contains the settings for converting table data into delimiter-separated values formats (for example, CSV, TSV) and vice versa.
When you change settings, the preview changes correspondingly.
Select a template that successfully converts the file data into a table. You can change settings of predefined templates or add a new template. To add a template, click Add Format button ().
Use the Add Format (), Remove Format (), Up () and Down () buttons to create, delete and reorder the formats; Copy Format () to create a copy of the selected format.
Select or type the character that you want to use as a separator for values.
Select or type the character that you want to use as a separator for rows.
Null value text
Select or type the text that you want to use if a cell contains the
Add row prefix/suffix
Click the link and type a row prefix and suffix. Prefix and suffix are character sequences which in addition to the row separator indicate the beginning and end of a row.
Each line in the area under Quotation is a quotation pattern. A quotation pattern includes:
If there is more than one pattern, the first pattern is used.
Use the Add (), Remove (), Up () and Down () buttons to create, delete and reorder the patterns.
Select when you want to enclose values within quotation characters.
Ignore or remove whitespace characters. If this checkbox is cleared, the whitespace characters are treated as parts of the corresponding values.
First row is header
Treat the first row as a row that contains column names.
First column is header
Treat the first column as a column that contains row names.