Форматирование sql запроса python

sqlparse – Parse SQL statements¶

The sqlparse module provides the following functions on module-level.

sqlparse. split ( sql, encoding=None ) ¶

Split sql into single statements.

Format sql according to options.

Available options are documented in Formatting of SQL Statements .

In addition to the formatting options this function accepts the keyword “encoding” which determines the encoding of the statement.

sqlparse. parse ( sql, encoding=None ) ¶

Parse sql and return a list of statements.

In most cases there’s no need to set the encoding parameter. If encoding is not set, sqlparse assumes that the given SQL statement is encoded either in utf-8 or latin-1.

Formatting of SQL Statements¶

The format() function accepts the following keyword arguments.

keyword_case Changes how keywords are formatted. Allowed values are “upper”, “lower” and “capitalize”. identifier_case Changes how identifiers are formatted. Allowed values are “upper”, “lower”, and “capitalize”. strip_comments If True comments are removed from the statements. truncate_strings If truncate_strings is a positive integer, string literals longer than the given value will be truncated. truncate_char (default: “[…]”) If long string literals are truncated (see above) this value will be append to the truncated string. reindent If True the indentations of the statements are changed. reindent_aligned If True the indentations of the statements are changed, and statements are aligned by keywords. use_space_around_operators If True spaces are used around all operators. indent_tabs If True tabs instead of spaces are used for indentation. indent_width The width of the indentation, defaults to 2. wrap_after The column limit (in characters) for wrapping comma-separated lists. If unspecified, it puts every item in the list on its own line. output_format If given the output is additionally formatted to be used as a variable in a programming language. Allowed values are “python” and “php”. comma_first If True comma-first notation for column names is used.

© Copyright 2023, Andi Albrecht Revision 8157d165 .

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

A Python based SQL formatter

License

PabloRMira/sql_formatter

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

A Python based SQL formatter

conda install -c pablormira sql_formatter

Format your SQL files via the command line

sql-formatter sql_file.sql sql_file2.sql

You can also format all your SQL-files via

To format all your SQL files recursively use

Controlling maximum length line via truncation

The sql_formatter will try to truncate too long lines in the SELECT clause for either

The default maximum line length is 82 after line stripping.

You can control the maximum length line using e.g.

sql-formatter sql_file.sql —max-line-length=50

pre-commit is a nice development tool to automatize the binding of pre-commit hooks. After installation and configuration pre-commit will run your hooks before you commit any change.

To add sql-formatter as a hook to your pre-commit configuration to format your SQL files before commit, just add the following lines to your .pre-commit-config.yaml :

repos: - repo: https://github.com/PabloRMira/sql_formatter rev: master hooks: - id: sql_formatter

If you want to install sql-formatter locally and use that instead of using pre-commit ‘s default environment, set repo: local in your .pre-commit-config.yaml file:

repos: - repo: local hooks: - id: sql_formatter name: SQL formatter language: system entry: sql-formatter files: \.sql$

repos: - repo: local hooks: - id: sql_formatter name: SQL formatter language: system entry: sql-formatter --max-line-length=50 files: \.sql$

for a custom maximum line length truncation of e.g. 50

To exemplify the formatting let’s say you have a SQL query like this

example_sql = """ create or replace table mytable as -- mytable example seLecT a.asdf, b.qwer, -- some comment here c.asdf, -- some comment there b.asdf2 frOm table1 as a leFt join table2 as b -- and here a comment on a.asdf = b.asdf -- join this way inner join table3 as c on a.asdf=c.asdf whEre a.asdf= 1 -- comment this anD b.qwer =2 and a.asdf=5 groUp by a.asdf """ 

Then you can use this package to format it so that it is better readable

from sql_formatter.core import format_sql print(format_sql(example_sql)) 

CREATE OR REPLACE TABLE mytable AS -- mytable example SELECT a.asdf, b.qwer, -- some comment here c.asdf, -- some comment there b.asdf2 FROM table1 as a LEFT JOIN table2 as b -- and here a comment ON a.asdf = b.asdf -- join this way INNER JOIN table3 as c ON a.asdf = c.asdf WHERE a.asdf = 1 -- comment this and b.qwer = 2 and a.asdf = 5 GROUP BY a.asdf 

It can even deal with subqueries and it will correct my favourite simple careless mistake (comma at the end of SELECT statement before of FROM) for you on the flow 🙂

print(format_sql(""" select asdf, cast(qwer as numeric), -- some comment qwer1 from (select asdf, qwer, from table1 where asdf = 1) as a left join (select asdf, qwer2 from table2 where qwer2 = 1) as b on a.asdf = b.asdf where qwer1 >= 0 """)) 

SELECT asdf, cast(qwer as numeric), -- some comment qwer1 FROM (SELECT asdf, qwer FROM table1 WHERE asdf = 1) as a LEFT JOIN (SELECT asdf, qwer2 FROM table2 WHERE qwer2 = 1) as b ON a.asdf = b.asdf WHERE qwer1 >= 0 

The formatter is also robust against nested subqueries

print(format_sql(""" select field1, field2 from (select field1, field2 from (select field1, field2, field3 from table1 where a=1 and b>=100)) """)) 

SELECT field1, field2 FROM (SELECT field1, field2 FROM (SELECT field1, field2, field3 FROM table1 WHERE a = 1 and b >= 100)) 

If you do not want to get some query formatted in your SQL file then you can use the marker /*skip-formatter*/ in your query to disable formatting for just the corresponding query

from sql_formatter.format_file import format_sql_commands print(format_sql_commands( """ use database my_database; -- My first view -- create or repLace view my_view as select asdf, qwer from table1 where asdf  

use database my_database; -- My first view -- CREATE OR REPLACE VIEW my_view AS SELECT asdf, qwer FROM table1 WHERE asdf  

SELECT field1, case when field2 > 1 and field2  10 and field1 = 'a' then 1 else 0 end as case_field, . FROM table1 WHERE field1 = 1 and field2  2 or field3 = 5 ORDER BY field1;

SELECT a.field1, a.field2, b.field3 FROM (SELECT field1, field2 FROM table1 WHERE field1 = 1) as a LEFT JOIN (SELECT field1, field3 FROM table2) as b ON a.field1 = b.field1;