• python »
  • python MySQLdb例子

python MySQLdb例子

以下代码使用了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)

This entry was posted in python