Home How to read a SQL file with python with proper character encoding?
Reply: 1

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

DPSSpatial
1#
DPSSpatial Published in 2017-12-07 22:53:53Z

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.

DPSSpatial
2#
DPSSpatial Reply to 2017-12-08 21:47:06Z

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!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.330295 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO