ChatGPT 并非总是理解 SQL，但这个 Python 工具可以

原文towardsdatascience.com/say-goodbye-to-sql-headaches-with-this-python-tool-75099f5ff33dhttps://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/f411ec0f210c2545786c1022c49304d5.pngImage by 2023852 from Pixabay如果你是一位每天与数据打交道的开发者或分析师SQL 查询对于许多不同的角色来说将是必不可少的技能之一。当我还在大学里当导师的时候有一个学生抱怨 SQL 是世界上最糟糕的编程语言。好吧SQL 是否是“编程语言”可能存在争议。然而毫无疑问它在语法方面相当结构化和严格。因此实现这样一个工具来完全理解其含义将会相对容易。不我们不需要 ChatGPT。在这篇文章中我将介绍一个帮助我们解析 SQL 查询甚至可以编程构建查询的库。如果你不知道为什么我们需要这样做那么阅读剩余内容将更加重要。1. 安装https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/976f3767eed4a1ccf77e6092e3c2291e.pngImage by AndreasAux from Pixabay像往常一样这部分内容可能有些枯燥但无论如何我必须包含这一部分。要安装这个库只需简单地运行以下命令。pip install sqlglot此外如果你处于一个更加灵活的环境比如你的笔记本电脑建议安装该软件包及其 Rust 分词器。这将使解析过程更加高效。pip3 installsqlglot[rs]然后在我们的 Python 代码中在使用之前我们应该导入这个库。在本文的所有示例中我将使用别名sg来代表库sqlglot因为我们在这个包中需要使用多个不同的函数。如果你想运行我的示例请别忘了运行下面的代码行。importsqlglotassg2. 之间不同 SQL 方言的转换https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/0be18d27fb7f56006514273268b6dc40.pngImage by Joe from Pixabay这是今天这个库将提供的第一个用例。我会给你一个我在工作中的真实用例。这发生在两年前。我们有一个遗留的数据可视化工具它连接到我们的 Azure Synapse然后运行大量的 SQL 查询以获取渲染到图表中的结果。我们的一个项目是退役 Azure Synapse 并拥抱 Databricks。然而问题是在那个可视化工具中有超过 500 个仪表板和数千个 SQL 查询需要从 T-SQL 转换为 Databricks SQL 以实现兼容性。我们为此付出了很多努力。当时不幸的是我们并不知道这个sqlglot库。现在让我们举一个简单的例子。假设我们有一个以下的 Databricks SQL 查询。大多数语法应该是通用的但LIMIT 10是针对某些数据库管理系统的特定用法。databricks_querySELECT * FROM Customer LIMIT 10如果你熟悉 SQL Server 或 Azure Synapse它们使用不同的语法来限制结果集的行数。那就是SELECT TOP n ... FROM ...。现在让我们看看如何使用sqlglot非常容易地反编译查询。sg.transpile(databricks_query,readdatabricks,writetsql)[0]在上面的代码中我们调用了sqlglot中的transpile()函数。然后我们提供了 SQL 语句databricks_query。然后我们告诉该函数这是一个 Databricks 方言readdatabricks并且我们希望它将其反编译为 T-SQLwritetsql。值得注意的是该函数实际上会返回一个列表。这是以防查询字符串中包含多个 SQL 语句的情况如下所示。SELECT*FROM Customer LIMIT10;SELECT*FROM Product LIMIT10;好的回到我们的反编译。我们只需要按照以下方式打印反编译后的 SQL 查询。print(sg.transpile(databricks_query,readdatabricks,writetsql)[0])我们可以看到查询已经正确地反编译为 T-SQL。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/19a6f4ed7a12208e203aff3480d4343d.png当然我们也可以将其从 T-SQL 反编译回 Databricks SQL如下所示。tsql_querySELECT TOP 10 * FROM Customerprint(sg.transpile(tsql_query,readtsql,writedatabricks)[0])https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/1d29f2968a5fb63a33f9f0b48c21e0b5.png另一个需要提到的是sqlglot在反编译时会尽力保留原始查询中的所有元素。例如如果查询中有一个注释它将保留它。tsql_query -- Get 10 Customers SELECT TOP 10 * FROM Customer print(sg.transpile(tsql_query,readtsql,writedatabricks)[0])上述代码给出了以下输出。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/664ec7f5df7273e0440dd45719d35190.png3. 美观格式化https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/646c53d32f3c4156915139da09f0c665.pngImage by Radosław Cieśla from Pixabay毫不奇怪的是sqlglot可以格式化 SQL 查询并以美观的格式输出因为它毕竟可以理解它。只需继续前面的例子尽管它输出带有注释的反编译查询但所有内容都在一行中。现在如果我们想提高可读性我们只需简单地将prettyTrue添加到函数中。print(sg.transpile(tsql_query,readtsql,writedatabricks,prettyTrue)[0])https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/868d77a8a6e717b5b95007f365a46ef0.png看看注释不仅有一个新行查询本身也被格式化为美观样式。当然我们可以有一个更复杂的 SQL 查询如下所示。它也可以被格式化。sql WITH baz AS (SELECT a, c FROM foo WHERE a 1) SELECT f.a, b.b, baz.c, CAST(b.a AS REAL) d FROM foo f JOIN bar b ON f.a b.a LEFT JOIN baz ON f.a baz.a print(sg.transpile(sql,writedatabricks,identifyTrue,prettyTrue)[0])上述函数identityTrue表示目标方言未指定源方言也将用作目标方言。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/0b148007b6aee094d90a400de201a948.png4. 获取查询的元数据https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/929c183093e9c6064d44f18cfe36ac71.png图片由LUM3N在Pixabay提供这里还有一个来自我工作的实际用例。在我们的数据平台上每天都有数百个用户向我们的数据库发送即兴查询。我们有一个强烈的需求那就是我们想知道“在某个特定时间谁查询了哪个表”。这个信息非常有用。例如一旦我们想要更改任何表我们就知道可能会受到影响的人。此外一些一年多没有使用过的表可能是一个减少或移除数据摄入频率的潜在机会以节省我们的云成本。不幸的是Azure Synapse 的日志并没有提供这样的信息从 SQL 查询中提取表也不是一件容易的事情。由于当时我们并不知道sqlglot所以我们编写了一个完整的正则表达式分支来实现这个功能。假设我们有一个如下所示的查询。它有点复杂因为它包含了一个 CTE公共表表达式。如果sqlglot可以处理这个查询那么它也能处理任何类型的子查询。# SQL querysql with cte1 as ( SELECT a.col1, a.col2, b.col3, b.col4, c.col5 FROM Table_A a LEFT JOIN Table_B b ON a.id b.id ) SELECT * FROM cte1 LEFT JOIN Table_C c on cte1.col1 c.col1 # Function to extract table namesparsedsg.parse_one(sql)让我来举例说明为什么我们使用parse_one()函数。在sqlglot中还有一个名为parse()的函数。与上面的transpile()函数类似它能够解析一个查询中的多个语句并返回一个数组。在我们的上下文中我不想让例子过于复杂。所以让我们只使用一个语句parse_one()函数给我们一个解析树这更容易展示。在将 SQL 查询解析到变量parsed之后我们可以看看里面有什么。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/82b9ac91b76eb6c1ccfd2d7940362358.pngsqlglot有其术语来标记以树形层次结构对象的形式表示的查询。使用sqlglot不需要理解这个术语。所以不用担心我会向你展示。现在让我们从解析对象中获取所有表。fortable_expinparsed.find_all(sg.exp.Table):print(table_exp)https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/d4f232d48f2309a41bc9a00047f5887d.png在上面的代码中我们只是调用了find_all()函数到解析对象上它返回了我们正在寻找的一切的列表。sg.exp.Table是一个枚举它只是告诉函数我们特别在寻找表。同样还有sg.exp.Column、sg.exp.Schema等等。好吧上面输出中仍然有两个问题。让我们假设我们只关心表名称而不是表别名和 CTE 名称。虽然这次可能不会完全现成但仍然不难且相当直观地实现。简单的想法是我们想要获取所有的 CTE 名称然后获取所有的表名称无论在表名称集中还是在 CTE 名称集中但不在表名称集中的都是感兴趣的。首先让我们找到所有的 CTE 名称。ctesset()forcteinparsed.find_all(sg.exp.CTE):ctes.add(cte.alias)枚举项sg.exp.CTE告诉find_all()函数获取所有 CTE。然后我们将cte.alias添加到ctes集中。然后让我们获取一个集中的所有表。我们可以通过调用table_exp.text(this)来获取标识符从而得到只包含名称的字符串。在我们将每个表名称添加到集中之前只需检查它是否在 CTE 集中。如果是则不要添加。tablesset()fortable_expinparsed.find_all(sg.exp.Table):table_nametable_exp.text(this)iftable_namenotinctes:tables.add(table_name)让我们执行代码并获取查询中的表名称集。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/33866a419db5b5b28b5d6edaa662c949.png5. 错误处理https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/f516a4e003befcf2e69d8b4460cecdef.pngErich Westendarp从Pixabay提供的图片到目前为止你可能想知道如果 SQL 语言无效会怎样。不用担心sqlglot会捕获它并告诉你错误在哪里以及为什么它不是一个有效的 SQL 查询。假设我们有一个以下查询。SELECT foo(FROM bar我们可以快速判断它不是一个有效的 SQL。让我们尝试解析它并捕获错误。错误是sqlglot提供的sg.errors.ParseError。importpprinttry:sg.parse(SELECT foo( FROM bar)exceptsg.errors.ParseErrorase:pprint.pprint(e.errors)在这个例子中我想仅为了演示目的利用pprint。它将以美观的格式输出错误信息。否则列表将一行打印出来。不用担心你的情况。你不必使用pprint。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/9b1b2638e6866f148536fe16ad8998d4.png我们可以看到错误信息中包含了足够关于位置和错误类型的信息。6. 以编程方式构建 SQL 语句https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/4e61ef07955b91f2d66ac11b0ae40ca3.pngeko pramono从Pixabay提供的图片为什么我们想要使用sqlglot为我们构建 SQL 语句根据你的实际场景可能有多个潜在的原因。减少 SQL 注入攻击的风险错误处理和验证可读性可维护性可扩展性我在这里只举一个例子。在代码中看到人们使用WHERE 11是很常见的。确实当处理我们不确定是否会有过滤条件的情况时这是一个聪明的解决方案。然而这是一个很好的例子证明了使用字符串构造技术构建 SQL 语句是有局限性的。也就是说我们需要处理语法不允许将一致的模式附加到查询字符串中的情况。现在让我们构建一个简单的查询来尝试sqlglot的这个功能。querysg.select(*).from_(Customer).where(sg.condition(age 18).and_(is_vip Y))print(query.sql())带下划线的from_()和and_()函数只是为了避免与 Python 的原生函数命名冲突所以请不要感到困惑这里并没有什么魔法与下划线有关。可以使用.sql()将构建的查询转换为字符串。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/53e2ae68602872796614f7bb36bd3739.png这个功能的另一个用例可能是多语言需求。也就是说一旦我们构建了查询我们就可以轻松地将其输出到不同的 SQL 语法中。让我们使用开始时提到的相同示例构建以下查询。# Building the queryquerysg.select(*).from_(Customer).limit(10)现在我们可以将查询输出到 Databricks SQL 中。# Generate query for Databricks SQL dialectsql_databricksquery.sql(dialectdatabricks)print(sql_databricks)https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/3c445bd9f0d93d2cf2012c533e8316df.png当然我们也可以轻松地以 T-SQL 语法输出它无需重新构建或转译。# Generate query for T-SQL dialectsql_tsqlquery.sql(dialecttsql)print(sql_tsql)https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/bb86ba84965e1f5aa403d3b771ffa7a6.png7. 查询优化https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/1987810fd4268e0fc29720a4d0441166.png图片由 Andrew Martin 提供来自 Pixabay最后但同样重要的是这个库也具有某种查询优化能力。例如如果我们定义了如下非常复杂的条件。bad_sqlsg.parse_one( SELECT * FROM my_table WHERE a1 OR (b2 OR (c3 AND d4)) )我可以说它甚至都不好读。现在让我们看看sqlglot如何优化它。good_sqlsg.optimizer.optimize(bad_sql)print(good_sql.sql(prettyTrue))https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/96231feebf270e477de840e98ad4f063.png它仍然很复杂但现在读起来要容易得多。除了优化语法表达式外它还可以实现常见的查询优化以提高性能。然而可能需要提供大量的上下文。如果您对此感兴趣可以亲自尝试一下。摘要https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/776edaca74a6778b933e8f49897e0033.png图片由 Mircea Ploscar 提供来自 Pixabay到目前为止我们已经看到了sqlglot提供的许多功能。我相信其中之一或多个应该有机会提高我们的生产力甚至解决我们从未能够解决的问题。这个库在生成式 AI 时代向我们展示了某种确定性是一块宝石。是的我是在说 ChatGPT 有时可能会给我们错误。然而对于像 SQL 这样的语法更加严格的编程语言我们当然不必验证sqlglot生成的 SQL 查询。肯定地说它无论如何都会正常工作 除非另有说明所有图片均由作者提供