Python值错误：索引968处不支持格式字符“，”（0x2c）

Question

在Stackoverflow上有许多类似的问题，我在这里挖掘了一段时间。然而，我仍然找不到解决我的具体问题的办法。你知道吗

我正在使用Python雪花连接器使用sql文件提取数据。我的文件目录如下所示：

SQL
   pay_gbd.sql
data_extract.py

支付_gbd.sql数据库如下所示，有两个参数：开始拾取数据和结束拾取日期：

WITH
payment as
(
    select
          confirmation_number
        , payment_statuses
        , case
            when lower(payment_statuses) like '%%show%%' then 'NOSHOW'
            when lower(payment_statuses) like '%%cancel%%' then 'CANCEL'
            else 'SHOW'
          end as payment_status
    from payment_table
),

rawData as 
(   select *
    from booking_table g
    where
        g.pu_timestamp >= %(start_pickup_date)s and g.pu_timestamp < %(end_pickup_date)s
        and g.supplier_car_days > 0)

select *
from rawData;

如您所见，我在like中使用了“%%s%%”以避免错误。你知道吗

数据_提取.py具有以下代码：

def executeSQLScriptsFromFile(filepath, param_dict):

    ctx = snowflake.connector.connect(
        user='USER_NAME',
        account='SECRET_1',
        region='us-east-1',
        warehouse='SECRET_2',
        database='SECRET_3',
        role='SECRET_4',
        password='SECRET_5')

    fd = open(filepath, 'r')
    query = fd.read()
    fd.close()
    print(query)
    cs = ctx.cursor()
    try:
        cur = cs.execute(query, param_dict)
        df = pd.DataFrame.from_records(iter(cur), columns=[x[0] for x in cur.description])
    finally:
        cs.close()
    ctx.close()

    return df

def extract_pay_gbd(start_pickup_date, end_pickup_date):
    pay_gbd_sqlpath = os.path.join(os.getcwd(), 'sql/pay_gbd.sql')

    print('Start extracting pay_gbd data from Snowflake, pickup date range: {} to {}'.format(start_pickup_date,                                                                                            end_pickup_date))
    param_dict = {'start_pickup_date': start_pickup_date, 'end_pickup_date': end_pickup_date}
    pay_gbd = executeSQLScriptsFromFile(pay_gbd_sqlpath, param_dict)

    return pay_gbd

但是，当我运行extract\u pay\u gbd函数时，总是会出现以下错误：

  File "C:\Users\...\data_extract.py", line 45, in executeSQLScriptsFromFile
    cur = cs.execute(query, param_dict)
  File "C:\Users\...\snowflake\connector\cursor.py", line 458, in execute
    query = command % processed_params
ValueError: unsupported format character ',' (0x2c) at index 968

打印（查询）的输出看起来与.sql文件中的查询完全相同：

WITH
payment as
(
    select
          confirmation_number
        , payment_statuses
        , case 
            when lower(payment_statuses) like '%%show%%' then 'NOSHOW'
            when lower(payment_statuses) like '%%cancel%%' then 'CANCEL'
            else 'SHOW'
          end as payment_status
    from payment_table
),

rawData as 
(   select *
    from booking_table g
    where
        g.pu_timestamp >= %(start_pickup_date)s and g.pu_timestamp < %(end_pickup_date)s
        and g.supplier_car_days > 0)

select *
from rawData;

如有任何建议，我们将不胜感激！你知道吗

Answer 1

根据@JohnGordon的提示，我发现并解决了这个问题。希望这里的解决方案对那些新来的雪花Python连接器有所帮助。你知道吗

问题是我在sql文件的注释部分中有一个“%”。删除它，代码将运行完美。你知道吗