Python SQL Object selecting data by dictionary and date

Question

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?

score 2 · Accepted Answer · answered Aug 25 '16 at 17:31

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

Python SQL Object selecting data by dictionary and date

1 Answers1