petl v1.7.26 - SQLAlchemy 2 and JSON Header Fixes

petl v1.7.26 was published on 21 September 2026. Database jobs are the reason to take the tag: SQLAlchemy 2 removed Engine.execute(), and petl can read and write through engines, connections, and sessions again. The same release also stops empty JSON Lines files and a one row dictionary sample from losing their headers.

The full release notes and downloads are on the GitHub release page. Changes since v1.7.25 are listed in the full changelog.

SQLAlchemy 2 rejects a raw SQL string passed to Connection.execute() and no longer provides Engine.execute(). On earlier petl releases an engine was refused, connection reads and writes failed, and a session could collide with an autobegin transaction. Pull request 719 restores those paths. It is the fix for issue 648.

The optional database extra drops the SQLAlchemy <2.0 cap. setup.py and requirements-database.txt still require 1.3.6 or newer. An engine is recognized without Engine.execute. When exec_driver_sql() exists, a raw string on an engine or connection uses it. SQLAlchemy expressions, and SQLAlchemy versions that lack that method, still call execute(). A session wraps a SQL string in text() so binds stay named.

petl/io/db_utils.py owns execution and transactions. petl/io/db.py and petl/io/db_create.py call those helpers, so create and drop follow the same rules as inserts. The default is commit=True. A session commits through the session API. On SQLAlchemy 1.4 and 2.x, an already active connection transaction is committed. commit=False leaves that transaction with the caller on every supported version. Opened connections and streaming results close on finish, failure, or iterator close. Writes use driver_connection on SQLAlchemy 2.

Raw strings on an engine or connection use driver parameters. Keyword arguments, a flat list, and a scalar are normalized for exec_driver_sql(). The old keyword form raised TypeError. Sessions keep named binds.

On Windows with Python 3.12.14, SQLAlchemy 2.0.54 and 1.4.54 each reported 674 passed and 16 skipped. SQLAlchemy 1.3.24 reported 650 passed and 40 skipped, with 24 of those skips in future mode cases that 1.3 cannot run. Upstream CI for commit 9da2d4f passed the database jobs, including MySQL and PostgreSQL.

JSON header discovery is two patches in petl/io/json.py. They do not share commits.

Pull request 716 handles an empty JSON Lines file. fromjson(path, lines=True) raised RuntimeError: generator raised StopIteration when no header was passed, including the empty file from tojson([('id',)], path, lines=True). iterjlines now yields an empty header tuple on exhaustion, matching fromdicts([]). An explicit header is kept. Blank lines and malformed records still raise. The JSON array reader is untouched.

Pull request 717 fixes sample=1 on dictionaries. fromdicts([{'id': '001'}], sample=1) produced [(), ()]: no field names and no values. iterpeek(..., 1) returns one object, not a list, so discovery walked the dictionary keys as if they were rows. A generator of the same record produced a different table. An empty input with sample=1 raised RuntimeError. JSON array files had the same defect.

Sampling now collects probe rows with islice and replays them with chain, for a normal iterator and a cached generator. Rows used for the header are still emitted as data. Sample size 0 and any sample larger than 1 keep the previous behavior. No public function was added.

DuplicateKeyError and FieldSelectionError formatted a tuple as the % argument list. Pull request 718 wraps the stored value in petl/errors.py so %r receives one argument. An empty tuple, or a tuple of several fields, raised TypeError inside str(error). A tuple with one field lost its markers. Scalars, exception attributes, and accepted selectors stay the same. Tests cover both classes plus valuecounter and strict lookupone. The source stays valid on Python 2.7, which was not run for this patch.

Pull request 714 corrects the valuecounter() docstring in petl/util/counting.py. A tuple of names or indexes is not one argument. valuecounter(table, fields) raises FieldSelectionError. Pass each field on its own, or unpack with *fields. Counting is unchanged, and the new examples are doctests. The docstring and the formatter both refer to issue 641. Docs do not change the exception text. The formatter does not change which calls are legal.

Pull request 715 only edits docs/related_work.rst. The csvkit paragraph linked to the picalo page on PyPI. It now points at the csvkit project page. The picalo entry stays. No library code changes. codeofwxz, umd0730, and Audgui-Byte each made a first contribution on this tag.

Take v1.7.26 when the [db] extra is what blocks SQLAlchemy 2. The lower bound remains 1.3.6.

Read docs/io.rst before passing a connection that already has an outer transaction. commit=True commits that transaction on SQLAlchemy 1.4 and 2.x. On SQLAlchemy 1.3, petl still opens a subtransaction and leaves the outer commit or rollback to the caller. commit=False keeps caller ownership on every supported version.

An empty JSON Lines file with an inferred header returns an empty table instead of RuntimeError. Branch on row count, not on that exception. fromdicts(..., sample=1) on one dictionary, including a JSON array of one object, keeps the header and the sampled row.

str(DuplicateKeyError) and str(FieldSelectionError) contain the tuple instead of raising TypeError. Separate arguments to valuecounter are unchanged. A tuple passed as one argument is still a FieldSelectionError. Only the text changed.