After significant web searching your articles on psycopg2 data retrieval are
the most comprehensive available. Below is a code block representing the
problem. The database is quite large, about 5 GB with several million
records. in the example ce_norm_norm is a string and row_num in an integer.
The method works if it modified to create a text file output. There have not
been problems in creating the large file. That approach allow a sql update
of the original file, however it is manual and error prone.
The preferred solution would be to create an update statement to accomplish
the following:
"update opace1 as o set ce_norm_add = t.ce_norm_add from trans as t where
o.row_num = t.row_num;"
Is a second cursor i.e. cursoro needed?
Given that the value of t.ce_norm_add (above) is available in values
records[0] and record and row_number record[1] is it possible to update
ce_norm_add in the table?
(Note: the example is not exact, the input string is in a separate column
and not overwritten. ce_norm_add is a new column and is being initialized.)
I hope this reaches you. Dan
conn_string = "host='localhost' dbname='opace0215' user='dan' port=5432"
print ("Connecting to database\n ->%s" % (conn_string))
conn = psycopg2.connect(conn_string)
cursori = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
--- cursoro = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursori.execute('select ce_norm_add, row_num from opace1')
i = 10
while i != 0
records = cursori.fetchone()
record = records[0]
.... calculate new record value
records[0] = record
? how to update database cell??
? "update opace1 as o set ce_norm_add = t.ce_norm_add from trans as t where
? o.row_num = t.row_num;" ??
i = i-1