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 " )
<sqlite3.Cursor object at 0x7fe2d21052d0>
>>> print(cur.fetchall)
<built-in method fetchall of sqlite3.Cursor object at 0x7fe2d21052d0>
>>> cur.fetchall
<built-in method fetchall of sqlite3.Cursor object at 0x7fe2d21052d0>
>>> cur.fetchall()
[]
>>> conn.execute('''
create table students
(ID int key not null,
NAME text not null,
AGE int not null);
''')
<sqlite3.Cursor object at 0x7fe2d2b3fdc0>
>>> conn.commit()
>>> conn.execute("insert into students(ID, NAME, AGE) values(1, 'Allen', 25)")
<sqlite3.Cursor object at 0x7fe2d20bc1f0>
>>> conn.execute("insert into students(ID, NAME, AGE) values(2, 'Maxsu', 20)")
<sqlite3.Cursor object at 0x7fe2d20bc260>
>>> conn.execute("insert into students(ID, NAME, AGE) values(2, 'Teddy', 24)")
<sqlite3.Cursor object at 0x7fe2d20bc1f0>
>>> conn.commit()
>>> cur = conn.execute("select * from students")
>>> cur.fetchall
<built-in method fetchall of sqlite3.Cursor object at 0x7fe2d20bc260>
>>> 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)
<sqlite3.Cursor object at 0x7fe2d21052d0>
>>> conn.commit()
>>> cur = conn.execute('select * from students')
>>> cur.fetchall
<built-in method fetchall of sqlite3.Cursor object at 0x7fe2d20bc1f0>
>>> 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")
<sqlite3.Cursor object at 0x7fe2d21052d0>
>>> conn.commit()
>>> cur = conn.execute('select * from students')
>>> cur.fetchall
<built-in method fetchall of sqlite3.Cursor object at 0x7fe2d20bc260>
>>> 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 "<pyshell#35>", line 1, in <module>
    conn.execute("delete NAME from students where ID=3")
sqlite3.OperationalError: near "NAME": syntax error
>>> conn.execute("delete from students where ID=3")
<sqlite3.Cursor object at 0x7fe2d21052d0>
>>> 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")
<sqlite3.Cursor object at 0x7fe2d21052d0>
>>> 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 "<pyshell#44>", line 1, in <module>
    conn.execute("select * from test.db")
sqlite3.OperationalError: no such table: test.db
>>> conn.execute("select * from test")
Traceback (most recent call last):
  File "<pyshell#45>", line 1, in <module>
    conn.execute("select * from test")
sqlite3.OperationalError: no such table: test
>>> conn.execute("select * from sqlite_master")
<sqlite3.Cursor object at 0x7fe2d21052d0>
>>> cur = conn.execute("select * from sqlite_master")
>>> cur.fetchall()
[('table', 'students', 'students', 2, 'CREATE TABLE students\n(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 " )
<sqlite3.Cursor object at 0x7fe2d20ce5e0>
>>> cur.fetchall()
[('students',)]
>>>