【python】SQLite3的简单使用（创建数据库、数据表、增删改查、修改字段）

1.说明

sqlite是一款轻型的SQL类型数据库，处理速度快且占用资源非常低，所以非常适合存储本地数据，据说处理速度比Mysql、PostgreSQL还快。目前Django默认的数据库就是SQLite3

2.导入模块

python3就已经内置了sqlite，所以我们不用额外安装，直接导入即可

import sqlite3

3.使用步骤

# 因为是本地文件，所以只需指定db文件位置即可，如果该文件不存在它会自动创建
# 也可以使用":memory:"代替文件路径，则存储在内存里而不是数据库文件
conn = sqlite3.connect('./data/eth_gas_price.sqlite3')

# 获取游标
cur = conn.cursor()

# 执行SQL
cursor.execute("SQL")

# 获取查询结果
cur.fetchone()  # 取出第一条,返回元组或None
cur.fetchall()  # 取出所有（返回列表套元组或空列表）
cur.fetchmany(6) # 取出指定数量（返回列表套元组或空列表）

# 提交（如果有DML操作则需要提交事务才生效）
conn.commit()

# 受影响行数
cur.rowcount

# 记得关闭游标和连接，避免内存泄露
cur.close()
conn.close()

3.举例

3.1 创建数据库

import sqlite3
from sqlite3 import OperationalError

conn = sqlite3.connect('./data/eth_gas_price.sqlite3')
cur = conn.cursor()
try:
    sql = """CREATE TABLE gas_price (
                id integer primary key autoincrement,
                coin_name varchar(15) not null,
                low integer,
                avg integer,
                high integer,
                c_time integer
            );"""
    cur.execute(sql)
    print("create table success")
    return True
except OperationalError as o:
    print(str(o))
    pass
    if str(o) == "table gas_price already exists":
        return True
    return False
except Exception as e:
    print(e)
    return False
finally:
    cur.close()
    conn.close()

3.2 增删改查

conn = sqlite3.connect('eth_gas_price.sqlite3')
cur = conn.cursor()
try:
    # 插入单条语句
    insert_sql = """insert into gas_price(coin_name,low,avg,high,c_time) values('omi',1,1.5,3,1639553204);"""
    cur.execute(insert_sql)
    print("rowcount1:", cur.rowcount)  # 结果：1
    
    # 插入多条语句，注意sqlite使用?做占位符
    insert_many_sql = """insert into gas_price(coin_name,low,avg,high,c_time) values(?,?,?,?,?);"""
    data_list = [("eth", 1, 2, 3, 1639553204), ("btc", 4, 5, 6, 1639553215)]
    cur.executemany(insert_many_sql, data_list)
    print("rowcount2:", cur.rowcount)  # 结果：2
    
    # 更新操作
    cur.execute("""update gas_price set coin_name="fil" where id=1; """)
	print("rowcount3:", cur.rowcount)  # 结果：1
	
	# 删除操作
	cur.execute("delete from gas_price where id=2;")
	
	# 查询
	cur.execute("select * from gas_price where id > 2;")
    print(cur.fetchone())  # 取出第一个，返回元组或None，例如(3, 'eth', 81, 81, 81, 1639553167)
    print(cur.fetchone())  # 取出第二个，每取一个游标里就少一个
    print(cur.fetchall())  # 返回一个列表套元组或空列表，例如[(3, 'eth', 81, 81, 81, 1639553167), (4, 'eth', 81, 81, 81, 1639553169), ...]

    conn.commit()
except Exception as e:
    print(str(e))
finally:
    cur.close()
    conn.close()

3.3 修改表结构

注意，sqlite支持重命名表名和字段名，也支持增加和删除字段，但是不支持修改字段，官方文档里有提到
https://sqlite.org/lang_altertable.html

如果想要修改表结构，可以新建一个表，复制好数据之后再删掉原表，最后重命名为原表，不能先重命名原表再重建同名表，不然会影响外键和索引的

Python代码如下

conn = sqlite3.connect('./data/eth_gas_price.sqlite3')
cur = conn.cursor()
cur.execute("create table test_tmp as select * from gas_price;")
cur.execute("drop table gas_price;")
cur.execute("""create table gas_price (
                id integer primary key autoincrement,
                coin_name varchar(15) not null,
                low integer not null,
                avg integer not null,
                high integer not null,
                c_time integer not null
            );""")

cur.execute("insert into gas_price select * from test_tmp;")
cur.execute("drop table test_tmp;")
conn.commit()

# 查看所有表
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cur.fetchall())
# 查看表结构
cur.execute("PRAGMA table_info(gas_price)")
print(cur.fetchall())