I am using SQLObject, a wrapper for python to manage SQL Queries, with Python 2.7. I know I can select data by using a dictionary such as:
restrictions = { ... }
selection = sql_table.selectBy(**restrictions).orderBy('-createdtime')
I can also query for date by doing:
selection = sql_table.select(sql_table.q.creatdtime>=datetime.datetime(year, month, day, 0, 0, 0, 0)
However, I want to use both together to sort by the date as well as the dictionary pairings. When I try to put them together like this:
selection = sql_table.select(**restrictions, sql_table.q.creatdtime>=datetime.datetime(year, month, day, 0, 0, 0, 0)
It doesn't work. Is there any way to filter the SQL query by a range of datetime and by the dictionary pairings?
Fixed the issue. In case you are here facing the same problem, here is the solution:
Since the SQLObject wrapper for python supports entering straight SQL queries, I opted to build it myself. First, I unpack all the restrictions as a query
select_string = " AND ".join(str(key) + "=" + str(restrictions[key]) for key in restrictions.keys())
I then wanted to add a restriction based on my dates. I knew that the column in my database that stored date and time was called createdtime, so I put the string as
select_string += " AND " + 'createdtime>=' + '"' + str(datetime.datetime(year, month, day, 0, 0, 0, 0)) + '"'
Note the quotation marks around the datetime object, even though it has been cast as a string, it still needs to have the quotation marks to work.
Therefore, my final code looks like this:
select_string = " AND ".join(str(key) + "=" + str(restrictions[key]) for key in restrictions.keys())
if date1:
select_string += " AND " + 'createdtime>=' + '"' + str(datetime.datetime(year, month, day, 0, 0, 0, 0)) + '"'
if date2:
select_string += " AND " + 'createdtime<=' + '"' + str(datetime.datetime(year2, month2, day2, 0, 0, 0, 0)) + '"'
selection = sql_table.select(select_string).orderBy('-createdtime')
return selection