[PYTHON] Easy to use SQLite3

SQLite is one of the database management systems (DBMS) used when you want to create a small database quickly.

What is a database?

A system for registering, deleting, and searching data.

Reference: Database book What is the database that I can't ask anymore?

Database type

And so on. (See: Comparison of MySQL, PostgreSQL, SQLite, Oracle DB)

Among them, SQLite3 is already included in the Python standard library, has few functions, and is easy to use.

How to use SQLite3

You need to write SQL separately from python.

#import
import sqlite3

#Connect to database
filepath = "test2.sqlite"
conn = sqlite3.connect(filepath) 
#If there is no file with the same name as filepath, a file will be created.

#Create table
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS items")

cur.execute("""CREATE TABLE items(
    item_id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    price INTEGER
)""")
conn.commit()

#Insert data in one shot
cur.execute('INSERT INTO items (name , price) VALUES (?,?)',("Orange", 520))
conn.commit()

#Insert data continuously
cur = conn.cursor()
data = [("Mango",770),("Kiwi", 400), ("Grape",800),("Peach",940),("Persimmon",700), ("Banana",400)]
cur.executemany(
    "INSERT INTO items (name, price) VALUES (?,?)", data)
conn.commit()

With the above, the database has been constructed and the data has been registered. Note that the command will not be reflected in the database unless conn.commit () is executed.

Let's display all the data.

#Extract all data
cur = conn.cursor()
cur.execute("SELECT item_id, name, price FROM items")
items_list = cur.fetchall()
items_list
[(1, 'Orange', 520),
 (2, 'Mango', 770),
 (3, 'Kiwi', 400),
 (4, 'Grape', 800),
 (5, 'Peach', 940),
 (6, 'Persimmon', 700),
 (7, 'Banana', 400)]

Let's display them one by one with a for statement.

#Extract all data (using for statement)
cur = conn.cursor()
cur.execute("SELECT item_id, name, price FROM items")
items_list = cur.fetchall()
for fr in items_list:
    print(fr)
(1, 'Orange', 520)
(2, 'Mango', 770)
(3, 'Kiwi', 400)
(4, 'Grape', 800)
(5, 'Peach', 940)
(6, 'Persimmon', 700)
(7, 'Banana', 400)

Let's search.

#400-Extract and display 700 yen data
cur = conn.cursor()
price_range = (400, 700)
cur.execute(
    "SELECT * FROM items WHERE price >=? AND PRICE <=?", price_range
)
fr_list = cur.fetchall()
for fr in fr_list:
    print(fr)
(1, 'Orange', 520)
(3, 'Kiwi', 400)
(6, 'Persimmon', 700)
(7, 'Banana', 400)

that's all

Recommended Posts

Easy to use SQLite3
Easy to use Flask
Easy to use E-Cell 4 Intermediate
Easy to use E-Cell 4 Beginner's edition
How to use SQLite in Python
Easy to use E-Cell 4 Advanced Edition
Easy to use Jupyter notebook (Python3.5)
Easy way to use Wikipedia in Python
Let's make jupyter lab easy to use
How to use xml.etree.ElementTree
How to use Python-shell
How to use tf.data
How to use virtualenv
How to use Seaboan
How to use image-match
How to use shogun
Easy way to use Python 2.7 on Cent OS 6
How to use Virtualenv
How to use numpy.vectorize
How to use pytest_report_header
How to use partial
How to use Bio.Phylo
How to use SymPy
How to use x-means
How to use WikiExtractor.py
How to use virtualenv
How to use Matplotlib
How to use iptables
How to use numpy
Reasons to use logarithm
How to use TokyoTechFes2015
How to use venv
How to use Pyenv
How to use list []
How to use python-kabusapi
Python-How to use pyinstaller
How to use OptParse
How to use return
How to use dotenv
How to use pyenv-virtualenv
How to use Go.mod
How to use imutils
How to use import
[Introduction to WordCloud] It's easy to use even with Jetson-nano ♬
Reasons to use long type in SQLite3 (C # Mono.Data.Sqlite)
How to use Qt Designer
How to use search sorted
python3: How to use bottle (2)
Understand how to use django-filter
Use MeCab to fetch readings
How to use the generator
QSM analysis-How to use MEDI-
How to use FastAPI ③ OpenAPI
Use sqlite3 with NAO (Pepper)
Easy to read control flow
Use sqlite load_extensions with Pyramid
How to use Python argparse
Easy to make with syntax
How to use IPython Notebook
How to use Pandas Rolling
[Note] How to use virtualenv