flask集成sqlalchemy

安装相关模块

  pip install flask-sqlalchemy

 

启动代码

from flask_sqlalchemy import SQLAlchemy
import datetime

app = Flask(__name__)
bootstrap = Bootstrap(app)
app.debug = True


app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///db/test.db'
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
db = SQLAlchemy(app)


#这个模块一定要在db变量声明之后 
#否则在model.user模块中将引用不到db变量

from model.user import Userinfo

from app import db

class Userinfo(db.Model):

    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(20),nullable=False,unique=True)
    passwd = db.Column(db.String(20),nullable=False)
    usertime=db.Column(db.DateTime,nullable=False)

    def __repr__(self):
        return '<User %r>' % self.name

 

实现初始化数据库数据

from model.user import Userinfo

@app.route('/')
def main():
    db.create_all()
    users=Userinfo.query.filter_by(name='admin').first()
    if not users:
        admin=Userinfo(name='admin',passwd='admin',usertime=datetime.datetime.now())
        db.session.add(admin)
        db.session.commit()
    return render_template('index.html', msg=m.loginmsg)

 

 

pycharm可视化打开sqlite

       首先打开database，一般在pycharm的右边侧栏中，也可以通过（View-->Tool Windows --> database）打开

       Data Source -> Sqlite(Xerial)

 

实现model的增删改查

# 查询所有用户
users_list = User.query.all()

# 查询用户名称为 test 的第一个用户, 并返回用户实例, 因为之前定义数据库的时候定义用户名称唯一, 所以数据库中用户名称为 test 的应该只有一个.
user = User.query.filter_by(username = 'test').first()
# or
user = User.query.filter(User.username == 'test').first()
# 查询用户 id, 用户 email, 用户 roles 类型

# 模糊查询, 查找用户名以abc 结尾的所有用户
users_list = User.query.filter(User.username.endsWith('c')).all()

#查询用户名不是 test 的第一个用户
user = User.query.filter(User.username != 'test').first()

    # 根据用户名, 判断用户是否已经存在
    if User.query.filter_by(username=req_data['username']) is not None:
        return {'msg': '用户已存在'}
        
    # 创建一个新用户
    user = User()
    user.username = req_data['username']
    user.email = user.username + "@4paradigm.com"
    user.password = req_data['password']
    for user_id in req_data['roles']:
        role = Role.query.filter(Role.id == user_id).first()
        user.roles.append(role)
    
    # 将新创建的用户添加到数据库会话中
    db.session.add(user)
    # 将数据库会话中的变动提交到数据库中, 记住, 如果不 commit, 数据库中是没有变化的.
    db.session.commit()

# 获取用户对象
user = User.qurey.filter_by(id = user_id).first()

# 修改用户信息
user.username = 'test_update'

# 提交数据库会话
db.session.commit()

# 获取用户对象
user = User.qurey.filter_by(id = user_id).first()

# 删除用户
db.session.delete(user)

# 提交数据库会话
db.session.commit()

 

日期和时间的计算处理

from datetime import date
from datetime import timedelta


class Userinfo(db.Model):  
   
  usertime=db.Column(db.Date,nullable=False,default=date.today())


date1 = user.usertime
date2 = date.today()
#date2 = date.today()+ timedelta(days=5)
# print(date1)
# print(date2)
# print((date2-date1).days,"**********")
tmpday=(date2-date1).days

from datetime import datetime
from datetime import timedelta

def get_date(days=N):
    return datetime.now() - timedelta(days=days)

 

flask实现单表数据序列化

from app import db
from datetime import date

class Userinfo(db.Model):

    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    name = db.Column(db.String(20),nullable=False,unique=True)
    passwd = db.Column(db.String(20),nullable=False)
    type =db.Column(db.Integer,nullable=False)
    usertime=db.Column(db.Date,nullable=False,default=date.today())

    def __repr__(self):
        return '<User %r>' % self.name

    def to_json(self):
        jsonstr = {
            'id': self.id,
            'name': self.name,
            'type': self.type,
            'usertime': self.usertime.strftime("%Y-%m-%d"),
            'aaa':"bbbbbb"
        }
        return jsonstr

@app.route('/users',methods=['GET'])
def get_users():
    users = Userinfo.query.all()
    res=[]
    for user in users:
        obj=user.to_json()
        res.append(obj)
    return jsonify({'users': res})

 

flask实现关联表序列化

artist_songs = db.Table('artist_songs',
      db.Column('song_id', db.Integer, db.ForeignKey('songs.id')),
      db.Column('artist_id', db.Integer, db.ForeignKey('artists.id'))
)


class songs(db.Model):

    __tablename__ = 'songs'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(255), nullable=True)
    lyrics = db.Column(db.Text(), nullable=True)
    created_at = db.Column(db.DateTime, nullable=False)
    artists = db.relationship('artists', secondary=artist_songs, backref=db.backref('artists'), lazy="select")
    lyric_copies = db.relationship('lyric_copies', backref='songs', lazy='select')

    def jsonstr(self):
        artist = []
        if self.artists is not None and len(self.artists) != 0:
            for art in self.artists:
                artist.append(art.name)

        lyricarr = []
        if self.lyric_copies is not None and len(self.lyric_copies) != 0:
            for lyr in self.lyric_copies:
                lyricarr.append(lyr.jsonstr())

        jsondata = {
            'ID': self.id,
            'title': self.title,
            'artist': artist,
            'lyric': self.lyrics,
            'copyrights': {
                'lyric': lyricarr
            }
        }
        return jsondata


class artists(db.Model):
    __tablename__ = 'artists'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(255), nullable=True)
    gender = db.Column(db.Integer, nullable=True)
    location = db.Column(db.String(255), nullable=True)
    created_at = db.Column(db.DateTime, nullable=False)
    updated_at = db.Column(db.DateTime, nullable=False)