Easily handle lists with python + sqlite3

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

Easily handle lists with python + sqlite3
Easily handle databases with Python (SQLite3)
Trying to handle SQLite3 with Python [Note]
Handle Excel with python
Handle rabbimq with python
Easily beep with python
Easily serverless with Python with chalice
[Tips] Handle Athena with Python
[Python] [SQLite3] Operate SQLite with Python (Basic)
Easily implement subcommands with python click
Handle Excel CSV files with Python
[Python] Collect images easily with icrawler!
Easily post to twitter with Python 3
Put protocol buffers into sqlite with python
Save / load in-memory DB with python sqlite3
Process multiple lists with for in Python
Easily download mp3 / mp4 with python and youtube-dl!
FizzBuzz with Python3
Scraping with Python
Statistics with python
Scraping with Python
Python with Go
Twilio with Python
Integrate with Python
Play with 2016-Python
AES256 with python
Tested with Python
python starts with ()
with syntax (Python)
Sqlite in python
Bingo with python
Zundokokiyoshi with python
Excel with Python
Microcomputer with Python
Cast with python
I tried hundreds of millions of SQLite with python
You can easily create a GUI with Python
Handle zip files with Japanese filenames in Python 3
[Rust / Python] Handle numpy with PyO3 (August 2020 version)
Getting started with AWS IoT easily in Python
Create a Python console application easily with Click
[Python] How to handle Japanese characters with openCV
How to handle datetime type in python sqlite3
Serial communication with Python
Zip, unzip with python
Django 1.11 started with Python3.6
Primality test with Python
Socket communication with Python
Data analysis with python 2
Try scraping with Python.
Learning Python with ChemTHEATER 03
Sequential search with Python
"Object-oriented" learning with python
Run Python with VBA
Handling yaml with python
Solve AtCoder 167 with python
Serial communication with python
[Python] Use JSON with Python
Learning Python with ChemTHEATER 05-1
Run prepDE.py with python3
1.1 Getting Started with Python