public
Created

Turns Postgresql SELECT statements into INSERT statements

  • Download Gist
select2insert.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
#!/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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.