FLASK学习记录-Flask-SQLAlchemy
Flask-SQLAlchemy连接常用数据库
以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)
增删改查：
$ 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)
结果：
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>
SQLAlchemy常用数据类型：
SQLAlchemy常用方法：
报错解决：
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()，如建库建表实例
