sqlite3 is a light module for database manager in python while json is dedicated for json format.
CREATE TABLE table_name(col1 INTEGER PRIMARY KEY, col2 TEXT NOT NULL, col3 REAL)
NULL is equivalent to None in Python.
datatype in sqlite3: NULL, INTEGER, REAL, TEXT, BLOB.
Result of SELECT query SQLITE database inside Python, will return None for NULL value. Other NOT NULL values are fetched in types declared in table creation.
The following code snippet elaborates above notes:
import sqlite3
conn = sqlite3.connect('test.db', 1)
cursor = conn.cursor()
# create table
sql_query = ''' CREATE TABLE IF NOT EXISTS table1 (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age REAL) '''
cursor.execute(sql_query)
conn.commit()
# insert table
sql_query = ''' INSERT OR IGNORE INTO table1 (id, name, age) VALUES(?, ?, ?) '''
values = [(1, 'Ram', None), (2, 'Thao', 28), (3, 'Truong', 33)]
for val_tuple in values:
cursor.execute(sql_query, val_tuple)
conn.commit()
# query table
sql_query = ''' SELECT id, name, age FROM table1 '''
cursor.execute(sql_query)
records = cursor.fetchall()
for record in records:
print [(type(item), item) for item in record]
Output:
[(<type 'int'>, 1), (<type 'unicode'>, u'Ram'), (<type 'NoneType'>, None)]
[(<type 'int'>, 2), (<type 'unicode'>, u'Thao'), (<type 'float'>, 28.0)]
[(<type 'int'>, 3), (<type 'unicode'>, u'Truong'), (<type 'float'>, 33.0)]
For json, dumps and loads are most used as following.
Note: null of json == None of Python
Code that elaborates use of json in Python:
import json
info = {'group1': [{'name' : 'td1', 'age' : 20}, {'name' : 'td2', 'age' : 15}],
'group2': [{'name' : 'td3', 'age' : None}, {'name' : 'td4', 'age' : 20}]
}
# dump to json
print 'Create json file'
with open('example.json', 'w') as fopen:
fopen.write(json.dumps(info))
# show file content
print 'Show saved json file'
with open('example.json') as fopen:
print fopen.read()
# load
print 'Load json file to Python dict'
with open('example.json') as fopen:
print json.load(fopen)
OUTPUT:
Create json file
Show saved json file
{"group1": [{"age": 20, "name": "td1"}, {"age": 15, "name": "td2"}], "group2": [{"age": null, "name": "td3"}, {"age": 20, "name": "td4"}]}
Load json file to Python dict
{u'group1': [{u'age': 20, u'name': u'td1'}, {u'age': 15, u'name': u'td2'}], u'group2': [{u'age': None, u'name': u'td3'}, {u'age': 20, u'name': u'td4'}]}
Ram notes 