agate-sql

0.7.3 · active · verified Wed Apr 15

agate-sql extends the `agate` data analysis library by adding robust SQL read/write capabilities. It enables users to import data from SQL databases into `agate.Table` objects and export `agate.Table` objects back to SQL databases. The current version is 0.7.3. It maintains a consistent release cadence, often aligned with `agate` updates, and introduces new features, bug fixes, and Python version support.

Common errors

```
ModuleNotFoundError: No module named 'agate_sql'
```
cause This error occurs when the 'agate-sql' package is not installed in your Python environment.

fix Install the package using pip: 'pip install agate-sql'.
```
AttributeError: 'Table' object has no attribute 'to_sql'
```
cause This error occurs when attempting to call the 'to_sql' method on an 'agate.Table' object, which does not have this method.

fix Use 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'
```
cause This error occurs when trying to import 'SQL' directly from 'agate', but it is actually part of the 'agate-sql' extension.

fix Import 'SQL' from 'agate_sql' instead: 'from agate_sql import SQL'.
```
TypeError: 'NoneType' object is not iterable
```
cause This error occurs when the 'agate-sql' extension is not properly initialized before use.

fix Ensure that 'agate-sql' is properly initialized by calling 'agate.Table.from_sql' or similar methods as documented.
```
ValueError: No SQL connection provided
```
cause This error occurs when attempting to read from or write to a SQL database without providing a valid connection.

fix Provide a valid SQL connection object when using 'agate-sql' functions that interact with a database.

Warnings

breaking The 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.
Fix: Remove any calls to `agatesql.patch()`. Simply `import agatesql` is sufficient.
gotcha When 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.
Fix: Always provide the specific table name you intend to interact with as the second argument to `from_sql()` and `to_sql()`.
gotcha Arbitrary 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()`.
Fix: Use `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.
gotcha agate-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.
Fix: Install the appropriate DBAPI driver for your database. For example, for PostgreSQL, run `pip install psycopg2-binary`.
breaking Python 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.
Fix: Upgrade 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).

Install

pip install agate-sql Install agate-sql
pip install agate-sql "sqlalchemy[postgresql]" Install with PostgreSQL driver example

Imports

agate
```
import agate
```
agatesql
wrong:
```
from agatesql import patch
```
correct:
```
import agatesql
```
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.

Quickstart

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.")

view raw JSON →