Reading SQL table in python

Question

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?

Answer 1

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.