python - psycopg2:“偏移”处或附近的语法错误,日期时间对象
问题描述
我正在使用 Python 和 psycopg2 运行以下 SQL 查询并且出现错误。
SQL查询:
UPDATE consumers SET is_valid = False WHERE system_id = 'SAMPLEID607' AND time IN (datetime.datetime(2020, 7, 21, 17, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 7, 0, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 22, 55, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)));
错误:
syntax error at or near "offset"
LINE 1: ...7, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330...
^
ERROR:root:SyntaxError: Error in invalidate_values function,
Traceback (most recent call last):
File "/location...../validation.py", line 246, in invalidate_values
CURSOR.execute(query)
psycopg2.errors.SyntaxError: syntax error at or near "offset"
LINE 1: ...7, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330...
^
UPDATE mqtt_consumer SET is_valid = True WHERE created_at >= '2020-08-22 12:50:26.935465';
ERROR:root:SyntaxError: Error in invalidate_values function, InFailedSqlTransaction: Error in validate_values function,
Traceback (most recent call last):
File "/location....../validation.py", line 265, in validate_values
CURSOR.execute(query)
psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block
变量及其值
SYSTEMS = {'SAMPLEID607': [{'timestamps': [datetime.datetime(2020, 7, 21, 17, 44, 29, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 7, 0, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None)), datetime.datetime(2020, 7, 24, 22, 55, 43, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=330, name=None))], 'values': [58788430000.0, 58788430000.0, 9.697836e-38]}]}
LAST_UNVALIDATED = '2020-08-22 12:50:26.935465'
Python函数:
def invalidate_values():
for k, v in SYSTEMS.items():
query = '''UPDATE consumers SET is_valid = False WHERE system_id = '{}' AND time IN {};'''.format(k, tuple(v[0]['timestamps']))
CURSOR.execute(query)
CONNECTION.commit()
return 1
def validate_values():
# validate all the rest of systems based on LAST_UNVALIDATED date
query = "UPDATE consumers SET is_valid = True WHERE created_at >= '{}';".format(LAST_UNVALIDATED)
CURSOR.execute(query)
CONNECTION.commit()
return 1
我应该如何解决这个问题?
解决方案
['timestamps']
我通过将列表中的值转换为str
from解决了这个问题datetime
。
推荐阅读
- iis - 将 410 错误重定向到 404 错误的 IIS 规则
- sql - 如何通过 SQL 查询提高订单的性能
- c# - 如何对跨度进行排序
在基于另一个 Span 的值的 ref 结构内 在同一个 ref 结构中? - navigation - 积压导航级别
- excel - 更新 zip 后更新 MS Word 中的工作表对象
- php - preg_match() : wordpress 网站中的 nextgen-gallery 插件错误
- javascript - 告诉 async() 函数等待完成 - Puppeteer 自动化项目
- google-translate - Google Translation V3 API - 词汇表资源错误
- postgresql - 如果其他表列具有值 else false,postgresql 更新 true
- python - 如何加载用于 CPU 推理的火炬模型