Python SQLite3数据库操作

连接数据库并建立表格

import sqlite3
conn = sqlite3.connect('data.db')# 连接数据库
cursor = conn.cursor()
cursor.execute('create table student(number varchar(20) primary key,name varchar(15),elective double,experiment double,required_course double,sum double)')# 建立表格
cursor.close()
conn.close()

插入数据

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
a = "1"
b = "1"
c = 1.0
d = 1
e = 1.0
f = 1.5
cursor.execute('insert into student(number,name,elective,experiment,required_course,sum)values(?,?,?,?,?,?)',(a,b,c,d,e,f))
cursor.close()
conn.commit()
conn.close()

批量插入

import sqlite3
a = "1"
b = "1"
c = 1.0
d = 1
e = 1.0
f = 1.5
temp = []
for i in range(1500):
    te = (str(i),b,c,d,e,f)
    temp.append(te)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.executemany('insert into student(number,name,elective,experiment,required_course,sum)values(?,?,?,?,?,?)',temp)
cursor.close()
conn.commit()
conn.close()

查看数据

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
num = "1"
cursor.execute('select * from student where number=?',(num,))
result = cursor.fetchall()
print(result)
cursor.close()
conn.close()

计算行数

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
num = "1"
cursor.execute('select count(*) from student')
result = cursor.fetchone()
print(result)
cursor.close()
conn.close()

修改数据

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
b = '66'
c = '66'
d = '66'
e = '77'
f = '88'
a = '1'
cursor.execute('update student set name=?,elective=?,experiment=?,required_course=?,sum=? where number=?',(b,c,d,e,f,a))
cursor.close()
conn.commit()
conn.close()

删除数据

import sqlite3
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
num = '1'
cursor.execute('delete from student where number = ?',(num,))
cursor.close()
conn.commit()
conn.close()