【Python】【SQLite3】PythonでSQLiteを操作する(基本編)

PythonでSQLiteを操作するときのメモ
備忘録です。

ソースコードと説明

sample.py

import sqlite3
from contextlib import closing

dbname = "sample.db"

with closing(sqlite3.connect(dbname)) as conn:
    c = conn.cursor()
    query = '''drop table if exists User'''
    c.execute(query)
    conn.commit()

with closing(sqlite3.connect(dbname)) as conn:
    c = conn.cursor()
    query = '''create table if not exists
                User(id integer primary key, name varchar(32))'''
    c.execute(query)
    conn.commit()

with closing(sqlite3.connect(dbname)) as conn:
    c = conn.cursor()
    query = ''' insert into User (id, name) values (?,?)'''
    user = (1, "Yamashita")
    c.execute(query, user)
    conn.commit()

with closing(sqlite3.connect(dbname)) as conn:
    c = conn.cursor()
    query = ''' insert into User (id, name) values (?,?)'''
    user = [
        (2, "Kinoshita")
        ,(3, "Hasegawa")
    ]
    c.executemany(query, user)
    conn.commit()

with closing(sqlite3.connect(dbname)) as conn:
    c = conn.cursor()
    query = "select * from User"
    for row in c.execute(query):
        print(row)

with closing(sqlite3.connect(dbname)) as conn:

sqliteを操作する際は、クエリの実行後commit(),close()が必要
contextlibのclosingを使用することで、connectionのclose()記載漏れを防げる

c.executemany(query, user)

executemany()を使用すると、タプルのリストを引数として渡して複数のinsertをまとめて記述できる