Is it expected that before_execute only has the statement and no param
values on a parameterized query? When I run the code below note that the
==>BE line doesn't actually have the bind values available just the
clauseelement
appears to be populated.
<output>
...
*==>BE ce: INSERT INTO testsqlatd.t (id, x) VALUES (?, ?), mp: (), p: {}*
...
</output>
If that is the case then I'm not sure how I could remove the second
parameter (field 'x') if it was None.
I've also taken a look at the before_cursor_execute to see if I can remove
null numeric bind parameters. In this case I can see the the bind
parameters (and their types) but the SQL has already been rendered which
means I can't easily remove the field reference.
<output>
...
2015-01-18 11:58:22,779 INFO sqlalchemy.engine.base.Engine ()
==>BCE kw: {'executemany': False, 'statement': u'INSERT INTO testsqlatd.t
(id, x) VALUES (?, ?)', 'parameters': (Decimal('1'), 1), 'cursor':
<pyodbc.Cursor object at 0xb5944c28>, 'context':
<sqlalchemy_teradata.base.TeradataExecutionContext object at 0xb595f80c>,
'conn': <sqlalchemy.engine.base.Connection object at 0xb595f7cc>}
2015-01-18 11:58:22,890 INFO sqlalchemy.engine.base.Engine INSERT INTO
testsqlatd.t (id, x) VALUES (?, ?)
2015-01-18 11:58:22,890 INFO sqlalchemy.engine.base.Engine (Decimal('1'), 1)
2015-01-18 11:58:22,907 INFO sqlalchemy.engine.base.Engine COMMIT
</output>
Here is the code:
<code>
####### End Boilerplate #########
def create_table(type_, engine):
metadata = MetaData(bind=engine, schema='testsqlatd')
t = Table('t', metadata, Column('id', Integer, primary_key=True),
Column('x', type_))
if metadata.bind.has_table('t'): t.drop()
t.create()
return t
def gen_insert_stmt(type_, value, engine):
t = create_table(type_, engine)
ins = t.insert().values(x=value).compile()
return ins
@event.listens_for(engine, "before_execute", named=True)
def before_execute(**kw):
print "==>BE ce: {}, mp: {}, p: {}".format(kw['clauseelement'],
kw['multiparams'], kw['params'])
@event.listens_for(engine, "before_cursor_execute", named=True)
def before_cursor_execute(**kw):
print "==>BCE kw: {}".format(kw)
ins = gen_insert_stmt(Boolean,1 , engine)
engine.connect().execute(ins)
</code>
I totally agree with many of the things you said about the broken driver
unfortunately I have verified that the current and 3 latest versions of
Teradata ODBC driver have this issue with trying to pass a None parameter
to a numeric column. This includes the Windows and Unix drivers.
I'm only trying to get this all to work as my company would like to move
off Teradata due to support costs. Because of this though the company will
not pay any additional support to TD therefore I can't get support for the
TD ODBC driver, although from what I can find this bug has been in their
ODBC driver since 2011 and my tests with the latest driver (15.00) show the
issue is still affecting the driver, so it is unlikely they would fix the
issue anyway.
I wish it wasn't like this but it is so my working plan and best idea to
get us off of their database is to re-write our current ETL pipeline to
fork the ETL data stream using SA. One stream would continue to flow to TD
and another to a more mainstream DB like PostgreSQL/Redshift. That way at
some point I could simply turn off the INSERTs to Teradata and continue to
enjoy the benefits of SA going forward. To get this all to work though at
minimum I need to get INSERT & UPDATE working to Teradata, even with all of
its warts, as well as possible.
Just to restate the issue for anyone who might like to help (besides Mike
B). I need to "rewrite" any parameterized INSERT statement that is
attempting to insert a None value into any numeric field
(Integer/Float/Real/Boolean) due to a long-standing bug in the Teradata
ODBC driver. The idea is that if the field in question doesn't have a
default clause and the field is explicitly being set in SA. It is
understood that you could simply not reference the field in the INSERT
statement but in an attempt to make this dialect relatively easy to use if
possible I was hoping to catch and fix this use case.
Thanks again if anyone can help.
On Friday, January 16, 2015 at 5:50:50 PM UTC-8, Lycovian wrote:
>
> For a Teradata SA dialect I had posted an earlier question regarding
> re-writing a query triggered only on Integer columns with primary_key=True.
> Thanks to a comment from Mike B I figured out a workaround. Today though
> I discovered thought that this issue is due to a rather nasty bug in
> Teradata's ODBC implementation. The short version is that via ODBC (in my
> case PyODBC and Ubuntu) you can not insert any numeric column as None. Le
> sigh.
>
> For example here is the issue showing a table can be inserted:
> # connection stuff here
> t = Table('test', metadata, Column('id', sqlalchemy.Integer(),
> primary_key=True), Column('x', sqlalchemy.Integer()))
> ins = t.insert().values(x=2).compile()
> engine.connect().execute(ins)
> # success!
>
> ins = t.insert().values(x=None).compile()
> engine.connect().execute(ins)
>
> ...<stack trace>
>
> /home/etl/build/sqlalchemy/lib/sqlalchemy/engine/default.pyc in
> do_execute(self, cursor, statement, parameters, context)
> 434
> 435 def do_execute(self, cursor, statement, parameters,
> context=None):
> --> 436 cursor.execute(statement, parameters)
> 437
> 438 def do_execute_no_params(self, cursor, statement,
> context=None):
>
> 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
> testsqlatd.test (id, x) VALUES (?, ?)'] [parameters: (Decimal('2'), None)]
>
> This is a rather serious bug in the driver and possibly it has been fixed
> in later versions of their products ODBC driver but I unfortunately don't
> have access to any updates.
>
> I guess the question is basically the same:
> *How do I remove a bindparameter in my dialect if the underlying datatype
> is numeric (Float/Integer/Boolean/etc) if the statement is an Insert (and
> update) and the bind value is None? *
>
> I've been looking at subclassing visit_insert or possibly visit_bindparam
> to remove parameters that are attempting to set None any number based
> parameter without success thus far. Does that sound reasonable? Anyone
> have an example of removing a bind parameter from an INSERT statement based
> on the bound value and the type?
>
>
>
>
--
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.
