Turns Postgresql SELECT statements into INSERT statements

select2insert.py

#!/usr/bin/env python
## Turns a SELECT sql statement into INSERT statements
## for copying into another database server with the same table.
## The SELECT statement should select all the columns.
## pgAdmin III will create a SELECT statement with all the columns by
## right clicking on a table and selecting 'Scripts->SELECT'
## The statement should come from STDIN. So you can dom something like $ cat some.sql | select2insert.py
## http://stackoverflow.com/questions/1820650/sql-script-to-create-insert-script/10392124#10392124
import fileinput
import re

statement = ' '.join(fileinput.input())
statement = statement.strip()

#statement = 'select cat, dog from bingo where b=stuff'
match = re.match(r'[sS][eE][lL][eE][cC][tT]\s(.*?)\s[fF][rR][oO][mM]\s+(\w+)\s*(.*)', statement)

if (match):
	names = match.group(1).strip()
	table = match.group(2).strip()
	where = match.group(3).strip()
else:
	names = statement
	table = '<TABLE>'
	where = ''


ns = [x.strip() for x in names.split(',')]
quoted = ['quote_nullable(' + x + ')' for x in ns]
insert = "SELECT  'INSERT INTO "+ table +" ( " + (', ').join(ns) + " ) VALUES(' || " + (" || ',' || ").join(quoted)  + " || ');' FROM " + table + ' ' + where
print insert

See http://stackoverflow.com/questions/1820650/sql-script-to-create-insert-script/10392124#10392124