If you are writing the SQL manually, you cannot have "db." in the table
names, as that is Python code, not SQL. Anyway, this is probably not the
best approach because IDs may not remain consecutive integers (e.g., if you
delete a record). Instead, maybe it would be simpler to just select that
last 22 records and then just take every other one of those.
As an aside, you can build up a query dynamically in a loop by using &= or
|=:
query_par |= db.articulos.id == i
As another aside, if you have a DAL query that works and you want to see
the SQL that was generated, you can use ._select instead of .select to
generate the SQL without executing the query (you can also look in
response.toolbar or db._lastsql).
Anthony
On Tuesday, January 27, 2015 at 6:59:32 PM UTC-5, José Eloy wrote:
>
> Hello!
>
> I need to build a variable query to select rows from a database. I need to
> get the last 11 records from my database, but only the even.
>
> This the code of the controller:
>
> # Muestra los últimos 11 artículos
> limite_articulos = 11
>
> # Getting the max id of the table:
> maxID =
> db(db.articulos).select(db.articulos.id.max()).first()[db.articulos.id.max()]
> # Getting the maxID-11
> limite_bajo = maxID-limite_articulos
>
> cuenta=0
> even_query = ""
>
> for i in range(limite_bajo,maxID):
> cuenta+=1
> if cuenta%2==0: # only the even records are saved
> #print str(i) + " es " + str(cuenta)
> query_par += "(db.articulos.id=='" + str(i) + "') | "
>
> even_query = query_par[:-2]
>
> The resulted even_query is as follows:
> even_query= (db.articulos.id=='2297') | (db.articulos.id=='2299') | (
> db.articulos.id=='2301') | (db.articulos.id=='2303') | (db.articulos.id
> =='2305')
>
> I use the resulted string in a query:
> articulos = db(even_query).select(db.articulos.id,
> db.articulos.titulo, db.articulos.contenido, db.articulos.autor,orderby=~
> db.articulos.id)
>
> But, I get the following error:
> *class 'gluon.contrib.pymysql.err.ProgrammingError'> (1064, u"You have an
> error in your SQL syntax; check the manual that corresponds to your MySQL
> server version for the right syntax to use near '=='2297') |
> (db.articulos.id <http://db.articulos.id>=='2299') | (db.articulos.id
> <http://db.articulos.id>=='2301') | (db.articulo' at line 1")*
> If I write the query directy I don't get the error:
> articulos = db((db.articulos.id=='2297') | (db.articulos.id=='2299')
> | (db.articulos.id=='2301') | (db.articulos.id=='2303') | (db.articulos.id
> =='2305')).select(db.articulos.id, db.articulos.titulo,
> db.articulos.contenido, db.articulos.autor, orderby=~db.articulos.id)
>
> The database is SQL Server 2005, Web2py 1.99.7.
>
> How can to write a variable query?
>
> Thanks for any help. Regards.
>
> P. D. I know my english is not very well, I hope you can understand me.
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
