sqlite3
https://docs.python.org/3/library/sqlite3.html
```python3 linenum="1"
import sqlite3
con = sqlite3.connect('example.db') # :memory: for in memory db
Doing things
con.close()
- Create a cursor object and CRUD
```python3 linenums="1"
cur = con.cursor()
# Create
cur.execute('CREATE TABLE status (state)')
con.commit()
- Request with parameters
- Prevent from SQL injection
1
2
3
4
5
6
7
8
9
10
11
12 | # ? style
cur.execute("insert into lang values (?, ?)", ("C", 1972))
lang_list = [
("Fortran", 1957),
("Python", 1991),
("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)
# named style
cur.execute("select * from lang where first_appeared=:year", {"year": 1972})
|
Read
| for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
print(row)
|
- Use
fetchone(), fetchmany(), fetchall()
| cur.execute('SELECT * FROM table WHERE a=b')
cur.fetchone() # None if no data available
|
Row
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | >>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = cur.fetchone()
>>> type(r)
<class 'sqlite3.Row'>
>>> tuple(r)
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
... print(member)
...
2006-01-05
BUY
RHAT
100.0
35.14
|
Other
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 | cur.executescript('''
create table person(
firstname,
lastname,
age
);
create table book(
title,
author,
published
);
insert into book(title, author, published)
values (
'Dirk Gently''s Holistic Detective Agency',
'Douglas Adams',
1987
);
''')
|
TODO: advanced usage
References
https://pythonexamples.org/python-sqlite3-tutorial/