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.

スクリーンショット 2016-08-09 11.40.22.png

[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

Recommended Posts

SQLite3 cheat sheet
Curry cheat sheet
pyenv cheat sheet
conda command cheat sheet
PIL / Pillow cheat sheet
Linux command cheat sheet
ps command cheat sheet
Spark API cheat sheet
Python3 cheat sheet (basic)
PySpark Cheat Sheet [Python]
Python sort cheat sheet
Go language cheat sheet
numpy memory reuse cheat sheet
[Python3] Standard input [Cheat sheet]
Data Science Cheat Sheet (Python)
Slack API attachments cheat sheet
Python Django Tutorial Cheat Sheet
scikit learn algorithm cheat sheet
Apache Beam Cheat Sheet [Python]
Google Test / Mock personal cheat sheet
Continuation Passing Style (CPS) Cheat Sheet
Python cheat sheet (for C ++ experienced)
Python Computation Library Cheat Sheet ~ itertools ~
Curry cheat sheet [Description example list version]
AtCoder cheat sheet in python (for myself)
Mathematical Optimization Modeler (PuLP) Cheat Sheet (Python)
A brief description of pandas (Cheat Sheet)
Excel-> pandas-> sqlite
Sqlite in python
SQLite3 cheat sheet
OpenFOAM post-processing cheat sheet (updated from time to time)
[Updating] Python Syntax cheat sheet for Java shop
Cheat sheet that does not cause an accident
Cheat sheet when scraping with Google Colaboratory (Colab)