Special Note
This article assumes that you do not wish to use a more sophisticated ORM tool such as
SQLAlchemy.
Some Setup
Let’s start with a Q&D sqlite database given the following sql.
|
|
create table sample(column1 INTEGER, column2 TEXT, column3 TEXT, column4 REAL);
insert into sample(column2, column2, column3, column4) values(1, "Record 1 Text A", "Record 1 Text B", 3.14159);
insert into sample(column1, column2, column3, column4) values(2, "Record 2 Text A", "Record 2 Text B", 6.28318);
insert into sample(column1, column2, column3, column4) values(3, "Record 3 Text A", "Record 3 Text B", 9.42477);
|
You can create the sqlite database given the following command.
|
|
$ sqlite3 sample.db < sample.sql
|
Some Different Methods
For this example we want each record returned via the sql select
statement to be its on JSON document. There are several ways of doing
this. All of them solve the problem reasonably well but I was in search
of the best way. In checking python.org, I discovered that the sqlite
connection object has an attribute falled row_factory. This attribute
can be modified provide selection results in a more advanced way.
Method 1 – My Preferred Method
From the
python docs,
we find that they already have a good factory for generating
dictionaries. It is my opinion that this functionality to should be
more explicitly enabled in the language.
In this method, we override the row_factory attribute with a callable
function that generates the python dictionary from the results.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
# https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.row_factory
import sqlite3
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]
|
Method 2 – Almost As Good As Method 1
This method is just about as good as method 1. Matter of fact, you
can get away with this one and be just fine. Functionally, the methods
are almost identical. With this method, the records can be accessed via
index or via column name. The biggest difference is that unlike method
1, these results don’t have the full functionality of a python
dictionary. For most people, this might be enough.
|
|
con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]
|
Putting It All Together
The following code snippet will extract a group of dictionaries based
on the select statement from the sqlite database and dump it to JSON
for display.
The Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
#!/bin/python
import sqlite3
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
connection = sqlite3.connect("sample.db")
connection.row_factory = dict_factory
cursor = connection.cursor()
cursor.execute("select * from sample")
# fetch all or one we'll go for all.
results = cursor.fetchall()
print results
connection.close()
|
The Results
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[
{
"column1": 1,
"column2": "Record 1 Text A",
"column3": "Record 1 Text B",
"column4": 3.14159
},
{
"column1": 2,
"column2": "Record 2 Text A",
"column3": "Record 2 Text B",
"column4": 6.28318
},
{
"column1": 3,
"column2": "Record 3 Text A",
"column3": "Record 3 Text B",
"column4": 9.42477
}
]
|
.png)
0 comments :