使用SQLAlchemy连接CockroachDB时持续报‘invalid connection option’错误

问题背景 #

尝试使用Python的SQLAlchemy结合SSL连接CockroachDB，测试了多种连接字符串与SSL参数组合，但所有组合均触发错误。

测试代码 #

import sqlalchemy as sa

cert_path = "/tmp/mountedcerts"

# 第一种SSL参数配置
ssl_args1 = {
    'sslmode': 'REQUIRED',
    'ssl_ca': f"{cert_path}/ca.crt",
    'ssl_cert': f"{cert_path}/client.root.crt",
    'ssl_key': f"{cert_path}/client.root.key"
}

# 第二种SSL参数配置
ssl_args2 = {
    'sslmode': 'REQUIRED',
    'ssl': {
        'cert': f"{cert_path}/client.root.crt",
        'key': f"{cert_path}/client.root.key",
        'ca': f"{cert_path}/ca.crt",
    }
}

conn_strs = [
    "postgresql://root@cockroachdb:26257/sample",
    "postgresql://root@cockroachdb:26257/sample?ssl=True",
    "postgresql+psycopg2://root@cockroachdb:26257/sample?sslmode=require"
]

ssl_args = [ssl_args1, ssl_args2]

def get_engine():
    for cs in conn_strs:
        for arg in ssl_args:
            yield sa.create_engine(cs, connect_args=arg)

def get_list_of_table():
    for engine in get_engine():
        try:
            with engine.connect() as conn:
                result = conn.execute("SHOW TABLES")
                return [row[0] for row in result]
        except Exception as ex:
            print(ex)

if __name__ == "__main__":
    get_list_of_table()

错误信息 #

(psycopg2.ProgrammingError) 无效DSN：无效连接选项 "ssl_ca"
(psycopg2.ProgrammingError) 无效DSN：无效连接选项 "ssl"
(psycopg2.ProgrammingError) 无效DSN：无效连接选项 "ssl"
(psycopg2.ProgrammingError) 无效DSN：无效连接选项 "ssl"
(psycopg2.ProgrammingError) 无效DSN：无效连接选项 "ssl_ca"
(psycopg2.ProgrammingError) 无效DSN：无效连接选项 "ssl"

解决方案 #

问题核心是psycopg2驱动对SSL参数的命名规则不匹配，修正参数名及配置结构即可解决：

• 替换参数名：ssl_ca→sslrootcert，ssl_cert→sslcert，ssl_key→sslkey
• 取消嵌套的ssl字典，将SSL参数直接放在connect_args根层级
• sslmode使用小写值require（psycopg2对大小写敏感）

修正后的代码如下：

import sqlalchemy as sa

cert_path = "/tmp/mountedcerts"

# 符合psycopg2规范的SSL参数配置
correct_ssl_args = {
    'sslmode': 'require',
    'sslrootcert': f"{cert_path}/ca.crt",
    'sslcert': f"{cert_path}/client.root.crt",
    'sslkey': f"{cert_path}/client.root.key"
}

# 明确指定psycopg2驱动的连接字符串
conn_str = "postgresql+psycopg2://root@cockroachdb:26257/sample"

def get_list_of_table():
    try:
        engine = sa.create_engine(conn_str, connect_args=correct_ssl_args)
        with engine.connect() as conn:
            result = conn.execute("SHOW TABLES")
            return [row[0] for row in result]
    except Exception as ex:
        print(ex)

if __name__ == "__main__":
    get_list_of_table()

关键说明 #

1. 严格匹配psycopg2的参数命名规范，避免使用其他驱动的参数名
2. sslmode必须用小写值，否则会被驱动判定为无效选项
3. 连接字符串无需额外添加ssl=True等冗余参数，所有SSL配置统一放在connect_args中更清晰

内容的提问来源于stack exchange，提问作者Dinesh