CODEWITHSUNDEEP
pythonsqltype-conversion
user3734568
user3734568

Reputation: 1461

Reading SQL table in python

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?

Upvotes: 5

Views: 3814

Answers (2)

Joshua Schlichting
Joshua Schlichting

Reputation: 3450

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.

Upvotes: 5

Mureinik
Mureinik

Reputation: 311188

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,))

Upvotes: 3

Related Questions