Try to connect SQLAlchemy to an IBM Netezza appliance today and you will hit a wall in the first thirty seconds: Can't load plugin: sqlalchemy.dialects:netezza.nzpy. The official dialect, nzalchemy, was written for the SQLAlchemy 1.3 era and has never been updated. It does not work on SQLAlchemy 1.4. It does not work on SQLAlchemy 2.0. The request to support modern SQLAlchemy has sat open on IBM’s issue tracker since 2021, where users eventually stopped asking and started recommending each other workarounds.
That matters more than it sounds, because SQLAlchemy is not a niche library. It is the connective tissue of the Python data world: pandas uses it for read_sql and to_sql, Apache Superset uses it for every dashboard query, Airflow uses it for hooks, Alembic uses it for schema migrations. A database without a working SQLAlchemy dialect is invisible to that entire ecosystem. For several years, Netezza has effectively been that database.
We have fixed it. Following the same engineering pass we recently gave the underlying Python driver, Smart Associates now maintains an enhanced build of nzalchemy that works on current SQLAlchemy, verified live against both classic NPS 7.x racks and current NPS 11 / Cloud Pak for Data systems.
What was actually broken
The failures were not one bug but a pile of them, and most produced misleading errors:
- The dialect would not even load. A packaging defect meant installing from source crashed halfway through and silently skipped registering the dialect with SQLAlchemy, which is where the infamous “Can’t load plugin” message comes from.
- Schema reflection crashed on modern SQLAlchemy. Listing schemas, views, or asking “does this table exist?” all used API calls that SQLAlchemy removed years ago. One upstream report claims
has_table“returns the wrong result”; in truth it never returned at all, it crashed, and the crash was being misread as a wrong answer. - Two applications could not run at once. On import, the library seized a log file in the current directory and configured Python’s global logging over the top of the host application’s own settings. Two processes then fought over the same file lock. For anything multi-process, which is to say any real deployment, that is disqualifying.
Our build repairs all of it: full reflection, Core and ORM operation, correct has_table answers, library-grade logging that stays out of your application’s way, and a dialect that registers reliably however it is installed. We validated on SQLAlchemy 2.0 and 1.4 against both Netezza generations, including the multilingual round-trip suite we built for the driver work: Chinese, Cyrillic, Arabic, Hebrew, Danish and more, byte-exact through the ORM.
It stacks on the fixed driver. The dialect runs on our enhanced nzpy build, so everything that layer already guarantees carries through: correct LATIN9 character decoding, safe parameter escaping, and queries that work on every NPS generation. SQLAlchemy code inherits all of it for free.
When to use SQLAlchemy, and when to stay on the raw driver
A question our customers ask, now that both layers work: which one should a given project use? They are not competitors. The dialect sits on top of the driver, and the right choice follows from what the code is doing.
| Reach for SQLAlchemy (nzalchemy) | Reach for the raw driver (nzpy) |
|---|---|
Ecosystem tools. pandas read_sql/to_sql, Superset dashboards, Airflow hooks, Alembic migrations: these speak SQLAlchemy URLs and nothing else. One connection string and Netezza joins the club: netezza+nzpy://user:pass@host:5480/DB |
Bulk data movement. Loading or unloading serious volumes belongs in Netezza external tables through the driver, not row-by-row inserts through an ORM. Orders of magnitude faster, and charset-correct. |
| Applications with models. Business applications that want ORM classes, sessions, and relationships instead of hand-written SQL. | Appliance plumbing. Catalogue queries, DR tooling, maintenance operations: work that is inherently Netezza-specific gains nothing from an abstraction layer. Our own SMF platform uses the driver directly for exactly this reason. |
| Multi-database products. Code that must run against Netezza today and PostgreSQL or something else tomorrow. SQLAlchemy is the portability layer; swap the URL, keep the code. | Minimal-footprint deployments. Agents and containers where every dependency counts: the driver is one pure-Python package. |
| Schema introspection. Reflection gives you tables, columns, keys and views as Python objects: the foundation for catalogue tooling, data dictionaries and migration scripts. | Maximum control. When you need to see and tune the exact SQL on the wire, prepared behaviour included. |
The short version: if a third-party tool or a database-portable application is involved, you want the dialect. If you are moving bulk data or talking to the appliance as an appliance, you want the driver. Most estates end up using both, which is why we fixed both.
Performance notes
Two things worth knowing. First, the dialect ships with SQLAlchemy’s compiled-statement cache enabled and working, which is the single biggest performance lever in SQLAlchemy 2.0: repeated queries skip recompilation entirely. We verified it engages rather than assuming the flag was honest. Second, the classic trap: an ORM makes it very easy to insert a million rows one at a time. Do not. The dialect supports multi-row inserts, and for genuine volume the answer is external tables through the driver underneath. Use each layer for what it is good at and Netezza remains extremely hard to outrun.
One connection string, and twenty years of appliance engineering becomes visible to pandas, Superset, Airflow and every other tool your analysts already use.
Where you get it
We fixed this because we wanted to use it ourselves. The defects above are exactly what kept SQLAlchemy out of our own Python stack; now they are gone, it is going in. If your team gave up on SQLAlchemy against Netezza at “Can’t load plugin”, it is worth thirty seconds to know that wall is gone: install the pair, point create_engine at your appliance, and watch pandas read a Netezza table like it was born to. Talk to us if you want them in your own estate.
Built from the published open source Netezza drivers (IBM/nzpy and IBM/nzalchemy under Apache-2.0, IBM/nzgo under MIT), verified live against both NPS 7.2 and NPS 11.2, and produced without decompiling, disassembling or inspecting any IBM proprietary binary.
Running Netezza? Let’s talk. Smart Associates has been engineering on IBM Netezza for two decades: disaster recovery, migrations, performance and the deep platform work in between. If any of the above sounds familiar, why not get in touch.
