我如何连接到Postgresql使用SSL从SqlAchemy+pg8000

Question

从SqlAlchemy通过pg8000连接到postgres，直到我在postgres上启用SSL。

db = create_engine('postgresql+pg8000://user:pass@hostname/dbname', echo=True).connect()

现在它似乎失败了:

File "/Library/Python/2.7/site-packages/pg8000/core.py", line 872, in __init__
raise InterfaceError("communication error", exc_info()[1])
sqlalchemy.exc.InterfaceError: (InterfaceError) ('communication error', error(61, 'Connection refused')) None None

Answer 1

您需要添加一个connect_args字典:

db = create_engine(
    'postgresql+pg8000://user:pass@hostname/dbname',
    connect_args={'sslmode':'require'},
    echo=True,
).connect()

Answer 2

被接受的答案不再有效，至少对于这些版本:

Python                   3.9
pg8000                   1.19.5
SQLAlchemy               1.4.12

pg8000文档描述了你必须做什么。使用

engine = create_engine('postgresql+pg8000://user:password@host/db', 
                       connect_args={'ssl_context': True})

将ssl.create_default_context()的结果传递给连接创建者。如果需要自定义SSL上下文，则将其作为值传递，而不是传递True:

import ssl
ssl_context = ssl.SSLContext()
# Set attributes as required  ...
engine = create_engine('postgresql+pg8000://user:password@host/db', 
                       connect_args={'ssl_context': ssl_context})