Python Profiling 'psycopg2._psycopg.cursor' takes long time

Question

I was trying to profile my python script to figure out why it takes so ling time to process data. Using cProfile and pstats, I got this result:

Tue Jan 20 08:49:08 2015    C:/Python27/profilingResults/profile_dump_1

         24236665 function calls (24236295 primitive calls) in 1566.843 seconds

   Random listing order was used

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
        1    0.000    0.000    0.000    0.000 C:\Python27\Lib\numbers.py:270(Rational)
     2745    0.003    0.000    0.010    0.000 C:\Python27\Lib\decimal.py:1768(_round_half_even)
        2    0.000    0.000    0.000    0.000 C:\Python27\Lib\calendar.py:71(__init__)
        1    0.000    0.000    0.000    0.000 C:\Python27\Lib\logging\__init__.py:458(format)
        1    0.000    0.000    0.000    0.000 C:\Python27\Lib\logging\__init__.py:1281(handle)
        1    0.000    0.000    0.000    0.000 C:\Python27\Lib\atexit.py:6(<module>)
        1    0.000    0.000    0.000    0.000 C:\Python27\Lib\decimal.py:321(Overflow)
     9539 1548.536    0.162 1548.536    0.162 {method 'execute' of 'psycopg2._psycopg.cursor' objects}

the last line seems not ok but I didn't get it. Why cursor causes this cost?

import psycopg2 as psycopg
try:
  connectStr = "dbname='postgis20' user='postgres' password='' host='localhost'"
  cx = psycopg.connect(connectStr)
  cu = cx.cursor()
  logging.info("connected to DB")
except:
  logging.error("could not connect to the database")

    global cx
        try: 
                 cu.execute("INSERT INTO taxi (userid,carNum) SELECT '"+str(msg['UserID'])+"',"+str(msg['CarNumber']))
                 cu.execute
                 cu.execute
                 cu.execute
                 cu.execute
                 ..
                 ..
                 ..
                 .

        except Exception, err:
                 print('ERROR: %s\n' % str(err))
                 cx.commit()
       cx.commit()

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

The cursor cost is due to the call to the execute function in the second block of code.(usually executing of the sql query and returning the result will take a little more time than any primitive operation)

you can find a lot of help with profiling here

edited May 23 '17 at 12:20

Community

1
1

answered Jan 20 '15 at 06:58

gman

1,242
2
16
29

score 1 · Answer 2 · answered Jan 20 '15 at 07:23

1

You have 9539 inserts with 0.162ms per insert. Sounds pretty normal. You can use batch insert or copy_from to speedup the whole thing.

answered Jan 20 '15 at 07:23

bav

1,543
13
13

Python Profiling 'psycopg2._psycopg.cursor' takes long time

2 Answers2