python操作sqlite3数据库：数据库学习的第一步

读取、创建数据库

数据库就类似一个Excel文件

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()

cursor.close()
conn.close()

创建表、删除表

表就类似Excel文件的sheet表，为了省事，我就将所有的数据格式定义为文本类型，具体都有什么格式，大家可以自行百度。

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()
#删除
# conn.execute('drop table people')  #删除一张表
# 创建名为Students的表，
cursor.execute("Create table Students \
(name text,\
id text,\
password text,\
bianyi text,\
ruangong text,\
yingyu text,\
gaoshu text,\
jiwang text)")
conn.commit()  # 更新数据库

cursor.close()
conn.close()

插入单条数据

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()

#问号为占位符，data为元组
data=('李明','17074502','17074502','89','91','93','92','94')
cursor.execute('INSERT into Students values(?,?,?,?,?,?,?,?)',data)
data=('张三','17074503','17074503','99','85','91','90','84')
cursor.execute('INSERT into Students values(?,?,?,?,?,?,?,?)',data)
conn.commit() # 更新数据库

cursor.close()
conn.close()

插入单条数据

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()

books=(('张三','17074503','17074503','99','85','91','90','84'),('李明','17074502','17074502','89','91','93','92','94'))
# 问号为占位符，用books进行填充，books为元组
cursor.executemany("INSERT INTO book VALUES(?, ?, ?)", books)
conn.commit()

cursor.close()
conn.close()

查询数据

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()

result=cursor.execute('SELECT * from Students')
all_students=result.fetchall()
print(all_students)
print(type(all_students))
for i in all_students:
    print(i)

cursor.close()
conn.close()

更新单条数据

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()
sql='''UPDATE Students
    set name='老大'
    where name='张三'
'''
result=cursor.execute(sql)
conn.commit()

cursor.close()
conn.close()

删除单条数据

import sqlite3
db_path = 'stus_data.db'
conn = sqlite3.connect(db_path)  # 没有会创建
cursor = conn.cursor()
sql='''DELETE
    from Students
    where name='老大'
'''
result=cursor.execute(sql)
conn.commit()

cursor.close()
conn.close()

其他操作

请点击这里跳转