Python调用Google Ads API时如何在SQL查询中插入日期变量

你不需要使用sqlite3的相关方法，该类方法仅适用于操作本地SQLite数据库，Google Ads API的GAQL查询仅需要传入符合语法要求的字符串即可，直接使用Python字符串格式化功能将已生成的yesterday变量插入查询语句即可。

具体修改方案 #

你只需要将原代码中的固定字符串查询，替换为f-string格式化的动态字符串即可，注意保留日期值两侧的单引号（GAQL要求字符串类型的条件值用单引号包裹）。

修改后的核心代码片段 #

# 用f-string将yesterday变量插入查询语句，注意保留外侧的单引号
query = f"""
    SELECT
      campaign.id,
      campaign.name,
      metrics.cost_micros,
      campaign.status
    FROM campaign
    WHERE segments.date = '{yesterday}'
        """

如果你的Python版本低于3.6不支持f-string，可以用format方法实现相同效果：

query = """
    SELECT
      campaign.id,
      campaign.name,
      metrics.cost_micros,
      campaign.status
    FROM campaign
    WHERE segments.date = '{}'
        """.format(yesterday)

完整修改后代码参考 #

import argparse
import sys

from google.ads.googleads.client import GoogleAdsClient
from google.ads.googleads.errors import GoogleAdsException
from datetime import date, timedelta

def main(client, customer_id):
    # 生成前一天的日期字符串，放到main函数内避免全局变量作用域问题
    yesterday_date = date.today() - timedelta(days=1)
    yesterday = yesterday_date.strftime("%Y-%m-%d")
    ga_service = client.get_service("GoogleAdsService")

    # 动态插入日期的查询语句
    query = f"""
        SELECT
          campaign.id,
          campaign.name,
          metrics.cost_micros,
          campaign.status
        FROM campaign
        WHERE segments.date = '{yesterday}'
            """

    # 发起流式搜索请求
    response = ga_service.search_stream(customer_id=customer_id, query=query)

    for batch in response:
        for row in batch.results:
            print(
                f"Campaign with ID {row.campaign.id}, status is {row.campaign.status}, cost is {row.metrics.cost_micros} and name "
                f'"{row.campaign.name}" was found.'
            )

注意事项 #

• 你已经生成的yesterday格式为YYYY-MM-DD，完全符合Google Ads API对segments.date字段的格式要求，无需额外调整
• 如果运行时出现变量找不到的报错，确认日期生成逻辑和查询语句在同一个作用域内即可

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