sqlalchemy使用上的小tip

Posted in 2017-3-14 6:16 | Category: Python | Tags: django sqlalchemy python

GitHub地址: https://github.com/honmaple/maple-json

sqlalchemy object序列化为json

灵感来源于 Django REST framework

多个实例

posts = Post.query.all()
serializer = Seralizer(posts,many=True)
data = serializer.data

单个实例

post = Post.query.first()
serializer = Seralizer(post,many=False)
data = serializer.data

排除字段

serializer = Seralizer(post,exclude=['title'])

仅包括字段

serializer = Seralizer(post,include=['title'])

关系查询深度

serializer = Seralizer(post,depth=3)
  • depth 默认为2

增加一些自定义的函数

serializer = Serializer(post,extra=['get_post_count'])

Post

class Post(Model):
    ......
    def get_post_count(self):
        return 11

可传递参数的函数

class PostSerializer(Serializer …

sqlalchemy序列化为json

Posted in 2016-12-13 7:53 | Category: Python | Tags: flask sqlalchemy python

为什么需要这个需求?

sqlalchemy 是个好东西,虽然其文档犹如老太婆的裹脚布--又臭又长,饱受诟病

使用 restful 时sqlalchemy返回的是一个 object 类,假设前后端分离,前端无法处理

如何实现?

直接给出代码

class Serializer(object):

    def __init__(self, instance, many=False, include=[], exclude=[], depth=2):
        self.instance = instance
        self.many = many
        self.include = include
        self.exclude = exclude
        self.depth = depth

    @property
    def data(self):
        if self.include and self.exclude:
            raise ValueError('include and exclude can\'t work together')
        if self.many:
            if isinstance(self.instance, list):
                return self._serializerlist(self.instance, self.depth)
            pageinfo = {
                'items': True,
                'pages': self.instance.pages,
                'has_prev': self.instance.has_prev,
                'page': self.instance.page,
                'has_next': self.instance.has_next,
                'iter_pages': list(self.instance.iter_pages(left_edge=1,
                                                            left_current …

sqlalchemy学习(一)

Posted in 2016-4-25 21:55 | Category: Linux | Tags: sql sqlalchemy python

sqlalchemy

系统的学一下sqlalchemyA,不过没有中文文档,只能用我的渣英文能力慢慢看原文档了

数据库连接

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)

echo为True输出日志

各种数据库连接方式

dialect+driver://username:password@host:port/database
  1. Postgresql

    # default
    engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')
    # psycopg2
    engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
    # pg8000
    engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
    
  2. MySQL

    # default
    engine = create_engine('mysql://scott:tiger@localhost/foo')
    # mysql-python
    engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
    # MySQL-connector-python
    engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
    # OurSQL
    engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')
    
  3. Oracle

    engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
    engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')
    
  4. SQLite

    # sqlite://<nohostname>/<path>
    # where <path> is relative:
    engine = create_engine('sqlite:///foo.db')
    #Unix/Mac - 4 initial slashes in total
    engine = create_engine …

关于sqlalchemy的filter_by与filter

Posted in 2016-3-5 17:18 | Category: Python | Tags: flask sqlalchemy

都可看做是where但用法不一样

filter_by

question = Questions.query.filter_by(id=1).first()

filter

question = Questions.query.filter(Questions.id==1).first()

其实就是===还有是否带类名的区别
如果要select * from questions where id < 5
这时只能使用filter

questions = Questions.query.filter(Questions.id < 5).all()

多个条件

question = Questions.query.filter_by(name='hello',id=5).first()
# 或者
question = Questions.query.filter(Questions.name=='hello',Questions.id==5).first()

关于sqlalchemy的desc

Posted in 2016-3-5 17:2 | Category: Python | Tags: flask sqlalchemy

也就是降序排序

简单使用

questions = Questions.query.order_by(Questions.time.desc()).all()

设置默认排序

如果几乎所有的questions都是按照时间降序排序,总不能每一条语句都加上order_by(Questions.time.desc())
所以设置默认排序是有效的

class Questions(db.Model):
    __tablename__ = 'questions'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(50), nullable=False)
    content = db.Column(db.Text, nullable=False)
    time = db.Column(db.DateTime, nullable=False)

    __mapper_args__ = {
        "order_by": time.desc()
    }

如代码所示,使用__mapper_args__就可,__mapper_args__可以做很多事,具体看这里
设置默认排序下面的做法是错误

    __mapper_args__ = {
        "order_by": 'Questions.time.desc()'
    }
    # 或者
    __mapper_args__ = {
        "order_by": 'Questions.time desc'
    }
    # 或者
    __mapper_args__ = {
        "order_by": 'desc(Questions.time)'
    }

多对多默认排序

比如这样

question = Questions.query.filter_by(id=1).first()
for reply in question.replies:
    print(reply.content)

又如何设置回复是按照回复时间排序的
总不能这样

for reply in sorted(question.replies):
# 这会报错的
TypeError: unorderable types: Replies() < Replies()

怎么设置

replies = db.relationship('Questions',
                          backref=db.backref('replies',
                                             lazy='dynamic',
                                             order_by …

flask-sqlalchemy使用

Posted in 2016-3-5 0:40 | Category: Python | Tags: flask sqlalchemy

简单的例子这里已经有了
中文 这里记录一下平时我遇到的一些问题

一对多

需求:一个问题对应多个回复
下面给出代码(字段不完整)

class Questions(db.Model):
    __tablename__ = 'questions'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(50), nullable=False)
    content = db.Column(db.Text, nullable=False)

    def __init__(self,title,content):
        self.title = title
        self.content = content

    def __repr__(self):
        return "<Questions %r>" % self.title


class Replies(db.Model):
    __tablename__ = 'replies'
    id = db.Column(db.Integer, primary_key=True)
    content = db.Column(db.Text, nullable=False)
    question_id = db.Column(db.Integer, db.ForeignKey('questions.id',
                                                      ondelete="CASCADE"))
    replies = db.relationship('Questions',
                              backref=db.backref('replies',
                                                 cascade='all,delete-orphan',
                                                 lazy='dynamic',
                                                 order_by='Replies.time')
                              )

    def __init__(self, content):
        self.content = content

    def __repr__(self):
        return "<Replies %r>" % self.content

会发现这样的两行(虽然实际上有好几行)

    question_id = db.Column(db.Integer, db.ForeignKey …