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.


blog comments powered by Disqus

Published

31 August 2016

Categories

literate programming org-mode Python SQLite3 lists

Tags