CODEWITHSUNDEEP
pythonsql-serverpandasencodingpyodbc
34293045
34293045

Reputation: 21

Pandas read_sql_query with SQL file encoded in UTF-8

I'm trying to run a query saved in an SQL file that's encoded in UTF-8. My code is as follows:

import pyodbc
import pandas as pd

query = open('test.sql', 'r')

print(query.read())

server = 'server' 
database = 'database' 
username = 'username' 
password = 'password' 

cnxn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};' +
    'SERVER=' + server + ';DATABASE=' + database +
    ';UID=' + username + ';PWD='+ password
)

df = pd.read_sql_query(query.read(), cnxn)
query.close()

print(df.head())

print(query.read()) works fine and correctly shows the contents of test.sql, but read_sql_query gives the following error:

pyodbc.Error: ('HY090', '[HY090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0) (SQLExecDirectW)')

If I change the encoding of the SQL file from UTF-8 to ANSI in Notepad++ before running, the code works fine with no error. How can I make this work without changing the encoding of the SQL file beforehand? I think maybe I should use .encode() or .decode() but I can't figure out how.

Upvotes: 0

Views: 1395

Answers (1)

34293045
34293045

Reputation: 21

Ok, problem solved, and I feel stupid. I was trying to use .read() twice--first in print() and then again in read_sql_query(). I just commented out the print(query.read()) and it worked fine. So it wasn't an encoding issue after all.

When I had previously encoded the file in ANSI using Notepad++ and got it to work, I must not have had print(query.read()) first. Sorry about that, this is my first program using open() and read().

Upvotes: 2

Related Questions