{
"metadata": {
"name": ""
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Storing and retrieving Datetime"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"By Nikolai Shokhirev http://www.numericalexpert.com/"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"SQLite natively supports only the types TEXT, INTEGER, REAL, BLOB and NULL. If you want to use other types you must add support for them yourself.
\n",
"This includes the following steps:\n",
"\n",
"* Define and register a callable to convert the custom Python type type into one of SQLite's supported types (adapter).\n",
"* Define and registers a callable to convert a bytestring from the database into a custom Python type (converter).\n",
"* Eneble inference of types from column names and/or from column types.\n",
"\n",
"SQLite3 comes with support of DATE and TIMESTAMP (datetime) types (see dbapi2.py).\n",
"The DATE field accepts a sring in ISO 8601 format 'YYYY-MM-DD' or datetime.date object:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import sqlite3\n",
"from datetime import datetime, date"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 1
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn = sqlite3.connect(\"person_db.sqlite\",detect_types=sqlite3.PARSE_DECLTYPES) \n",
"conn.execute(\"CREATE TABLE person (id integer primary key, firstname text, lastname text, dob date)\")"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 2,
"text": [
""
]
}
],
"prompt_number": 2
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"he TIMESTAMP field accepts a sring in ISO 8601 format 'YYYY-MM-DD HH:MM:SS.mmmmmm' or datetime.datetime object:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn.execute(\"INSERT INTO person(firstname, lastname, dob) values (?, ?, ?)\", (\"Joe\",\"Doe\",\"2003-06-25\"))\n",
"d = date(1723,06,05)\n",
"conn.execute(\"INSERT INTO person(firstname, lastname, dob) values (?, ?, ?)\", (\"Adam\",\"Smith\", d))\n",
"conn.commit()\n",
"cur = conn.cursor()\n",
"cur.execute(\"SELECT * FROM person\")\n",
"print cur.fetchone()\n",
"print cur.fetchone()\n",
"conn.close()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"(1, u'Joe', u'Doe', datetime.date(2003, 6, 25))\n",
"(2, u'Adam', u'Smith', datetime.date(1723, 6, 5))\n"
]
}
],
"prompt_number": 3
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn = sqlite3.connect(\"log_db.sqlite\",detect_types=sqlite3.PARSE_DECLTYPES) \n",
"conn.execute(\"CREATE TABLE logs (id integer primary key, message text, [timestamp] timestamp)\")"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 4,
"text": [
""
]
}
],
"prompt_number": 4
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The TIMESTAMP field accepts a sring in ISO 8601 format 'YYYY-MM-DD HH:MM:SS.mmmmmm' or datetime.datetime object:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn.execute(\"INSERT INTO logs(message, timestamp) values (?, ?)\", (\"message: error\",'2012-12-25 23:59:59'))\n",
"conn.execute(\"INSERT INTO logs(message, timestamp) values (?, ?)\", (\"message: ok\", datetime.now()))\n",
"conn.commit()\n",
"cur = conn.cursor()\n",
"cur.execute(\"SELECT * FROM logs\")\n",
"print cur.fetchone()\n",
"print cur.fetchone()\n",
"conn.close()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"(1, u'message: error', datetime.datetime(2012, 12, 25, 23, 59, 59))\n",
"(2, u'message: ok', datetime.datetime(2014, 1, 5, 16, 52, 57, 523000))\n"
]
}
],
"prompt_number": 5
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Storing and retrieving custom types"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"from decimal import Decimal"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 6
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The adapter accepts as single parameter the Python value, and must return a value of the following types: int, long, float, str or buffer."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def decimal2float(val):\n",
" return float(str(val))\n",
"sqlite3.register_adapter(Decimal, decimal2float)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 7
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn = sqlite3.connect(\":memory:\",detect_types=sqlite3.PARSE_DECLTYPES) \n",
"conn.execute(\"CREATE TABLE numbers (number decimal)\")\n",
"conn.execute(\"INSERT INTO numbers(number) values (?)\", (Decimal('123.456789'),))\n",
"conn.commit()\n",
"cur = conn.cursor()\n",
"cur.execute(\"SELECT * FROM numbers\")\n",
"print cur.fetchone()\n",
"conn.close()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"(123.456789,)\n"
]
}
],
"prompt_number": 8
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"To retrieve numbers as a Decimal, we have to implement and register a converter."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"def float2decimal(val):\n",
" return Decimal(str(val))\n",
"sqlite3.register_converter(\"decimal\", float2decimal)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 9
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn = sqlite3.connect(\":memory:\",detect_types=sqlite3.PARSE_DECLTYPES) \n",
"conn.execute(\"CREATE TABLE numbers (number decimal)\")\n",
"conn.execute(\"INSERT INTO numbers(number) values (?)\", (Decimal('123.456789'),))\n",
"conn.commit()\n",
"cur = conn.cursor()\n",
"cur.execute(\"SELECT * FROM numbers\")\n",
"print cur.fetchone()\n",
"conn.close()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"(Decimal('123.456789'),)\n"
]
}
],
"prompt_number": 10
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Accessing columns by name"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"conn = sqlite3.connect(\"person_db.sqlite\",detect_types=sqlite3.PARSE_DECLTYPES)\n",
"conn.row_factory = sqlite3.Row\n",
"cur = conn.cursor()\n",
"cur.execute(\"SELECT * FROM person where id=6\")\n",
"for row in cur:\n",
" print row['firstname']\n",
" print 'DoB:', row['dob']\n",
"conn.close()"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 11
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Using the connection as a context manager"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"# example from http://docs.python.org/2.7/library/sqlite3.html\n",
"con = sqlite3.connect(\":memory:\")\n",
"con.execute(\"create table person (id integer primary key, firstname varchar unique)\")\n",
"\n",
"# Successful, con.commit() is called automatically afterwards\n",
"with con:\n",
" con.execute(\"insert into person(firstname) values (?)\", (\"Joe\",))\n",
"\n",
"# con.rollback() is called after the with block finishes with an exception, the\n",
"# exception is still raised and must be caught\n",
"try:\n",
" with con:\n",
" con.execute(\"insert into person(firstname) values (?)\", (\"Joe\",))\n",
"except sqlite3.IntegrityError:\n",
" print \"couldn't add Joe twice\""
],
"language": "python",
"metadata": {},
"outputs": [
{
"output_type": "stream",
"stream": "stdout",
"text": [
"couldn't add Joe twice\n"
]
}
],
"prompt_number": 12
},
{
"cell_type": "code",
"collapsed": false,
"input": [],
"language": "python",
"metadata": {},
"outputs": []
}
],
"metadata": {}
}
]
}