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