CODEWITHSUNDEEP
pythonpyodbc
Vilius
Vilius

Reputation: 53

"Invalid character value for cast specification" when using pyodbc in Python

I have made a python script that connects to Mssql database and inserts something there.

Problem is in this line:

cursor.execute("insert into TREND_DATA (TREND_ID, TREND_DATE, TREND_VALUE) values ((select TREND_ID from TRENDS where TREND_NAME = ?), ?, ?)", name, date, trend_value)

After this line I get this error:

cursor.execute("insert into TREND_DATA (TREND_ID, TREND_DATE, TREND_VALUE) values ((select TREND_ID from TRENDS where TREND_NAME = ?), ?, ?)", name, date, trend_value)
pyodbc.DataError: ('22018', '[22018] [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (0) (SQLExecDirectW)')

I tried putting 'abc' instead of "name" but i get the same error"

cursor.execute("insert into TREND_DATA (TREND_ID, TREND_DATE, TREND_VALUE) values ((select TREND_ID from TRENDS where TREND_NAME = ?), ?, ?)", 'abc', date, trend_value)

However this line works and I get new lines in database:

cursor.execute("insert into TREND_DATA (TREND_ID, TREND_DATE, TREND_VALUE) values ((select TREND_ID from TRENDS where TREND_NAME = 'abc'), ?, ?)", date, trend_value)

What am I doing wrong?

Upvotes: 5

Views: 13302

Answers (4)

Hugo Vares
Hugo Vares

Reputation: 1167

I was having this error and was able to solve this by matching the types from the source with the destination.

In my case, I was bulk converting all the source to string before the insert and at the destination table actually had a 2 float columns.

After matching the types, the insert worked just fine.

Upvotes: 1

Ciprian Alexandru
Ciprian Alexandru

Reputation: 91

I had a similar problem.

Used bulk_save_objects and worked pretty well, but with pyodbc 4.0.22 it began to throw a type cast error for bigints (long in python).

'[22018] [Microsoft][ODBC Driver 13 for SQL Server]Invalid character value for cast specification (0) (SQLExecDirectW)') [SQL: u'INSERT INTO [gg_expo] ([ID], [Status], [Timestamp]) VALUES (?, ?, ?)'] [parameters: ((3903146347805142551L, 1, u'2018-08-01T09:06:34.244'), (3959371193446428183L, 0, u'2018-08-01T09:06:55.128'),

I had to use add_all(the_list_of_rows). Hope this helps someone!

Upvotes: 0

L.S.
L.S.

Reputation: 506

You could use format to solve this:

cursor.execute("insert into TREND_DATA (TREND_ID, TREND_DATE, TREND_VALUE) values ((select TREND_ID from TRENDS where TREND_NAME = {0}), ?, ?)".format(name), date, trend_value)

It seems like the ? inside () is a problem.

However this has some security implications and should not be used as permanent solution.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

Changes introduced in pyodbc 4.0.22 caused several different errors while working with query parameters. Those issues were addressed by the release of pyodbc 4.0.23.

Upvotes: 3

Related Questions