Viewing: raw.githubusercontent.com

[ Web Proxy ]

Viewing: https://raw.githubusercontent.com/We2one/Notes/master/python-basic-notes/python-days/Day_34.md

### Flak #### 1.Flask ##### 1.1 ORM + ORM (Object Relational Mapping ) : ,. + : ##### 1.2 Flask-SQLAlchemy 1. Flask-SQLAlchemy : `pip install flask-sqlalchemy` 2. 1. SQLite (python) 1. dbSettings.py ```python import os class Config: # # __file__ : BASE_DIR = os.path.abspath(os.path.dirname(__file__)) # URL SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(BASE_DIR, "my_db.sqlite") # - sql # app.config['SQLALCHEMY_CEHO'] = True # SQLALCHEMY_TRACK_MODIFICATIONS = True ``` 2. app.py SQLite ````python from flask import Flask from flask_sqlalchemy import SQLAlchemy from db_setting import Config app = Flask(__name__) # app app.config.from_object(Config) # db = SQLAlchemy(app) class Student(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(32)) # db.create_all() if __name__ == '__main__': app.run() ```` 2. MySQL 1. PyMySQL : `pip install pymysql` 2. `pymysql.install_as_MySQLdb()` #2.x MySQLdb ,3.x,MySQLdb 3. : `CREATE DATABASE db_name charset=utf-8` 4. Flask-SQLAlchemy + SQLALCHEMY_DATABASE_URI MySQL + : `SQLALCHEMY_DATABASE_URI = 'mysql://username:password@server/db_name'` | | | | -------- | ---------- | | mysql | | | username | | | password | | | server | IP | | db | | + `SQLALCHEMY_TRACK_MODIFICATIONS=True` : True , 5. : `db.create_all()` 1. `.create_all()` 2. : `.create_all()`,,`.create_all()` ````python from flask import Flask from flask import render_template from flask_sqlalchemy import SQLAlchemy import pymysql app = Flask(__name__) pymysql.install_as_MySQLdb() # 2.x MySQLdb ,3.x,MySQLdb # app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:root@127.0.0.1:3306/test' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) class Student(db.Model): __tablename__ = 'Student' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(32)) # db.create_all() # db.drop_all() ```` ##### 1.3 + SQLALCHEMY + | | | | -------- | ---------------------- | | Integer | | | Float | | | Date | () | | DateTime | () | | Text | | | String | | + | | | | ----------- | ---------- | | primary_key | | | unique | | | index | | | nullable | | | default | | + + : + : + ```python class Person(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(32), unique=True) password = db.Column(db.String(32)) nickname = db.Column(db.String(32), default="") age = db.Column(db.Integer, default=18) gender = db.Column(db.String(16), default="") score = db.Column(db.Float, nullable=True) ``` ##### 1.4 1. app.py : , ```python from flask import Flask from flask_sqlalchemy import SQLAlchemy from db_setting import Config app = Flask(__name__) app.config.from_object(Config) db = SQLAlchemy(app) ``` 2. main.py : ```python from __init__ import app if __name__ == '__main__': app.run() ``` 3. models.py : () ````python from __init__ import db class Student(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(32)) # db.create_all() ```` 4. views.py : () ```python from __init__ import app from flask import render_template @app.route('/') def hello_world(): return 'Hello World!' ``` ##### 1.5 Flask-SQLAlchemy 1. 1. 1. ```python person = Person( username = "", password = "123456", nickname = "", age = 12, score = 45.5 ) ``` 2. ```python person1 = Person( username = "", password = "123456", nickname = "", age = 12, score = 45.5 ) person2 = Person( username = "", password = "123456", nickname = "", age = 22, score = 45.5 ) ``` 2. | | | | | ------------------------------------------------------------ | ------------------------------------------------------------ | ------------------------------------------------------------ | | `.query.all()` | ,, |
`person_list = Person.query.all()` | | `.query.get()` | id ,, None | id 255
``person_list = Person.query.get(255)`` | | `.query.filter()`

`.query.filter_by()` | | 21
`persion_list=Person.query.filter(Person.age>21,Person.gender=="")`

`persion_list=Person.query.filter_by(gender=="")` | | `like()` |
*%* : 0
*_* : | 21
`persion_list=Person.query.filter(Person.age>21,Person.gender=="",Person.nickname.like('%'))` | | `.query.filter().limit()`
`.query.filter_by().limit()` | | 1021
`persion_list=Person.query.filter(Person.age>21,Person.gender=="").limit(10)` | | `.query.filter().limit().offset()`
`.query.filter_by().limit().offset()` | , | ,1021
`persion_list=Person.query.filter(Person.age>21,Person.gender=="").limit(10).offset(20)` | | `.query.filter().limit().order_by()`
`.query.filter_by().limit().order_by()` |
: `.order_by(db_name.)`
: `.order_by(db_name..desc())` | ,,
`persion_list=Person.query.filter(Person.gender=="",Person.nickname.like('%')).order_by(Person.age.desc())` | | `max()``min()``count()``sum``avg` | | func `from sqlalchemy import func`

