FLask SQLAlchemy

FLask SQLAlchemy 实际上是 SQLAlchemy 在 Flask 框架中的封装。因此，FLask SQLAlchemy 支持 SQLAlchemy 中的所有语法，以下是 SQLAlchemy 中常用的一些语法（不限于 Flask SQLAlchemy，适用于所有使用 SQLAlchemy 的项目）：

创建表格
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
tablename = ‘users’

id = Column(Integer, primary_key=True)
name = Column(String(50), nullable=False)
email = Column(String(120), nullable=False, unique=True)
created_at = Column(DateTime, default=datetime.datetime.utcnow)

连接数据库
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(‘postgresql://user:password@localhost/mydatabase’)
Session = sessionmaker(bind=engine)
session = Session()
创建关联关系
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
tablename = ‘addresses’

id = Column(Integer, primary_key=True)
street = Column(String(120))
city = Column(String(50))
state = Column(String(50))
zipcode = Column(String(10))
user_id = Column(Integer, ForeignKey('users.id'))

user = relationship("User", back_populates="addresses")

添加数据
user = User(name=‘John Doe’, email=‘john.doe@example.com’)
session.add(user)
session.commit()

address = Address(street=‘123 Main St’, city=‘Springfield’, state=‘IL’, zipcode=‘12345’, user=user)
session.add(address)
session.commit()
查询数据

查询所有数据

users = session.query(User).all()

根据条件查询单个数据

user = session.query(User).filter_by(id=1).first()

连接查询，获取用户及其关联的地址信息

user_with_addresses = session.query(User).join(User.addresses).filter_by(id=1).first()
更新数据
user = session.query(User).filter_by(id=1).first()
user.name = ‘Jane Doe’
session.commit()
删除数据
user = session.query(User).filter_by(id=1).first()
session.delete(user)
session.commit()