Pandas read_sql_query with SQL file encoded in UTF-8

Question

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.

Thanks. I tried changing query.read() to query.read().decode('utf-8-sig') and got "AttributeError: 'str' object has no attribute 'decode'". How would I use .decode() in this case? — 34293045
– 34293045, Commented Aug 26, 2021 at 18:06
You could try encoding the string first, then re-decoding query.read().encode("utf-8").decode() — Caleb Courtney
– Caleb Courtney, Commented Aug 26, 2021 at 18:08
Are there any characters which cannot be converted to ANSI? Are you sure it's UTF-8 and not UTF-16? — Charlieface
– Charlieface, Commented Aug 26, 2021 at 18:12
Sorry, I said decode, then re-encode, when I meant the other way around. Ultimately, it looks like there is bad encoding in the query somewhere (maybe a new line character?), so that's what you want to get rid of. — Caleb Courtney
– Caleb Courtney, Commented Aug 26, 2021 at 18:12

Frodnar · Accepted Answer · 2021-08-27 08:48:44Z

2

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().

edited Aug 27, 2021 at 8:48

Frodnar

2,2722 gold badges8 silver badges21 bronze badges

answered Aug 26, 2021 at 18:40

34293045

214 bronze badges

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

1 Comment

Caleb Courtney Over a year ago

And now I feel dumb too! Of course, you read the file, and so there's nothing left to read. So the string is length 0 when it's sent via the query.

Collectives™ on Stack Overflow

Pandas read_sql_query with SQL file encoded in UTF-8

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related