Using SQLAlchemy and pymssql with Babelfish 4.8.0 on FoundryDB

Python developers working with SQL Server typically reach for one of two libraries: pymssql for direct TDS access, or SQLAlchemy for ORM-based workflows. Both work with Babelfish, but SQLAlchemy requires three specific workarounds due to differences in how Babelfish implements the TDS protocol.

This post covers both approaches with complete, working code tested against Babelfish 4.8.0 on PostgreSQL 16.11 running on FoundryDB staging. The pymssql section expands on driver-level coverage with additional tests for stored procedures and batch insert performance.

Prerequisites

• A running Babelfish instance on FoundryDB
• Python 3.10+
• Your Babelfish host, username, and password from the FoundryDB API

Install both libraries:

pip install pymssql sqlalchemy

Part 1: pymssql (Direct TDS Driver)

pymssql connects directly to the TDS endpoint on port 1433. No ORM overhead, no abstraction layers, no workarounds needed.

Connection

import pymssql

conn = pymssql.connect(
    server='YOUR_HOST',
    port=1433,
    user='app_user',
    password='YOUR_PASSWORD',
    tds_version='7.3',
)
cursor = conn.cursor()

Always specify tds_version='7.3' explicitly. This maps to the SQL Server 2012 wire protocol, which Babelfish handles correctly.

CRUD Operations

# Create
cursor.execute("""
    CREATE TABLE products (
        id INT IDENTITY(1,1) PRIMARY KEY,
        name NVARCHAR(100) NOT NULL,
        price DECIMAL(10,2),
        in_stock BIT DEFAULT 1
    )
""")
conn.commit()

# Insert with parameters
cursor.execute(
    "INSERT INTO products (name, price) VALUES (%s, %d)",
    ('Widget', 29.99)
)
conn.commit()

# Select with filter
cursor.execute("SELECT id, name, price FROM products WHERE price > %d", (20.00,))
for row in cursor.fetchall():
    print(f"  {row[0]}: {row[1]} - ${row[2]}")

# Update
cursor.execute("UPDATE products SET price = %d WHERE name = %s", (34.99, 'Widget'))
conn.commit()

# Delete
cursor.execute("DELETE FROM products WHERE name = %s", ('Widget',))
conn.commit()

All CRUD operations work without modification. Parameterized queries use pymssql's %s and %d format specifiers, not SQLAlchemy-style :param syntax.

Transactions

conn.autocommit(False)

cursor.execute("INSERT INTO products (name, price) VALUES ('Temp Item', 9.99)")
conn.rollback()
# Row not persisted (confirmed)

cursor.execute("INSERT INTO products (name, price) VALUES ('Real Item', 19.99)")
conn.commit()
# Row persisted (confirmed)

Both commit and rollback work correctly. Babelfish handles transaction boundaries identically to SQL Server.

Stored Procedures

cursor.execute("""
    CREATE PROCEDURE usp_get_products @min_price DECIMAL(10,2)
    AS
    BEGIN
        SELECT name, price FROM products WHERE price >= @min_price ORDER BY price DESC
    END
""")
conn.commit()

# Call the procedure
cursor.execute("EXEC usp_get_products @min_price = %d", (15.00,))
rows = cursor.fetchall()
for row in rows:
    print(f"  {row[0]}: ${row[1]}")

Stored procedure creation and execution via EXEC with named parameters both work. Result sets are returned through the standard cursor interface.

Batch Insert Performance

import time

data = [(f"Product_{i}", round(10 + i * 0.5, 2)) for i in range(1000)]

start = time.time()
cursor.executemany(
    "INSERT INTO products (name, price) VALUES (%s, %d)",
    data
)
conn.commit()
elapsed = time.time() - start

print(f"Inserted 1000 rows in {elapsed:.1f}s (~{1000/elapsed:.0f} rows/sec)")

Result from the live test: 1000 rows in approximately 17 seconds, roughly 60 rows per second. This is the per-statement insert rate over TDS. For higher throughput, consider batching multiple VALUES clauses in a single INSERT statement or using the PostgreSQL port (5432) with psycopg2 and COPY.

