This should not be necessary. If the primary key value is not included in the
INSERT, it is not rendered, unless the dialect is setting up flags that tell it
to do so.
Given this example:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
data = Column(String(10))
e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(data='asdf'))
s.commit()
the output of the INSERT is:
INSERT INTO a (data) VALUES (%s)
The behavior of INSERT primary key generation is determined by flags like
postfetch_lastrowid, implicit_returning, and
preexecute_autoincrement_sequences. I think for the PK being rendered
specifically, you’re looking at preexecute_autoincrement_sequences. It’s
rendering the bound value there which it expects your dialect to place the
value of a sequence.nextval there. It sounds like you don’t want this.
The DDL here looks very similar to SQL Server so you probably want to look
closely at mssql/base.py and mssql/pyodbc.py to see how it works.
Lycovian <mfwil...@gmail.com> wrote:
> 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.
--
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.
