參考資料： http://tw.gitbook.net/sqlite/sqlite_python.html
此網站用來快速查詢SQLlite3的各種指令及用法，這不是資料庫教學，
我是假設讀者是熟知資料庫的語法及運作
(1) SQLlite 安裝
conda install -c anaconda sqlite
(2) 資料庫管理
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
(3) 各種重要的 SQLite 點命令，如下：
| 命令 |
描述 |
| .backup ?DB? FILE |
Backup DB (default "main") to FILE |
| .bail ON|OFF |
Stop after hitting an error. Default OFF |
| .databases |
List names and files of attached databases |
| .dump ?TABLE? |
Dump the database in an SQL text format. If TABLE specified, only dump tables matching LIKE pattern TABLE. |
| .echo ON|OFF |
Turn command echo on or off |
| .exit |
Exit SQLite prompt |
| .explain ON|OFF |
Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. |
| .header(s) ON|OFF |
Turn display of headers on or off |
| .help |
Show this message |
| .import FILE TABLE |
Import data from FILE into TABLE |
| .indices ?TABLE? |
Show names of all indices. If TABLE specified, only show indices for tables matching LIKE pattern TABLE. |
| .load FILE ?ENTRY? |
Load an extension library |
| .log FILE|off |
Turn logging on or off. FILE can be stderr/stdout |
| .mode MODE |
Set output mode where MODE is one of:
-
csv Comma-separated values
-
column Left-aligned columns.
-
html HTML <table> code
-
insert SQL insert statements for TABLE
-
line One value per line
-
list Values delimited by .separator string
-
tabs Tab-separated values
-
tcl TCL list elements
|
| .nullvalue STRING |
Print STRING in place of NULL values |
| .output FILENAME |
Send output to FILENAME |
| .output stdout |
Send output to the screen |
| .print STRING... |
Print literal STRING |
| .prompt MAIN CONTINUE |
Replace the standard prompts |
| .quit |
Exit SQLite prompt |
| .read FILENAME |
Execute SQL in FILENAME |
| .schema ?TABLE? |
Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE. |
| .separator STRING |
Change separator used by output mode and .import |
| .show |
Show the current values for various settings |
| .stats ON|OFF |
Turn stats on or off |
| .tables ?PATTERN? |
List names of tables matching a LIKE pattern |
| .timeout MS |
Try opening locked tables for MS milliseconds |
| .width NUM NUM |
Set column widths for "column" mode |
| .timer ON|OFF |
Turn the CPU timer measurement on or off |
(4) 格式化輸出的方式在本基礎教程中，以下已經列出：
sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>
(5) Create Table
sqlite> CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
列出Tables
sqlite>.tables
COMPANY DEPARTMENT
Table schema：
sqlite>.schema COMPANY
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
Insert Table
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
Select Table
SELECT * FROM table_name;
下面是python 程式的寫法
Create Table
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print "Table created successfully";
conn.close()
Insert 或update delete Table
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.commit()
print "Records created successfully";
conn.close()
Select Table
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "
"
print "Operation done successfully";
conn.close()
