SQLAlchemy数据库增删改查

https://www.cnblogs.com/wt7018/p/11617878.html

https://www.jianshu.com/p/b7704b6cb2ee  (增删该查，看这个)

 https://www.jianshu.com/p/1ba45bd6c351

https://blog.csdn.net/lv5751394/article/details/97049066

 

用法: 

Book.query.order_by(User.create_time.desc()).all()

Book.query.limit(2).all()

 

其中有个 排重的用法distinct()，要配合group_by 才可以实现

 

 

来源: https://segmentfault.com/a/1190000004618621

 

 

# coding=utf-8
from flask_sqlalchemy import SQLAlchemy
from flask import Flask
from sqlalchemy import Column,Integer,String,Boolean,Float
import sys

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = \
'mysql+cymysql://root:root@localhost:3306/lucaxTest?unix_socket=/Applications/MAMP/tmp/mysql/mysql.sock'
db = SQLAlchemy(app)

#book模型，传入db核心对象把Book插入db
class Book(db.Model):
　　__tablename__ = 'Book'
　　#Integer数字类型,primary_key主键(不可能出现重复),autoincrement自增长
　　id=Column(Integer,primary_key=True,autoincrement=True)
　　#String字符串类型,最大50字符,nullable是否为空
　　title=Column(String(50),nullable=False)
　　#default当为空给他自动赋值
　　author=Column(String(50),default="未名")
db.create_all(app=app)

#插入数据
@app.route("/add/")
def Add():
　　article = Book(title="英语2", author="李四哥")
　　db.session.add(article)
　　db.session.commit()
　　return "霸气"

 

#另外一种插入数据的写法

@app.route("/New/")
def New():
　　book=Book()
　　book.title="最新的书籍"
　　book.author="佚名"
　　db.session.add(book)
　　db.session.commit()
　　return "霸气"

 

#查询数据
@app.route("/Check/")
def Check():
　　#查询所有返回的是数组
　　result = Book.query.all()
　　#指定某列的值输出
　　print (result[0].title,file = sys.stderr)
　　#条件查询 #这个有点问题，见下面有个and的例子
　　result =Book.query.filter( Book.id == '1' and Book.title== "老舍").all()
　　#模糊查询。#    参考https://blog.csdn.net/chenmozhe22/article/details/95494947
　　result =Book.query.filter( Book.id.like("%1%")).all()
　　print (result,file = sys.stderr )
　　return "霸气"

in查询的写法:

filter( Book.id.in_(1,2,3,4,5)).all()

 

#修改数据
@app.route("/Update/")
def Update():
　　result = Book.query.filter(Book.id == '1').first()
　　result.title = '语文二册'
　　db.session.commit()
　　return "霸气"

#删除数据
@app.route("/Delect/")
def Delect():
　　result = Book.query.filter(Book.id == '1').first()
　　db.session.delete(result)
　　db.session.commit()
　　return "霸气"

 

#执行原生sql
@app.route("/Sql/")
def Sql():
　　# 查询
　　result = db.session.execute('select * from Book')
　　print (list(result), file = sys.stderr)
　　#增加
　　result = db.session.execute("INSERT INTO Book VALUES(6,'小学数学','佚名')")
　　db.session.commit()
　　#修改
　　result = db.session.execute("UPDATE Book SET title='英语2' where id=4")
　　db.session.commit()
　　#删除
　　result = db.session.execute("DELETE FROM Book where id=4")
　　db.session.commit()

return "霸气"

 

app.run(debug=True)

 

 

 

 

 

 

 

 

# 查询条件
# 1.equal
res = session.query(Article).filter(Article.id == 21).first()
# print(res)

# 2.notequal
res = session.query(Article).filter(Article.id != 21).all()
# print(res)

# 3.like & ilike不区分大小写
res = session.query(Article).filter(Article.title.like('title%')).all()
# print(res)

# 4.in
res = session.query(Article).filter(Article.title.in_(['title0', 'title1'])).all()
# print(res)

# 5.not in
res = session.query(Article).filter(~Article.title.in_(['title0', 'title1'])).all()
# print(res)
res = session.query(Article).filter(Article.title.notin_(['title0', 'title1'])).all()
# print(res)

# 6.isnull
res = session.query(Article).filter(Article.content == None).all()
# print(res)

# 7.is not null
res = session.query(Article).filter(Article.content != None).all()
# print(res)

# 8 and
res = session.query(Article).filter(Article.content == None, Article.title.notin_(['title0', 'title1'])).all()
# print(res)
res = session.query(Article).filter(and_(Article.content == None, Article.title.notin_(['title0', 'title1']))).all()
# print(res)

# 9 or
res = session.query(Article).filter(
or_(Article.content != None, Article.title.notin_(['title0', 'title1', 'title5']))).all()
print(res)

 

https://blog.csdn.net/lv5751394/java/article/details/97049066