{"id":6498,"library":"agate-sql","title":"agate-sql","description":"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.","status":"active","version":"0.7.3","language":"en","source_language":"en","source_url":"https://github.com/wireservice/agate-sql","tags":["data analysis","SQL","database","agate","data frames"],"install":[{"cmd":"pip install agate-sql","lang":"bash","label":"Install agate-sql"},{"cmd":"pip install agate-sql \"sqlalchemy[postgresql]\"","lang":"bash","label":"Install with PostgreSQL driver example"}],"dependencies":[{"reason":"Core data analysis library that agate-sql extends.","package":"agate","optional":false},{"reason":"Provides the underlying SQL toolkit for database interaction.","package":"SQLAlchemy","optional":false},{"reason":"Required for connecting to specific database types (e.g., PostgreSQL, MySQL, Oracle). Must be installed separately based on your database choice.","package":"DBAPI driver (e.g., psycopg2, mysqlclient, orlacle-driver)","optional":true}],"imports":[{"symbol":"agate","correct":"import agate"},{"note":"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.","wrong":"from agatesql import patch","symbol":"agatesql","correct":"import agatesql"}],"quickstart":{"code":"import agate\nimport agatesql\nimport os\n\n# This example assumes a PostgreSQL database named 'test_db' with a table 'my_data'.\n# For a real scenario, replace with your database connection string and table name.\n# Ensure the appropriate SQLAlchemy driver (e.g., psycopg2) is installed.\n\n# Create a dummy agate table for demonstration\ndata = [\n    ['apple', 10],\n    ['banana', 20],\n    ['cherry', 30]\n]\ncolumn_names = ['fruit', 'quantity']\ncolumn_types = [agate.Text(), agate.Number()]\n\ntable = agate.Table(data, column_names, column_types)\n\n# --- Writing to SQL (example using a dummy connection string) ---\n# In a real application, use a proper connection string like 'postgresql:///mydb'\n# and ensure your database is running and credentials are correct.\n# For testing, you might use an in-memory SQLite database:\n# SQLALCHEMY_DATABASE_URL = 'sqlite:///:memory:'\nSQLALCHEMY_DATABASE_URL = os.environ.get('SQLALCHEMY_DATABASE_URL', 'sqlite:///:memory:')\nDB_TABLE_NAME = os.environ.get('DB_TABLE_NAME', 'test_fruits')\n\ntry:\n    print(f\"Attempting to write table '{DB_TABLE_NAME}' to {SQLALCHEMY_DATABASE_URL}...\")\n    table.to_sql(SQLALCHEMY_DATABASE_URL, DB_TABLE_NAME, overwrite=True)\n    print(\"Table written successfully.\")\n\n    # --- Reading from SQL ---\n    print(f\"Attempting to read table '{DB_TABLE_NAME}' from {SQLALCHEMY_DATABASE_URL}...\")\n    new_table = agate.Table.from_sql(SQLALCHEMY_DATABASE_URL, DB_TABLE_NAME)\n    print(\"Table read successfully. Contents:\")\n    new_table.print_table()\n\n    # --- Reading from a SQL query (if the dialect supports it) ---\n    # Note: Column types will be inferred from the returned data, not SQL schema.\n    print(f\"Attempting to read from SQL query on '{DB_TABLE_NAME}'...\")\n    query_table = agate.Table.from_sql_query(SQLALCHEMY_DATABASE_URL, f\"SELECT fruit, quantity FROM {DB_TABLE_NAME} WHERE quantity > 15\")\n    print(\"Query results:\")\n    query_table.print_table()\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    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.\")\n","lang":"python","description":"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)."},"warnings":[{"fix":"Remove any calls to `agatesql.patch()`. Simply `import agatesql` is sufficient.","message":"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.","severity":"breaking","affected_versions":"Before 0.4.0"},{"fix":"Always provide the specific table name you intend to interact with as the second argument to `from_sql()` and `to_sql()`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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()`.","severity":"gotcha","affected_versions":"All versions"},{"fix":"Install the appropriate DBAPI driver for your database. For example, for PostgreSQL, run `pip install psycopg2-binary`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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).","message":"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.","severity":"breaking","affected_versions":"From 0.7.3 onwards for Python 3.8/3.9"}],"env_vars":null,"last_verified":"2026-04-15T00:00:00.000Z","next_check":"2026-07-14T00:00:00.000Z","problems":[{"fix":"Install the package using pip: 'pip install agate-sql'.","cause":"This error occurs when the 'agate-sql' package is not installed in your Python environment.","error":"ModuleNotFoundError: No module named 'agate_sql'"},{"fix":"Use the 'to_sql' method provided by 'agate-sql' by importing and using the appropriate functions from the 'agate_sql' module.","cause":"This error occurs when attempting to call the 'to_sql' method on an 'agate.Table' object, which does not have this method.","error":"AttributeError: 'Table' object has no attribute 'to_sql'"},{"fix":"Import 'SQL' from 'agate_sql' instead: 'from agate_sql import SQL'.","cause":"This error occurs when trying to import 'SQL' directly from 'agate', but it is actually part of the 'agate-sql' extension.","error":"ImportError: cannot import name 'SQL' from 'agate'"},{"fix":"Ensure that 'agate-sql' is properly initialized by calling 'agate.Table.from_sql' or similar methods as documented.","cause":"This error occurs when the 'agate-sql' extension is not properly initialized before use.","error":"TypeError: 'NoneType' object is not iterable"},{"fix":"Provide a valid SQL connection object when using 'agate-sql' functions that interact with a database.","cause":"This error occurs when attempting to read from or write to a SQL database without providing a valid connection.","error":"ValueError: No SQL connection provided"}]}