Save SQL to YAML as is

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 ....

I use following settings of YAML

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

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 ....

Is there any way to achieve output #1?

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*/'}

Solution

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 never automatically 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)

will give you:

{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 to handformat 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 ...."}

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.

You can do:

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)

and get a YAML literal scalar with preserved newlines:

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.