For a custom dialect I am working on I need to rewrite the INSERT statement that SA generates sometimes. In this particularly case if a column is defined as an identity column and the insert statement doesn't specify a value for the identity field I need to remove the reference to the column and its associated None value that is passed to ODBC for the INSERT statement. This is because of an issue (bug?) with the vendors ODBC implementation where if you attempt to insert a None value into an Identity column it throws an error.
For example given this table def: class Foo(Base): __tablename__ = 'foo' id = Column(sqlalchemy.Integer, primary_key=True, autoincrement=True) txt = Column(sqlalchemy.String(50), nullable=False) Which generates this DDL: 2015-01-13 12:31:47,631 INFO sqlalchemy.engine.base.Engine CREATE TABLE foo ( id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 NO CYCLE), txt VARCHAR(50) NOT NULL, PRIMARY KEY (id) ) If I attempt to do an ODBC parameterized INSERT using SA, it generates this SQL which in this specific case is invalid for the database: rec = Foo(txt='c') session.add(rec) session.commit() SA generates this SQL and passes it through to ODBC: INSERT INTO foo (id, txt) VALUES (None, 'c') This unfortunately is invalid SQL for this database if the id column is an Identity column and pyodbc throws an error: sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Teradata][ODBC Teradata Driver][Teradata Database] The source parcel length does not match data that was defined. (-2673) (SQLExecDirectW)') [SQL: u'INSERT INTO foo (id, txt) VALUES (?, ?)'] [parameters: (None, 'c')] So only in this case I am looking for a way to rewrite the above query and remove the reference to the id column. For example if the id column is an Identity column the parameterized query that is sent to pyodbc should be: INSERT INTO foo (txt) VALUES ('c') Can someone point me to any examples to rewrite my query to remove the reference to the id column (and it's None parameter) at the field level for my custom dialect? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.