psycopg2 The error message does not match the actual situation

[a984120978]

This is a bug tracker
If you have a question, such has "how do you do X with Python/PostgreSQL/psycopg2" please write to the mailing list or open a question instead.

Please complete the following information:

• OS:win11
• Psycopg version:2.9.9
• Python version:3.11
• PostgreSQL version:15.9
• pip version 22.1
• PC NAME: pc20070343

Describe the bug
Please let us know:
Another computer is connected normally

I cannot connect to myself using PC20070343

conn = psycopg2.connect( host="pc20070343", port="5432")
Traceback (most recent call last):
File "C:\Users\Ot903056\PycharmProjects\PublicServer\test.py", line 4, in
conn = psycopg2.connect(
^^^^^^^^^^^^^^^^^
File "C:\Users\Ot903056\PycharmProjects\PublicServer\venv\Lib\site-packages\psycopg2_init_.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xd6 in position 84: invalid continuation byte

BUT!
conn = psycopg2.connect( host="127.0.0.1", port="5432") Successfully connected!

conn = psycopg2.connect( host="locationhost", port="5432") # not input database

Traceback (most recent call last):
File "C:\Users\Ot903056\PycharmProjects\PublicServer\test.py", line 4, in
conn = psycopg2.connect(
^^^^^^^^^^^^^^^^^
File "C:\Users\Ot903056\PycharmProjects\PublicServer\venv\Lib\site-packages\psycopg2_init_.py", line 122, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xd6 in position 61: invalid continuation byte

The error message does not match the actual situation

[dvarrazzo]

What is the encoding of your database?

What happens if you connect with the same connection string using psycopg 3?

[a984120978]

UTF8,
Other computer links to pcname are normal
Local connection must be 127.0.0.1 or localhost

[dvarrazzo]

Sorry, apart from the encoding, I forgot to ask, what is the database language?

What happens if you repeat the same operations using psycopg 3? Or with psql, using the same connection string?

[a984120978]

pip install psycopg
ip = 'pc20070343'
engine = create_engine(f'postgresql+psycopg://{user}:{password}@{ip}:5432/big_data?client_encoding=utf8')
sqlalchemy.exc.OperationalError: (psycopg.OperationalError) connection failed: :a879:f4a4:63c3:cebc), port 5432 failed: ��������: û���������� "fe80::a879:f4a4:63c5:cebc%14", �û� "pc20070343", ���ݿ� "mcs", no encryption �� pg_hba.conf ��¼
(Background on this error at: https://sqlalche.me/e/20/e3q8)
change ip = 127.0.0.1'
Successfully connected!

[eayin2]

I run into the same issue. If you run different versions of postgresql, then the database you want to connect to, might not be on the default postgresql port 5432, but on the next port 5433. If you use psycopg3 you will see the correct error message about a wrong port, but in psycopg2 an ambigious UnicodeDecodeError error is displayed.

[dvarrazzo]

@eayin2 thank you for confirming that the issue is fixed in psycopg 3.

Fixing the issue in psycopg2 is not trivial, so it will not be done, unless someone wants to fund for the development.

[Kevin0Jung]

hey dude, I got the same problem, and I solved it. Are you from China? If you create database with Collate and Ctype in Chinese but not in en_US.utf8 that will case the UnicodeDecodeError

[Xiaoguangnihao]

嘿老兄，我也遇到了同样的问题，我已经解决了。你是从中国来的吗？如果你使用中文**Collat​​​​​​e和Ctype**创建数据库，但不是en_US.utf8 这样，屁股UnicodeDecodeError

如何处理呢？

[Kevin0Jung]

嘿老兄，我也遇到了同样的问题，我已经解决了。你是从中国来的吗？如果你使用中文**Collat​​​​​​e和Ctype**创建数据库，但不是en_US.utf8 这样，屁股UnicodeDecodeError

如何处理呢？

创建数据库时指定Collate and Ctype为en_US.utf8

[BaobaoAndDabao]

我是来自中国程序员 , 我尝试解决了这个问题, 只需要使用psycopg3作为后端数据库的驱动就可以解决这个问题:
DATABASE_URL = (
f"postgresql+psycopg://{config['username']}:{config['password']}@"
f"{config['host']}:{config['port']}/{config['database']}"
)

创建 SQLAlchemy 引擎和会话

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

解释: f"postgresql+psycopg://{config['username']}:{config['password']}@" 中的 psycopg 代表使用 psycopg3

[infosec-ssk-dev]

solved for me after giving correct password for the user.