I am trying to connect to PostgreSQL with asyncpg and SQLAlchemy. PostgreSQL instance is run by Docker.
My error is:
Traceback (most recent call last):
File "<frozen runpy>", line 198, in _run_module_as_main
File "<frozen runpy>", line 88, in _run_code
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/src/db/fill_data.py", line 17, in <module>
asyncio.run(execute())
File "/usr/lib/python3.12/asyncio/runners.py", line 194, in run
return runner.run(main)
^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/asyncio/runners.py", line 118, in run
return self._loop.run_until_complete(task)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/asyncio/base_events.py", line 684, in run_until_complete
return future.result()
^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/src/db/fill_data.py", line 12, in execute
await session.execute(text(file.read()))
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/ext/asyncio/session.py", line 452, in execute
result = await greenlet_spawn(
^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 197, in greenlet_spawn
result = context.throw(*sys.exc_info())
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2181, in _execute_internal
conn = self._connection_for_bind(bind)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2050, in _connection_for_bind
return trans._connection_for_bind(engine, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "<string>", line 2, in _connection_for_bind
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/orm/state_changes.py", line 139, in _go
ret_value = fn(self, *arg, **kw)
^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1144, in _connection_for_bind
conn = bind.connect()
^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3279, in connect
return self._connection_cls(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
self._dbapi_connection = engine.raw_connection()
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3303, in raw_connection
return self.pool.connect()
^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
return _ConnectionFairy._checkout(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
fairy = _ConnectionRecord.checkout(pool)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
rec = pool._do_get()
^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
with util.safe_reraise():
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
return self._create_connection()
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
return _ConnectionRecord(self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
self.__connect()
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
with util.safe_reraise():
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
raise exc_value.with_traceback(exc_tb)
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 643, in connect
return dialect.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 616, in connect
return self.loaded_dbapi.connect(*cargs, **cparams)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 937, in connect
await_only(creator_fn(*arg, **kw)),
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 127, in await_only
return current.driver.switch(awaitable) # type: ignore[no-any-return]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 192, in greenlet_spawn
value = await result
^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/asyncpg/connection.py", line 2329, in connect
return await connect_utils._connect(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 1017, in _connect
raise last_error or exceptions.TargetServerAttributeNotMatched(
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 991, in _connect
conn = await _connect_addr(
^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 828, in _connect_addr
return await __connect_addr(params, True, *args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 873, in __connect_addr
tr, pr = await connector
^^^^^^^^^^^^^^^
File "/home/rroxxxsii/PycharmProjects/SQLAlchemyTraining/venv/include/python/lib/python3.12/site-packages/asyncpg/connect_utils.py", line 744, in _create_ssl_connection
tr, pr = await loop.create_connection(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/asyncio/base_events.py", line 1077, in create_connection
infos = await self._ensure_resolved(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/asyncio/base_events.py", line 1453, in _ensure_resolved
return await loop.getaddrinfo(host, port, family=family, type=type,
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/asyncio/base_events.py", line 898, in getaddrinfo
return await self.run_in_executor(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/concurrent/futures/thread.py", line 58, in run
result = self.fn(*self.args, **self.kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/lib/python3.12/socket.py", line 963, in getaddrinfo
for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
socket.gaierror: [Errno -3] Temporary failure in name resolution
Here is docker-compose:
services:
db:
image: postgres:16-alpine
restart: always
env_file:
- .env
ports:
- '5432:5432'
.env file:
POSTGRES_USER=tuser
POSTGRES_PASSWORD=tpass
POSTGRES_HOST=db
POSTGRES_PORT=5432
POSTGRES_DB=tdb
Code to establish connection:
def create_engine() -> AsyncEngine:
engine = create_async_engine(f"postgresql+asyncpg://tuser:tpass@db:5432/tdb", echo=True)
return engine
def get_async_session(engine: AsyncEngine = create_engine()) -> async_sessionmaker[AsyncSession]:
return async_sessionmaker(engine)
And code I am trying to execute:
async def execute() -> None:
pool = get_async_session()
with open("chinook_postgres.sql") as file:
async with pool() as session:
await session.execute(text(file.read()))
await session.commit()
if __name__ == "__main__":
asyncio.run(execute())