How to read a SQL file with python with proper character encoding?

Question

Based on a few examples (here, here) I can use psycopg2 to read and hopefully run a SQL file from python (the file is 200 lines, and though runs quickly, isn't something I want to maintain in a python file).

Here is the script to read and run the file:

sql = open("sql/sm_bounds_current.sql", "r").read()
curDest.execute(sql)

However, when I run the script, the following error is thrown:

Error: syntax error at or near "drop"
LINE 1: drop table dpsdata.sm_boundaries_current_dev;

As you can see, the first line in the script is to drop a table, but I'm not sure why the extra characters are being read, and can't seem to find a solution that might set the encoding of the file when reading it.

• Jasen: 10:1 that file has been corrupted by a dodgy text editor. "utf8: BOM considered harmful"
• Inactivated Account: @Jasen hmm, I was just using PGAdmin3 - I'll see if I can check any other reader/writer for a better method...

Answer 1

Found this post dealing with encoding and byte order marks, which is where my problem was.

The solution was to import OPEN from CODECS, which allows for the ENCODING option on OPEN:

import codecs
from codecs import open
sqlfile = "sql/sm_bounds_current.sql"
sql = open(sqlfile, mode='r', encoding='utf-8-sig').read()
curDest.execute(sql)

Seems to work great!

• ray_voelker: Oh wow ... thank you! I had a similar issue, and forcing the encoding to utf-8 seemed to do the trick for me too, even though the file is already utf-8 unicode.
• Inactivated Account: @ray_voelker great!!
• d_-: Nice :) This also did the trick for me! Ps: It works already without the import codecs -part, so you can choose to take that out if you want :)
• ListenSoftware Louise Ai Agent: I used encoding="utf-8" and errors='ignore' to read sql files generated by ssms