Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

psycopg2 The error message does not match the actual situation #1634

Open
a984120978 opened this issue Oct 13, 2023 · 11 comments
Open

psycopg2 The error message does not match the actual situation #1634

a984120978 opened this issue Oct 13, 2023 · 11 comments

Comments

@a984120978
Copy link

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
Copy link
Member

What is the encoding of your database?

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

@a984120978
Copy link
Author

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

@dvarrazzo
Copy link
Member

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
Copy link
Author

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
Copy link

eayin2 commented Dec 3, 2023

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
Copy link
Member

@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
Copy link

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
image

@Xiaoguangnihao
Copy link

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

如何处理呢?

@Kevin0Jung
Copy link

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

如何处理呢?

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

@BaobaoAndDabao
Copy link

我是来自中国程序员 , 我尝试解决了这个问题, 只需要使用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
Copy link

solved for me after giving correct password for the user.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

7 participants