I have a new app that already uses sqlalchemy, so has sqlalchemy table definitions already.
I'd like to use sqlalchemy to generate an SQL script to migrate data from an old app. So I want to generate a PostgreSQL insert statement something like this:
INSERT INTO widgets (public_identifier, name, spam) VALUES
('006b92ce-a767-11e6-b47e-f30689ab4e07', 'joe', 'spam'),
('1b74c518-a767-11e6-a2df-abc58de4160b', 'adam', 'ham'),
...
ON CONFLICT ON CONSTRAINT widgets_public_identifier_key DO NOTHING;
I'd like to write that to a largeish file so that it can be reviewed for correctness / moved to another machine before import into the new app's database. The file won't be too large, maybe 10000 rows (though no harm if it's COPY rather than INSERT INTO I guess).
Before anybody 'answers' this by explaining that one doesn't normally want to textually substitute parameter values in this way (e.g. for security and correctness reasons): that is true, but does not apply in this situation: here, the generated SQL file must be reviewed by a human being before being executed unchanged.
The problem I have is that I don't know how to tell sqlalchemy to substitute parameter values into the generated SQL rather than generating %s substitution parameters. Obviously I could just generate the SQL 'by hand', but since this kind of thing crops up not infrequently and seems should be convenient to do it using sqlalchemy I'd like to do it that way.
(I also don't know how to insert multiple lines in one statement, nor how best to omit the id field from the generated INSERT statements, but that's not really the focus of this question.)
Here's what I have so far:
from sqlalchemy import MetaData, Table, Column, String, Integer
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Insert
from sqlalchemy.dialects import postgresql
metadata = MetaData()
table = Table(
'widgets',
metadata,
Column('id', Integer, autoincrement=True, primary_key=True),
Column('public_identifier', String(255), unique=True, nullable=False),
Column('name', String(255), nullable=True),
Column('spam', String(255), nullable=True),
)
@compiles(Insert)
def prefix_inserts(insert, compiler, **kw):
return compiler.visit_insert(insert, **kw) + " ON CONFLICT ON CONSTRAINT widgets_public_identifier_key DO NOTHING"
q = table.insert()
print str(q.compile(dialect=postgresql.dialect()))
Which generates this output:
INSERT INTO widgets (id, public_identifier, name, spam) VALUES (%(id)s, %(public_identifier)s, %(name)s, %(spam)s) ON CONFLICT ON CONSTRAINT widgets_public_identifier_key DO NOTHING
How can I make that match the desired output above (at least as far as parameters go)?
