PostgreSQL copy to with encoding using psycopg2

Question

I am fetching data from remote database and trying to write it with UTF8 encoding as CSV file. This is what I have now:

query = "select * from hotels limit 50000"
output_query = "copy ({0}) to stdout with csv header ecnoding 'UTF8'".format(query)
filename = "hotels.csv"

with open(filename, 'w') as f:
    cur.copy_expert(output_query, f)

But this usually throws:

UnicodeEncodeError: 'charmap' codec can't encode character '\u016f' in position 52: character maps to <undefined>

If i change query limit to lets say 1000 mentioned exception does not occur, but if i try to load generated csv file using pandas, following exception occurs:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe4 in position 14: invalid continuation byte

Do you have any idea what is the problem? How to export sql table to csv the proper way? Thanks in advance!

i_rezic · Accepted Answer · 2019-09-18 09:13:24Z

2

I should have added encoding='utf-8' in open function.

answered Sep 18, 2019 at 9:13

i_rezic

3722 silver badges17 bronze badges

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

1 Comment

mlissner Over a year ago

It's good practice to always set the encoding in your open() commands. There's a Semgrep rule for this if you use CI, that can catch it.

Collectives™ on Stack Overflow

PostgreSQL copy to with encoding using psycopg2

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related