sqlite3 和在 Python 中的使用教程


Author: yifei / Created: April 4, 2018, 1:17 p.m. / Modified: April 4, 2018, 9:18 p.m. / Edit

Yifei's notes:

for databases, we do not put much logic in there, just use it as a reliable persistent store and use its powerful b-tree for sorting. 所谓的事务、锁、存储过程、外键等等不适合互联网公司的业务场景,而更适合于“企业级”“IOE”这些应用。

Commands

% sqlite3 DATABASE

.databases  show databases connected to
.dump TABLE dump table in SQL format
.import FILE ?TABLE?    import SQL data into table
.indices ?TABLE?    show indices of table
.mode MODE  set output mode(csv, column
.read FILE  excute FILE
.schema ?TABLE? show create statement
.restore  ?DB? FILE restore db from file
.headers ON|OFF show headers

美化输出

让 sqlite 的输出更美观,使用 .mode column.headers on 两个命令

sqlite> select * from foo;
234|kshitiz|dba.se
sqlite> .mode column
sqlite> select * from foo;
234         kshitiz     dba.se
sqlite> .headers on
sqlite> select * from foo;
bar         baz         baf
----------  ----------  ----------
234         kshitiz     dba.se

导出 csv

导出

.mode csv 
-- use '.separator SOME_STRING' for something other than a comma.
.headers on 
.out file.dmp 
select * from MyTable;

导入

.mode csv
.import CSV_FILE TABLE_NAME

备份还原

sqlite3 DATABASE .dump > backup.sql sqlite3 DATABASE < backup.sql

或者

sqlite3> .read backup.sql
sqlite3> .dump backup.sql

在 Python 中使用 SQLite

basic usages

The pattern here is to connect to a database and you get connection object which is a handle to the database, the you get a cursor to the database and use the cursor to execute sql and retrieve data, the data is represented as rows.

objects Connection, Cursor, Row

Connect

import sqlite3

conn = sqlite3.connect('database.db') # connections
cursor = conn.cursor() # get cursor
cursor.execute('CREATE TABLE books (id int primary key, name text)')
cursor.execute('INSERT INTO books (name) VALUES (' sophie's choice')')
cursor.execute('INSERT INTO books (name) VALUES ('the bible')')
conn.commit() # always remember to commit
cursor.execute('SELECT * FROM books')

conn.close()

A better approach would be

with sqlite3.connect('db') as conn:
    cursor = conn.cursor()
    # cursor executes

Building statement

# fabricating statement
cursor.execute('select from books where name = ?', ('the bible',)) 
# NOTE the param must be a sequence

# You could also use named placeholders
cursor.execute('insert into books (name) values (:name)', {name: 'the bible'})

Retrive Data

for retrieving the data, there are two ways:

  1. use fetchone, fetchmany(NUMBER), fetchall
  2. use the cursor as an iterator
for row in c.execute('SELECT * FROM stocks ORDER BY price'):
    print row[0], row[1], row['id'], row['name']

The row object can be accessed as a tuple, or a dict

Others

lastrowid

This read-only attribute provides the rowid of the last modified row. It is only set if you issued a INSERT statement using the execute()method. For operations other than INSERT or when executemany() is called, lastrowid is set to None.


评论区