Re: is there a way to make this more efficient

On 06/09/2015 08:08 AM, Dan Sawyer wrote:
> Below is a snip it of python/psycopg2 code. It is inefficient when
> compared with parallel logic that creates a file and then updates the
> table in postgres sql from the file. In a test data base it takes 24
> seconds to update 100,000 records. The actual database is over 5,000,000
> records. The data base is on a solid state drive, I would imagine it
> would be very inefficient on a hard drive.
>
> # init lines
> conn_string = "host='localhost' dbname='opace0421' 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)
> work_mem = 2048
> cursori.execute('SET work_mem TO %s', (work_mem,))
> cursori.execute('select address_2, row_num from opace')
> i = 1
> while i != 100000:
> records = cursori.fetchone()
> record = records['address_2']
> rn = str(records['row_num'])
>
> #python code to create replacement string s
>
> cursoro.execute("UPDATE opace SET p_norm_add = %s WHERE row_num
> = %s", (s, rn,))

Build a list of dicts with s and rn and then use executemany():

http://initd.org/psycopg/docs/cursor.html

This separates the Python manipulations from the database operation.

> i = i+1
> conn.commit()
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com