databases
databases copied to clipboard
encode
Parameters are not set via DSN url
Hello we use databases lib but I cant' set SSL=true via DSN
@functools.lru_cache(maxsize=None)
def init_db() -> Database:
settings = get_settings()
db_dsn = "postgresql://user:pass@db-host:6432/service_name?application_name=service_name&ssl=true"
if settings.testing:
db = Database(db_dsn, force_rollback=True)
else:
db = Database(db_dsn)
return db
When I try to set parameters via DSN URL, I get errors like this:
Running migrations...
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 238, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 657, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 490, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 126, in connect
dsn = _ext.make_dsn(dsn, **kwargs)
File "/usr/local/lib/python3.8/site-packages/psycopg2/extensions.py", line 175, in make_dsn
parse_dsn(dsn)
psycopg2.ProgrammingError: invalid dsn: invalid connection option "ssl"
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/bin/alembic", line 8, in <module>
sys.exit(main())
File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 577, in main
CommandLine(prog=prog).main(argv=argv)
File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 571, in main
self.run_cmd(cfg, options)
File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 548, in run_cmd
fn(
File "/usr/local/lib/python3.8/site-packages/alembic/command.py", line 298, in upgrade
script.run_env()
File "/usr/local/lib/python3.8/site-packages/alembic/script/base.py", line 489, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
module = load_module_py(module_id, path)
File "/usr/local/lib/python3.8/site-packages/alembic/util/compat.py", line 184, in load_module_py
spec.loader.exec_module(module)
File "<frozen importlib._bootstrap_external>", line 783, in exec_module
File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
File "migrations/env.py", line 72, in <module>
run_migrations_online()
File "migrations/env.py", line 60, in run_migrations_online
with connectable.connect() as connection:
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2218, in connect
return self._connection_cls(self, **kwargs)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 103, in __init__
else engine.raw_connection()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2317, in raw_connection
return self._wrap_pool_connect(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2288, in _wrap_pool_connect
Connection._handle_dbapi_exception_noconnection(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1554, in _handle_dbapi_exception_noconnection
util.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
return fn()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 303, in unique_connection
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
rec = pool._do_get()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/impl.py", line 238, in _do_get
return self._create_connection()
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 308, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 437, in __init__
self.__connect(first_connect_check=True)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 657, in __connect
pool.logger.debug("Error on connect(): %s", e)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.raise_(
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/pool/base.py", line 652, in __connect
connection = pool._invoke_creator(self)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 490, in connect
return self.dbapi.connect(*cargs, **cparams)
File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 126, in connect
dsn = _ext.make_dsn(dsn, **kwargs)
File "/usr/local/lib/python3.8/site-packages/psycopg2/extensions.py", line 175, in make_dsn
parse_dsn(dsn)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) invalid dsn: invalid connection option "ssl"
(Background on this error at: http://sqlalche.me/e/f405)
Starting service-name service...
INFO: Started server process [15]
INFO: Waiting for application startup.
[2021-07-26 16:15:24,378 ERROR] uvicorn.error | Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 517, in lifespan
await self.startup()
File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 494, in startup
await handler()
File "/usr/local/lib/python3.8/site-packages/databases/core.py", line 84, in connect
await self._backend.connect()
File "/usr/local/lib/python3.8/site-packages/databases/backends/postgres.py", line 65, in connect
self._pool = await asyncpg.create_pool(str(self._database_url), **kwargs)
File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 398, in _async__init__
await self._initialize()
File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 426, in _initialize
await first_ch.connect()
File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 125, in connect
self._con = await self._pool._get_new_connection()
File "/usr/local/lib/python3.8/site-packages/asyncpg/pool.py", line 468, in _get_new_connection
con = await connection.connect(
File "/usr/local/lib/python3.8/site-packages/asyncpg/connection.py", line 1668, in connect
return await connect_utils._connect(
File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 652, in _connect
con = await _connect_addr(
File "/usr/local/lib/python3.8/site-packages/asyncpg/connect_utils.py", line 631, in _connect_addr
await asyncio.wait_for(connected, timeout=timeout)
File "/usr/local/lib/python3.8/asyncio/tasks.py", line 494, in wait_for
return fut.result()
asyncpg.exceptions.ProtocolViolationError: unsupported startup parameter: ssl
[2021-07-26 16:15:25,184 ERROR] uvicorn.error | Application startup failed. Exiting.
Versions:
- python-3.8
- alembic = 1.6.5
- asyncpg = 0.20.1
- databases = {version = "^0.4.3", extras = ["postgresql"]}
- psycopg2-binary = 2.9.1
- SQLAlchemy = 1.3.24
But in the documentation I see that settings via DSN URL should be work fine - https://github.com/encode/databases/blob/2194ffbd670388722e0aabfe4764bdc77a7b420a/docs/connections_and_transactions.md#connection-options
Hi
It looks like you don t have libssl on your machine. Try something like this:
<your-package-manager> install openssl
Hope that helps
I have libssl in my mac, but still getting this error connecting through SQLAlchemy via ?ssl=true... What I ended up doing was to remove the ssl=true from my connection string and use sslmode and the certificate path like this: sslmode=verify-full&sslrootcert=/path/to/cert.crt
That only worked... I am not sure why the ssl=true is breaking the code. My libraries version:
- sqlalchemy 2.0.21
- psycopg2 2.9.9
- Python 3.11.4 [Clang 14.0.3 (clang-1403.0.22.14.1)] on darwin
