CODEWITHSUNDEEP
pythonsqlsql-serversqlalchemypyodbc
Jack
Jack

Reputation: 538

Python SQL: Error reading and executing SQL file

I'm trying to red and execute a SQL file in Python using sqlalchemy. Should be simple, right?

conn=sqlalchemy.create_engine('mssql+pyodbc://' + DSN).connect()
query = open('../toy_example.sql',encoding="utf-8").read()
trans = conn.begin()
conn.execute(query)
trans.commit()

I get this error

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '\ufeff'. (102) (SQLExecDirectW)")
[SQL: drop table temp;

With t0 as (select 1+1)
select * into temp from t0]

Why am I getting this error? I'm not sure if this is a file encoding error or a SQLAlchemy error. Ideally, this should be simple.

EDIT:

This code works fine, assuming the table temp exists: 

conn=sqlalchemy.create_engine('mssql+pyodbc://' + DSN).connect()
query = "drop table temp; With t0 as (select 1+1 t) select * into temp from t0"
trans = conn.begin()
conn.execute(query)
trans.commit()

EDIT 2:

For reference, here is a link to the file toy_example.sql.
http://s000.tinyupload.com/index.php?file_id=62746453331292257227

Upvotes: 2

Views: 1167

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

(I saved it as encoded UTF-8 codepage 65001 in SQL Server Management Studio)

The UTF-8 option near the top of the "Encoding" list in the SSMS "Advanced Save Options" dialog is "UTF-8 with signature"

enter image description here

That option will write the Unicode BOM (Byte Order Mark), encoded as \xEF\xBB\xBF, at the beginning of the file

enter image description here

If we read the file in Python using the standard "utf-8" encoding we get the Unicode BOM character \ufeff included in the string

with open(r"C:\Users\Gord\Desktop\SQLQuery1.sql", encoding="utf-8") as f:
    s = f.read()
print(repr(s))  # '\ufeffSET NOCOUNT ON;'

However, if we read the file using Python's "utf-8-sig" encoding then we get the string with the BOM character removed

with open(r"C:\Users\Gord\Desktop\SQLQuery1.sql", encoding="utf-8-sig") as f:
    s = f.read()
print(repr(s))  # 'SET NOCOUNT ON;'

Upvotes: 1

Related Questions