sqlalchemy执行sql语句
#删除user表中的所有数据,表中的自增字段又从默认值开始 session.execute("truncate table user")
mysql语句查看表结构详细信息
desc table_name 语句:
如:(查看person表的结构)
desc person; +-------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(8) | YES | MUL | NULL | | +-------+------------+------+-----+---------+----------------+
sqlalchemy 模型与表的映射
class Article(db.Model): __tablename__='article' id=db.Column(db.Integer,primary_key=True,autoincrement=True) title=db.Column(db.String(100),nullable=False) content=db.Column(db.Text,nullable=False) db.create_all()
+---------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | varchar(100) | NO | | NULL | |
| content | text | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
### FLask -SQLALchemy数据的增、删、改、查:
1.增:
#增加:
article1 = Article(title=' aaa' , content= ' bbb')
db. session. add(article1)
#事务
db. session. commit()
2.查:
# select * from article where article.title='aaa' ;
article1 = Article. query. filter(Article.title == 'aaa').first()
print (article1. title)
3.改:
# 1.先把你要更改的数据查找出来
article1 = Article. query. filter(Article.title == 'aaa'). first()
# 2.把这条数据,你需要修改的地方进行修改
article1.title = 'new title '
# 3.做事务的提交
db. session. commit( )
4.删:
# 1.把需要删除的数据查找出来
article1 = Article. query. filter(Article.content == 'bbb'). first()
# 2.把这条数据删除掉
db. session. delete(article1)
# 3.做事务提交
db. session. commit( )
### Flask- SQLALchemy外键及其关系:
1.外键:
class User(db. Model): __tablename__ ='user' id = db.Column(db.Integer, primary_ key=True, autoincrement=True) username = db.Column(db.String(100) ,nullable=False) class Article( db.Model): __tablename__ ='article' id = db.Column( db.Integer, primary_ key=True, autoincrement=True) title = db.Column(db.String(100) , nullable=False) content = db.Column(db.Text, nullable=False) author_id = db.Column(db.Integer, db.ForeignKey('user.id') author = db.relationship( 'User' , backref=db.backref( 'articles'))
2. author = db. relationship( 'User', backref=db. backref('articles'))
解释:
* 给'Article'这个模型添加一个'author'属性,可以访问这篇文章的作者的数据,像访问普通模型一样。
* 'backref'是定义反向引用,可以通过'User.articles'访问这个模型所写的所有文章。