Python 操作数据库

Flask 中ORM操作数据库

from flask import Flask
from apps.book import bp as book_bp # 导入蓝图
from apps.user import bp as user_bp
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
#注册蓝图
app.register_blueprint(book_bp)
app.register_blueprint(user_bp)

#数据库配置变量
HOSTNAME = 'localhost'
PORT = '3306'
DATABASE = 'FlaskDB'
USERNMAE='root'
PASSWORD = 'root'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNMAE,PASSWORD,HOSTNAME,PORT,DATABASE)
app.config['SQLALCHEMY_DATABASE_URI']=DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS']=True
db = SQLAlchemy(app)

#定义ORM模型
class User(db.Model):
    __tablename__ ='user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(200),nullable=False)

class Article(db.Model):
    __tablename__ ='article'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    title = db.Column(db.String(200),nullable=False)
    content = db.Column(db.Text,nullable=False)
    # 外键：
    # 1. 外键的数据类型一定要看所引用的字段的类型
    # 2. db.Foreignkey("表名.字段名")
    # 3. 外键是属于数据库层面的，不推荐在ORM中使用
    author_id = db.Column(db.Integer,db.ForeignKey('user.id'))

    # relationship:
    # 1. 第一个参数是模型的名字，必须要和模型的名字一致
    # 2. backref( back reference)：代表反向引用，代表对方访问我的时候的字段名称
    author = db.relationship("User",backref='articles')

db.drop_all()
db.create_all()

@app.route('/article')
def article_view():
    #1.添加数据
    # article = Article(title='西游记',content='xxx')
    # db.session.add(article)
    # #做一个提交操作
    # db.session.commit()
    # 2. 查询数据
    # artcile = Article.query.filter_by(id=1)[0]
    # print(artcile.title)
    # 3. 修改数据
    # artcile = Article.query.filter_by(id=1)[0]
    # artcile.content = 'yyy'
    # db.session.commit()
    # 4. 删除数据
    artcile = Article.query.filter_by(id=1).delete()
    db.session.commit()

    return '数据操作成功'

@app.route('/otm')
def one_to_many():
    article = Article(title='111',content='xxx')
    user = User(username='jojo')
    article.author = user
    db.session.add(article)
    db.session.commit()
    return 'one to many 数据操作成功'

@app.route('/')
def index():
    return '我是首页'

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