This is a pre-production deployment of Warehouse, however changes made here WILL affect the production instance of PyPI.
Latest Version Dependencies status unknown Test status unknown Test coverage unknown
Project Description
# dbtools
A simple interface to SQLite databases.

* [Documentation](http://jhamrick.github.io/dbtools/)
* [GitHub](https://github.com/jhamrick/dbtools/)
* [PyPI](https://pypi.python.org/pypi/dbtools)

## Overview

This module handles simple interfacing with a SQLite database.
Inspired by [ipython-sql](https://pypi.python.org/pypi/ipython-sql)
`dbtools` returns
[pandas DataFrame](http://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe)
objects from `SELECT` queries, and can handle basic forms of other SQL
statements (`CREATE`, `INSERT`, `UPDATE`, `DELETE`, and `DROP`).

The goal is *not* to replicate the full functionality of
[SQLAlchemy](http://www.sqlalchemy.org/) or really to be used for
object-relational mapping at all. This is meant to be used more for
scientific data collection (e.g., behavioral experiments) as
convenient access to a robust form of storage.

## Installation

The easiest way to get `dbtools` is with `pip`:

```bash
pip install dbtools
```

Alternately, you can clone the repository and install from source:

```bash
git clone git@github.com:jhamrick/dbtools.git
cd dbtools
python setup.py install
```

There is also a `Makefile` in the root of the repository which is just
a convenience wrapper around `setup.py`. So, `make install` is
equivalent to `python setup.py install`. You can use whichever one you
prefer.

## Examples

### Create and load

```python
>>> from dbtools import Table
>>> tbl = Table.create("data.db", "People",
... [('id', int),
... ('name', str),
... ('age', int),
... ('height', float)],
... primary_key='id',
... autoincrement=True)
>>> tbl
People(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, height REAL)
>>> type(tbl)
<class 'dbtools.table.table'="">
```

If a table already exists, we can just directly create a Table object:

```python
>>> tbl = Table("data.db", "People")
>>> tbl
People(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, height REAL)
>>> tbl.columns
(u'id', u'name', u'age', u'height')
>>> tbl.primary_key
u'id'
>>> tbl.autoincrement
True
```

### Insert

Inserting with a list (excluding `id`, because it autoincrements):

```python
>>> tbl.insert(["Alyssa P. Hacker", 25, 66.24])
>>> tbl.select()
name age height
id
1 Alyssa P. Hacker 25 66.24
>>> type(tbl.select())
<class 'pandas.core.frame.dataframe'="">
```

Inserting with a dictionary:

```python
>>> tbl.insert({
... 'name': 'Ben Bitdiddle',
... 'age': 24,
... 'height': 70.1})
>>> tbl.select()
name age height
id
1 Alyssa P. Hacker 25 66.24
2 Ben Bitdiddle 24 70.10
```

You can insert as many things as you want as a time -- just pass them
in as a list of lists and/or dictionaries.

### Select

The previous two examples already used an instance of selection with
`tbl.select()`, which is the equivalent of doing `FROM People SELECT
*`. You can use slicing to select rows (but only if the primary key
column is an integer and autoincrements). Note that because SQLite
databases are one-indexed, indexing the zeroth element returns an
empty `DataFrame`.

```python
>>> tbl[1]
name age height
id
1 Alyssa P. Hacker 25 66.24
>>> tbl[2:]
name age height
id
2 Ben Bitdiddle 24 70.1
```

If you pass in a string or sequence of strings, it will treat them as
column names and select those columns:

```python
>>> tbl['name', 'height']
name height
id
1 Alyssa P. Hacker 66.24
2 Ben Bitdiddle 70.10
```

More advanced selection can be done through the `select` method by
specifying the `where` keyword argument (and you can use the `?`
syntax from the `sqlite3` library for untrusted inputs):

```python
>>> tbl.select(where='age>24')
name age height
id
1 Alyssa P. Hacker 25 66.24
>>> tbl.select(columns=['name', 'height'], where=('age>?', 24))
name height
id
1 Alyssa P. Hacker 66.24
```

### Update

Updating data in the table works by taking a dictionary (with the keys
being columns, and values being new data) and (optionally) a `where`
keyword argument like in the `select` method to specify what data
should be updated.

```python
>>> tbl.update({'age': 26}, where='id=1')
>>> tbl.select()
name age height
id
1 Alyssa P. Hacker 26 66.24
2 Ben Bitdiddle 24 70.10
```

### Delete

Deleting a row or rows requires specifying a `where` keyword argument
like in `select` and `update` (if it is not given, all rows are
deleted).

```python
>>> tbl.delete(where='height<70')
>>> tbl.select()
name age height
id
2 Ben Bitdiddle 24 70.1
```

### Drop

Finally, the `drop` method is used to drop (delete) an entire table
from its database. Of course, this means it can't be accessed
afterwards because it no longer exists.

```python
>>> tbl.drop()
>>> tbl.select()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "dbtools/table.py", line 339, in select
cur.execute(*cmd)
sqlite3.OperationalError: no such table: People
```
Release History

Release History

0.4.0

This version

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.03.3

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.03.2

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.03.1

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.03

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.02

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

0.01

History Node

TODO: Figure out how to actually get changelog content.

Changelog content for this version goes here.

Donec et mollis dolor. Praesent et diam eget libero egestas mattis sit amet vitae augue. Nam tincidunt congue enim, ut porta lorem lacinia consectetur. Donec ut libero sed arcu vehicula ultricies a non tortor. Lorem ipsum dolor sit amet, consectetur adipiscing elit.

Show More

Download Files

Download Files

TODO: Brief introduction on what you do with files - including link to relevant help section.

File Name & Checksum SHA256 Checksum Help Version File Type Upload Date
dbtools-0.4.0.tar.gz (13.9 kB) Copy SHA256 Checksum SHA256 Source Nov 25, 2013

Supported By

WebFaction WebFaction Technical Writing Elastic Elastic Search Pingdom Pingdom Monitoring Dyn Dyn DNS HPE HPE Development Sentry Sentry Error Logging CloudAMQP CloudAMQP RabbitMQ Heroku Heroku PaaS Kabu Creative Kabu Creative UX & Design Fastly Fastly CDN DigiCert DigiCert EV Certificate Rackspace Rackspace Cloud Servers DreamHost DreamHost Log Hosting