在database.py文件中
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
app=Flask(__name__)
#连接mysql数据库,用户名:root 密码:111111 数据库名:mydatabase
app.config['SQLALCHEMY_DATABASE_URI']='mysql://root:111111@localhost/mydatabase'
db=SQLAlchemy(app)
class User(db.Model):
id=db.Column(db.Integer,primary_key=True)
username=db.Column(db.String(80),unique=True)
email=db.Column(db.String(120),unique=True)
def __init__(self,username,,email):
self.username=username
self.email=email
def __repr__(self):
return '<User %r>' %self.username
创建数据库和表(在flask模式下):
>>>from database import db
>>>db.create_all()
创建用户实例:
>>>from database import User
>>>admin=User('admin','admin@example.com')
>>>guest=User('guest','guest@example.com')
将实例添加到数据库中:
>>>db.session.add(admin)
>>>db.session.add(guest)
>>>db.session.commit()
查询数据库:
>>>users=User.query.all()
>>>users
[<User u'admin'>,<User u'guest'>]
按条件查询
>>>admin=User.query.filter_by(username='admin').first()
>>>admin
<User u'admin'>
简单的关系(多对一)
在database.py文件中加入如下内容:
from datetime import datetime
class Post(db.Model):
id=db.Column(db.Integer,primary_key=True)
title=db.Column(db.String(80))
body=db.Column(db.Text)
pub_date=db.Column(db.DateTime)
category_id=db.Column(db.Integer,db.ForeignKey('category.id'))
category=db.relationship('Category',backref=db.backref('posts',lazy='dynamic'))
def __init__(self,title,body,category,pub_date=None):
self.title=title
self.body=body
if pub_date is None:
pub_date=datetime.utcnow()
self.pub_date=pub_date
self.category=category
def __repr__(self):
return '<Post %r>' %self.title
class Category(db.Model):
id=db.Column(db.Integer,primary_key=True)
name=db.Column(db.String(50))
def __init__(self,name):
self.name=name
def __repr__(self):
return '<Category %r>' %self.name
创建对象:
>>>py=Category('python')
>>>p=Post('hello python!','Python is pretty cool',py)
>>>db.session.add(py)
>>>db.session.add(p)
posts声明为动态关系,并在反向引用中,它作为查询出现:
>>>py.posts
<sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x1027d37d0>
获得关联的所有帖子:
>>>py.posts.all()
[<Post 'hello Python!'>]