Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
218 views
in Technique[技术] by (71.8m points)

python - Issue for insert using 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]) )

I am getting the following error:

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The INSERT statement has invalid syntax. There something wrong inside the for loop you mention.
You should include the for loop in the question.

INSERT INTO "DUMMY1km" (data) VALUES ([[-3000 -3000 -3000 .....

A valid statement could look like this - assuming your column is of type integer[].
... which you should also include in the question.

INSERT INTO "DUMMY1km"(data) VALUES ('{-3000, -3000}'::int[])

or

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[])

or

INSERT INTO "DUMMY1km"(data) VALUES (ARRAY[[-3000, -3000],[-3000, -3000]])

More on array value input in the manual.

Ergo:

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 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];'

Disclaimer: I am an expert with PostgreSQL, not so much with Python. For somebody who knows Python better than me, it should be easy to format the string accordingly. I found the above quote in a quick research on the web.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...