一 sqlite 与 python 的类型对应
二 实例
import sqlite3 def sqlite_basic(): # Connect to db conn = sqlite3.connect( ' test.db ') # create cursor c = conn.cursor() # Create table c.execute( ''' create table if not exists stocks (date text, trans text, symbol text, qty real, price real) ''' ) # Insert a row of data c.execute( ''' insert into stocks values ('2006-01-05','BUY','REHT',100,35.14) ''' ) # query the table rows = c.execute( " select * from stocks ") # print the table for row in rows: print(row) # delete the row c.execute( " delete from stocks where symbol=='REHT' ") # Save (commit) the changes conn.commit() # Close the connection conn.close() def sqlite_adv(): conn = sqlite3.connect( ' test2.db ') c = conn.cursor() c.execute( ''' create table if not exists employee (id text, name text, age inteage) ''') # insert many rows for t in [( ' 1 ', ' itech ', 10), ( ' 2 ', ' jason ', 10), ( ' 3 ', ' jack ', 30), ]: c.execute( ' insert into employee values (?,?,?) ', t) # create index create_index = ' CREATE INDEX IF NOT EXISTS idx_id ON employee (id); ' c.execute(create_index) # more secure t = ( ' jason ',) c.execute( ' select * from employee where name=? ', t) # fetch query result for row in c.fetchall(): print(row) conn.commit() conn.close() def sqlite_adv2(): # memory db con = sqlite3.connect( " :memory: ") cur = con.cursor() # execute sql cur.executescript( ''' create table book( title, author, published ); insert into book(title, author, published) values ( 'AAA book', 'Douglas Adams', 1987 ); ''') rows = cur.execute( " select * from book ") for row in rows: print( " title: " + row[0]) print( " author: " + row[1]) print( " published: " + str(row[2])) def sqlite_adv3(): import datetime # Converting SQLite values to custom Python types # Default adapters and converters for datetime and timestamp con = sqlite3.connect( " :memory: ", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES) cur = con.cursor() cur.execute( " create table test(d date, ts timestamp) ") today = datetime.date.today() now = datetime.datetime.now() cur.execute( " insert into test(d, ts) values (?, ?) ", (today, now)) cur.execute( " select d, ts from test ") row = cur.fetchone() print today, " => ", row[0], type(row[0]) print now, " => ", row[1], type(row[1]) cur.execute( ' select current_date as "d [date]", current_timestamp as "ts [timestamp]" from test ') row = cur.fetchone() print " current_date ", row[0], type(row[0]) print " current_timestamp ", row[1], type(row[1]) # sqlite_basic() # sqlite_adv() # sqlite_adv2() #sqlite_adv3()
完！