Copyright (C) J. Férard 2018

SQLite on DBF is a very simple tool that allows to get a sqlite3 connection or to process a SQL script (sqlite flavour) on a set of dBase tables (dbf files). Under GPL v.3

Notes:

• A part of this tool was inspired by https://github.com/olemb/dbfread/blob/master/examples/dbf2sqlite by Ole Martin Bjørndalen / UiT The Arctic University of Norway (under MIT licence)
• The example files are adapted from https://www.census.gov/data/tables/2016/econ/stc/2016-annual.html (I didn't find a copyright, but this is fair use I believe)

Windows/Linux:

[sudo] python setup.py install

After the installation:

python -m sqliteondbf -v examples/example.sql

Or

sqliteondbf -v examples/example.sql

It's also possible to execute an inline command (see below for the $convert command):

python -m sqliteondbf -e "$convert 'examples' example.db 'utf-8'"

On Linux, please escape the $ sign:

python -m sqliteondbf -e "\$convert 'examples' example.db 'utf-8'"

This will convert all the dbf files in the examples directory and subdirectories into a sqlite3 databas names example.db.

In a python script (see examples/examples.py):

import sqliteondbf logging.basicConfig(level=logging.INFO) connection = sqliteondbf.connect("path/to/dbf/dir") # now use the sqlite3 connection as usual

There is a mandatory semicolon between instructions.

Usual SQL (sqlite flavour) instructions are simply executed on the current connection.

There are four special instructions that begins with a $ sign: connect, convert, export, def.

To use a set of dbf files, type:

$connect dbf path/to/files/ [encoding]

The current connection is set to an in-memory database which contains all dbf tables.

To use an existing sqlite database a source, type:

$connect sqlite path/to/sqlite.db

The current connection is set to a slite database. This is equivalent to sqlite3.connect("path/to/sqlite.db") in a python script.

Similar to connect, but for saving the sqlite database

$convert path/to/files/ path/to/sqlite.db [encoding]

The current connection to the database is set to the new sqlite database.

Save the result of the last select to a csv file:

$export file.csv

If the result was already fetched, the query is rerun.

To use a custom python function in the script:

$def func(args): ... return ret

To use a custom python aggregate function in the script:

$aggregate Class(): def __init__(self): ... def step(self, v): ... def finalize(self): return ret

Make a dump of the base:

$dump fname.sql

Print the result of the last select on the terminal:

$view [limit]

An optional argument limit sets the maximum number of rows to display. If limit is omitted, the its value is 100. If limit == -1, then no limit is set.

If the result was already fetched, the query is rerun.

Print a string or a list of string on the terminal

$print something