SQL Table

The sqltable directive provides a mechanism for populating a table using content drawn from an SQL SELECT statement.

Directive-Type:sqltable Doctree Element:  table Directive Arguments:  3, optional (table title) Directive Options:  Possible Directive Content:  An SQL data source

The following options are recognized:

header: Comma delimited list of heading column names.

widths: [int, ...], relative widths of the table columns.

driver: The driver name to use for connecting to the database. For Excel

spreadsheets, use “xlsx”. For SQLite databases, use “sqlite3”.

source: The data source that will be passed on to the driver for connecting

to the database. For “xlsx” use the path to the spreadsheet. For “sqlite3”, use the path to the sqlite3 database file.

sql: string, SQL that will be used to retrieve the tablular data.

Note that for now the table header values must be specified.

Example

Given an XLSX file, data/test.xlsx containing a work sheet Sheet1 we can use the sqltable directive to render all or a portion of the table.

For example:

.. sqltable:: Example of SQL table
   :header: Name, Age, Folly, Date
   :widths: 10 10 20 10
   :driver: xlsx
   :source: source/data/test.xlsx
   :sql: SELECT * FROM Sheet1 WHERE Age > 12;

Renders as:

Example

Name	Age	Folly	Date
Jane	14	Running fast	2010-01-11 00:00:00
Bob	48	Driving range	1998-08-21 00:00:00
Sue	32	Epicurial facilitation	2011-04-15 00:00:00
Rene	36	Speed	2009-07-12 00:00:00

Module

Implements the SQLTable docutils directive.

License

This work was created by participants in the DataONE project, and is
jointly copyrighted by participating institutions in DataONE. For
more information on DataONE, see our web site at http://dataone.org.

  Copyright 2011

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

  http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

SQLTable is an extension to docutils reStructuredText processor which adds the ability to pull table content from an SQL data source, a CSV file, or an Excel spread sheet. SQLManager is a convenient tool for working with SQLite databases.

Project: DataONE Author: Dave Vieglais

Example for use in Sphinx, add to conf.py after import sys, os:

#Import the SQLTable directive RST extension
from sqltable import SQLTable
from docutils.parsers.rst import directives
directives.register_directive('sql-table', SQLTable)

class sqltable.SQLTable(name, arguments, options, content, lineno, content_offset, block_text, state, state_machine)

check_requirements()

get_sql_data()

Returns rows, max_cols

process_header_option()

Returns table_head

run()