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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
