Problems inserting utf8 data to PostgreSQL with Python

Question

I am reading scandinavian language websites with a web-crawler - and wish to insert them into my PostgreSQL database.

Originally I tried to encode my PSQL DB as utf-8, then manually tried to insert the characters that would be of a problem like this:

Insert into name (surname) VALUES ('Børre');

This was done in the windows PSQL shell.

This gave me the following error: ERROR: invalid byte sequence for encoding "UTF8": 0x9b. So after doing some googling I changed the client encoding to latin1. Now that statement was successfull. The server encoding is still utf8.

When I do the same insert through my python script the name appears in my database as B°rre. If I change back the encoding of client to utf8, I also get entries with wrong special characters.

My python script is utf8 encoded, but prints the name correct.

Insert statement:

con = psycopg2.connect(*database details*)

print("Opened database successfully")

cur = con.cursor()

#INSERT NAME

query = "INSERT INTO name (surname) VALUES (%s) RETURNING id"

data = ('børre')

cur.execute(query,data)

As previously stated, print(personObject.surname) gives 'Børre'

If I try the following:

query = "INSERT INTO name (surname) VALUES (%s) RETURNING id"

data = ('børre'.encode('utf-8'))

cur.execute(query,data)

I get the following in my database:

\x62c383c2b8727265

Why don't you use UTF-8 encoding? Today, there exists no reason not to use it. — Daniel
– Daniel, Commented Dec 30, 2016 at 21:39
Python version is 3.x. The reason why I changed from utf-8 is stated in the start of the question. I will update the question with stack trace asap. — bjornasm
– bjornasm, Commented Dec 30, 2016 at 21:44
The stack trace does not output anything, I get no error in python. @LaurentLAPORTE — bjornasm
– bjornasm, Commented Dec 30, 2016 at 21:53

Jasen · Accepted Answer · 2016-12-31 00:25:42Z

1

psycopg2 doesn't understand postgresql queries it just converts the arguments given into their postgresql representation

if you give it an array of bytes to will convert it to a postgresql BYTEA literal,

data = ('børre'.encode('utf-8')) gets you a bytes.

so, don't do that, use a string.

The code fragment you have at the top should work.

In the error I see ø encoded as hex c383c2b8, that hex translates to UTF8 as two charactersÃ and ¸. It looks to me like python thinks your script is not wtitten is UTF8, but instead some other codepage.

edited Dec 31, 2016 at 0:25

answered Dec 30, 2016 at 22:31

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

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

1 Comment

bjornasm Over a year ago

Thank you for your answer! Any suggestion on how I will get 'børre' to be 'børre' in the PSQL database as well?

vic.zhang · Accepted Answer · 2021-06-15 10:31:23Z

0

using client_encoding key words
eg: conn=psycopg2.connect("dbname='foo' user='dbuser' password='mypass' client_encoding='utf8'")

answered Jun 15, 2021 at 10:31

vic.zhang

1391 silver badge6 bronze badges

Collectives™ on Stack Overflow

Problems inserting utf8 data to PostgreSQL with Python

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related