SQLAlchemy - Connection Select
Пример 1 - прочитать из базы значение
| комманда | состояние соединения | последняя SQL | комментарии |
|---|---|---|---|
| engine = create_engine(f'postgresql://...') | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| result = conn.execute("SELECT name FROM host LIMIT 10") | idle in transaction | SELECT name FROM host LIMIT 10 | данные уже в result |
| for row in result: print("name:", row['name']) | idle in transaction | SELECT name FROM host LIMIT 10 | |
| conn.close() | idle | ROLLBACK | соединение вернулось в пул |
первая особенность: соединение не закрывается. Иногда его таки нужно закрыть. Способы:
вызвать engine.dispose() при этом pool пересоздастся, закрыв существующие соединения, engine можно будет использовать далее, не происходит уничтожение объекта. Более того, видимо engine запоминает информацию о базе, и при следующем соединении не делает несколько служебных SELECT, как при самом первом соединении.
| engine = create_engine(f'postgresql://...') | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| result = conn.execute("SELECT name FROM host LIMIT 10") | idle in transaction | SELECT name FROM host LIMIT 10 | данные уже в result |
| for row in result: print("name:", row['name']) | idle in transaction | SELECT name FROM host LIMIT 10 | |
| conn.close() | idle | ROLLBACK | соединение вернулось в пул |
| engine.dispose() | нет соединения |
использовать другой pool, например create_engine(f'postgresql://user:password@localhost:5432/db', poolclass=sqlalchemy.pool.NullPool)
| engine = create_engine(f'postgresql://...', poolclass=NullPool) | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| result = conn.execute("SELECT name FROM host LIMIT 10") | idle in transaction | SELECT name FROM host LIMIT 10 | данные уже в result |
| for row in result: print("name:", row['name']) | idle in transaction | SELECT name FROM host LIMIT 10 | |
| conn.close() | нет соединения |
добраться до соединения psycopg2 и закрыть его. это не правильно, при выходе из программы пул захочет закрыть соединение, соединение уже закрыто - исключение.
| engine = create_engine(f'postgresql://...') | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| result = conn.execute("SELECT name FROM host LIMIT 10") | idle in transaction | SELECT name FROM host LIMIT 10 | данные уже в result |
| for row in result: print("name:", row['name']) | idle in transaction | SELECT name FROM host LIMIT 10 | |
| conn.connection.connection.close() | нет соединения | так делать неправильно |
отсоединить соединение от пула, тогда оно реально закроется
| engine = create_engine(f'postgresql://...') | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| result = conn.execute("SELECT name FROM host LIMIT 10") | idle in transaction | SELECT name FROM host LIMIT 10 | данные уже в result |
| for row in result: print("name:", row['name']) | idle in transaction | SELECT name FROM host LIMIT 10 | |
| conn.detach() | idle in transaction | SELECT name FROM host LIMIT 10 | отсоединяем от пула |
| conn.close() | нет соединения |
вторая особенность: после показа всех значений состояние остается idle in transaction.
В документации сказано, что по умолчанию самый базовый уровень DBAPI всегда работает в режиме транзакции (в документации постгрес отмечено что это дефолтный режим https://www.postgresql.org/docs/current/ecpg-sql-set-autocommit.html). но у Connection, Engine, Executable есть настройки execution_options(), и там установленно autocommit=true, это настройка alchemy, не постгреса, работает таким образом: sqlalchemy пытается определить тип команды (даже с помощью регулярок) и если команда INSERT, UPDATE, DELETE и прочие DDL, то sqlalchemy посылает автоматически COMMIT, очевидно для SELECT COMMIT не посылается, и мы находимся в транзакции. решения 2:
вызвать commit() не на чем, так как транзакцию не получали, зато можно послать это слово прямым текстом: conn.execute("COMMIT"), причем можно послать эту команду подрят несколько раз, исключения не будет.
| engine = create_engine(f'postgresql://...') | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| result = conn.execute("SELECT name FROM host LIMIT 10") | idle in transaction | SELECT name FROM host LIMIT 10 | данные уже в result |
| for row in result: print("name:", row['name']) | idle in transaction | SELECT name FROM host LIMIT 10 | |
| conn.execute("COMMIT") | idle | COMMIT | добились конца транзакции |
| conn.close() | idle | ROLLBACK | соединение вернулось в пул |
совет из док.: conn.execute(text("select name from host LIMIT 1").execution_options(autocommit=True))
| engine = create_engine(f'postgresql://...') | нет соединения | ||
| conn = engine.connect() | idle | ROLLBACK | |
| conn.execute(text("select name from host LIMIT 10").execution_options(autocommit=True)) | idle | COMMIT | закончили транзакцию |
| for row in result: print("name:", row['name']) | idle | COMMIT | |
| conn.close() | idle | COMMIT | соединение вернулось в пул |
третья особенность: все данные сразу попадают из execute в result.
(в этом можно убедиться выбрав большое кол-во данные и померяв память программы) при этом если закрыть соединение, то данные из result будет получить нельзя. Чтоб получить данные из базы по частям нужно создать курсор postgres, в терминах psycopg это серверный курсор или именованный курсор.