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.
Advertisement
Answer
Use psycopg2 to establish the data connection. There are quite a few examples in the documentation:
Once you have your data source configured, iterate through the results of your “SELECT” statement building a INSERT INTO
statement by 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 simply 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