[Flask] SQLAlchemy + pagination + MySQL problems

[Scott Massey]

I started getting this error when I switched from msqlite to MariaDB on a
digital ocean droplet. The syntax of how the ORM generates the query seems
to be the problem, but I don't know how to fix it. Changing the paginate
parameters such as 'per_page' only makes the params show as '-20, 20', for
example. See the error below:

sqlalchemy.exc.ProgrammingError

ProgrammingError: (pymysql.err.ProgrammingError) (1064, u"You have an error
in your SQL syntax; check the manual that corresponds to your MariaDB
server version for the right syntax to use near '-10, 10' at line 3")

[SQL: u'SELECT post.id AS post_id, post.title AS post_title, post.text AS
post_text, post.publish_date AS post_publish_date, post.user_id AS
post_user_id \nFROM post ORDER BY post.publish_date DESC \n LIMIT
%(param_1)s, %(param_2)s'] [parameters: {u'param_1': -10, u'param_2': 10}]

Here is the line that queries for posts:

@blog_blueprint.route('/')
@blog_blueprint.route('/<int:page>')
@cache.cached(timeout=60)
def home(page=1):
    posts = Post.query.order_by(Post.publish_date.desc()).paginate(page, 10)
    recent, top_tags = sidebar_data()

    return render_template(
    'home.html',
    posts=posts,
    recent=recent,
    top_tags=top_tags
    )

This first query fails with that same error, the second works via CLI, so
this seems to be what flask is sending to MySQL:

MariaDB [flaskblog]> SELECT post.id AS post_id, post.title AS post_title,
post.text AS post_text, post.publish_date AS post_publish_date, post.user_id
AS post_user_id \n FROM post ORDER BY post.publish_date DESC LIMIT -10, 10;

MariaDB [flaskblog]> SELECT post.id AS post_id, post.title AS post_title,
post.text AS post_text, post.publish_date AS post_publish_date, post.user_id
AS post_user_id \n FROM post ORDER BY post.publish_date DESC LIMIT 0, 10;

I am about to just take pagination out, because it's my first flask project
and it's just a blog, but I have spent hours banging my head. Thanks!

Scott
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/flask/attachments/20160511/7b85cf13/attachment.html>