Saving data to SQLite at the terminal¶

Save to sqlite using csvkit¶

In [ ]:

%%bash

csvsql --db sqlite:///iris_01.db --insert iris.csv

%%bash csvsql --db sqlite:///iris_01.db --insert iris.csv

In [ ]:

%%bash

sql2csv --db sqlite:///iris_01.db --query "SELECT * FROM iris ORDER BY sepal_width;" | head -n 10 | csvlook

%%bash sql2csv --db sqlite:///iris_01.db --query "SELECT * FROM iris ORDER BY sepal_width;" | head -n 10 | csvlook

| sepal_length | sepal_width | petal_length | petal_width | species         |
| ------------ | ----------- | ------------ | ----------- | --------------- |
|          5.0 |         2.0 |          3.5 |         1.0 | Iris-versicolor |
|          6.0 |         2.2 |          4.0 |         1.0 | Iris-versicolor |
|          6.2 |         2.2 |          4.5 |         1.5 | Iris-versicolor |
|          6.0 |         2.2 |          5.0 |         1.5 | Iris-virginica  |
|          4.5 |         2.3 |          1.3 |         0.3 | Iris-setosa     |
|          5.5 |         2.3 |          4.0 |         1.3 | Iris-versicolor |
|          6.3 |         2.3 |          4.4 |         1.3 | Iris-versicolor |
|          5.0 |         2.3 |          3.3 |         1.0 | Iris-versicolor |
|          4.9 |         2.4 |          3.3 |         1.0 | Iris-versicolor |

Save to sqlite using sqlitebiter¶

In [ ]:

%%bash

sqlitebiter file iris.csv
# by default sqlitebiter creates a database called "out.sqlite"
# rename the database
cp out.sqlite iris_02.db
rm out.sqlite

%%bash sqlitebiter file iris.csv # by default sqlitebiter creates a database called "out.sqlite" # rename the database cp out.sqlite iris_02.db rm out.sqlite

[INFO] convert 'iris.csv' to 'iris' table
[INFO] converted results: source=1, success=1, created-table=0
[INFO] database path: out.sqlite

Verify data was loaded in.

In [ ]:

%%bash

sql2csv --db sqlite:///iris_01.db --query "SELECT * FROM iris;" | head -n 10 | csvlook

%%bash sql2csv --db sqlite:///iris_01.db --query "SELECT * FROM iris;" | head -n 10 | csvlook

| sepal_length | sepal_width | petal_length | petal_width | species     |
| ------------ | ----------- | ------------ | ----------- | ----------- |
|          5.1 |         3.5 |          1.4 |         0.2 | Iris-setosa |
|          4.9 |         3.0 |          1.4 |         0.2 | Iris-setosa |
|          4.7 |         3.2 |          1.3 |         0.2 | Iris-setosa |
|          4.6 |         3.1 |          1.5 |         0.2 | Iris-setosa |
|          5.0 |         3.6 |          1.4 |         0.2 | Iris-setosa |
|          5.4 |         3.9 |          1.7 |         0.4 | Iris-setosa |
|          4.6 |         3.4 |          1.4 |         0.3 | Iris-setosa |
|          5.0 |         3.4 |          1.5 |         0.2 | Iris-setosa |
|          4.4 |         2.9 |          1.4 |         0.2 | Iris-setosa |

Save to sqlite using sqlite-utils¶

In [ ]:

%%bash

sqlite-utils insert iris_03.db iris iris.csv --csv

%%bash sqlite-utils insert iris_03.db iris iris.csv --csv

Verify load by querying using a file¶

In [ ]:

%%bash

cat example_query.sql

%%bash cat example_query.sql

SELECT *
FROM iris
WHERE species = 'Iris-virginica'
ORDER BY petal_width desc;

In [ ]:

%%bash

sql2csv example_query.sql --db sqlite:///iris_03.db | head -n 10 | csvlook

%%bash sql2csv example_query.sql --db sqlite:///iris_03.db | head -n 10 | csvlook

| sepal_length | sepal_width | petal_length | petal_width | species        |
| ------------ | ----------- | ------------ | ----------- | -------------- |
|          6.3 |         3.3 |          6.0 |         2.5 | Iris-virginica |
|          7.2 |         3.6 |          6.1 |         2.5 | Iris-virginica |
|          6.7 |         3.3 |          5.7 |         2.5 | Iris-virginica |
|          5.8 |         2.8 |          5.1 |         2.4 | Iris-virginica |
|          6.3 |         3.4 |          5.6 |         2.4 | Iris-virginica |
|          6.7 |         3.1 |          5.6 |         2.4 | Iris-virginica |
|          6.4 |         3.2 |          5.3 |         2.3 | Iris-virginica |
|          7.7 |         2.6 |          6.9 |         2.3 | Iris-virginica |
|          6.9 |         3.2 |          5.7 |         2.3 | Iris-virginica |

In [ ]:

Created in Deepnote