首页 > 数据库 >FLASK学习记录-Flask-SQLAichemy

FLASK学习记录-Flask-SQLAichemy

时间：2024-04-02 09:46:59浏览次数：15  
标签：02 sqlalchemy 04 FLASK 09 2024 SQLAichemy user Flask

Flask-SQLAichemy连接常用数据库

 以sqlite3为例：

建库建表：

#!/usr/bin/python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_,or_
import sqlite3

app=Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////usr/dog/flask_web/flask-test2/visitors.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)
app.app_context().push()

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)
    age = db.Column(db.Integer, default=10, index=True)

if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    app.run(host='0.0.0.0',port=8000,debug=True)
View Code

 增删改查：

$ cat proj_createUser.py
#!/usr/bin/python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_,or_
import sqlite3

app=Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////usr/dog/flask_web/flask-test2/visitors.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)
app.app_context().push()

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20), unique=True)
    age = db.Column(db.Integer, default=10, index=True)

@app.route("/insert")
def insert():
    user1=User(name='aaa',age=7)
    user2=User(name='bbb',age=25)
    user3=User(name='ccc',age=26)
    db.session.add(user1)
    db.session.add(user2)
    db.session.add(user3)
    db.session.commit()
    return "insert successful"

@app.route("/filter")
def filter():
    #1. age==25
    filter1=User.query.filter(User.age==25).all()
    #2. age==25
    filter2=User.query.filter_by(age=25).all()
    #3. and_
    filter3=User.query.filter(and_(User.age>25 ,User.age<25)).all()
    #4. or_
    filter4 = User.query.filter(or_(User.name=='aaa',User.name=='bbb')).all()
    #5. in_
    filter5= User.query.filter(User.name.in_(['ccc','aaa'])).all()
    #6. in_
    filter6 = User.query.filter(~User.name.in_(['bbb', 'ccc'])).all()
    #7. is_
    filter7 = User.query.filter(User.name.is_(None)).all()
    #8. isnot
    filter8 = User.query.filter(User.name.isnot(None)).all()
    #9. like, contains()
    filter9 = User.query.filter(User.name.like("%b%")).all()
    filter10 = User.query.filter(User.name.contains("b")).all()
    print(filter1,filter2,filter3,filter4,filter5,filter6,filter7,filter8,filter9,filter10)
    return "filter query"

@app.route("/update")
def update():
    update1=User.query.get(1)
    update1.age=60
    db.session.commit()
    updateA=User.query.filter(User.name.like('a%')).update({User.age:User.age+1},synchronize_session=False)
    db.session.commit()
    print(updateA)
    return "update Action"

@app.route("/delete")
def delete():
    user=User.query.get(1)
    db.session.delete(user)
    db.session.commit()
    deleteA = User.query.filter(User.name.like('b%')).delete(synchronize_session=False)
    db.session.commit()
    print(deleteA)
    return "delete Action"

if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    app.run(host='0.0.0.0',port=8000,debug=True)
View Code

结果：

2024-04-02 09:27:07,082 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:27:07,082 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user")
2024-04-02 09:27:07,082 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-02 09:27:07,083 INFO sqlalchemy.engine.Engine
DROP TABLE user
2024-04-02 09:27:07,083 INFO sqlalchemy.engine.Engine [no key 0.00011s] ()
2024-04-02 09:27:07,086 INFO sqlalchemy.engine.Engine COMMIT
2024-04-02 09:27:07,086 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:27:07,087 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("user")
2024-04-02 09:27:07,087 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-02 09:27:07,087 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("user")
2024-04-02 09:27:07,087 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-04-02 09:27:07,088 INFO sqlalchemy.engine.Engine
CREATE TABLE user (
        id INTEGER NOT NULL,
        name VARCHAR(20),
        age INTEGER,
        PRIMARY KEY (id),
        UNIQUE (name)
)


2024-04-02 09:27:07,088 INFO sqlalchemy.engine.Engine [no key 0.00013s] ()
2024-04-02 09:27:07,090 INFO sqlalchemy.engine.Engine CREATE INDEX ix_user_age ON user (age)
2024-04-02 09:27:07,090 INFO sqlalchemy.engine.Engine [no key 0.00010s] ()
2024-04-02 09:27:07,092 INFO sqlalchemy.engine.Engine COMMIT
 * Debugger is active!
 * Debugger PIN: 342-232-362
