pythonpython-3.xpython-2.7sqlite

SQLite - type casting in python

I am new to python. I am moving my table data from AWS Redshift to in memory SQLite database in python.

Please refer the table structure below:

Redshift Table :

s0 1

My value field in the above column is in Decimal at Redshift.

When I tried to bring the data to SQLite, I found that the result which I am getting from Redshift contains Decimal keyword in the result so I was not able to insert it into the SQLite table which was also marked as Decimal ( Here is the link for the same issue which I have raised earlier ). So I changed my datatype as varchar(30) so that my values can fit in the SQLite table.

Please refer the table structure below for my in memory sqllite table :

in memory sqllite table

enter image description here

Now I wanted to do the sum of the column value on the timestamp basis in my python script, but I am not able to get the expected result.

Here is the query which I am using in Redshift to get the sum and I am getting the expected output :

select sum(value) from table where id = 9831 and item_date = '2018-11-01' and to_char(item_datetime, 'HH24MI') between '0000' and '2359';

Here is the query which I am using in my script to type cast the value column to decimal and item_datetime to strftime to get the required output :

select sum(CAST(value as decimal)) from table where id = 9831 and item_date = '2018-11-01' and strftime(item_datetime, 'HH24MI') between '0000' and '2359';

But I am getting output as None. Cloud somebody explain me this behavior and what I am doing wrong here ? How to deal with this issue ?

Solution

  • In SQLite it goes like this:

    ... AND STRFTIME('%H%M', item_datetime) BETWEEN '0000' AND '2359'

    For more info see the docs.