Python SQLite3数据库操作

￡神魔★判官ぃ 2023-06-13 11:13 78阅读 0赞

连接数据库并建立表格

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()