以下代码使用了torndb（torndb是对mysqldb的简单封装，调用和使用cursor一致）
mysqldb如果查询时传入的参数是值序列，则sql的paramstyle必须是format，即%s作为占位符；
如果传入的参数是键值序列，则sql的paramstyle必须是pyformat，即%(xxx)s作为占位符；

1.count

    def get_all_topics_count_by_node_slug(self, node_slug):
        sql = 'SELECT COUNT(0) FROM topic LEFT JOIN node ON topic.node_id = node.id WHERE node.slug = %s'
        return self.db.get(sql, node_slug)['COUNT(0)']

2.select(分页)

    def get_all_topics_by_node_slug(self, node_slug, current_page=1, page_size=36, ):
        sql = '''SELECT topic.*,
                author_user.username as author_username,
                author_user.nickname as author_nickname,
                author_user.avatar as author_avatar,
                author_user.uid as author_uid,
                author_user.reputation as author_reputation,
                node.name as node_name,
                node.slug as node_slug,
                last_replied_user.username as last_replied_username,
                last_replied_user.nickname as last_replied_nickname
                FROM topic
                LEFT JOIN user AS author_user ON topic.author_id = author_user.uid
                LEFT JOIN node ON topic.node_id = node.id
                LEFT JOIN user AS last_replied_user ON topic.last_replied_by = last_replied_user.uid
                WHERE node.slug = %s
                ORDER BY last_touched DESC, created DESC, last_replied_time DESC, id DESC
                LIMIT %s, %s'''

        total_count = self.get_all_topics_count_by_node_slug(node_slug)
        total_page = int(math.ceil(total_count / float(page_size)))
        current_page = current_page if current_page <= total_page else total_page
        current_page = current_page if current_page >= 1 else 1
        previous_page = current_page - 1 if current_page > 1 else 1
        next_page = current_page + 1 if current_page < total_page else total_page

        result = {
            "list": self.db.query(sql, node_slug, (current_page-1)*page_size, page_size),
            "page": {
                "prev": previous_page,
                "next": next_page,
                "current": current_page,
                "pages": total_page,
                "total": total_count,
                "size": page_size
            }
        }
        return result

3.insert（任意字段）

    def add_new_topic(self, topic_info):
        sql = 'INSERT INTO topic ('
        sql += ', '.join(topic_info.keys())
        sql += ') VALUES ('
        sql += ', '.join(['%s'] * len(topic_info.keys()))
        sql += ')'
        return self.db.insert(sql, *topic_info.itervalues())

4.update（任意字段，pyformat）

    def update_topic_by_topic_id(self, topic_id, topic_info):
        sql = 'UPDATE topic SET '
        sql += ', '.join(['%s = %%(%s)s' % (k, k) for k in topic_info.keys()])
        sql += ' WHERE id = %(id)s'
        print sql
        return self.db.update(sql, id=topic_id, **topic_info)

The post python MySQLdb例子 appeared first on wingyiu.