使用 sqlalchemy 转义单引号字符以执行 sql 查询

Question

我正在将大量 json 数据加载到雪花中。 json 行的某些值包含单引号，当我尝试使用 parse_json 将 json 插入到雪花中时，它会返回错误。我使用 sqlalchemy 作为 python 的连接器。 这是我的代码

connection.execute(
                f'insert into json_demo select parse_json( """{json.dumps(data)}""" )')

json数据示例如下：

[
  {
    "name": "Milka Luam",
    "ID": "124",
    "UnitAddressLine1": "1262 University Runa",
    "UnitCity": "Jas sti'n"
  },
  {
    "name": "Rahu Liran",
    "ID": "541",
    "UnitAddressLine1": "1262 University Blina",
    "UnitCity": "Rish 21"
  },
  ...
]

unity city Jas sti'n 中的单引号返回错误。 这是错误：

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): SQL compilation error:
syntax error line 1 at position 47 unexpected 'UnitCity'.
syntax error line 1 at position 47 unexpected 'UnitCity'.
parse error line 1 at position 90,841 near '<EOF>'.

我无法手动添加转义字符，因为我正在加载大量行。

Answer 1

使用 f 字符串插入值很容易出错并且存在安全风险。请改用 SQLAlchemy 的文本函数并绑定参数。

# Demonstrate query text
import json

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


json_ = json.dumps({'a': "Jas sti'n"})

stmt = sa.text("""insert into json_demo select parse_json(:json_)""")

stmt = stmt.bindparams(json_=json_)
print(
    stmt.compile(
        dialect=postgresql.dialect(), compile_kwargs={'literal_binds': True}
    )   
)

输出：

insert into json_demo select parse_json('{"a": "Jas sti''n"}')

要执行该语句，您需要执行以下操作

stmt = sa.text("""insert into json_demo select parse_json(:json_)""")
with engine.begin() as conn:
    conn.execute(stmt, {'json_': json_})

Answer 2

作为解决方案，您可以使用三引号 "Jas sti'n":

"""Jas sti'n"""

[
  {
    "name": "Milka Luam",
    "ID": "124",
    "UnitAddressLine1": "1262 University Runa",
    "UnitCity": """Jas sti'n"""
  }
]

或者您可以在引号前放置一个反斜杠以转义it:

"Jas sti\'n"

[
  {
    "name": "Milka Luam",
    "ID": "124",
    "UnitAddressLine1": "1262 University Runa",
    "UnitCity": "Jas sti\'n"
  }
]

这两种解决方案都使 python 将 ' 读取为字符串的一部分。