Skip to content
Back to top
View this page

# agate-sql 0.7.2

agate-sql adds SQL read/write support to agate.

Important links:

- agate https://agate.rtfd.org

- Documentation: https://agate-sql.rtfd.org

- Repository: https://github.com/wireservice/agate-sql

- Issues: https://github.com/wireservice/agate-sql/issues

## Install

To install:

    pip install agate-sql

For details on development or supported platforms see the agate documentation.

Warning

You’ll need to have the correct sqlalchemy drivers installed for whatever database you plan to access. For instance, in order to read/write tables in a Postgres database, you’ll also need to `pip install psycopg2`.

## Usage

agate-sql uses a monkey patching pattern to add SQL support to all `agate.Table` instances.

    import agate
    import agatesql

Importing `agatesql` attaches new methods to `agate.Table`. For example, to import a table named `doctors` from a local postgresql database named `hospitals` you will use `from_sql()`:

    new_table = agate.Table.from_sql('postgresql:///hospitals', 'doctors')

To save this table back to the database:

    new_table.to_sql('postgresql:///hospitals', 'doctors')

The first argument to either function can be any valid sqlalchemy connection string. The second argument must be a database name. (Arbitrary SQL queries are not supported.)

That’s all there is to it.

# API

agatesql.table.from_sql(cls, connection_or_string, table_name)

  Create a new `agate.Table` from a given SQL table. Types will be inferred from the database schema.

  Monkey patched as class method `Table.from_sql()`.

  Parameters:

    - connection_or_string – An existing sqlalchemy connection or connection string.

    - table_name – The name of a table in the referenced database.

agatesql.table.from_sql_query(self, query)

  Create an agate table from the results of a SQL query. Note that column data types will be inferred from the returned data, not the column types declared in SQL (if any). This is more flexible than `from_sql()` but could result in unexpected typing issues.

  Parameters:

    query – A SQL query to execute.

agatesql.table.to_sql(self, connection_or_string, table_name, overwrite=False, create=True, create_if_not_exists=False, insert=True, prefixes=[], db_schema=None, constraints=True, unique_constraint=[], chunk_size=None, min_col_len=1, col_len_multiplier=1)

  Write this table to the given SQL database.

  Monkey patched as instance method `Table.to_sql()`.

  Parameters:

    - connection_or_string – An existing sqlalchemy connection or a connection string.

    - table_name – The name of the SQL table to create.

    - overwrite – Drop any existing table with the same name before creating.

    - create – Create the table.

    - create_if_not_exists – When creating the table, don’t fail if the table already exists.

    - insert – Insert table data.

    - prefixes – Add prefixes to the insert query.

    - db_schema – Create table in the specified database schema.

    - constraints – Generate constraints such as `nullable` for table columns.

    - unique_constraint – The names of the columns to include in a UNIQUE constraint.

    - chunk_size – Write rows in batches of this size. If not set, rows will be written at once.

    - col_min_len – The minimum length of text columns.

    - col_len_multiplier – Multiply the maximum column length by this multiplier to accomodate larger values in later runs.

agatesql.table.to_sql_create_statement(self, table_name, dialect=None, db_schema=None, constraints=True, unique_constraint=[])

  Generates a CREATE TABLE statement for this SQL table, but does not execute it.

  Parameters:

    - table_name – The name of the SQL table to create.

    - dialect – The dialect of SQL to use for the table statement.

    - db_schema – Create table in the specified database schema.

    - constraints – Generate constraints such as `nullable` for table columns.

    - unique_constraint – The names of the columns to include in a UNIQUE constraint.

agatesql.table.sql_query(self, query, table_name='agate')

  Convert this agate table into an intermediate, in-memory sqlite table, run a query against it, and then return the results as a new agate table.

  Multiple queries may be separated with semicolons.

  Parameters:

    - query – One SQL query, or multiple queries to be run consecutively separated with semicolons.

    - table_name – The name to use for the table in the queries, defaults to `agate`.

## Authors

