I want to avoid searching for the same thing as much as possible by summarizing the operations that are often used in SQLite3.
** [SQLite3] Introduction to SQLite starting by comparing command examples with other databases This article is a must read when using SQLite for the first time in a while because it is compact and neatly organized. ** **
Connect to DB (create if not)
$ sqlite3 test.sqlite3
show
View settings
sqlite> .show
echo: off
eqp: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:
sqlite> .separator <delimitter(, / \t)>
sqlite> .import <file_name(.csv / .tsv)> <table_name>
sqlite> .headers on --Any
sqlite> .mode csv
sqlite> .output <filename(.csv)>
sqlite> select * from <table_name>;
sqlite> .table
sqlite> .schema <table_name>
SQL
There are many parts that are the same as ordinary SQL, so be careful
sqlite> .read test.sql
create
** It is better to specify the type of create as much as possible. It is not cast and for example integer / text is mixed (I was addicted) **
sqlite> create table <table_name> (<column1> [type1], <column2> [type2], ...);
Please note that the data type may get stuck.
If the inserted data can be cast to the specified data type, cast it, but if not, store it as it is (different data types can be mixed in one column)
[SQLite3] Introduction to SQLite starting by comparing command examples with other databases
--Refer to this article: Using SQLite3 with Python --This library seems to be useful (unconfirmed) supersqlite
When used from IntelliJ, table name completion is also effective and comfortable.
[database] => [+] => [data source] => [sqlite] => [xerial]
Create a new data source with and specify the db file name[test connection]
Just ok (download the driver if necessary)
Reference: SQLite client for Mac
Quickstart on Crunchbase analysis using Ibis and SQLite
-[SQLite3] Introduction to SQLite starting by comparing command examples with other databases -Use SQLite3 with Python --SQLite client for Mac
Recommended Posts