CODEWITHSUNDEEP
postgresqlunicodepsycopg2
Pavel Pereverzev
Pavel Pereverzev

Reputation: 499

psycopg2 does not insert unicode data

I have a script that takes data from one database and according to tables' names and fields copies it into another database. The issue is about unicode data, I need to insert some words in Russian but every time psycopg2 writes it as if it were default string.

import psycopg2
import psycopg2.extensions

conn_two = psycopg2.connect(user="postgres", password="password", host = "localhost", port= "5432", dbname = "base2")
cur_2  = conn_two.cursor()
sql = 'INSERT INTO {} ({})  VALUES {};'.format('"tb_names"', '"num", "name", "district"',  (23, 'Рынок', 'Волжский')) 
cur_2.execute(sql)
conn_two.commit()

Here is how the result looks like in pgAdmin4:

enter image description here

I also tried to set exteinsions and insert data in unicode, but in this case I have an error

import psycopg2
import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)

conn_two = psycopg2.connect(user="postgres", password="password", host = "localhost", port= "5432", dbname = "base2")
conn_two.set_client_encoding("utf-8")
conn_two.set_client_encoding('UNICODE')
cur_2  = conn_two.cursor()
sql = 'INSERT INTO {} ({})  VALUES {};'.format('"tb_names"', '"num", "name", "district"',  (23, u'Рынок', u'Волжский')) 
cur_2.execute(sql)
conn_two.commit()


Traceback (most recent call last):
  File "D:\_Scripts\pgadmin.py", line <>, in <module>
    cur_2.execute(sql)
psycopg2.ProgrammingError: ОШИБКА:  тип "u" не существует # - says that type "u" does not exist
LINE 1: ...ing_ex" ("num", "name", "district")  VALUES (23, u'\u0420\u...
                                                            ^

What should be done here?

Upvotes: 1

Views: 595

Answers (1)

Alastair McCormack
Alastair McCormack

Reputation: 27704

Don't prepare your string with the values baked in (using string formatting or concatenation).

Instead, use %s in the SQL string as placeholders, then pass your values to the .execute method as an argument.

If you're on Python 2.x, non-ASCII strings should be passed as Unicode types.

E.g.

Python 2.x

sql = 'INSERT INTO "tb_names" ("num", "name", "district") VALUES (%s, %s, %s);'
cur_2.execute(sql, (23, u'Рынок', u'Волжский'))

Python 3.x

sql = 'INSERT INTO "tb_names" ("num", "name", "district") VALUES (%s, %s, %s);'
cur_2.execute(sql, (23, 'Рынок', 'Волжский'))

Upvotes: 2

Related Questions