sqlite

sqlite是一款非常流行的轻型数据库,在手机端,许多app的数据就存储在sqlite中。相比于mysql,oracle, postgresql等数据库,我最喜欢它的地方在于,使用sqlite无需复杂的安装和配置,在python环境下,直接使用即可。

1、 python连接sqlite

只需两行代码,就可以连接到sqlite数据库

import sqlite3
conn = sqlite3.connect('test.db')

如果test.db这个文件不存在,则上面的代码会创建它并连接,如果存在则直接连接。

除了创建文件外,还可以在内存中创建数据

import sqlite3
conn = sqlite3.connect(":memory:")

2、创建表

下面的代码将创建一个张user表

import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

table_sql = """
create table user(
  id INTEGER PRIMARY KEY autoincrement NOT NULL ,
  name text NOT NULL,
  age INTEGER NOT NULL
)
"""
cursor.execute(table_sql)
conn.commit()  # 一定要提交,否则不会执行sql
conn.close()

sqlite一共有5中数据类型可以定义

类型 描述
NULL NULL 值
INTEGER 带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中
REAL 浮点值,存储为 8 字节的 IEEE 浮点数字。
TEXT 字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储
BLOB blob 数据,完全根据它的输入存储

3. insert数据

import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

sql_lst = [
    "insert into user(name, age)values('lili', 18)",
    "insert into user(name, age)values('poly', 19)",
    "insert into user(name, age)values('lilei', 30)"
]
for sql in sql_lst:
    cursor.execute(sql)
    conn.commit()
conn.close()

4. 查询

先看一个简单的示例

import sqlite3
conn = sqlite3.connect('test.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

sql = "select * from user"
cursor.execute(sql)
rows = cursor.fetchall()  # 获取全部数据
for row in rows:
    print(row.keys(), tuple(row))

conn.close()

运行结果

['id', 'name', 'age'] (1, 'lili', 18)
['id', 'name', 'age'] (2, 'poly', 19)
['id', 'name', 'age'] (3, 'lilei', 30)
  • row.keys() 返回列的名字
  • tuple(row) 获取tuple形式的数据

如果想以字典形式获取数据,则需要指定工厂方法,也就是一个解析数据的函数,将元组类型数据转换为字典类型数据。

import sqlite3
conn = sqlite3.connect('test.db')

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()

sql = "select * from user"
cursor.execute(sql)
rows = cursor.fetchall()  # 获取全部数据
for row in rows:
    print(row)

conn.close()

4. update数据

import sqlite3
conn = sqlite3.connect('test.db')

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()

# 修改数据
update_sql = "update user set age = 22 where id = 1"
cursor.execute(update_sql)
conn.commit()

sql = "select * from user"
cursor.execute(sql)
rows = cursor.fetchall()  # 获取全部数据
for row in rows:
    print(row)

conn.close()

5. 删除

import sqlite3
conn = sqlite3.connect('test.db')

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()

# 删除数据
delete_sql = "delete from user where id = 1"
cursor.execute(delete_sql)
conn.commit()

sql = "select * from user"
cursor.execute(sql)
rows = cursor.fetchall()  # 获取全部数据
for row in rows:
    print(row)

conn.close()

6. 批量插入, executemany

如果你有大量数据需要写入,那么不建议你使用execute,因为每一次执行execute,都要和数据库进行一次数据交互,而批量执行则可以免去这种频繁的数据交互。

import sqlite3
conn = sqlite3.connect('test.db')

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()

# 批量执行
sql = "insert into user(name, age)values(?, ?)"
user_lst = [('lili', 18), ('poly', 19), ('lilei', 30)]
cursor.executemany(sql, user_lst)

sql = "select * from user"
cursor.execute(sql)
rows = cursor.fetchall()  # 获取全部数据
for row in rows:
    print(row)

conn.close()

扫描关注, 与我技术互动

QQ交流群: 211426309

加入知识星球, 每天收获更多精彩内容

分享日常研究的python技术和遇到的问题及解决方案