On 6/4/15 6:20 PM, Sylvester Steele wrote:
Hi Guys,
We use SQLAlchemy (version 0.7.6) only to get the raw cursor, then use
that to execute queries.
I just ran into the following issue with BULK INSERT statements:
I have a table with 4 columns which has a primary key constraint. If
the primary key constraint is violated, I get an error. This is how it
should be.
However, if I insert 'NaN' into a column with a floating point number,
no error is thrown. The BULK INSERT process is actually halted, and
not all data is inserted. Here is my query (with sensitive info changed)
BULK INSERT DB_NAME..TBL_NAME FROM '\\file_on_server' WITH
(FIELDTERMINATOR= ',',ROWTERMINATOR=
'\n',FIRE_TRIGGERS,CHECK_CONSTRAINTS, MAXERRORS= 0, BATCHSIZE= 1000)
The offending row looks like this:
SOME_STR,SOME_DT,SOME_INT,nan
Here is the code that runs this query:
engine_con = engine.connect()
engine_con = engine_con.connection.connection
cursor = engine_con.cursor()
orig_autocommit = engine_con.autocommit
engine_con.autocommit = True
res = cursor.execute(sql)
that's the raw DBAPI cursor so this has to do with the DBAPI you're
using (pyodbc, pymssql, etc.). You'd have to figure it out at that
end, SQLAlchemy isn't involved once you have the cursor object. The
MSSQL drivers are pretty rough.
If I run this on SQL server studio I get the following:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character
for the specified codepage) for row 388594, column 4 (COL_NAME).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an
error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Why is the cursor.execute statement not throwing this error? What can
I do to get access to it?
Thanks!
--
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [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.
