Since there is no list type in sqlite3, for example, if you want to store a list of integers, use str (); to store it in a string separated by (semicolon), and store it with split () to retrieve it. ) And ʻint () is required.
The sqlite3 module has a mechanism to register these operations in advance and automatically execute them as needed.
If you know that all the types of the list elements match, you can handle the list seamlessly by registering the conversion function using register_adapter () and register_converter (). ..
In the following example, a type called ʻIntListis defined in testtable and the conversion method with python data type is registered usingregister_adapter ()andregister_converter (). Detect_types = sqlite3.PARSE_DECLTYPES` is required when connecting.
test.py
import sqlite3
CREATE_TABLE = u"""
create table if not exists testtable (
id integer primary key,
intlist IntList
);
"""
IntList = list
sqlite3.register_adapter(IntList, lambda l: ';'.join([str(i) for i in l]))
sqlite3.register_converter("IntList", lambda s: [int(i) for i in s.split(';')])
def main():
con = sqlite3.connect(":memory:", detect_types = sqlite3.PARSE_DECLTYPES)
con.row_factory = sqlite3.Row
con.execute(CREATE_TABLE)
insert_list = [1,2,3]
con.execute(u'insert into testtable values(?, ?)', (1, insert_list))
con.commit()
cur = con.cursor()
cur.execute(u'select * from testtable;')
assert insert_list == cur.fetchone()['intlist']
if __name__ == '__main__':
main()
Of course, register_adapter () and register_converter () can also be used with user-defined types.
Reference: http://docs.python.jp/2/library/sqlite3.html#id6
Recommended Posts