Применение SQLAlchemy Core с точки зрения образования соединений и транзакций с базой.

Как сказано в документации, есть Engine, который скрывает за собой пул и диалект.

подход такой: нужно брать соединения, делать некий логически связанный блок действий, и вызывать conn.close(), которое возвращает соединение в пул.

соединение находится всегда в транзакции.

В процессе работы имеете действие с 

если использовать соединения для выборки данных и оставить его у себя

Для долго работающих скриптов очень важно правильно закрывать ресурсы.

Engine - самая важная штука, создавать его рекомендуется по одному на пару "поток программы-база", engine скрывает за собой диалект и пул. Создается функцией create_engine.

Пример 1 - прочитать из базы значение подробнее

engine = create_engine(f'postgresql://user:password@localhost:5432/db')
conn = engine.connect()
result = conn.execute("select name from host LIMIT 1")
for row in result:
    print("name:", row['name'])
# idle in transaction
result.close() # ничего не меняется
conn.close() # ROLLBACK, соединение не закрывается, возвращается в пул

первая особенность: соединение не закрывается. Иногда его таки нужно закрыть. Есть два способа:

1. вызвать engine.dispose()
2. использовать другой pool

вторая особенность: после показа всех значений состояние остается idle in transaction. Решения 2:

1. conn.execute("COMMIT")
2. совет из док.: conn.execute(text("select name from host LIMIT 1").execution_options(autocommit=True))
3. вернуть соединение в пул - conn.close() - как в данном примере

третья особенность: все данные сразу попадают из execute в result. Решение для больших данных: использовать серверный курсор.

 Пример 2 - прочитать из базы значение подробнее

engine = create_engine(f'postgresql://user:password@localhost:5432/db')
result = engine.execute("select name from host LIMIT 1")
for row in result:
    print("name:", row['name'])

с этим примеров все в порядке, однако сложности возникают если не прочитать result до конца или вообще не читать, для надежности лучше всегда вызывать result.close()

 Пример 3 - Обновить значение

engine = create_engine(f'postgresql://user:password@localhost:5432/db')
conn = engine.connect()
conn.execute("UPDATE host SET name = 'test1' WHERE host_id = 713")
# idle, COMMIT
conn.close() # COMMIT, соединение не закрывается, возвращается в пул

тут все нормально