參考資料: 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()