[PYTHON] SQLite3 cheat sheet

Purpose

I want to avoid searching for the same thing as much as possible by summarizing the operations that are often used in SQLite3.

Introduction

** [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. ** **

Basic operations / commands, etc.

DB connection

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:

Data import / export

Import csv / tsv

sqlite> .separator <delimitter(, / \t)> 
sqlite> .import <file_name(.csv / .tsv)> <table_name>

Export as csv / tsv

sqlite> .headers on --Any
sqlite> .mode csv
sqlite> .output <filename(.csv)>
sqlite> select * from <table_name>;

Table operation

Show table list

sqlite> .table

View schema

sqlite> .schema <table_name>

SQL

There are many parts that are the same as ordinary SQL, so be careful

Execute external SQL file

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], ...);

Caution

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

Call from python

--Refer to this article: Using SQLite3 with Python --This library seems to be useful (unconfirmed) supersqlite

Use from IntelliJ

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

Cooperation with Ibis

Quickstart on Crunchbase analysis using Ibis and SQLite

References

-[SQLite3] Introduction to SQLite starting by comparing command examples with other databases -Use SQLite3 with Python --SQLite client for Mac