Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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()
- psql <dbname> <username> < test.sql
- import psycopg
- conn = psycopg2.connect("dbname=test user=postgres") # change this according to your RDBMS configuration
- cursor = conn.cursor()
- table_name='YOUR_TABLE_HERE' # place your table name here
- with open("table_dump.sql") as f:
- cursor.execute("SELECT * FROM %s" % (table_name)) # change the query according to your needs
- column_names = []
- columns_descr = cursor.description
- for c in columns_descr:
- column_names.append(c[0])
- insert_prefix = 'INSERT INTO %s (%s) VALUES ' % (table_name, ', '.join(column_names))
- rows = cursor.fetchall()
- for row in rows:
- row_data = []
- for rd in row:
- if rd is None:
- row_data.append('NULL')
- elif isinstance(rd, datetime.datetime):
- row_data.append("'%s'" % (rd.strftime('%Y-%m-%d %H:%M:%S') ))
- else:
- row_data.append(repr(rd))
- f.write('%s (%s);n' % (insert_prefix, ', '.join(row_data))) # this is the text that will be put in the SQL file. You can change it if you wish.
- def create_essentials():
- yaml_file = open("settings.yaml", 'r')
- settings = yaml.load(yaml_file)
- db_name = settings["db_name"]
- db_user = settings["db_user"]
- db_password = settings["db_password"]
- db_host = settings["db_host"]
- db_port = settings["db_port"]
- backup_path = settings["backup_path"]
- filename = settings["filename"]
- filename = filename + "-" + time.strftime("%Y%m%d") + ".backup"
- command_str = str(db_host)+" -p "+str(db_port)+" -d "+db_name+" -U "+db_user
- return command_str, backup_path, filename
- def backup_database(table_names=None):
- command_str,backup_path,filename = create_essentials()
- command_str = "pg_dump -h "+command_str
- if table_names is not None:
- for x in table_names:
- command_str = command_str +" -t "+x
- command_str = command_str + " -F c -b -v -f '"+backup_path+"/"+filename+"'"
- try:
- os.system(command_str)
- print "Backup completed"
- except Exception as e:
- print "!!Problem occured!!"
- print e
- def restore_database(table_names=None):
- command_str,backup_path,filename = create_essentials()
- command_str = "pg_restore -h "+command_str
- if table_names is not None:
- for x in table_names:
- command_str = command_str +" -t "+x
- command_str = command_str + " -v '"+backup_path+"/"+filename+"'"
- try:
- os.system(command_str)
- print "Restore completed"
- except Exception as e:
- print "!!Problem occured!!"
- print e
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement