python + sqlite3 基本操作

连接数据库

import sqlite3


# 连接数据库(如果不存在则创建)
conn = ('')
print("Opened database successfully")

# 创建游标
cursor = ()

...

# 关闭游标
()
# 提交事物
()
# 关闭连接
()

创建表

...
# 创建游标
cursor = ()

# 创建表
sql = 'CREATE TABLE Student(id integer PRIMARY KEY autoincrement, Name  varchar(30), Age integer)'
(sql)

# 提交事物
()
...

插入数据

...
# 创建游标
cursor = ()

# 插入数据
sql = "INSERT INTO Student(Name, Age) VALUES(\'love\', 22)"
(sql)

# 插入数据 2
data = ('love2', 2221) # or ['love2', 2221]
sql = "INSERT INTO Student(Name, Age) VALUES(?, ?)"
(sql, data)

# 提交事物
()
...

查询数据

...
# 创建游标
cursor = ()

# 查询数据
sql = "select * from Student"
values = (sql)
for i in values:
    print(i)

# 查询数据 2
sql = "select * from Student where id=?"
values = (sql, (1,))
for i in values:
    print('id:', i[0])
    print('name:', i[1])
    print('age:', i[2])

# 提交事物
()
...

其他操作

自增字段起始位置

# 设置起始值为1

update sqlite_sequence SET seq = 0 where name = '表名';

# 设置全部表起始值为默认值

delete from sqlite_sequence where name='TableName'; --注意表名区分大小写