Python bigquery API中的SQL查询中出现“意外的字符串文本”错误，并带有反勾号

Question

我尝试在BigQuery数据集中遍历表，执行SQL查询并导出表结果。尝试在for循环中包含表名时出错。在

for i in range(0, 3):
  dataset_id = 'test_dataset'
  tabelname = "test"+ str(i) 
  tableOutName = tabelname + "_cleaned"

  job_config = bigquery.QueryJobConfig()

  # Set the destination table
  table_ref = client.dataset(dataset_id).table(tableOutName)
  job_config.destination = table_ref
  sql = "\"\"\"" + " SELECT * FROM " + "`my-bucket-name.{}.{}` ".format(dataset_id, tabelname) +  "WHERE SAFE.ST_GeogFromText(WKT) IS NOT NULL " +"\"\"\""

  # Start the query, passing in the extra configuration.
  query_job = client.query(
      sql,
      location='EU',
      job_config=job_config)  # API request - starts the query

  query_job.result()  # Waits for the query to finish
  print('Query results loaded to table {}'.format(table_ref.path))

Answer 1

在Python代码中，documentation中三引号的用法是delimiters for the string，而不是字符串本身的一部分。在

因此，必须相应地更改以下代码行：

  sql = "\"\"\"" + " SELECT * FROM " + "`my-bucket-name.{}.{}` ".format(dataset_id, tabelname) +  "WHERE SAFE.ST_GeogFromText(WKT) IS NOT NULL " +"\"\"\""

文档是这样使用的：

^{pr2}$