The following individuals have contributed code to agate-sql:

- Christopher Groskopf

- Adrian Klaver

- James McKinney

- Chris Keller

- git-clueless

- z2s8

- Jake Zimmerman

- Shige Takeda

- Roger Webb

- Steve Kowalik

## Changelog

### 0.7.2 - Jan 9, 2024

- fix: Remove internal use of transactions (added in 0.6.0), because csvkit’s csvsql already starts a transaction.

### 0.7.1 - Jan 9, 2024

- feat: Add experimental support for Ingres.

- fix: Restore internal use of transactions instead of savepoints, because not all database engines support savepoints.

### 0.7.0 - Oct 18, 2023

- feat: Use Fast Executemany Mode when using the PyODBC SQL Server dialect.

- Add Python 3.12 support.

- Drop support for Python 3.6 (2021-12-23), 3.7 (2023-06-27).

### 0.6.0 - Sep 26, 2023

- Allow SQLAlchemy 2. Disallow SQLAlchemy < 1.4.

### 0.5.9 - Jan 28, 2023

- Disallow SQLAlchemy 2.

### 0.5.8 - September 15, 2021

- Fix tests for Linux packages.

### 0.5.7 - July 13, 2021

- Add wheels distribution.

### 0.5.6 - March 4, 2021

- Fix test that fails in specific environments.

### 0.5.5 - July 7, 2020

- Set type to `DATETIME` for datetime (MS SQL).

- Drop support for Python 2.7 (EOL 2020-01-01), 3.4 (2019-03-18), 3.5 (2020-09-13).

### 0.5.4 - March 16, 2019

- Add `min_col_len` and `col_len_multiplier` options to `Table.to_sql()` to control the length of text columns.

- agate-sql is now tested against Python 3.7.

- Drop support for Python 3.3 (end-of-life was September 29, 2017).

Dialect-specific:

- Add support for CrateDB.

- Set type to `BIT` for boolean (MS SQL).

- Eliminate SQLite warning about Decimal numbers.

### 0.5.3 - January 28, 2018

- Add `chunk_size` option to `Table.to_sql()` to write rows in batches.

- Add `unique_constraint` option to `Table.to_sql()` to include in a UNIQUE constraint.

Dialect-specific:

- Specify precision and scale for `DECIMAL` (MS SQL, MySQL, Oracle).

- Set length of `VARCHAR` to `1` even if maximum length is `0` (MySQL).

- Set type to `TEXT` if maximum length is greater than 21,844 (MySQL).

### 0.5.2 - April 28, 2017

- Add `create_if_not_exists` flag to `Table.to_sql()`.

### 0.5.1 - February 27, 2017

- Add `prefixes` option to `to_sql()` to add expressions following the INSERT keyword, like OR IGNORE or OR REPLACE.

- Use `TIMESTAMP` instead of `DATETIME` for DateTime columns.

### 0.5.0 - December 23, 2016

- `VARCHAR` columns are now generated with proper length constraints (unless explicilty disabled).

- Tables can now be created from query results using `from_sql_query()`.

- Add support for running queries directly on tables with `sql_query()`.

- When creating tables, `NOT NULL` constraints will be created by default.

- SQL create statements can now be generated without being executed with `to_sql_create_statement()`

### 0.4.0 - December 19, 2016

- Modified `example.py` so it no longer depends on Postgres.

- It is no longer necessary to run `agatesql.patch()` after importing agatesql.

- Upgrade required agate to `1.5.0`.

### 0.3.0 - November 5, 2015

- Add `overwrite` flag to `Table.to_sql()`.

- Removed Python 2.6 support.

- Updated agate dependency to version 1.1.0.

- Additional SQL types are now supported. (#4, #10)

### 0.2.0 - October 22, 2015

- Add explicit patch function.

### 0.1.0 - September 22, 2015

- Initial version.

## License

The MIT License

Copyright (c) 2017 Christopher Groskopf and contributors

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

## Indices and tables

- Index

- Module Index

- Search Page