python - python将列表值发送到postgres
问题描述
我可以使用这个 python 脚本读取可用文件的内容:
import psycopg2
from config import config
import os
path = 'my_path'
user_id = '000'
session_id = '12345'
params = config()
conn = None
try:
params = config()
conn = psycopg2.connect(**params)
cur = conn.cursor()
for root, dirs, files in os.walk(path):
for file in files:
if file.endswith('.plt'):
with open(os.path.join(root, file), 'r') as f:
content = f.readlines()[6:]
values = [line.strip().split(',') for line in content]
print(values)
query = "INSERT INTO tempo (user_id, session_id, lat, lon, flag, alt, days, gpsdate,gpstime)" \
"VALUES (%{user_id}, %{session_id},%s,%s,%s,%s,%s,%s,%S)"
#cur.executemany(query, values)
cur.close()
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
finally:
if conn is not None:
conn.close()
输出:
[['40.014395', '116.30643', '0', '492', '39751.986099537', '2008-10-30', '23:39:59'], ['40.014535', '116.306443', '0', '492', '39751.9861226852', '2008-10-30', '23:40:01'], ['40.014629', '116.30649', '0', '492', '39751.9861574074', '2008-10-30', '23:40:04'], ['40.014487', '116.306449', '0', '491', '39751.9861921296', '2008-10-30', '23:40:07'], ['40.014487', '116.306449', '0', '491', '39751.9862268518', '2008-10-30', '23:40:10'], ['40.014495', '116.306446', '0', '491', '39751.98625', '2008-10-30', '23:40:12'], ['40.014502', '116.306467', '0', '491', '39751.9863078704', '2008-10-30', '23:40:17'], ['40.014501', '116.306427', '0', '491', '39751.9863657407', '2008-10-30', '23:40:22'], ['40.014481', '116.306393', '0', '491', '39751.9864236111', '2008-10-30', '23:40:27'], ['40.013746', '116.306367', '0', '82', '39751.9864467593', '2008-10-30', '23:40:29'], ['40.01376', '116.306418', '0', '77', '39751.9865046296', '2008-10-30', '23:40:34'], ['40.013771', '116.306424', '0', '81', '39751.9865625', '2008-10-30', '23:40:39']]
[['39.984094', '116.319236', '0', '492', '39744.2451967593', '2008-10-23', '05:53:05'], ['39.984198', '116.319322', '0', '492', '39744.2452083333', '2008-10-23', '05:53:06'], ['39.984224', '116.319402', '0', '492', '39744.2452662037', '2008-10-23', '05:53:11'], ['39.984211', '116.319389', '0', '492', '39744.2453240741', '2008-10-23', '05:53:16'], ['39.984217', '116.319422', '0', '491', '39744.2453819444', '2008-10-23', '05:53:21'], ['39.98471', '116.319865', '0', '320', '39744.2454050926', '2008-10-23', '05:53:23']]
但是,当我尝试将这些值发送到 postgres 表 uncommenting#cur.executemany(query, values)
时,我得到 list out of range 错误。
$python dump.py
list index out of range
我该如何解决这个问题?
解决方案
让您的生活更轻松,只需使用 %{field_name}s 占位符。然后你不必担心索引。仅供参考:
%{user_id}, %{session_id},%s,%s,%s,%s,%s,%s,%S)
不适用于此:
cur.executemany(query, values)
有两个原因:
1)这%{user_id}
应该是%{user_id}s
2)命名占位符需要从字典中获取它们的值,例如:
{"user_id": 1, "session_id": 2}
推荐阅读
- php - 如何将 URL 中的子文件夹重写为 PHP 获取参数
- c# - 正则表达式匹配当前年份日期
- c++ - 向量的向量初始化可以进行范围构造吗?
- python - 如何从非透视平面文件生成透视时间序列表
- json - 如何在反应表格单元格中包含 url
- powershell - 脚本环境影响父 shell
- javascript - 在 IDEA (PhpStorm) 的“结构”面板中显示私有 JS 道具
- c++ - 为什么返回指针而不是变量本身对于具有引用返回类型的函数是正确的
- project-reactor - 为什么 Project Reactor 的 Mono 没有 share() 运算符?
- r - S3 - R 中的延迟方法注册