时间戳

首页 > 解决方案 > Psycopg2 无法适应间歇性发生的类型“元组”问题

python - Psycopg2 无法适应间歇性发生的类型“元组”问题

问题描述

最近我们将 Django 和 Python 版本升级到以下版本

Python 3.9.5，Django 3.2，Psycopg2 2.9.1，Httpd 2,4.2，Postgres 12.8

观察到应用程序因psycopg2.ProgrammingError: can't adapt type 'tuple'错误而间歇性失败

以下cursor.execute()是执行前记录的查询和参数

{'employer_id': 200176, 'approximate_age_band': ('30-39', '50-59')

SELECT month_key as cadence_val, sum(eligible_count) as eligible
    FROM xyz_table_name 
    WHERE employer_id = %(employer_id)s      
    AND approximate_age_band IN %(approximate_age_band)s GROUP BY 1 ORDER BY 1;

下面是完整的堆栈跟踪

2021-10-27 05:00:52,280 [ERROR] django.request: Internal Server Error: mobile_report/reports
Traceback (most recent call last):
  File "/var/www/pulse_preprod/service/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: can't adapt type 'tuple'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/var/www/service_rest/service/api/views.py", line 375, in mobile_visits
    data = get_report_data(request, "VisitsReport", src_employer_id)
  File "/var/www/service_rest/service/api/views.py", line 453, in get_report_data
    data = get_data(src_employer_id, filter_params, report_key, **params)
  File "/var/www/service_rest/service/api/views.py", line 466, in get_data
    data = report_obj.get_data(employer, filter_values, **params)
  File "/var/www/service_rest/service/reports/pulse/mobile.py", line 49, in get_data
    rows1, cadence_list = self.get_rows_from_query(cursor, sql1, employer_id, table_name,
  File "/var/www/service_rest/service/reports/__init__.py", line 1415, in get_rows_from_query
    cursor.execute(sql, params)
  File "/var/www/pulse_preprod/service/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/var/www/pulse_preprod/service/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/www/pulse_preprod/service/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/var/www/pulse_preprod/service/venv/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/www/pulse_preprod/service/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.Error: can't adapt type 'tuple'

在多次尝试期间，请求运行良好几次（从数据库返回预期数据）但随机抛出此错误。

这有点排除了 python 依赖或 psycopg2 扩展或数据库数据问题，对吗？

任何帮助表示赞赏。

代码分散在多种方法中，但粘贴了要点

from django.db import connection

cursor = connection.cursor()

sql2 = """SELECT month_key as cadence_val, sum(eligible_count) as eligible FROM xyz_table_name WHERE employer_id = %(employer_id)s AND approximate_age_band IN %(approximate_age_band)s GROUP BY 1 ORDER BY 1;"""

age_param = ['30-39', '50-59']  #List of strs from request body
params  = {'employer_key': 200176, 'approximate_age_band': tuple(age_param)}

cursor.execute(sql, params)
rows = cursor.fetchall()

更新：

  1. 另一个观察结果是，当 Httpd 服务重新启动时，请求在最初的几个小时内运行良好。
  2. 其他没有任何基于元组的参数的查询一直运行良好

标签： pythondjangopostgresqlpsycopg2

解决方案

最简单的解决方法是approximate_age_band用作列表：

cur = connection.cursor()

values = {'employer_id': 200176, 'approximate_age_band': ['30-39', '50-59']}

query = """select *
from test t
where t.approximate_age_band = ANY(%(approximate_age_band)s)
"""
print(cur.mogrify(query, values).decode('utf-8')) # Show the executed SQL statement
cur.execute(query, values)
print(cur.fetchall())

出去：

select *
from test t
where t.approximate_age_band = ANY(ARRAY['30-39','50-59'])
[
  (None, None, 420, 509, '50-59'), 
  (None, None, 210, 508, '30-39')
]

推荐阅读