SQLite is one of the database management systems (DBMS) used when you want to create a small database quickly.
A system for registering, deleting, and searching data.
Reference: Database book What is the database that I can't ask anymore?
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.
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