T9uJc4Qt

1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4
5import sqlite3
6
7
8if __name__ == '__main__':
9    dbpath = 'sample.sqlite3'
10    connection = sqlite3.connect(dbpath)
11    cursor = connection.cursor()
12
13    # create table
14    try:
15        cursor.execute('DROP TABLE IF EXISTS sample')
16        cursor.execute('CREATE TABLE IF NOT EXISTS sample (id INTEGER PRIMARY KEY, name TEXT)')
17
18        # INSERT
19        cursor.execute('INSERT INTO sample VALUES (1, "Satoh")')
20        # using placeholder
21        cursor.execute('INSERT INTO sample VALUES (2, ?)', ('Suzuki',))
22        cursor.execute('INSERT INTO sample VALUES (?, ?)', (3, 'Takahashi'))
23        cursor.execute('INSERT INTO sample VALUES (:id, :name)', {'id': 4, 'name': 'Tanaka'})
24
25        persons = [
26            (5, 'Itoh'),
27            (6, 'Watanabe')
28        ]
29        cursor.executemany('INSERT INTO sample VALUES (?, ?)', persons)
30
31        # error
32        cursor.execute('INSERT INTO sample VALUES (1, "Nakamura")')
33
34    except sqlite3.Error as e:
35        print('sqlite3 Error occured:', e.args[0])
36
37    # select
38    cursor.execute('SELECT * FROM sample ORDER by id')
39
40    res = cursor.fetchall()
41    print(res)
42
43    print(len(res))
44    print(cursor.rowcount)
45
46    cursor.execute('SELECT * FROM sample ORDER BY id')
47    print(cursor.fetchone())
48    print(cursor.fetchone())
49    print(cursor.fetchone())
50
51    # WHERE
52    cursor.execute('SELECT * FROM sample WHERE name=?', ('Satoh',))
53    print(cursor.fetchone())
54
55    # not match
56    cursor.execute('SELECT * FROM sample WHERE name=?', ('Nakamura',))
57    print(cursor.fetchall())
58    print(cursor.fetchone())
59
60    for row in cursor.execute('SELECT * FROM sample ORDER by id ASC'):
61        print(row)
62
63    cursor.execute('SELECT * FROM sample ORDER BY id ASC')
64    for row in cursor.fetchall():
65        print(row)
66
67    # update
68    cursor.execute('UPDATE sample SET name=? WHERE id=1', ('Kobayashi',))
69    cursor.execute('UPDATE sample SET name=? WHERE id=?', ('Katoh', 2))
70    connection.commit()
71
72    print('\n### after update')
73    for row in cursor.execute('SELECT * FROM sample ORDER by id ASC'):
74        print(row)
75
76    # delete
77    cursor.execute('DELETE FROM sample WHERE id > 3')
78    connection.commit()
79
80    print('\n### after delete')
81    for row in cursor.execute('SELECT * FROM sample ORDER by id ASC'):
82        print(row)
83
84
85    connection.close()