`result = db.session.query(func.count(Person.id)).filter(Person.gender=="",Person.nickname.like('%')).all()` | | `group_by(db_name.)` | | func `from sqlalchemy import func`

`result = db.session.query(Person.gender, func.count(Persion.id)).group_by(Persion.gender).all()` | | `and_()``or_()``not_()` | | `from sqlalchemy import and_,or_,not_`

`person_list = Person.query.filter(or_(Person.gender=="",Person.nickname.like("%")))` | 3. : `.session.commit()` ```python # id 5 person = Person.query.get(5) person.gender = "" db.session.commit() ``` 4. ```python # id 25 person = Person.query.get(25) db.session.delete(person) db.session.commit() ``` 2. 1. id , 2. 1. ```python def update(self): db.session.add() db.session.commit() ``` 2. ```python def update(self): db.session.commit() ``` 3. ```python def update(self): db.session.delete() db.session.commit() ``` 4. ##### 1.6 Flask-SQLAlchemy 1. , (foreign-key) 1. : 2. ```python ## class Position(Base): __tablename__ = "position" p_name = db.Column(db.String(32)) p_level = db.Column(db.Integer) p_person = db.relationship( "Person" # backref="our_position" # ,person ) ## class Person(Base): # id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(32), unique=True) password = db.Column(db.String(32)) nickname = db.Column(db.String(32), default="") age = db.Column(db.Integer, default=18) gender = db.Column(db.String(16), default=18) score = db.Column(db.Float, nullable=True) # p_position , position id p_position = db.Column(db.Integer, db.ForeignKey("position.id")) ``` + `__tablename__=""` : + `p_position = db.Column(db.Integer, db.ForeignKey("position.id"))` : p_position , position id + `p_person = db.relationship("Person", backref="our_position")` : ,backref + relationship : ,, + : `uselist=False` 3. 1. ```python # 1. , pos = Position(p_name="", p_level=1) pos.save() # per = Person(username='', password="123456", nickname="", p_position=1) per.save() # position = Position.query.filter_by(p_name="").first() per = Person(username='', password="123456", nickname="", p_position=position_id) per.save() ``` 2. relationship (relationship ) ```python # 2. per = Person() per.username = "" per.password="12212" per.nickname = "" # per.save() # , pos = Position.query.filter(Position.p_name="").first() pos.p_person = [per] pos.save() ``` 3. relationship (relationship ) ```python # 3. per = Person() per.username = "" per.password="12212" per.nickname = "" # backref , # , per.our_position = Position.query.filter_by(p_name="").first() per.save() ``` 4. ```python # id = 1 ## person = Person.query.get(1) pos = person.our_position.p_name print(pos) # ## pos = Position.query.get(1) person_list = pos.p_person print(person_list) ``` 2. 1. OA 1. : 2. : 3. : () 4. : () 5. : , 2. 1. () ```python # pos_per = db.Table( 'pos_per', # db.Column('pos_id', db.Integer, db.ForeignKey("position_id")), db.Column('per_id', db.Integer, db.ForeignKey('permission.id')), ) ``` 2. () ```python # class Position(Base): p_name = db.Column(db.String(32)) p_level = db.Column(db.Integer) p_person = db.relationship( "Person", backref='our_position', ) p_permission = db.relationship( "Permission", secondary=pos_per, backref="p_position", ) ``` 3. () ```python # class Person(Base): username = db.Column(db.String(32), unique=True) password = db.Column(db.String(64)) nickname = db.Column(db.String(32)) age = db.Column(db.Integer) gender = db.Column(db.String(16)) score = db.Column(db.Float) p_position = db.Column(db.Integer, db.ForeignKey("position.id")) ``` 4. ```python # class Permission(Base): per_name = db.Column(db.String(256)) ``` 3. 1. ```python # p_list = [ "", "", "", "", ] for per in p_list: p = Permission() p.per_name = per p.save() # pos1 = Position(p_name="", p_level=1) pos2 = Position(p_name="", p_level=2) pos1.save() pos2.save() ``` 2. ```python # Position -> Permission # pos = Position.query.filter(Position.p_name=="").first() per = Permission.query.filter(Permission.per_name=="").first() pos.p_permission = [per] pos.save() ``` 3. ```python # , per = Permission.query.filter(Permission.per_name=="").first() pos = Position.query.filter(Position.p_name=="").first() per.p_position = [pos] per.save() ``` 4. ```python # pos = Position.query.filter(Position.p_name=="").first() per = pos.p_permission print(per) # per = Permission.query.filter(Permission.per_name=="").first() pos = per.p_position print(pos) ```

Web Proxy Viewer | New URL | Original Page