python sqlite操作_python.sqlite3 简单操作

importsqlite3defsqlite_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 inrows:print(row)#delete the row

c.execute("delete from stocks where symbol=='REHT'")#Save (commit) the changes

conn.commit()#Close the connection

conn.close()defsqlite_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 inc.fetchall():print(row)

conn.commit()

conn.close()defsqlite_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',

);''')

rows= cur.execute("select * from book")for row inrows:print("title:" +row[0])print("author:" + row[1])print("published:" + str(row[2]))defsqlite_adv3():importdatetime#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])