Lathund SQLite

Detta inlägg är mest en lathund för mig själv när jag ska hantera tabelldata på ett effektivt sätt i SQLite. Den kanske även kan vara användbar för andra, därför publicering här.

Data kan vara i flera källformat men det enklaste är om det är en csv-fil som text. För tester har jag en tabell med data från en ”TechDisc” som är en sensor som sitter på en frisbee som samlar in massa data från olika kast. Det är inte så avgörande och beskrivningen här är i stort oberoende av tabellinnehållet.

För det första importerar SQLite data från csv som textsträngar. Om jag som i det här fallet vill importera vissa kolumner som decimaltal så behöver jag först skapa ett schema för tabellen som ska fyllas.

Ett sätt att göra detta är att helt enkelt importera tabellen som den är och generera schemat för den i SQLite.

sqlite3 databas.db
sqlite> .import --csv techdisc.csv kast
sqlite> .schema
CREATE TABLE IF NOT EXISTS "kast"(
"id" TEXT, "time" TEXT, "timeSeconds" TEXT, "speedMph" TEXT,
 "speedKmh" TEXT, "advanceRatio" TEXT, "spinRpm" TEXT, "launchAngle" TEXT,
 "noseAngle" TEXT, "hyzerAngle" TEXT, "wobbleAngle" TEXT, "distanceFeet" TEXT,
 "distanceMeters" TEXT, "throwType" TEXT, "primaryThrowType" TEXT, "tags" TEXT,
 "notes" TEXT);

Kopiera hela CREATE kommandot till en textfil och ändra TEXT till INTEGER eller REAL som är de andra datatyper som kan vara aktuella. SQLite har inte datatypen DATE så i de flesta fall är det just text, integer eller real (decimaltal) som är aktuella. Spara filen som exempelvis databas.schema.

Radera den tidigare skapade databasen och gör sedan om genereringen med det nya schemat.

sqlite3 databas.db < databas.schema
sqlite3 databas.db
sqlite> .import --csv --skip 1 techdisk.csv kast

Skillnaden mot tidigare är att CREATE kommandot körs när databasen skapas, vilket kan testas med SQLite kommandot .schema när databasen öppnats.

Kommandot .import har nu även en instruktion om att hoppa över en rad. Eftersom det redan finns en tabell så försöker nämligen SQLite läsa in alla data från tabellen, även den första raden som är rubriker. Detta ställer till det ganska mycket så därför är detta tillägg nödvändigt.

Testa ett SELECT kommando med WHERE och ORDER BY för att se om det verkar fungera. Om du vill se resultatet lite ”snyggare” så kan du skriva in .www eller .excel först på en rad och sedan SELECT kommandot. Beroende på vad du väljer så visas då resultatet i webbläsaren eller i ett kalkylark.

sqlite> .www
sqlite> SELECT * FROM kast WHERE distanceMeters > 65 ORDER BY noseAngle LIMIT 12;

SQLite är världens mest spridda databas som finns överallt. Den enda stora nackdelen är att om man ska hantera data tillsammans med andra och kunna skriva till databasen från flera klienter samtidigt, då kommer det inte att fungera. Det är samma utgångsläge som för GeoPackage, som bygger på SQLite.

Kommandon är helt vanliga SQL kommandon och ska man lära sig skriptspråk inom GIS så är antagligen SQL ett av dessa. Att QGIS interna skriptspråk bygger på SQL blandat med Python gör det inte mindre användbart.