Skip to content

@agentgt /select2insert.py
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Turns Postgresql SELECT statements into INSERT statements
#!/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
Something went wrong with that request. Please try again.