problems querying sqlite

[Marc A. Garrett]

I've imported the data into a sqlite database as described on the site
(http://stat-computing.org/dataexpo/2009/sqlite.html). But when I
attempt to query the ontime table the query returns 0 rows.

My sqlite file is 11GB, so the import appears to have worked, but I
just can't find the data. Any ideas?

OS X Leopard, sqlite3.

Thanks,
--
Marc A. Garrett
ma...@since1968.com
AIM: since1968

[hadley wickham]

Hi Marc,

How are you trying to query it? From sqlite or from R? What does

select count(*) from ontime;

return?

Hadley

--
http://had.co.nz/

[Marc A. Garrett]

Querying the count shows 0 results.

I'm querying directly from sqlite. Just for kicks I tested with R,
which also does not work.

Finally, running ".tables" shows that ontime is the only table in the
database.

Here's the results of running .schema. I can't see any issues:

CREATE TABLE ontime ( Year int, Month int, DayofMonth int, DayOfWeek
int, DepTime int, CRSDepTime int, ArrTime int, CRSArrTime int,
UniqueCarrier varchar(5), FlightNum int, TailNum varchar(8),
ActualElapsedTime int, CRSElapsedTime int, AirTime int, ArrDelay int,
DepDelay int, Origin varchar(3), Dest varchar(3), Distance int, TaxiIn
int, TaxiOut int, CancellationCode varchar(1), Diverted varchar(1),
CarrierDelay int, WeatherDelay int, NASDelay int, SecurityDelay int,
LateAircraftDelay int );
CREATE INDEX date on ontime(year, month, dayofmonth);
CREATE INDEX dest on ontime(dest);
CREATE INDEX origin on ontime(origin);
CREATE INDEX year on ontime(year);

--
Marc A. Garrett
ma...@since1968.com
AIM: since1968

[hadley wickham]

Hmmmm. Can you run vacuum and then see how big the database is? I
wonder if something went wrong with the delete from ontime where year
== "year"; statement and everything got deleted.

Hadley

--
http://had.co.nz/

[Marc A. Garrett]

I bet that was it. Vacuuming the database shrank it to 8K.

I re-ran the import for a single year, tested the count, and it works.
I'll check the delete statement closely before I run it again.

Thanks,
--
Marc A. Garrett
ma...@since1968.com
AIM: since1968

[hadley wickham]

On Wed, Aug 20, 2008 at 3:30 AM, Marc A. Garrett <ma...@since1968.com> wrote:
>
> I bet that was it. Vacuuming the database shrank it to 8K.
>
> I re-ran the import for a single year, tested the count, and it works.
> I'll check the delete statement closely before I run it again.

Use this sql to delete the header rows:

delete from ontime where typeof(year) == "text";

(I've updated the webpage too)

I obviously just assumed that the previous sql would work, without
testing it in conjunction with sqlite's non-standard type casting.

Hadley

--
http://had.co.nz/