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 '
class UserModel(db.Model):
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
name = db.Column(db.String(200))
# 初始化
def __init__(self, name):
self.name = name
# 打印字符串
def __repr__(self):
return '<User %r>' % self.name
>>> db.drop_all()
>>> db.create_all()
>>> add1()
>>> add2()
>>> user = UserModel.query.filter(UserModel.name.contains("t")).all()
>>> print(user)
[<User 't1'>]
>>> user = UserModel.query.filter_by(name="t1").all()
>>> user
[<User 't1'>]
>>> user = UserModel.query.get(1)
>>> user
Out[7]: <User 't1'>
>>> user = UserModel.query.first()
>>> user
Out[7]: <User 't1'>
>>> user = UserModel.query.first()
>>> user.name = "ttt"
>>> db.session.commit()
>>> user = UserModel.query.first()
>>> db.session.delete(user)
>>> db.session.commit()
class Article(db.Model):
__tablename__ = "article"
db.Column(name="xxx")
import enum
class TagEnum(enum.Enum):
python = "python"
flask = "flask"
django = "django"
class Article(Base):
__tablename__ = "article"
id = Column(Integer,primary_key=True,autoincrement=True)
# tag = Column(Enum('python','django','flask'))
tag = Column(Enum(TagEnum))
# article = Article(tag='1')
# article = Article(tag='python')
article = Article(tag=TagEnum.python)
class Article(Base):
__tablename__ = "article"
id = Column(Integer,primary_key=True,autoincrement=True)
create_time = Column(Date)
from datetime import date
article = Article(create_time=date(year=2017,month=10,day=8))
class Article(Base):
__tablename__ = "article"
id = Column(Integer,primary_key=True,autoincrement=True)
create_time = Column(DateTime)
from datetime import datetime
article = Article(create_time=datetime(year=2017,month=11,day=11,hour=11,minute=11,second=11,microsecond=11))
class Article(Base):
__tablename__ = "article"
id = Column(Integer,primary_key=True,autoincrement=True)
create_time = Column(Time)
from datetime import time
article = Article(create_time=time(hour=11,minute=11,second=11,microsecond=11))
# article = Article(create_time=datetime.now())
class ArticleModel(db.Model):
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),name="标题",nullable=False)
phone = db.Column(db.String(11),unique=True)
update_time = db.Column(db.DateTime, onupdate=datetime.now, default=datetime.now)
# 初始化
def __init__(self, title,phone):
self.phone = phone
self.title = title
>>> from app import db
>>> from app import ArticleModel
>>> db.drop_all()
>>> db.create_all()
>>> article = ArticleModel(title="123",phone="1234567890")
>>> db.session.add(article)
>>> db.session.commit()
class ArticleModel(db.Model):
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),name="标题",nullable=False)
update_time = db.Column(db.DateTime, onupdate=datetime.now, default=datetime.now)
price = db.Column(db.Float,nullable=False)
# 初始化
def __init__(self, title,price):
self.price = price
self.title = title
def add():
for x in range(6):
article = ArticleModel(title='title{}'.format(x),price=random.randint(50,100))
db.session.add(article)
db.session.commit()
>>> from app import *
>>> add()
# 聚合函数
count = db.session.query(db.func.count(Article.id)).first()
print(count)
avg = db.session.query(db.func.avg(Article.price)).first()
print(avg)
max = db.session.query(db.func.max(Article.price)).first()
print(max)
min = db.session.query(db.func.min(Article.price)).first()
print(min)
sum = db.session.query(db.func.sum(Article.price)).first()
print(sum)
print(db.func.sum(Article.title))
>>> from app import *
>>> func()
class Article(db.Model):
__tablename__ = "article"
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),nullable=False)
price = db.Column(db.Float,nullable=False)
content = db.Column(db.Text)
def __repr__(self):
return "<Article(title:%s)>" % self.title
>>> for x in range(6):
...: article = Article(title='{}'.format(x))
...: db.session.add(article)
...: db.session.commit()
...:
...: for x in range(6):
...: article = Article(title='title{}'.format(x))
...: db.session.add(article)
...: db.session.commit()
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
username = db.Column(db.String(50))
class Article(db.Model):
__tablename__ = "article"
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),nullable=False)
content = db.Column(db.Text,nullable=False)
# 外键，没有指定，就默认为RESTRICT
# RESTRICT:阻止删除数据
# uid = Column(Integer,ForeignKey("user.id",ondelete="RESTRICT"))
# 级联删除
# uid = Column(Integer, ForeignKey("user.id", ondelete="CASCADE"))
# 只有父表被删除，子表修改为NULL
uid = db.Column(db.Integer, db.ForeignKey("user.id", ondelete="SET NULL"))
class User(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
username = db.Column(db.String(50))
articles = db.relationship('Article')
class Article(db.Model):
__tablename__ = "article"
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),nullable=False)
content = db.Column(db.Text,nullable=False)
uid = db.Column(db.Integer, db.ForeignKey("user.id", ondelete="SET NULL"))
# 映射到User模型
author = db.relationship("User")
def __repr__(self):
return "<Article(title:%s),content:%s>" % (self.title,self.content)
class User(db.Model):
....
articles = db.relationship('Article',uselist=False)
class Article(db.Model):
....
author = db.relationship("User",uselist=False)
....
class User(db.Model):
....
articles = db.relationship('Article',backref=db.backref("author",uselist=False))
def add():
user = User(username="demo1")
db.session.add(user)
db.session.commit()
user = User.query.first()
article = Article(title="title1",content="123",uid=user.id)
db.session.add(article)
db.session.commit()
db.drop_all()
db.create_all()
# 创建数据
add()
class User(db.Model):
....
articles = db.relationship('Article',backref=db.backref("author"))
class Article(db.Model):
....
def add():
user = User(username="demo1")
db.session.add(user)
db.session.commit()
user = User.query.first()
for i in range(6):
article = Article(title="title{}".format(i),content="{}".format(i),uid=user.id)
db.session.add(article)
db.session.commit()
class Article(db.Model):
__tablename__ = "article"
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),nullable=False)
content = db.Column(db.Text,nullable=False)
# 排序
author = db.relationship("User",backref=db.backref("articles",order_by=create_time.desc()))
class Article(db.Model):
....
__mapper_args__ = {
"order_by":create_time.desc(),
}
>>> db.drop_all()
>>> db.create_all()
>>> for i in range(1000):
article = Article(title="title{}".format(i))
db.session.add(article)
>>> db.session.commit()
select * from article limit 4 offset 9
or
select * from article limit 9,4
>>> articles = Article.query.offset(9).limit(4).all()
>>> for article in articles:
>>> print(article.title)
title9
title10
title11
title12
>>> articles = Article.query.all()[9:9+4]
>>> for article in articles:
>>> print(article.title)
title9
title10
title11
title12
>>> result = db.session.query(User.age,db.func.count(User.id)).group_by(User.age).all()
>>> result
[(17, 1), (18, 2), (19, 1), (20, 1)]
select count(*) from user group by age where age < 18;
>>> result = db.session.query(User.age,db.func.count(User.id)).group_by(User.age).having(User.age < 18).all()
>>> result
[(17, 1)]
class User(db.Model):
__tablename__ = "user"
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
username = db.Column(db.String(50),nullable=False)
def __repr__(self):
return "<User(id:%s,username:%s)>" % (self.id,self.username)
class Article(db.Model):
__tablename__ = 'article'
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
title = db.Column(db.String(50),nullable=False)
create_time = db.Column(db.DateTime,nullable=False,default=datetime.now)
uid = db.Column(db.Integer,db.ForeignKey("user.id"))
author = db.relationship("User",backref=db.backref("articles"))
def __repr__(self):
return "<Article(title:%s)>" % self.title
db.drop_all()
db.create_all()
user1 = User(username="angle")
user2 = User(username="miku")
for i in range(1):
article = Article(title="title1 %s" % i)
article.author = user1
db.session.add(article)
db.session.commit()
for i in range(1,3):
article = Article(title="title1 %s" % i)
article.author = user2
db.session.add(article)
db.session.commit()
for u,a in db.session.query(User,Address).filter(User.id == Address.user.id).all()
print(u)
print(a)
select user.id,user.username from user
inner join article on user.id = article.uid
group by user.id
order by count(article.id) desc;
>>> result = db.session.query(User).join(Article).group_by(User.id).order_by(db.func.count(Article.id).desc()).all()
>>> result
[<User(id:2,username:miku)>, <User(id:1,username:angle)>]
select article.id,article.title,article.create_time,article.uid from article
inner join user on user.id = article.uid
group by user.id
order by count(article.id) desc;
>>> result = db.session.query(Article).join(User).group_by(User.id).order_by(db.func.count(Article.id).desc()).all()
>>> result
[<Article(title:title1 1)>, <Article(title:title1 0)>]
select article.id ,article.title,article.create_time,article.uid from article
left outer join user on user.id = article.uid
group by user.id
order by count(article.id) desc;
>>> result = db.session.query(Article).outerjoin(User).group_by(User.id).order_by(db.func.count(Article.id).desc()).all()
>>> result
[<Article(title:title1 1)>, <Article(title:title1 0)>]
class User(db.Model):
__tablename__ = "user"
id = db.Column(db.Integer,primary_key=True,autoincrement=True)
username = db.Column(db.String(50),nullable=False)
city = db.Column(db.String(50),nullable=False)
age = db.Column(db.Integer,default=0)
def __repr__(self):
return "<User(username:%s)>" % self.username
>>> db.session.query(User).filter(db.text("age<:value and username=:name")). \
...: params(value=16, name='A').order_by(User.id).one()
<User(username:A)>
>>> db.session.query(User).from_statement(db.text("select * from user where username=:name and age<:age")).params(name='A',age=16).all()
[<User(username:A)>]
>>> stmt = db.text("select id,username FROM user where username=:name")
>>> stmt = stmt.columns(User.username, User.id)
>>> db.session.query(User).from_statement(stmt).params(name='A').all()
[<User(username:A)>]
pip install flask-script
from flask_script import Manager
from app import app,db
# 使用Manager创建一个对象
manager = Manager(app)
@manager.command
def greet():
print("你好")
if __name__ == '__main__':
manager.run()
python manage.py greet
from flask_script import Manager
from myapp import app,BackendUser,db
from db_script import db_manager
# 使用Manager创建一个对象
manager = Manager(app)
@manager.option("-u","--username",dest="username")
@manager.option("-a","--age",dest="age")
def add_user1(username,age):
print("用户名:{},年龄:{}".format(username,age))
@manager.option("-u","--username",dest="username")
@manager.option("-e","--email",dest="email")
def add_user2(username,email):
user = BackendUser(username=username,email=email)
db.session.add(user)
db.session.commit()
if __name__ == '__main__':
manager.run()
from flask import Flask
from exts import db
import config
app = Flask(__name__)
# 从配置文件获取配置参数
app.config.from_object(config)
# 懒加载进行初始化app
db.init_app(app)
pip install flask-migrate
from flask_script import Manager
from app import app
from flask_migrate import Migrate,MigrateCommand
from exts import db
manager = Manager(app)
# 用来绑定app和flask_migrate的
Migrate(app,db)
# 添加Migrate的所有子命令到db下
manager.add_command("db",MigrateCommand)
if __name__ == '__main__':
manager.run()
python manage.py db init
python manage.py db migrate
# python manage.py db migrate -m "initial migratetion"
python manage.py db upgrade
python manage.py db downgrade version(上一个版本的版本号)
python manage.py db --help
python manage.py db init
python manage.py db migrate
python manage.py db upgrade
# 改变字段后重复2~3步骤
# 回滚操作
python manage.py db downgrade version
# python manage.py db downgrade 289402d590c2