pymssql Test Summary

Feature	Status
Raw connection + @@VERSION	Works
CREATE, INSERT, SELECT, UPDATE, DELETE	Works
Parameterized queries	Works
Transactions (commit + rollback)	Works
Stored procedures (EXEC with params)	Works
Batch insert via executemany	Works (60 rows/sec)

---

Part 2: SQLAlchemy ORM

SQLAlchemy connects to SQL Server using the mssql+pymssql dialect. It works with Babelfish, but three incompatibilities surface at connection time and during batch operations. Each has a straightforward workaround.

The 3 Workarounds

Before creating your engine, apply all three patches. Here is the complete setup:

from sqlalchemy import create_engine, Column, Integer, String, Float, func
from sqlalchemy.orm import DeclarativeBase, Session
from sqlalchemy.dialects.mssql.base import MSDialect

# --- Workaround 1: @@VERSION parsing ---
# Babelfish returns a non-standard @@VERSION string:
#   "Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8 ..."
# SQLAlchemy's MSSQL dialect parses this to extract the server version,
# but the regex expects "Microsoft SQL Server" format. When parsing fails,
# server_version_info is set to None, and subsequent feature checks crash.
# Fix: intercept the setup and provide a compatible version tuple.

original_setup = MSDialect._setup_version_attributes
def patched_setup(self):
    if self.server_version_info is None:
        self.server_version_info = (15, 0, 0)
        self._is_sql2005 = True
        self._is_sql2008 = True
        self._is_sql2012 = True
        self._is_sql2017 = True
        self._supports_offset_fetch = True
        self._supports_nvarchar_max = True
    else:
        original_setup(self)
MSDialect._setup_version_attributes = patched_setup

# --- Workaround 2: COMMIT/ROLLBACK outside transactions ---
# Babelfish runs TDS connections in autocommit mode by default.
# SQLAlchemy issues a ROLLBACK when connecting (to reset state) and
# a COMMIT after DDL statements. Both fail because there is no
# active transaction to commit or roll back.
# Fix: catch and suppress the errors.

for method_name in ('do_rollback', 'do_commit'):
    original = getattr(MSDialect, method_name)
    def make_patched(orig):
        def patched(self, dbapi_connection):
            try:
                orig(self, dbapi_connection)
            except Exception:
                pass
        return patched
    setattr(MSDialect, method_name, make_patched(original))

# --- Workaround 3: disable insertmanyvalues ---
# SQLAlchemy 2.0+ uses INSERT...SELECT FROM (VALUES (...)) ORDER BY
# for batch inserts (the "insertmanyvalues" optimization). Babelfish
# does not support ORDER BY in this context and raises a syntax error.
# Fix: disable the optimization at engine creation.

engine = create_engine(
    "mssql+pymssql://app_user:YOUR_PASSWORD@YOUR_HOST:1433/master",
    use_insertmanyvalues=False,
)

Each workaround addresses a specific gap between Babelfish's TDS implementation and what SQLAlchemy's MSSQL dialect expects. None of them affect the correctness of your ORM queries.

Define a Model

class Base(DeclarativeBase):
    pass

class Employee(Base):
    __tablename__ = "employees"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    department = Column(String(50))
    salary = Column(Float)

Create Tables

Base.metadata.create_all(engine)

SQLAlchemy emits standard DDL (CREATE TABLE with identity columns). This works without modification after the workarounds are applied.

INSERT

with Session(engine) as session:
    session.add_all([
        Employee(name="Alice", department="Engineering", salary=95000),
        Employee(name="Bob", department="Marketing", salary=78000),
        Employee(name="Carol", department="Engineering", salary=102000),
        Employee(name="Dave", department="Sales", salary=71000),
    ])
    session.commit()

SELECT with Filter and Order

with Session(engine) as session:
    engineers = (
        session.query(Employee)
        .filter(Employee.department == "Engineering")
        .order_by(Employee.salary.desc())
        .all()
    )
    for emp in engineers:
        print(f"  {emp.name}: ${emp.salary:,.0f}")

