5

I am trying to read SQL table in python. below script I was using and able to read data correctly. 

Data = pd.read_sql_query("SELECT * from Data where [ID] = " + id ,engine)

But when in type for column ID changed to nvarchar, I got below error.

DatabaseError: Execution failed on sql 'SELECT * from Data where [ID] = 123': ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to numeric. (8114) (SQLExecDirectW)')

Is there any way in which we can filter the table using a nvarchar column?

Improve this question
1
  • 1
    ID value needs to be the same data type, if nvarchar then it has to be like this where [ID] = '123' Commented Sep 21, 2018 at 13:49

2 Answers 2

Reset to default
5

Important note:

I really need to emphasize that while this solution did solve the original question's problem, string concatenation used within SQL query text usually indicates a SQL injection vulnerability. Don't do this. Consider that most packages and libraries for SQL connections will offer a bona fide method for passing data into your query using placeholders along with parameters- use this. If you are determined to use string concatenation anyway- don't concatenate data from external sources/users/clients/anyone but 'you.' You have been warned.

Original answer

If ID is a nvarchar, you need to be casting id to be that as well.

Data = pd.read_sql_query("SELECT * from Data where [ID] = CAST(" + id + " AS NVARCHAR(10))", engine)

NOTE: I passed in a 10 for your NVARCHAR(size), but you'll ultimately have to determine what that should be.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I tried above but got blank dataframe, type of variable id in python is str. should i use LIKE in place of = when executing query
It sounds like your data type error issue has been solved. If you're having a SQL specific issue now, it would be wise to open a new SO question. The data type in Python isn't going to matter, what matters is the raw text being passed to the server. If your error is gone, please accept the answer and create a new SQL question and we can help you figure out why you aren't getting the results you expected :) . Do not just start changing out things in your query. If you're not getting results, it means there isn't an ID that matches what you've queried.
able to get desired results from your answer, thanks a lot for your help
@user3734568 It was my pleasure. On a side note, if you are getting the value forid from a user facing interface, consider the security issues that Mureinik mentioned about SQL injections.
3

SQL Server will attempt to convert the nvarchar column to an integer to match the literal type of the ID, and as you've seen, that won't fly - instead you need to pass the ID as an nvarchar literal. One way to do is with single quotes and a preceding N:

Data = pd.read_sql_query("SELECT * from Data where [ID] = N'" + id + "'" ,engine)

But such string manipulation techniques make your code vulnerable to SQL Injection attacks. A better idea would be to pass the ID as a parameter:

Data = pd.read_sql_query("SELECT * from Data where [ID] = %s", engine, params=(id,))
Improve this answer

2 Comments

This will not run (first example). The string is not closed properly. I'm not able to edit it for you since the edit would be less than 6 character changes.
@JoshuaSchlichting oops, I missed a " there - thanks for noticing. Edited and fixed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.