Repository: meltano/sdk
Pull request #2200 (2024-01-30 21:45:38)

fix(targets): Quote column names in INSERT statement
- Update sql.py
- Mattwill09 patch 1 (#1)
- Update singer_sdk/sinks/sql.py
- Use IdentifierPreparer


<!-- readthedocs-preview meltano-sdk start -->
----
📚 Documentation preview 📚: https://meltano-sdk--2200.org.readthedocs.build/en/2200/

<!-- readthedocs-preview meltano-sdk end -->

codspeed-hq[bot]: ## [CodSpeed Performance Report](https://codspeed.io/meltano/sdk/branches/edgarrmondragon/fix/insert-quote-column-names)

### Merging #2200 will **not alter performance**

<sub>Comparing <code>edgarrmondragon/fix/insert-quote-column-names</code> (2334a17) with <code>main</code> (af3b678)</sub>



### Summary

`✅ 6` untouched benchmarks





codecov[bot]: ## [Codecov](https://app.codecov.io/gh/meltano/sdk/pull/2200?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Comparison is base [(`af3b678`)](https://app.codecov.io/gh/meltano/sdk/commit/af3b678ddda34112b56954d568fe152a7e45dfbf?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) 88.43% compared to head [(`2334a17`)](https://app.codecov.io/gh/meltano/sdk/pull/2200?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano) 88.46%.


<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##             main    #2200      +/-   ##
==========================================
+ Coverage   88.43%   88.46%   +0.02%     
==========================================
  Files          54       54              
  Lines        4714     4716       +2     
  Branches      961      962       +1     
==========================================
+ Hits         4169     4172       +3     
+ Misses        384      383       -1     
  Partials      161      161              
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/meltano/sdk/pull/2200?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=meltano).


Patch:
diff --git a/singer_sdk/sinks/sql.py b/singer_sdk/sinks/sql.py
index 3a37570d..991ba431 100644
--- a/singer_sdk/sinks/sql.py
+++ b/singer_sdk/sinks/sql.py
@@ -10,6 +10,7 @@ from textwrap import dedent
 
 import sqlalchemy as sa
 from pendulum import now
+from sqlalchemy.sql import quoted_name
 from sqlalchemy.sql.expression import bindparam
 
 from singer_sdk.connectors import SQLConnector
@@ -279,10 +280,14 @@ class SQLSink(BatchSink):
             An insert statement.
         """
         property_names = list(self.conform_schema(schema)["properties"].keys())
+        column_identifiers = [
+            self.connector.quote(quoted_name(name, quote=True))
+            for name in property_names
+        ]
         statement = dedent(
             f"""\
             INSERT INTO {full_table_name}
-            ({", ".join(property_names)})
+            ({", ".join(column_identifiers)})
             VALUES ({", ".join([f":{name}" for name in property_names])})
             """,  # noqa: S608
         )


Test patch:
diff --git a/tests/samples/test_target_sqlite.py b/tests/samples/test_target_sqlite.py
index 0d34c60a..edf88ee9 100644
--- a/tests/samples/test_target_sqlite.py
+++ b/tests/samples/test_target_sqlite.py
@@ -484,14 +484,15 @@ def test_record_with_missing_properties(
                 "properties": {
                     "id": {"type": "integer"},
                     "name": {"type": "string"},
+                    "table": {"type": "string"},
                 },
             },
             [],
             dedent(
                 """\
                 INSERT INTO test_stream
-                (id, name)
-                VALUES (:id, :name)""",
+                (id, name, "table")
+                VALUES (:id, :name, :table)""",
             ),
         ),
     ],