python3 + flask + sqlalchemy +orm(1):链接mysql 数据库

1、pycharm中新建一个flask项目

2、按装flask、PyMySQL、flask-sqlalchemy

3、项目下面新建一个config.py 文件

DEBUG=True
#dialect+driver://root:[email protected]:3306/
DIALECT='mysql'
DRIVER='pymysql'
USERNAME='root'
PASSWORD='1q2w3e4r5t'
HOST='127.0.0.1'
PORT=3306
DATABASE='db_demo1'

SQLALCHEMY_DATABASE_URI="{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALECT,DRIVER,USERNAME,PASSWORD,HOST,PORT,DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS=False
print(SQLALCHEMY_DATABASE_URI)

4、app.py 文件

fromflaskimportFlask
importconfig

fromflask_sqlalchemyimportSQLAlchemy
app=Flask(__name__)
app.config.from_object(config)

db=SQLAlchemy(app)
db.create_all()



@app.route('/')
defindex():
return'index'



if__name__=='__main__':
app.run()

执行app.py 文件，结果如下，表面执行成功

fromflaskimportFlask
importconfig

fromflask_sqlalchemyimportSQLAlchemy
app=Flask(__name__)
app.config.from_object(config)

db=SQLAlchemy(app)
db.create_all()



@app.route('/')
defindex():
return'index'



if__name__=='__main__':
app.run()

FLASK_APP = test_sqlalchemy.py
FLASK_ENV = development
FLASK_DEBUG = 1
In folder /Users/autotest/PycharmProjects/python3_flask
/Users/autotest/PycharmProjects/python3_flask/venv/bin/python /Applications/PyCharm.app/Contents/helpers/pydev/pydevd.py --module --multiproc --qt-support=auto --client 127.0.0.1 --port 55365 --file flask run
pydev debugger: process 3089 is connecting

Connected to pydev debugger (build 182.4505.26)
* Serving Flask app "test_sqlalchemy.py" (lazy loading)
* Environment: development
* Debug mode: on
mysql+pymysql://root:[email protected]:3306/db_demo1?charset=utf8
* Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
* Restarting with stat
pydev debugger: process 3090 is connecting

mysql+pymysql://root:[email protected]:3306/db_demo1?charset=utf8
* Debugger is active!
* Debugger PIN: 216-502-598

python3 + flask + sqlalchemy +orm(2):数据库中添加表

往数据库中添加一张保存文章的表，表明为article，字段有id，title，content

同样一个配置文件：config.py

DEBUG=True
#dialect+driver://root:[email protected]:3306/
DIALECT='mysql'
DRIVER='pymysql'
USERNAME='root'
PASSWORD='1q2w3e4r5t'
HOST='127.0.0.1'
PORT=3306
DATABASE='db_demo1'

SQLALCHEMY_DATABASE_URI="{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALECT,DRIVER,USERNAME,PASSWORD,HOST,PORT,DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS=False
print(SQLALCHEMY_DATABASE_URI)

flask app 中新建一个class Blog，里面定义好id ，title ，content。代码执行到db.create_all()时，会自动在数据库中创建一个表，表明为blog

fromflaskimportFlask
importconfig

fromflask_sqlalchemyimportSQLAlchemy
fromsqlalchemy.ext.declarativeimportdeclarative_base

app=Flask(__name__)
app.config.from_object(config)

db=SQLAlchemy(app)
Base=declarative_base()


classBlog(db.Model):
__tablename__='blog'
id=db.Column(db.Integer,primary_key=True,autoincrement=True)
title=db.Column(db.String(100),nullable=False)
content=db.Column(db.Text,nullable=True)


db.create_all()


@app.route('/')
defindex():
return'index'


if__name__=='__main__':
app.run(debug=True)

启动flask app，数据库中查询表和表结构如下，有新增相应的表，说明新建表成功

数据库表中的数据增删改查

#新增
blog=Blog(,content="thisismyfirstblog")
db.session.add(blog)
db.session.commit()

#查询
#res=Blog.query.filter(Blog.title=="firstblog")[0]

res=Blog.query.filter(Blog.title=="firstblog").first()
print(res.title)
#修改
blog_edit=Blog.query.filter(Blog.title=="firstblog").first()
blog_edit.
db.session.commit()
#删除
blog_delete=Blog.query.filter(Blog.title=="firstblog").first()
db.session.delete(blog_delete)
db.session.commit()

完整代码

fromflaskimportFlask
importconfig

fromflask_sqlalchemyimportSQLAlchemy
fromsqlalchemy.ext.declarativeimportdeclarative_base

app=Flask(__name__)
app.config.from_object(config)

db=SQLAlchemy(app)
Base=declarative_base()


classBlog(db.Model):
__tablename__='blog'
id=db.Column(db.Integer,primary_key=True,autoincrement=True)
title=db.Column(db.String(100),nullable=False)
content=db.Column(db.Text,nullable=True)


db.create_all()


@app.route('/')
defindex():
#新增
blog=Blog(,content="thisismyfirstblog")
db.session.add(blog)
db.session.commit()

#查询
#res=Blog.query.filter(Blog.title=="firstblog")[0]

res=Blog.query.filter(Blog.title=="firstblog").first()
print(res.title)
#修改
blog_edit=Blog.query.filter(Blog.title=="firstblog").first()
blog_edit.
db.session.commit()
#删除
blog_delete=Blog.query.filter(Blog.title=="firstblog").first()
db.session.delete(blog_delete)
db.session.commit()


return'index'


if__name__=='__main__':
app.run(debug=True)

一篇文章有多个tag，一个tag也可以属于多篇文章，文章和tag存在多对多关系

config.py

DEBUG=True
#dialect+driver://root:[email protected]:3306/
DIALECT='mysql'
DRIVER='pymysql'
USERNAME='demo_user'
PASSWORD='demo_123'
HOST='172.16.10.6'
PORT=3306
DATABASE='db_demo1'

SQLALCHEMY_DATABASE_URI="{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALECT,DRIVER,USERNAME,PASSWORD,HOST,PORT,DATABASE)
SQLALCHEMY_TRACK_MODIFICATIONS=False
print(SQLALCHEMY_DATABASE_URI)

app.py

fromflaskimportFlask
importconfig

fromflask_sqlalchemyimportSQLAlchemy
fromsqlalchemy.ext.declarativeimportdeclarative_base

app=Flask(__name__)
app.config.from_object(config)

db=SQLAlchemy(app)
Base=declarative_base()

article_tag=db.Table('article_tag',
db.Column('article_id',db.Integer,db.ForeignKey("article.id"),primary_key=True),
db.Column('tag_id',db.Integer,db.ForeignKey("tag.id"),primary_key=True)
)

classArticle(db.Model):
__tablename__='article'
id=db.Column(db.Integer,primary_key=True,autoincrement=True)
title=db.Column(db.String(100),nullable=True)
tags=db.relationship('Tag',secondary=article_tag,backref=db.backref('articles'))


classTag(db.Model):
__tablename__='tag'
id=db.Column(db.Integer,primary_key=True,autoincrement=True)
name=db.Column(db.String(100),nullable=True)

db.create_all()


@app.route('/')
defindex():
article1=Article()
article2=Article()

tag1=Tag(name='1111')
tag2=Tag(name='2222')

article1.tags.append(tag1)
article1.tags.append(tag2)
article2.tags.append(tag1)
article2.tags.append(tag2)
db.session.add(article1)
db.session.add(article2)
db.session.add(tag1)
db.session.add(tag2)
db.session.commit()
return'index'


if__name__=='__main__':
app.run(debug=True)