问题描述
我正在使用Pytables模块从.mat文件读取数据.读取数据后,我想使用psycopg将此数据插入数据库.这是一个示例代码段:
I am reading data from a .mat file using the Pytables module. After reading the data, I want to insert this data into the database using psycopg.Here is a sample code piece:
file = tables.openFile(matFile)
x = 0
#populate the matData list
for var in dest:
data = file.getNode('/' + var)[:]
matData.append(data)
x = x+1
#insert into db
for i in range(0,x):
cur.execute("""INSERT INTO \"%s\" (%s) VALUES (%s)""" % tableName,dest[i],matData[i]) )
我遇到以下错误:
Traceback (most recent call last):
File "./loadDBFromMAT.py", line 111, in <module>
readInputFileAndLoad(args.matFileName,args.tableName)
File "./loadDBFromMAT.py", line 77, in readInputFileAndLoad
cur.execute("INSERT INTO \"%s\" (%s) VALUES (%s)" % (tableName,dest[i],matData[i]) )
psycopg2.ProgrammingError: syntax error at or near "["
LINE 1: INSERT INTO "DUMMY1km" (data) VALUES ([[-3000 -3000 -3000 .....
如果有人可以提出解决办法,那就太好了.谢谢!
It would be great if anyone can suggest a workaround for this.Thanks!
推荐答案
INSERT
语句的语法无效.您提到的for循环中有问题.
The INSERT
statement has invalid syntax. There something wrong inside the for loop you mention.
一个有效的语句可能看起来像这样-假设您的列的类型为integer[]
.
A valid statement could look like this - assuming your column is of type integer[]
.
INSERT INTO "DUMMY1km"(data) VALUES ('{-3000, -3000}'::int[])
或
INSERT INTO "DUMMY1km"(data) VALUES (ARRAY[-3000, -3000]) -- note the "ARRAY"
或用于二维数组(看起来与错误味精类似):
or for a 2-dimensional array (looks a bit like that in the error msg.):
INSERT INTO "DUMMY1km"(data) VALUES ('{{-3000, -3000}, {-3000, -3000}}'::int[])
或
INSERT INTO "DUMMY1km"(data) VALUES (ARRAY[[-3000, -3000],[-3000, -3000]])
有关手册中数组值输入的更多信息
More on array value input in the manual.
matData [i]必须包含ARRAY[-3000, -3000]
或其他列出的有效语法变体之一,而不是[[-3000 -3000 -3000 ...
,后者对整数数组无效.
matData[i] needs to contain ARRAY[-3000, -3000]
or one of the other listed variants of valid syntax instead of [[-3000 -3000 -3000 ...
which isn't valid for an integer array.
Psychopg自动将 PostgreSQL数组转换为 Python列表.构建INSERT时,需要将列表转换回数组.我引用了此处:
Psychopg automatically converts a PostgreSQL array into a Python list. When building the INSERT, you need to convert the list back to an array. I quote from here:
Python lists are converted into PostgreSQL ARRAYs:
>>> cur.mogrify("SELECT %s;", ([10, 20, 30], ))
'SELECT ARRAY[10, 20, 30];'
这篇关于使用psycopg插入的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!