Skip to content

使用SQLite

使用传统的connect()

使用方式与 Python 自带的 sqlite3 的使用方式类似
不同的是执行增删改操作，会直接生效，不需要 commit，也不需要 Cursor 对象

创建表

1
2
3
4
from sqlalchemy import create_engine
engine = create_engine('sqlite:///user.db')
with engine.connect() as conn:
    conn.execute('create table user (id int(10) primary key, name varchar(20))')

写入记录

1
2
3
4
5
6
from sqlalchemy import create_engine
engine = create_engine('sqlite:///user.db')
with engine.connect() as conn:
    conn.execute('insert into user (id, name) values (1, "cai")')
    conn.execute('insert into user (id, name) values (2, "nocilantro")')
    conn.execute('insert into user (id, name) values (3, "不要香菜🙏")')

查询记录

1
2
3
4
5
6
7
8
9
from sqlalchemy import create_engine
engine = create_engine('sqlite:///user.db')
with engine.connect() as conn:
    res = conn.execute('select * from user')
    data = res.fetchall()
    print(data)
"""
[(1, 'cai'), (2, 'nocilantro'), (3, '不要香菜🙏')]
"""

设置check_same_thread

sqlite 只支持单线程，所以一般不进行设置

sqlite:///user.db?check_same_thread=False

使用connect()事务

使用事务可以进行批量提交和回滚

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
from sqlalchemy import create_engine

engine = create_engine('sqlite:///user.db')

with engine.connect() as conn:
    trans = conn.begin()
    try:
        r1 = conn.execute("insert into user (id, name) values (4, 'moon')")
        r2 = conn.execute("insert into user (id, name) values (5, 'sun')")
        trans.commit()
    except:
        trans.rollback()
        raise

使用Session对象(不使用模型类)

Session 可以记录和跟踪数据的改变，在适当的时候提交，并且支持强大的 ORM 的功能

这里的增删改需要 commit()

创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///user.db')

DBSession = sessionmaker(bind=engine) # 创建会话类

session = DBSession() # 创建会话对象

session.execute('create table user (id int(10) primary key, name varchar(20))')

写入记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///user.db')
DBSession = sessionmaker(bind=engine) # 创建会话类
session = DBSession() # 创建会话对象

session.execute('insert into user (id, name) values (1, "cai")')
session.execute('insert into user (id, name) values (2, "nocilantro")')
session.execute('insert into user (id, name) values (3, "不要香菜🙏")')
session.execute('insert into user (id, name) values (:id, :name)', {'id': '666', 'name': 'cool'})

session.commit()

注意参数使用 dict，并在 sql 语句中使用:key占位

查询记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('sqlite:///user.db')
DBSession = sessionmaker(bind=engine) # 创建会话类
session = DBSession() # 创建会话对象

res = session.execute('select * from user')
data = res.fetchall()
print(data)

session.commit()
"""
[(1, 'cai'), (2, 'nocilantro'), (3, '不要香菜🙏'), (666, 'cool')]
"""