python sqlite3 创建、读取、修改、删除

操作示例，聊作笔记

# python sqlite3 的操作

#导入sqlite3
import sqlite3 as sq

#打开数据库连接

conn = sq.connect('test.db')
cur = conn.cursor()
print('open database successfully')

#查看存在的数据表
#conn.execute("select name from sqlite_master where type='table' order by name " )  #??
#conn.commit()

#清除已存在的数据表 students

conn.execute(''' drop table students''')
conn.commit()

#创建一个表
conn.execute('''
create table students
(ID int key not null,
NAME text not null,
AGE int not null);
''')

>>> import sqlite3 as s
>>> conn = s.connect('test.db')
>>> cur = conn.cursor()
>>> cur.execute("select name from sqlite_master where type='table' order by name " )

>>> print(cur.fetchall)

>>> cur.fetchall

>>> cur.fetchall()
[]
>>> conn.execute('''
create table students
(ID int key not null,
NAME text not null,
AGE int not null);
''')

>>> conn.commit()
>>> conn.execute("insert into students(ID, NAME, AGE) values(1, 'Allen', 25)")

>>> conn.execute("insert into students(ID, NAME, AGE) values(2, 'Maxsu', 20)")

>>> conn.execute("insert into students(ID, NAME, AGE) values(2, 'Teddy', 24)")

>>> conn.commit()
>>> cur = conn.execute("select * from students")
>>> cur.fetchall

>>> cur.fetchall()
[(1, 'Allen', 25), (2, 'Maxsu', 20), (2, 'Teddy', 24)]
>>> data = [(4, 'Allen2', 35), (5, 'Maxsu2', 30), (6, 'Teddy2', 34)]
>>> com = "insert into students values(?, ?, ?)"
>>> conn.executemany(com, data)

>>> conn.commit()
>>> cur = conn.execute('select * from students')
>>> cur.fetchall

>>> cur.fetchall()
[(1, 'Allen', 25), (2, 'Maxsu', 20), (2, 'Teddy', 24), (4, 'Allen2', 35), (5, 'Maxsu2', 30), (6, 'Teddy2', 34)]
>>> conn.execute("update students set AGE=40 where ID=4")

>>> conn.commit()
>>> cur = conn.execute('select * from students')
>>> cur.fetchall

>>> cur.fetchall()
[(1, 'Allen', 25), (2, 'Maxsu', 20), (2, 'Teddy', 24), (4, 'Allen2', 40), (5, 'Maxsu2', 30), (6, 'Teddy2', 34)]
>>> conn.execute("delete NAME from students where ID=3")
Traceback (most recent call last):
  File "", line 1, in 
    conn.execute("delete NAME from students where ID=3")
sqlite3.OperationalError: near "NAME": syntax error
>>> conn.execute("delete from students where ID=3")

>>> conn.commit()
>>> cur = conn.execute("select * from students")
>>> cur.fetchall()
[(1, 'Allen', 25), (2, 'Maxsu', 20), (2, 'Teddy', 24), (4, 'Allen2', 40), (5, 'Maxsu2', 30), (6, 'Teddy2', 34)]
>>> conn.execute("delete from students where ID=2")

>>> conn.commit()
>>> cur= conn.execute('select * from students')
>>> cur.fetchall()
[(1, 'Allen', 25), (4, 'Allen2', 40), (5, 'Maxsu2', 30), (6, 'Teddy2', 34)]
>>> conn.execute("select * from test.db")
Traceback (most recent call last):
  File "", line 1, in 
    conn.execute("select * from test.db")
sqlite3.OperationalError: no such table: test.db
>>> conn.execute("select * from test")
Traceback (most recent call last):
  File "", line 1, in 
    conn.execute("select * from test")
sqlite3.OperationalError: no such table: test
>>> conn.execute("select * from sqlite_master")

>>> cur = conn.execute("select * from sqlite_master")
>>> cur.fetchall()
[('table', 'students', 'students', 2, 'CREATE TABLE studentsn(ID int key not null,nNAME text not null,nAGE int not null)')]
>>> cur.execute("select name from sqlite_master where type='table' order by name " )

>>> cur.fetchall()
[('students',)]
>>>