enhanced sql formula to optionally accept custom aliases instead of a…

…,b,c (#2561) * enhanced sql formula to optionally accept custom aliases instead of a, b, c
xlwings · Dec 16, 2024 · 184f3a6 · 184f3a6
1 parent 5178907
commit 184f3a6
Show file tree

Hide file tree

Showing 2 changed files with 39 additions and 12 deletions.
diff --git a/docs/extensions.rst b/docs/extensions.rst
@@ -14,8  14,22 @@ The xlwings addin comes with a built-in extension that adds in-Excel SQL syntax
 
 .. code::
 
-    =sql(SQL Statement, table a, table b, ...)
     =sql(SQL Statement, [alias a], table a, [alias b], table b, ...)
 
 .. figure:: ./images/sql.png
 
-As this extension uses UDFs, it's only available on Windows right now.
 If there are no aliases provided, tables are aliased automatically as ``a``, ``b``, ``c``, etc. as the screenshot shows. Since v0.33.5 you can, however, provide your own alias/table name like so:
 
 .. code::
 
     =sql(
         "SELECT customers.name, o.product, o.amount
         FROM customers
         INNER JOIN b ON customers.customer_id = o.customer_id",
         "customers", A1:D4,
         "o", Orders[#All]
     )
 
 This is, you need to provide the table name/alias first before providing the range reference as the next argument, similar to how the ``=LET`` formula works in Excel.
 
 As this extension uses UDFs, it's only available on Windows right now. This formula can be used on macOS and Excel on the web though via xlwings Server.
diff --git a/xlwings/ext/sql.py b/xlwings/ext/sql.py
@@ -17,30  17,43 @@ def conv_value(value, col_is_str):
 @func
 @arg("tables", expand="table", ndim=2)
 @ret(expand="table")
-def sql(query, *tables):
-    return _sql(query, *tables)
 def sql(query, *table_or_alias):
     return _sql(query, *table_or_alias)
 
 
 @func
 @arg("tables", expand="table", ndim=2)
-def sql_dynamic(query, *tables):
 def sql_dynamic(query, *table_or_alias):
     """Called if native dynamic arrays are available"""
-    return _sql(query, *tables)
     return _sql(query, *table_or_alias)
 
 
-def _sql(query, *tables):
 def _sql(query, *tables_or_aliases):
     """Excel formula: =SQL(query, ["alias1"], range1, ["alias2"], range2, ...)"""
     conn = sqlite3.connect(":memory:")
-
     c = conn.cursor()
 
-    for i, table in enumerate(tables):
     tables = []
     current_alias = None
 
     # Process arguments into (alias, table) pairs
     for table_or_alias in tables_or_aliases:
         if len(table_or_alias[0]) == 1 and isinstance(table_or_alias[0][0], str):
             current_alias = table_or_alias[0][0]
         else:
             if current_alias is None:
                 # Auto-assign alias (A, B, C...)
                 current_alias = chr(65   len(tables))
             tables.append((current_alias, table_or_alias))
             current_alias = None
 
     for alias, table in tables:
         cols = table[0]
         rows = table[1:]
         types = [any(isinstance(row[j], str) for row in rows) for j in range(len(cols))]
-        name = chr(65   i)
 
         stmt = "CREATE TABLE %s (%s)" % (
-            name,
             alias,
             ", ".join(
                 "'%s' %s" % (col, "STRING" if typ else "REAL")
                 for col, typ in zip(cols, types)
@@ -50,7  63,7 @@ def _sql(query, *tables):
 
         if rows:
             stmt = "INSERT INTO %s VALUES %s" % (
-                name,
                 alias,
                 ", ".join(
                     "(%s)"
                     % ", ".join(