Output:

  Carol: $102,000
  Alice: $95,000

UPDATE

with Session(engine) as session:
    alice = session.query(Employee).filter_by(name="Alice").first()
    alice.salary = 98000
    session.commit()

DELETE

with Session(engine) as session:
    dave = session.query(Employee).filter_by(name="Dave").first()
    session.delete(dave)
    session.commit()

Aggregates

with Session(engine) as session:
    avg_salary = session.query(func.avg(Employee.salary)).scalar()
    print(f"Average salary: ${avg_salary:,.0f}")

Output:

Average salary: $91,667

SQLAlchemy Test Summary

Feature	Status
Table creation (DDL)	Works
ORM INSERT (add_all + commit)	Works
SELECT with filter and order_by	Works
UPDATE	Works
DELETE	Works
Aggregate (func.avg)	Works

All operations pass after the three workarounds. The workarounds only affect connection initialization and batch insert strategy. Standard ORM query generation is unaffected.

---

Complete Working Example

Here is a single script that applies all workarounds and exercises both pymssql and SQLAlchemy. Replace YOUR_HOST and YOUR_PASSWORD with your FoundryDB Babelfish credentials.

import pymssql
from sqlalchemy import create_engine, Column, Integer, String, Float, func
from sqlalchemy.orm import DeclarativeBase, Session
from sqlalchemy.dialects.mssql.base import MSDialect

HOST = "YOUR_HOST"
USER = "app_user"
PASSWORD = "YOUR_PASSWORD"

# ---- pymssql: direct TDS ----
conn = pymssql.connect(server=HOST, port=1433, user=USER, password=PASSWORD, tds_version='7.3')
cursor = conn.cursor()
cursor.execute("SELECT @@VERSION")
print("pymssql connected:", cursor.fetchone()[0][:60])
conn.close()

# ---- SQLAlchemy: apply workarounds ----
original_setup = MSDialect._setup_version_attributes
def patched_setup(self):
    if self.server_version_info is None:
        self.server_version_info = (15, 0, 0)
        self._is_sql2005 = True
        self._is_sql2008 = True
        self._is_sql2012 = True
        self._is_sql2017 = True
        self._supports_offset_fetch = True
        self._supports_nvarchar_max = True
    else:
        original_setup(self)
MSDialect._setup_version_attributes = patched_setup

for method_name in ('do_rollback', 'do_commit'):
    original = getattr(MSDialect, method_name)
    def make_patched(orig):
        def patched(self, dbapi_connection):
            try:
                orig(self, dbapi_connection)
            except Exception:
                pass
        return patched
    setattr(MSDialect, method_name, make_patched(original))

engine = create_engine(
    f"mssql+pymssql://{USER}:{PASSWORD}@{HOST}:1433/master",
    use_insertmanyvalues=False,
)

class Base(DeclarativeBase):
    pass

class Employee(Base):
    __tablename__ = "employees"
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    department = Column(String(50))
    salary = Column(Float)

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all([
        Employee(name="Alice", department="Engineering", salary=95000),
        Employee(name="Bob", department="Marketing", salary=78000),
    ])
    session.commit()

    avg = session.query(func.avg(Employee.salary)).scalar()
    print(f"SQLAlchemy connected. Average salary: ${avg:,.0f}")

Base.metadata.drop_all(engine)
print("Done.")

---

When to Use Which

	pymssql	SQLAlchemy
Best for	Scripts, migrations, raw SQL	Applications with complex models
Setup complexity	None	3 workarounds required
Batch insert speed	~60 rows/sec via executemany	Comparable (with insertmanyvalues disabled)
ORM features	None	Full (relationships, queries, sessions)
Stored procedures	EXEC via cursor	Not idiomatic (use text() or raw connection)

For migration scripts and one-off queries, pymssql is simpler. For applications with structured models and query builders, SQLAlchemy works well once the workarounds are in place.

Get Started

Provision a Babelfish instance on FoundryDB and connect with pymssql or SQLAlchemy using the examples above. For full API documentation, see docs.foundrydb.com.