Repository: inspera/blackbricks (language: python)

Issue:
Fix handling of magic sql in sql cells
This bugfix fixes the issue when %sql is not on a separate line.

**Change of source code:**

- Resulting SQL cell is correct, (even) when %sql is not on a separate line
  - Example:
    - %sql SELECT current_date
  - it doesn't matter if NewLine or SPACE or TAB (or any combination of SPACE/TAB/NewLine) follows %sql
- Exactly 1 additional (empty) line is added after %sql in the resulting/formatted output **always**. It means, the output is deterministic.
- In addition the list **magics[]** has been removed from the code. It is/was not used.

**Test cases adapted:**

Added test cases to test some weird cases, when:
- MAGIC %sql is not on a separate line (Databricks allows this)
- MAGIC %sql is followed by 0/1/multiple NewLines

-------------------

The program was tested solely for our own use cases, which might differ from yours. 

-------------------

Dejan.Hrubenja dejan.hrubenja@mercedes-benz.com 

-------------------

Mercedes-Benz Tech Innovation GmbH (ehemals/formerly Daimler TSS GmbH)
Wilhelm-Runge-Straße 11
89081 Ulm, Germany
Phone: +49 731 50506
Fax: +49 731 5056599
E-Mail: [techinnovation@mercedes-benz.com](mailto:techinnovation@mercedes-benz.com)

Sitz und Registergericht/Domicile and Register Court: Ulm, HRB-Nr./Commercial Register No.: 3844
Geschäftsführung/Management: Daniel Geisel (Vorsitzender/Chairperson), Isabelle Krautwald

https://www.mercedes-benz-techinnovation.com/en/imprint/












Discussion / hints:

Fix patch:
diff --git a/blackbricks/blackbricks.py b/blackbricks/blackbricks.py
index 2114456..0bef1b7 100644
--- a/blackbricks/blackbricks.py
+++ b/blackbricks/blackbricks.py
@@ -95,14 +95,13 @@ def _format_sql_cell(
     if NOFMT in cell.strip().splitlines()[0]:
         return title_line + cell
 
-    magics = []
     sql_lines = []
-    for line in cell.strip().splitlines():
-        if line.strip().startswith("# MAGIC %sql"):
+    for line in (line.strip() for line in cell.strip().splitlines()):
+        if line == "# MAGIC %sql":
             continue
-        words = line.split()
-        magic, sql = words[:2], words[2:]
-        magics.append(magic)
+        elif line.startswith("# MAGIC %sql"):
+            line = line.replace(" %sql", "", 1)
+        sql = line.split()[2:]  # Remove "# MAGIC".
         sql_lines.append(" ".join(sql).strip())
 
     return (
@@ -111,7 +110,9 @@ def _format_sql_cell(
         + "\n".join(
             f"# MAGIC {sql}"
             for sql in sqlparse.format(
-                "\n".join(sql_lines), reindent=True, keyword_case=sql_keyword_case
+                "\n".join(sql_lines).strip(),
+                reindent=True,
+                keyword_case=sql_keyword_case,
             ).splitlines()
         )
     )


Test patch:
diff --git a/test_notebooks/test.py b/test_notebooks/test.py
index bc72502..e8380ed 100644
--- a/test_notebooks/test.py
+++ b/test_notebooks/test.py
@@ -71,4 +71,11 @@ def test_func(input_param):
 # MAGIC           COLLECT_SET(bar.fizz)[0],
 # MAGIC           FIRST(bar.baz)
 # MAGIC    FROM dsa.asd bar
-# MAGIC    GROUP BY bar.fizzbuzz);
\ No newline at end of file
+# MAGIC    GROUP BY bar.fizzbuzz);
+
+# COMMAND ----------
+
+# MAGIC %sql
+# MAGIC SELECT id
+# MAGIC FROM test_table
+# MAGIC LIMIT 1
\ No newline at end of file