Python SQL: Error reading and executing SQL file

Question

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

You have multiple queries in the SQL file, SQLAlchemy doesn't support executing them like that. See the duplicate for workarounds. — Barmar, Mar 12 '20 at 00:09
@Barmar. First, that's just not true. SQLAlchemy does seem to support executing multiple statements for me. See my edit — Jack, Mar 12 '20 at 15:50
@Barmar Second, my question wasn't about executing multiple sql statements, so it shouldn't have been marked as a duplciate — Jack, Mar 12 '20 at 15:52
I assumed that a .sql file would contain multiple statements. But now that I review the error message, it looks like the problem is with a Unicode escape sequence `'\ufeff'` — Barmar, Mar 12 '20 at 15:53
@Barmar No problem and thank you for re-opening the question. I've seen that particular issue before, and based on the question you linked, maybe it might depend on the type of database. I'll tag my question with the type of database just to be safe. — Jack, Mar 12 '20 at 16:10
Try searching for that in the file, and post the query that contains it — Barmar, Mar 12 '20 at 16:15
@Barmar No problem. See edit. I can't find that sequence in the file (I saved it as encoded UTF-8 codepage 65001 in SQL Server Management Studio) — Jack, Mar 12 '20 at 16:25
It's ASCII text, not encoded, I'm not sure where it's coming from if you don't see it. — Barmar, Mar 12 '20 at 16:33
@GordThompson Sorry I'm not familiar with unicode terminology. Is there something I should change in my file or in my Python code? — Jack, Mar 12 '20 at 17:23

score 1 · Accepted Answer · answered Mar 13 '20 at 11:27

(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"

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

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;'

Excellent! I think the signature part is what threw me off. Thank you for pointing it out. — Jack, Mar 16 '20 at 18:17

Python SQL: Error reading and executing SQL file

1 Answers1