Sign in to follow this  
Followers 0
Illuma

SQLite data extraction

3 posts in this topic

Hi

I am playing with the SQLite database functions in Autoit, I have worked out 99% of what I would like to do but I am stuck on how to extract data from the database.

I am recording when data is inserted into the database by having a date column / field next to this date field I am also recording a couple of numeric values in their own fields.

Where I am totally stuck is what way I need to go about extracting certain data between two given dates, I have attached an example script I am playing with.

Any help pointing me in the correct direction would be greatly appreciated.

#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
_SQLite_Open()
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-01-01','123','10');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-02-01','111','20');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-03-05','158','30');") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-05-01','188','30');") ; INSERT Data

;Here I need to extract the sum of column c ONLY between these two dates 2013-12-30 and 2013-01-05, hence the first line is dropped as it is out of range
;and the total returned for the sum of the selected data should be 80



_SQLite_Close()
_SQLite_Shutdown()

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Try this and read my comments:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
_SQLite_Open()  ; this creates a (temporary) memory database
; please declare datatypes to avoid shooting yourself in the foot later
_SQLite_Exec(-1, "CREATE TABLE aTest (a text, b int, c int);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-01-01',123,10);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-02-01',111,20);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-03-05',158,30);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-05-01',188,30);") ; INSERT Data

;Here I need to extract the sum of column c ONLY between these two dates 2013-12-30 and 2013-01-05, hence the first line is dropped as it is out of range
;and the total returned for the sum of the selected data should be 80

Local $irows, $icols, $arows

_SQLite_GetTable2d(-1, "select sum(c) as Result from aTest where a between '2013-01-05' and '2013-12-30';", $arows, $icols, $icols)
_ArrayDisplay($arows)

_SQLite_Close()
_SQLite_Shutdown()
Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thanks very much jchd, I thought using the 2D array was the way to go but I had no idea I could create the complete formula and only extract the data required. I was looking at getting all the data as an array then taking only what I needed from that data.

In my actual program I have declared my data types, this was just one of my test programs I was playing with to see if I could work it out myself. 

your example works perfectly I can now fiddle with it and try it in my main program, once again thanks for the help.

 

 

Try this and read my comments:

#include <Array.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

_SQLite_Startup()
_SQLite_Open()  ; this creates a (temporary) memory database
; please declare datatypes to avoid shooting yourself in the foot later
_SQLite_Exec(-1, "CREATE TABLE aTest (a text, b int, c int);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-01-01',123,10);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-02-01',111,20);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-03-05',158,30);") ; INSERT Data
_SQLite_Exec(-1, "INSERT INTO aTest(a,b,c) VALUES ('2013-05-01',188,30);") ; INSERT Data

;Here I need to extract the sum of column c ONLY between these two dates 2013-12-30 and 2013-01-05, hence the first line is dropped as it is out of range
;and the total returned for the sum of the selected data should be 80

Local $irows, $icols, $arows

_SQLite_GetTable2d(-1, "select sum(c) as Result from aTest where a between '2013-01-05' and '2013-12-30';", $arows, $icols, $icols)
_ArrayDisplay($arows)

_SQLite_Close()
_SQLite_Shutdown()

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0