连接到数据库,如果没有,自动创建
import sqlite3
path_name = 'xiaoxiang.db'
conn = sqlite3.connect(path_name)#连接到数据库,如果没有,自动创建
获取游标
cur = conn.cursor()
cur.execute('SELECT SQLITE_VERSION();')#执行“查看SQLITE版本”的操作
print(cur.fetchone())#打印1个结果
输出:
(‘3.21.0’,)
SQLite自带一个表
cur.execute("SELECT name FROM sqlite_master WHERE type = 'table';").fetchall()
输出:
[(‘book’,)]
建表
cur.execute("DROP TABLE IF EXISTS book;")
cur.execute("CREATE TABLE book(id INT, name TEXT, price DOUBLE);")
输出:
<sqlite3.Cursor at 0x4643fa0>
向表中填入数据
cur.execute("INSERT INTO book VALUES(1,'肖秀荣考研',13.20);")
cur.execute("INSERT INTO book VALUES(2,'小红书',15.20);")
cur.execute("INSERT INTO book VALUES(3,'Python入门',15.80);")
cur.execute("INSERT INTO book VALUES(4,'Linux入门',63.2);")
输出:
<sqlite3.Cursor at 0x4643fa0>
一次插入多条数据
more_data = (
(5,'软件工程',26.36),
(6,'随机过程',56.12),
(7,'概率论',54.45)
)
cur.executemany("INSERT INTO book VALUES(?,?,?);",more_data)
输出:
<sqlite3.Cursor at 0x4643fa0>
提交操作
conn.commit()
查询
rows = cur.execute("SELECT * FROM book").fetchall()
for row in rows:#按行输出
print(row)
输出:
(1, ‘肖秀荣考研’, 13.2)
(2, ‘小红书’, 15.2)
(3, ‘Python入门’, 15.8)
(4, ‘Linux入门’, 63.2)
(5, ‘软件工程’, 26.36)
(6, ‘随机过程’, 56.12)
(7, ‘概率论’, 54.45)
关闭连接
conn.close()