将 SQL 按原样保存到 YAML

问题描述

我想以我自己的格式将 SQL 保存到 YAML 文件中，如下所示:

I want to save SQL to YAML-file in my own format as below:

(1)
sql: SELECT DISTINCT p.id_product,
                     p.price AS price,
                     sp.reduction AS discount
       FROM ....

我使用以下 YAML 设置

I use following settings of YAML

yaml.safe_dump(app_config,
               stream,
               indent=4,
               default_flow_style=False,
               encoding='utf-8',
               allow_unicode=True)

但是我得到了 YAML 的经典"丑陋输出

however I got 'classic' ugly output of YAML

(2)
sql: SELECT DISTINCT p.id_product, p.price AS price, sp.reduction AS discount, sp.reduction_type
    AS discount_type, pl.description_short AS description FROM ....

有什么方法可以实现输出 #1?

Is there any way to achieve output #1?

附注.repr(config) 等于:

PS. repr(config) equal:

{'mapping': {'/*ID_LANG*/': 'AND pl.id_lang IN (/*VALUE*/)', '/*REFERENCE*/': "AND p.reference LIKE '%/*VALUE*/%'", }, 'sql': 'SELECT DISTINCT p.id_product, p.price AS price, sp.reduction AS discount, sp.reduction_type AS discount_type, pl.description_short AS description, pl.name AS name, i.id_image as image, p.reference AS model, m.name AS manufacturer, pl.available_now AS stock_status FROM /*PREFIX*/product p LEFT JOIN /*PREFIX*/product_lang pl ON (p.id_product = pl.id_product) LEFT JOIN /*PREFIX*/manufacturer m ON (m.id_manufacturer = p.id_manufacturer) LEFT JOIN /*PREFIX*/image i ON (i.id_product = p.id_product) LEFT JOIN /*PREFIX*/specific_price sp ON (sp.id_product = p.id_product) LEFT JOIN /*PREFIX*/category pc ON p.id_category_default = pc.id_category WHERE i.cover = 1 /*WHERE*/'}

推荐答案

如果您的输入格式是一些未格式化的 SQL(没有换行符和缩进空格)，就像您似乎从输出 (2) 中获取的一样，您将 从不自动获得不错的输出:

If your input format is some unformatted SQL (no newlines and indent spaces), like you seem to have taken from the output (2) you will neverautomatically get nice output:

import yaml

sql = ("SELECT DISTINCT p.id_product, "
                      "p.price AS price, "
                      "sp.reduction AS discount, "
                      "sp.reduction_type AS discount_type, "
                      "pl.description_short AS description "
                      "FROM ....")
app_config = dict(sql=sql)
print yaml.dump(app_config)

会给你:

{sql: 'SELECT DISTINCT p.id_product, p.price AS price, sp.reduction AS discount, sp.reduction_type
    AS discount_type, pl.description_short AS description FROM ....'}

正如你所发现的.你可以尝试使用换行符和缩进手动格式化字符串

as you found out. You can try tohandformat the string with newlines and indentation

app_config = dict(sql="""\
SELECT DISTINCT p.id_product,
                p.price AS price,
                sp.reduction AS discount,
                sp.reduction_type AS discount_type,
                pl.description_short AS description
    FROM ....""")
print yaml.dump(app_config)

但输出也好不到哪里去:

but the output is not much better:

{sql: "SELECT DISTINCT p.id_product,\n                p.price AS price,\n        \
    \        sp.reduction AS discount,\n                sp.reduction_type AS discount_type,\n\
    \                pl.description_short AS description\n    FROM ...."}

我建议您采用不同的方法并安装一个 sql 格式化程序，例如 sqlparse 或 format-sql 结合 ruamel.yaml(我是 PyYAML 增强版的作者)，它支持多行文字字符串往返.在一点帮助下，它还可以用于生成正确且更好(如果不是更好)的 YAML 输出.

I suggest you take a different approach and install an sql formatter like sqlparse or format-sql in combination with ruamel.yaml (I am the author of that enhanced version of PyYAML), which supports multi-line literal string roundtripping. With a little help it can also be used to generate correct and better (if not goodr) looking YAML output.

你可以这样做:

import ruamel.yaml
from ruamel.yaml.scalarstring import PreservedScalarString
import sqlparse

sql = ("SELECT DISTINCT p.id_product, "
       "p.price AS price, "
       "sp.reduction AS discount, "
       "sp.reduction_type AS discount_type, "
       "pl.description_short AS description "
       "FROM ....")
fsql = sqlparse.format(sql, reindent=True, keyword_case="upper").encode('utf-8')

app_config = dict(sql=PreservedScalarString(fsql))
print ruamel.yaml.dump(app_config, Dumper=ruamel.yaml.RoundTripDumper)

并获得一个 保留换行符的 YAML 文字标量:

sql: |-
  SELECT DISTINCT p.id_product,
                  p.price AS price,
                  sp.reduction AS discount,
                  sp.reduction_type AS discount_type,
                  pl.description_short AS description
  FROM ....

希望足够接近你想要的.

Hopefully close enough to what you wanted.

这篇关于将 SQL 按原样保存到 YAML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！