6-----Flask-SQLAlchemy 学习与数据库版本管理

SQLAlchemy   https://dormousehole.readthedocs.io/en/latest/patterns/sqlalchemy.html?highlight=sql

 

 

pip install Flask-SQLAlchemy 
pip install SQLAlchemy 


[root@localhost project]# cat hello.py 
from flask import Flask,render_template
from flask_sqlalchemy import SQLAlchemy
import json

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@127.0.0.1:3306/devops'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

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


###创建数据：
In [1]: from hello import db                                                                                                                                                               
/opt/py3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '

In [2]: db.create_all()    

##插入数据：
In [1]: from hello import User                                                                                                                             

In [4]: from hello import db      

In [2]: admin=User('admin','admin@example.com')                                                                                                            

In [3]: guest=User('guest','guest@example.com')                                                                                                            
                                                                                                          
In [5]: db.session.add(admin)                                                                                                                              

In [6]: db.session.add(guest)                                                                                                                              

In [7]: db.session.commit()                                                                                                                                

##插入多个数据：
In [11]: db.session.add_all([xiaoluo,test])                                                                                                                

In [12]: db.session.commit()                                                                                                                               

In [13]: User.query.all()                                                                                                                                  
Out[13]: [<User 'admin'>, <User 'guest'>, <User 'xiaoluo'>, <User 'test'>]


##查询：
In [8]: User.query.all()                                                                                                                                   
Out[8]: [<User 'admin'>, <User 'guest'>]

In [14]: data=User.query.all()                                                                                                                             
In [15]: data                                                                                                                                              
Out[15]: [<User 'admin'>, <User 'guest'>, <User 'xiaoluo'>, <User 'test'>]

#条件判断查询：
In [18]: data=User.query.filter_by(id=3).first()                                                                                                           
In [19]: data                                                                                                                                              
Out[19]: <User 'xiaoluo'>

##查询返回结果：
In [19]: data                                                                                                                                              
Out[19]: <User 'xiaoluo'>
In [20]: data.username                                                                                                                                     
Out[20]: 'xiaoluo'
In [21]: data.email                                                                                                                                        
Out[21]: 'xiaoluo@exampl.com'

####

In [25]: data=User.query.all()                                                                                                                             

In [26]: data                                                                                                                                              
Out[26]: [<User 'admin'>, <User 'guest'>, <User 'xiaoluo'>, <User 'test'>]

In [27]: for i in data: 
    ...:     print(i) 
    ...:                                                                                                                                                   
<User 'admin'>
<User 'guest'>
<User 'xiaoluo'>
<User 'test'>
In [29]: for i in data: 
    ...:     print(i.username)                                                                                                                                                    
admin
guest
xiaoluo
test

###更新某个数据：
                                                                                                  
In [38]: data_name=User.query.filter_by(id=3).first()                                                                                                      
In [39]: data_name.username                                                                                                                                
Out[39]: 'xiaoluo‘
In [40]: data_name.username='xiao'                                                                                                                         
In [41]: db.session.add(data_name)                                                                                                                         
In [42]: db.session.commit()  

##删除：
In [44]: data_name=User.query.filter_by(id=3).first()                                                                                                      
In [45]: db.session.delete(data_name)                                                                                                                      
In [46]: db.session.commit()                                                                                                                               
In [47]: User.query.all()                                                                                                                                  
Out[47]: [<User 'admin'>, <User 'guest'>, <User 'test'>]

###外键索引之间查询：

n [3]: from hello import *                                                                                                                                

In [4]: py = Category('Python')                                                                                                                            

In [5]: p = Post('Hello Python!', 'Python is pretty cool', py)                                                                                             

In [6]: db.session.add(py)                                                                                                                                 

In [7]: db.session.add(p)                                                                                                                                  

In [8]: db.session.commit()                                                                                                                                

In [11]: py.posts                                                                                                                                          
Out[11]: <sqlalchemy.orm.dynamic.AppenderBaseQuery at 0x7fbe0f4e5898>

In [12]: py.posts.all()                                                                                                                                    
Out[12]: [<Post 'Hello Python!'>]

In [13]: py                                                                                                                                                
Out[13]: <Category 'Python'>

In [14]: py.name                                                                                                                                           
Out[14]: 'Python'

In [15]: py.posts.all()                                                                                                                                    
Out[15]: [<Post 'Hello Python!'>]

In [18]: for i in py.posts.all(): 
    ...:     print(i.body)                                                                                                                                                  
Python is pretty cool


In [19]: p                                                                                                                                                 
Out[19]: <Post 'Hello Python!'>
In [20]: p.body                                                                                                                                            
Out[20]: 'Python is pretty cool'



In [19]: p                                                                                                                                                 
Out[19]: <Post 'Hello Python!'>

In [20]: p.body                                                                                                                                            
Out[20]: 'Python is pretty cool'

In [21]: p.category                                                                                                                                        
Out[21]: <Category 'Python'>

In [22]: p.category.name                                                                                                                                   
Out[22]: 'Python'

有关联关系的时候，建立外键，等索引可以帮助我们快速的进行管理查询搜索。


一对一关系：

一个丈夫、一个妻子

一对多：
一本书、多个作者

多对多：
多个作者，多本书，，，一个作者可能写了多本书，，或者多本书也有多个作者。




###数据库迁移管理：
安装两个模块：
 1093  pip install flask-migrate
 1100  pip install flask_script
########

(py3) [root@localhost project]# cat hello.py 
from flask import Flask,render_template
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@127.0.0.1:3306/devops'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

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


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    pub_date = db.Column(db.DateTime)

    category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
    category = db.relationship('Category',
        backref=db.backref('posts', lazy='dynamic'))

    def __init__(self, title, body, category, pub_date=None):
        self.title = title
        self.body = body
        if pub_date is None:
            pub_date = datetime.utcnow()
        self.pub_date = pub_date
        self.category = category

    def __repr__(self):
        return '<Post %r>' % self.title


class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    def __init__(self, name):
        self.name = name

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






@app.route('/')
def index():
    return json.dumps(result)

@app.route('/user/<username>')
def show_user_profile(username):
    # show the user profile for that user
    return 'User %s' % username

@app.route('/post/<int:post_id>')
def show_post(post_id):
    # show the post with the given id, the id is an integer
    return 'Post %d' % post_id


@app.route('/hello/<name>')
def hello(name=None):
    ##查询数据库返回一个json数据
    data = [{'a':name}]
    return json.dumps(data)


if __name__ == '__main__':
    app.run(host='0.0.0.0',debug=True)


(py3) [root@localhost project]# cat manager.py 
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
#from hello import app,db
from hello import *
migrate = Migrate(app, db)

manager = Manager(app)
manager.add_command('db', MigrateCommand)

if __name__ == '__main__':
    manager.run()


##使用方法：
(py3) [root@localhost project]# python manager.py db init  ##初始化版本库
(py3) [root@localhost project]# python manager.py db migrate   ##更新版本历史
(py3) [root@localhost project]# python manager.py db upgrade   更新数据库字段
/opt/py3/lib/python3.6/site-packages/flask_sqlalchemy/__init__.py:835: FSADeprecationWarning: SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and will be disabled by default in the future.  Set it to True or False to suppress this warning.
  'SQLALCHEMY_TRACK_MODIFICATIONS adds significant overhead and '
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 929da0c8c92d, empty message
(py3) [root@localhost project]#