Save SQL to YAML as is

Question

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

can we assume the {1) and (2) are not part of the YAML output? Can you include the output of repr(app_config)? — Anthon, May 20 '15 at 18:46
I should have quoted that. The string "(1)" resp. "(2)" that are in the code markup block (above the "sql") those are not part of YAML block. I tried to put that outside of the block, but then you get a lot of white space. — Anthon, May 21 '15 at 04:41

score 2 · Accepted Answer · answered May 20 '15 at 19:33

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.

Save SQL to YAML as is

1 Answers1

Linked