agate-sql 0.7.2

Build status Coverage status PyPI downloads Version License Support Python versions

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

Important links:

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 PostgreSQL database, you’ll also need to pip install psycopg2.

agate-sql supports all included dialects. It is known to work with these external dialects: CrateDB, Ingres.

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:

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 PostgreSQL.

  • 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