SQLAlchemy基本用法

SQLAlchemy是Python中一个通过 ORM 操作数据库的框架

安装

pip3 install sqlalchemy

引入

from sqlalchemy import create_engine, or_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import sessionmaker

连接数据库

举例 sqlite3

Base = declarative_base()
engine = create_engine('sqlite:///main.db', encoding="utf-8", echo=True)
DBSession = sessionmaker(bind=engine)
session = DBSession()

创建表对象

class Tag(Base):
    __tablename__ = 'tag'
    id = Column(Integer, primary_key=True)
    name = Column(String())
    num = Column(Integer)

新增

tag = Tag(name='Tony', num=18)  # 创建一个student对象
session.add(tag)  # 添加到session
session.commit()  # 提交到数据库

查询

查询所有

query = session.query(Tag).all()

条件查询

query = session.query(Tag).filter(Tag.num == 18).all()

条件查询返回第一行

query = session.query(Tag).filter(Tag.num == 18).first()

排序

query = session.query(Tag).filter(Tag.num == 18).order_by(Tag.num.desc()).all()

and查询

query = session.query(Tag).filter(Tag.num == 18, Tag.name == 'Tony').all()

or查询

query = session.query(Tag).filter(or_(Tag.num == 18, Tag.name == 'Tony')).all()

like查询

query = session.query(Tag).filter(Tag.name.like('%Tony%')).all()

in查询

query = session.query(Tag).filter(Tag.name.in_(['Tony', 'a'])).all()

编辑

session.query(Tag).filter(Tag.id == 1).update({'num': 33})
session.commit()

删除

session.query(Tag).filter(Tag.id == 1).delete()
session.commit()

统计

count = session.query(Tag).count()
print(count)
«
»