Install & Compatibility
Where this runs
tested against v0.7.3 · pip install
no network on importno background threads
Install × environment matrix
Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux
Code
Verified usage
Verified import paths — ran on the pinned version, not inferred.
agatesql
✓ import agatesql
✗ from agatesql import patch
As of agate-sql 0.4.0, it is no longer necessary to explicitly call `agatesql.patch()` after importing `agatesql`. The monkey-patching of `agate.Table` happens automatically upon import.
This quickstart demonstrates how to create an `agate.Table`, write it to a SQL database, and then read data back using `from_sql` and `from_sql_query`. It uses an in-memory SQLite database by default but can be configured for other databases via an environment variable. Remember to install the necessary SQLAlchemy database drivers for your chosen database (e.g., `psycopg2-binary` for PostgreSQL).
import agate
import agatesql
import os
# This example assumes a PostgreSQL database named 'test_db' with a table 'my_data'.
# For a real scenario, replace with your database connection string and table name.
# Ensure the appropriate SQLAlchemy driver (e.g., psycopg2) is installed.
# Create a dummy agate table for demonstration
data = [
['apple', 10],
['banana', 20],
['cherry', 30]
]
column_names = ['fruit', 'quantity']
column_types = [agate.Text(), agate.Number()]
table = agate.Table(data, column_names, column_types)
# --- Writing to SQL (example using a dummy connection string) ---
# In a real application, use a proper connection string like 'postgresql:///mydb'
# and ensure your database is running and credentials are correct.
# For testing, you might use an in-memory SQLite database:
# SQLALCHEMY_DATABASE_URL = 'sqlite:///:memory:'
SQLALCHEMY_DATABASE_URL = os.environ.get('SQLALCHEMY_DATABASE_URL', 'sqlite:///:memory:')
DB_TABLE_NAME = os.environ.get('DB_TABLE_NAME', 'test_fruits')
try:
print(f"Attempting to write table '{DB_TABLE_NAME}' to {SQLALCHEMY_DATABASE_URL}...")
table.to_sql(SQLALCHEMY_DATABASE_URL, DB_TABLE_NAME, overwrite=True)
print("Table written successfully.")
# --- Reading from SQL ---
print(f"Attempting to read table '{DB_TABLE_NAME}' from {SQLALCHEMY_DATABASE_URL}...")
new_table = agate.Table.from_sql(SQLALCHEMY_DATABASE_URL, DB_TABLE_NAME)
print("Table read successfully. Contents:")
new_table.print_table()
# --- Reading from a SQL query (if the dialect supports it) ---
# Note: Column types will be inferred from the returned data, not SQL schema.
print(f"Attempting to read from SQL query on '{DB_TABLE_NAME}'...")
query_table = agate.Table.from_sql_query(SQLALCHEMY_DATABASE_URL, f"SELECT fruit, quantity FROM {DB_TABLE_NAME} WHERE quantity > 15")
print("Query results:")
query_table.print_table()
except Exception as e:
print(f"An error occurred: {e}")
print("Please ensure you have the correct SQLAlchemy drivers installed for your database (e.g., `pip install psycopg2-binary` for PostgreSQL) and your database is accessible.")
Debug
Known issues
breakingThe explicit call to `agatesql.patch()` is no longer needed. In `agate-sql` versions 0.4.0 and later, `agate.Table` is monkey-patched automatically when `agatesql` is imported. Calling `patch()` explicitly in newer versions might not cause an error but is redundant and indicates outdated usage.fixRemove any calls to `agatesql.patch()`. Simply `import agatesql` is sufficient.
affects: Before 0.4.0
gotchaWhen using `Table.from_sql()` and `Table.to_sql()`, the second argument expects the 'table name', not the 'database name'. There has been community confusion (reported as a 'docs thinko') where this was sometimes misinterpreted. Using a database name instead of a table name will lead to errors.fixAlways provide the specific table name you intend to interact with as the second argument to `from_sql()` and `to_sql()`.
affects: All versions
gotchaArbitrary SQL queries are not directly supported by `Table.from_sql()` or `Table.to_sql()`. These methods are designed for whole-table operations. For executing custom SQL queries and importing their results into an `agate.Table`, you must use `Table.from_sql_query()`.fixUse `agate.Table.from_sql_query(connection_string, "SELECT * FROM my_table WHERE ...")` for custom queries. Ensure the query returns results in a format `agate` can parse.
affects: All versions
gotchaagate-sql relies on SQLAlchemy, which in turn requires specific DBAPI drivers for each database backend (e.g., `psycopg2` for PostgreSQL, `mysqlclient` for MySQL). These drivers are not installed with `agate-sql` by default and must be installed separately by the user.fixInstall the appropriate DBAPI driver for your database. For example, for PostgreSQL, run `pip install psycopg2-binary`.
affects: All versions
breakingPython 3.8 and 3.9 support was dropped in recent versions to accommodate newer Python releases (3.13, 3.14). If you are using these older Python versions, you will need to stick to an older `agate-sql` release.fixUpgrade to a supported Python version (3.10+) or use an older `agate-sql` version (e.g., <=0.7.2 for Python 3.8/3.9 compatibility).
affects: From 0.7.3 onwards for Python 3.8/3.9
Errors
Common errors & fixes
ModuleNotFoundError: No module named 'agate_sql'
This error occurs when the 'agate-sql' package is not installed in your Python environment.
fixInstall the package using pip: 'pip install agate-sql'.
AttributeError: 'Table' object has no attribute 'to_sql'
This error occurs when attempting to call the 'to_sql' method on an 'agate.Table' object, which does not have this method.
fixUse the 'to_sql' method provided by 'agate-sql' by importing and using the appropriate functions from the 'agate_sql' module.
ImportError: cannot import name 'SQL' from 'agate'
This error occurs when trying to import 'SQL' directly from 'agate', but it is actually part of the 'agate-sql' extension.
fixImport 'SQL' from 'agate_sql' instead: 'from agate_sql import SQL'.
TypeError: 'NoneType' object is not iterable
This error occurs when the 'agate-sql' extension is not properly initialized before use.
fixEnsure that 'agate-sql' is properly initialized by calling 'agate.Table.from_sql' or similar methods as documented.
ValueError: No SQL connection provided
This error occurs when attempting to read from or write to a SQL database without providing a valid connection.
fixProvide a valid SQL connection object when using 'agate-sql' functions that interact with a database.
Upgrade
Version history
0.7.3latest on PyPI · released Dec 15, 2025
Audit
Dependencies
agaterequiredCore data analysis library that agate-sql extends.
SQLAlchemyrequiredProvides the underlying SQL toolkit for database interaction.
DBAPI driver (e.g., psycopg2, mysqlclient, orlacle-driver)optionalRequired for connecting to specific database types (e.g., PostgreSQL, MySQL, Oracle). Must be installed separately based on your database choice.