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.

10:1 that file has been corrupted by a dodgy text editor. "utf8: BOM considered harmful" — Jasen, Dec 08 '17 at 04:05
@Jasen hmm, I was just using PGAdmin3 - I'll see if I can check any other reader/writer for a better method... — DPSSpatial, Dec 08 '17 at 19:54

score 5 · Accepted Answer · answered Dec 08 '17 at 21:47

5

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!

answered Dec 08 '17 at 21:47

DPSSpatial

767
3
11
31

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. – ray_voelker Jun 07 '18 at 18:03
@ray_voelker great!! – DPSSpatial Jun 07 '18 at 19:19
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 :) – d_- Mar 30 '21 at 09:52
I used encoding="utf-8" and errors='ignore' to read sql files generated by ssms – Golden Lion Nov 09 '22 at 23:00

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

1 Answers1