Python操作BigQuery用字符串格式化写UPDATE语句报错问题

问题根源 #

你用字符串格式化生成UPDATE语句时，字符串类型的变量没有被包裹单引号，导致BigQuery把字符串值当成了SQL标识符（比如列名），从而触发语法错误。比如你的代码生成的SQL会是这样：

UPDATE `riscos-dev.survey_test.data-test-bdrn` SET informaci_meteorol_gica = Neu, risc = [你的risc值] WHERE objectid = 3

这里Neu没有单引号，BigQuery无法识别它是字符串值，因此报错。

另外，直接用字符串格式化拼接SQL语句存在SQL注入风险，这也是不推荐的写法。

解决办法 #

方法1：手动为字符串变量添加单引号（不推荐） #

在格式化时给字符串类型的参数套上单引号：

sql = "UPDATE `riscos-dev.survey_test.data-test-bdrn` SET informaci_meteorol_gica = '{inf}', risc = '{ri}' WHERE objectid = {obj_id}".format(
    inf=df.informaci_meteorol_gica[index],
    ri=df.risc[index],
    obj_id=df.objectid[index]
)

注意：如果变量值本身包含单引号，这种写法会再次触发语法错误，且无法防范SQL注入，仅适合测试场景。

方法2：使用BigQuery参数化查询（推荐） #

利用BigQuery Client的参数化查询功能，自动处理变量的类型和转义，同时避免SQL注入：

from google.cloud import bigquery

client = bigquery.Client()

# 定义带参数占位符的SQL模板
sql = """
    UPDATE `riscos-dev.survey_test.data-test-bdrn`
    SET informaci_meteorol_gica = @inf, risc = @ri
    WHERE objectid = @obj_id
"""

# 定义参数字典，键对应占位符，值包含类型和实际数据
job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("inf", "STRING", df.informaci_meteorol_gica[index]),
        bigquery.ScalarQueryParameter("ri", "STRING", df.risc[index]),
        bigquery.ScalarQueryParameter("obj_id", "INT64", df.objectid[index]),
    ]
)

# 执行查询
query_job = client.query(sql, job_config=job_config)
query_job.result()  # 等待任务完成

这种方式会由BigQuery自动处理字符串的引号包裹和特殊字符转义，彻底避免语法错误和注入风险。

内容的提问来源于stack exchange，提问作者Marc G