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
– Jasen, Commented Dec 8, 2017 at 4:05
@Jasen hmm, I was just using PGAdmin3 - I'll see if I can check any other reader/writer for a better method... — Inactivated Account
– Inactivated Account, Commented Dec 8, 2017 at 19:54

Inactivated Account · Accepted Answer · 2017-12-08 21:47:06Z

7

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 8, 2017 at 21:47

Inactivated Account

7953 gold badges14 silver badges32 bronze badges

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

4 Comments

ray_voelker Over a year ago

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 Over a year ago

@ray_voelker great!!

d_- Over a year ago

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 Over a year ago

I used encoding="utf-8" and errors='ignore' to read sql files generated by ssms

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related