试图5天学会python——SQLite数据库

一、连接数据库并创建表

# 创建表
import sqlite3
from venv import create

# 连接数据库,如果不存在就创建数据库

# 1. 创建链接
conn = sqlite3.connect('snake.db')
print("conn success !")

# 2. 创建游标
cursor = conn.cursor()
print('cursor success')

# 3. 执行sql语句
# 3.1 创建表
cursor.execute('''create table Ranking 
				(id integer primary key, 
				name text, score integer, 
				timestamp integer)''')
print("Raking create success !")

# 4. 关闭游标
cursor.close()
print("cursor closed")

# 5.关闭连接
conn.close()
print('conn closed !')

二、插入数据

import sqlite3

# 插入数据库
# 1. 创建链接
conn = sqlite3.connect('snake.db')
print("conn success !")

# 2. 创建游标
cursor = conn.cursor()
print('cursor success')

# 3. 执行sql语句
cursor.execute(
    '''insert into Ranking (id, name, score, timestamp) values 
    (1, "zhagsan", 1456, 1659663412)''')
cursor.execute(
    '''insert into Ranking (id, name, score, timestamp) values 
    (2, "lisi", 324, 1659663562)''')
cursor.execute(
    '''insert into Ranking (id, name, score, timestamp) values 
    (3, "wangwu", 234, 1659668512)''')
print('insert success !')

# 4. 关闭游标
cursor.close()
print("cursor closed")

# 5. 提交事务：当对数据库数据有改变时，必须要提交
conn.commit()

# 6.关闭连接
conn.close()
print('conn closed !')

三、修改数据

from select import select
import sqlite3

# 查询数据
# 1. 创建链接
conn = sqlite3.connect('snake.db')
print("conn success !")

# 2. 创建游标
cursor = conn.cursor()
print('cursor success')

id = 3
score = 1000

# 3. 执行sql语句
# cursor.execute('select * from Ranking')
cursor.execute('update Ranking set score = ? where id = ?', 
				(score, id))

# 4. 获取查询结果
selectALL = cursor.fetchall() # 查询所有数据
print(selectALL)

selectOne = cursor.fetchone()
print(selectOne)

selectMany = cursor.fetchmany(size=2)
print(selectMany)

# 5. 关闭游标
cursor.close()
print("cursor closed")

conn.commit()

# 6.关闭连接
conn.close()
print('conn closed !')

四、查询数据

from select import select
import sqlite3

# 查询数据
# 1. 创建链接
conn = sqlite3.connect('snake.db')
print("conn success !")

# 2. 创建游标
cursor = conn.cursor()
print('cursor success')

temp_id = 0
score = 100

# 3. 执行sql语句
# cursor.execute('select * from Ranking')
cursor.execute('select * from Ranking where id > ? and score > ?', 
				(temp_id,score))

# 4. 获取查询结果
selectALL = cursor.fetchall() # 查询所有数据
print(selectALL)

selectOne = cursor.fetchone()
print(selectOne)

selectMany = cursor.fetchmany(size=2)
print(selectMany)

# 5. 关闭游标
cursor.close()
print("cursor closed")

# 6.关闭连接
conn.close()
print('conn closed !')

五、删除数据

from select import select
import sqlite3

# 查询数据
# 1. 创建链接
conn = sqlite3.connect('snake.db')
print("conn success !")

# 2. 创建游标
cursor = conn.cursor()
print('cursor success')

temp_id = 2
score = 100

# 3. 执行sql语句
# cursor.execute('select * from Ranking')
cursor.execute(
    'delete from Ranking where id = ?', (temp_id,))

# 4. 获取查询结果
selectALL = cursor.fetchall() # 查询所有数据
print(selectALL)

selectOne = cursor.fetchone()
print(selectOne)

selectMany = cursor.fetchmany(size=2)
print(selectMany)

# 5. 关闭游标
cursor.close()
print("cursor closed")

conn.commit()

# 6.关闭连接
conn.close()
print('conn closed !')