[Python] 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 :



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



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 ?

Continue reading...