Postgresql Database Backup Using Python

Question

I would like to backup database using Python code. I want to backup some tables of related data. How to backup and how to choose desired tables using "SELECT" statement?

e.g.

I want to get data from 2014-05-01 to 2014-05-10 of some tables and output this result as .sql extension file

How can I get this format using python code? If you don't mind, please explain. Thanks.

Answer 1

Use psycopg2 to establish the data connection. There are quite a few examples in the documentation:

http://initd.org/psycopg/

Once you have your data source configured, iterate through the results of your "SELECT" statement building a "INSERT INTO" statement via printing the result set to a file. Basically some reverse logic.

That way, if the time comes and you need to use your backup file, you simple run the SQL file which inserts the data back in...

Example:

        import psycopg2
        import sys


        con = None

        try:

            con = psycopg2.connect(database='local', user='local', password='local',port='1970')
            cur = con.cursor()
            cur.execute('SELECT x FROM t')
            f = open('test.sql', 'w')
            for row in cur:
              f.write("insert into t values (" + str(row) + ");")
        except psycopg2.DatabaseError, e:
            print 'Error %s' % e
            sys.exit(1)
        finally:
            if con:
                con.close()

Then to restore:

psql <dbname> <username> < test.sql

Cheers,