Обсуждение: how to update specific cells

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

On 06/08/2015 08:23 AM, Dan Sawyer wrote:
>
> 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]

You are using DictCursor so to make thinks clearer, I would use:

record = records["ce_norm_add"]

then you do not have to worry about ordering issues. I would actually
change the above to RealDictCursor so you are working with an actual
dictionary.

>      .... calculate new record value
>      records[0] = record

Same here.

>      ? 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;"  ??

See here:

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

I would use the named style as you could then pass the record dictionary
as the parameters.

>      i = i-1
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com