2024-04-02 09:27:11,710 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:27:11,712 INFO sqlalchemy.engine.Engine INSERT INTO user (name, age) VALUES (?, ?)
2024-04-02 09:27:11,712 INFO sqlalchemy.engine.Engine [generated in 0.00083s] ('aaa', 7)
2024-04-02 09:27:11,713 INFO sqlalchemy.engine.Engine INSERT INTO user (name, age) VALUES (?, ?)
2024-04-02 09:27:11,713 INFO sqlalchemy.engine.Engine [cached since 0.001441s ago] ('bbb', 25)
2024-04-02 09:27:11,713 INFO sqlalchemy.engine.Engine INSERT INTO user (name, age) VALUES (?, ?)
2024-04-02 09:27:11,713 INFO sqlalchemy.engine.Engine [cached since 0.001648s ago] ('ccc', 26)
2024-04-02 09:27:11,713 INFO sqlalchemy.engine.Engine COMMIT
119.123.40.41 - - [02/Apr/2024 09:27:11] "GET /insert HTTP/1.1" 200 -
2024-04-02 09:28:29,530 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:28:29,532 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.age = ?
2024-04-02 09:28:29,532 INFO sqlalchemy.engine.Engine [generated in 0.00021s] (25,)
2024-04-02 09:28:29,533 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.age = ?
2024-04-02 09:28:29,533 INFO sqlalchemy.engine.Engine [cached since 0.001201s ago] (25,)
2024-04-02 09:28:29,534 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.age > ? AND user.age < ?
2024-04-02 09:28:29,534 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (25, 25)
2024-04-02 09:28:29,535 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.name = ? OR user.name = ?
2024-04-02 09:28:29,535 INFO sqlalchemy.engine.Engine [generated in 0.00014s] ('aaa', 'bbb')
2024-04-02 09:28:29,536 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.name IN (?, ?)
2024-04-02 09:28:29,537 INFO sqlalchemy.engine.Engine [generated in 0.00017s] ('ccc', 'aaa')
2024-04-02 09:28:29,538 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE (user.name NOT IN (?, ?))
2024-04-02 09:28:29,538 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ('bbb', 'ccc')
2024-04-02 09:28:29,539 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.name IS NULL
2024-04-02 09:28:29,539 INFO sqlalchemy.engine.Engine [generated in 0.00012s] ()
2024-04-02 09:28:29,539 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.name IS NOT NULL
2024-04-02 09:28:29,540 INFO sqlalchemy.engine.Engine [generated in 0.00012s] ()
2024-04-02 09:28:29,540 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.name LIKE ?
2024-04-02 09:28:29,541 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ('%b%',)
2024-04-02 09:28:29,542 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE (user.name LIKE '%' || ? || '%')
2024-04-02 09:28:29,542 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ('b',)
[<User 2>] [<User 2>] [] [<User 1>, <User 2>] [<User 1>, <User 3>] [<User 1>] [] [<User 1>, <User 2>, <User 3>] [<User 2>] [<User 2>]
2024-04-02 09:28:29,542 INFO sqlalchemy.engine.Engine ROLLBACK
119.123.40.41 - - [02/Apr/2024 09:28:29] "GET /filter HTTP/1.1" 200 -
/usr/dog/flask_web/flask-test2/proj_createUser.py:57: LegacyAPIWarning: The Query.get() method is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. The method is now available as Session.get() (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  update1=User.query.get(1)
2024-04-02 09:29:15,804 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:29:15,805 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.id = ?
2024-04-02 09:29:15,805 INFO sqlalchemy.engine.Engine [generated in 0.00018s] (1,)
2024-04-02 09:29:15,807 INFO sqlalchemy.engine.Engine UPDATE user SET age=? WHERE user.id = ?
2024-04-02 09:29:15,807 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (60, 1)
2024-04-02 09:29:15,807 INFO sqlalchemy.engine.Engine COMMIT
2024-04-02 09:29:15,810 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:29:15,811 INFO sqlalchemy.engine.Engine UPDATE user SET age=(user.age + ?) WHERE user.name LIKE ?
2024-04-02 09:29:15,811 INFO sqlalchemy.engine.Engine [generated in 0.00015s] (1, 'a%')
2024-04-02 09:29:15,812 INFO sqlalchemy.engine.Engine COMMIT
1
119.123.40.41 - - [02/Apr/2024 09:29:15] "GET /update HTTP/1.1" 200 -
/usr/dog/flask_web/flask-test2/proj_createUser.py:67: LegacyAPIWarning: The Query.get() method is considered legacy as of the 1.x series of SQLAlchemy and becomes a legacy construct in 2.0. The method is now available as Session.get() (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
  user=User.query.get(1)
2024-04-02 09:29:54,449 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:29:54,450 INFO sqlalchemy.engine.Engine SELECT user.id AS user_id, user.name AS user_name, user.age AS user_age
FROM user
WHERE user.id = ?
2024-04-02 09:29:54,450 INFO sqlalchemy.engine.Engine [cached since 38.64s ago] (1,)
2024-04-02 09:29:54,451 INFO sqlalchemy.engine.Engine DELETE FROM user WHERE user.id = ?
2024-04-02 09:29:54,451 INFO sqlalchemy.engine.Engine [generated in 0.00019s] (1,)
2024-04-02 09:29:54,452 INFO sqlalchemy.engine.Engine COMMIT
2024-04-02 09:29:54,454 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-02 09:29:54,455 INFO sqlalchemy.engine.Engine DELETE FROM user WHERE user.name LIKE ?
2024-04-02 09:29:54,455 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ('b%',)
2024-04-02 09:29:54,455 INFO sqlalchemy.engine.Engine COMMIT
1
119.123.40.41 - - [02/Apr/2024 09:29:54] "GET /delete HTTP/1.1" 200 -

########sqlite3部分的结果##############
$ sqlite3 visitors.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> .tables
user
sqlite> select * from user;
sqlite> select * from user;
1|aaa|7
2|bbb|25
3|ccc|26
sqlite> select * from user;
1|aaa|61
2|bbb|25
3|ccc|26
sqlite> select * from user;
3|ccc|26
sqlite>
View Code

 

 

报错解决：

RuntimeError: Working outside of application context.

This typically means that you attempted to use functionality that needed
the current application. To solve this, set up an application context
with app.app_context(). See the documentation for more information.

增加：app.app_context().push()，如建库建表实例

标签：02,sqlalchemy,04,FLASK,09,2024,SQLAichemy,user,Flask
From： https://www.cnblogs.com/jackbond/p/18109818

相关文章

  • flask模板介绍
    flask模板render_template加载html文件，默认文件路径在templates下fromflaskimportFlask,render_template,requestapp=Flask(__name__)@app.route('/',methods=['GET'])defindex():my_str='Hellobenben'my_int=request.args.get('......
  • FLASK学习记录-sqlite3基本操作
    sqltie3是内置模块，数据库操作，以及表的增删改查参考https://www.runoob.com/sqlite/sqlite-python.html实例创建数据库$sqlite3test.dbSQLiteversion3.34.12021-01-2014:10:07Enter".help"forusagehints.sqlite>.databasesmain:/usr/dog/flask_web/flask-test2......
  • FLASK学习记录-Flask表单（一）
     Form表单是一个包含表单元素的区域，允许用户在表单中输入各种信息元素，是前后端交互的重要标签，Form标签常用属性如下：  常用表单控件： 实例：app.pyfromflaskimportFlask,render_template,requestapp=Flask(__name__)@app.route('/',methods=["get","post"]......
  • python+django在线政务便民服务系统flask
     随着时代在飞速进步，每个行业都在努力发展现在先进技术，通过这些先进的技术来提高自己的水平和优势，在线政务服务中心管理当然不能排除在外。在线政务服务中心管理系统是在实际应用和软件工程的开发原理之上，运用python语言以及vue框架进行开发。首先要进行需求分析，分析出在线政......
  • 【python】深入探讨flask是如何预防CSRF攻击的
    ✨✨欢迎大家来到景天科技苑✨✨......
  • FLASK学习记录-宏、模板继承
    宏{%macroname%}{%endmacro%}app.pyfromflaskimportFlask,render_templateapp=Flask(__name__)@app.route('/')defindex1():returnrender_template("macro1.html")@app.route("/")defindex2():returnrend......
  • FLASK学习记录-Jinja2模块引擎
    Flask中引入了jinja2模板引擎，可以显示动态数据、数据过滤、语句控制、模板继承和引用等。实战实例app.pyfromflaskimportFlask,render_templateapp=Flask(__name__)@app.route('/')defindex():LibraryName="NationalLibrary"visitor={"name":"J......
  • Flask python 开发篇：模型（model）Flask-SQLAlchemy的使用
    Flask-SQLAlchemy实现模型一、为什么使用模型？二、Flask-SQLAlchemy的引入三、使用Flask-SQLAlchemy构建模型文件3.1、安装扩展3.2、配置3.3、实战使用3.4、与蓝图相结合使用一、为什么使用模型？上一篇分享了蓝图的使用，也说蓝图相当于了php中控制器+路由的使用，那根......
  • Python Flask-Mail实现邮件发送
    使用falsk-mail发送邮件一、邮件发送的扩展二、根据文档，总结发送邮件的流程三、实现邮件发送功能3.1、安装扩展3.2、配置3.3、发送邮件（创建mail、message实例并发送）3.4、发送带附件的邮件3.5、批量发送邮件3.6、异步发送邮件四、写在最后一、邮件发送的扩展关于......
  • FLASK学习记录-PIPENV虚拟环境搭建
     $pipinstallflask-ihttps://pypi.tuna.tsinghua.edu.cn/simpleLookinginindexes:https://pypi.tuna.tsinghua.edu.cn/simpleCollectingflaskDownloadinghttps://pypi.tuna.tsinghua.edu.cn/packages/93/a6/aa98bfe0eb9b8b15d36cdfd03c8ca86a03968a87f27ce22......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99