SQLite3 dynamic typing - Cooking Python in Emacs

Definition
Documentation
Fixtures
- Dynamic typing explanation
- Python lists
Results
Conclusion

Definition

Data types in any RDBMS serve for data integrity protection. Its plane definition in many cases might prevent data corruption by rejecting erroneous data writing into DB cells.

SQLite3 in addition allows you to store Python lists in its tables. It is not a bug but a feature.

Documentation

The main article about data types in SQLite3.

Fixtures

Dynamic typing explanation

First name	Last name	Age
John	Doe	33
Ian	Lord	69
Steven	Fry	66

DROP TABLE IF EXISTS customers;
CREATE TABLE IF NOT EXISTS customers(first_name TEXT, last_name TEXT, age INT);
.mode csv
.import $orgtable customers
SELECT * FROM customers;
-- DROP TABLE customers; -- sometimes it is necessary

first_name	last_name	age
John	Doe	33
Ian	Lord	69
Steven	Fry	66

Perfect! Now take an attempt to insert the row with wrong data type.

INSERT INTO customers VALUES('Phil', 'Collins', 'ABBA');
SELECT * FROM customers;

first_name	last_name	age
John	Doe	33
Ian	Lord	69
Steven	Fry	66
Phil	Collins	ABBA

SELECT typeof(age) FROM customers;

typeof(age)
integer
integer
integer
text

It is dynamic typing. In SQLite, the datatype of a value is associated with the value itself, not with its container.

Python lists

import sqlite3

conn = sqlite3.connect('data_types.db')
c = conn.cursor()
c.execute('SELECT COUNT(*) FROM customers')
num_rows = c.fetchone()[0]
print(num_rows)
# conn.close()

>>> >>> >>> <sqlite3.Cursor object at 0xb7233220>
>>> 4

sample_list = ['This', 'is', 'a', 'sample', 'list']
c.execute('INSERT INTO customers VALUES(?, ?, ?)', ('Captain', 'Ahab', repr(sample_list)))
conn.commit()
c.execute('SELECT * FROM customers')
result = c.fetchall()
print(result)

<sqlite3.Cursor object at 0xb7233220>
>>> <sqlite3.Cursor object at 0xb7233220>
>>> [('John', 'Doe', 33), ('Ian', 'Lord', 69), ('Steven', 'Fry', 66), ('Phil', 'Collins', 'ABBA'), ('Captain', 'Ahab', "['This', 'is', 'a', 'sample', 'list']")]

SELECT * FROM customers;

first_name	last_name	age
John	Doe	33
Ian	Lord	69
Steven	Fry	66
Phil	Collins	ABBA
Captain	Ahab	['This', 'is', 'a', 'sample', 'list']

SELECT typeof(age) FROM customers;

typeof(age)
integer
integer
integer
text
text

Seems like a plain text representation of Python list. Try to retrieve the sample list out form SQLite3:

c.execute('SELECT age FROM customers WHERE first_name=?', ('Captain',))
sample_list = eval(c.fetchone()[0])
type(sample_list)
print(sample_list)
conn.close()

<sqlite3.Cursor object at 0xb7233220>
>>> <class 'list'>
['This', 'is', 'a', 'sample', 'list']

Results

Be careful, though that you are certain no user-generated data can get into the column, or the eval() is a security risk.

Conclusion

Dynamic typing requres to check your data apard and before any table INSERT/UPDATE operation;
Yes, it is possible to store Python lists in SQLite3 cells;
List locates in a table cell as its representation, thus any SQL operations under its content are unavailable.

← Previous Archive Next →

blog comments powered by Disqus

Published

31 August 2016

SQLite3 dynamic typing and Python lists

Table of Contents