Re: how to update specific cells

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(dot)klaver(at)aklaver(dot)com