CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
vastlysuperiorman
vastlysuperiorman

Reputation: 1784

Python: Start psql query, don't wait for response

I am using python and psycopg2 to start a COPY TO CSV that will take a long time (possibly hours). The copy to file work is to be handled by postgres, so no information needs to be returned to my python script.

Is there a way for me to pass the query to postgres, and then disconnect without waiting for a response so my program can work on other tasks?

Here is the method that starts the job:

def startJob(self):
    #This bit will take the information and flags from the file and start the psql job
    conn = psycopg2.connect('dbname=mydb user=postgres')
    cur = conn.cursor()
    beginClause = "COPY ("
    selectClause = """SELECT '%s' FROM db """ % ','.join(self.flags)
    whenClause = """WHERE 'start' BETWEEN '%s' AND '%s'""" % self.info['begin'] self.info['end']
    destClause = """) TO '/dest/%s' WITH CSV HEADER""" % self.name

    fullQuery = beginClause + selectClause + whenClause + destClause

    #I want the execute to start the job, and then return so that I can
    #resume regular operation of the program
    cur.execute(fullQuery) 

    conn.close()
    self.changeStatus('progress')

Upvotes: 4

Views: 4854

Answers (1)

Greg
Greg

Reputation: 6759

There is an async ability in psycopg2, you can't disconnect, but you can effectively run your job in the background and wait for the result (if you wish). See:

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

about halfway down:

conn = psycopg2.connect(database='mydb', async=1)

If you run your job on that connection you should be able to let it run without your program's attendance. If you want to jump in async with both feet, I recommend looking at txpostgres.

http://txpostgres.readthedocs.org/

-g

Upvotes: 8

Related Questions