agate-sql 0.7.0#
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
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
.
Changelog#
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
andcol_len_multiplier
options toTable.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 toTable.to_sql()
to write rows in batches.Add
unique_constraint
option toTable.to_sql()
to include in a UNIQUE constraint.
Dialect-specific:
Specify precision and scale for
DECIMAL
(MS SQL, MySQL, Oracle).Set length of
VARCHAR
to1
even if maximum length is0
(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 toTable.to_sql()
.
0.5.1 - February 27, 2017#
Add
prefixes
option toto_sql()
to add expressions following the INSERT keyword, like OR IGNORE or OR REPLACE.Use
TIMESTAMP
instead ofDATETIME
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 toTable.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.