Interaction Python & SQLite3

SQLite est le système de gestion de bases de données relationnelles orienté fichier. Nous verrons connecter se connecter à une base de donnée SQLite et effectuer les opérations CRUD.

Prerequisites

No requirements

Packages

# !pip install -q pandas

import sqlite3
import pandas as pd

Paths & Config

DATABASE = "algojungle.db"

Connection to database

conn = sqlite3.connect(database=DATABASE)

# Create a cursor
cur = conn.cursor()

Interaction with database

0. Create a table

sql1 = "DROP TABLE IF EXISTS customers;";

sql2 = """
CREATE TABLE customers
(
    DepartmentID INTEGER PRIMARY KEY NOT NULL,
    Name VARCHAR(100),
    GroupName VARCHAR(100)
);
"""

cur.execute(sql1)
cur.execute(sql2)

conn.commit()

1. CREATE : Insert data

sql3 = """
INSERT INTO customers (DepartmentID, Name, GroupName) 
VALUES (1, 'Engineering', 'Research and Development'),
(2, 'Tool Design', 'Research and Development'),
(3, 'Sales', 'Sales and Marketing'),
(4, 'Marketing', 'Sales and Marketing'),
(5, 'Purchasing', 'Inventory Management'),
(6, 'Research and Development', 'Research and Development'),
(7, 'Production', 'Manufacturing'),
(8, 'Production Control', 'Manufacturing'),
(9, 'Human Resources', 'Executive General and Administration'),
(10, 'Finance', 'Executive General and Administration'),
(11, 'Information Services', 'Executive General and Administration'),
(12, 'Document Control', 'Quality Assurance'),
(13, 'Quality Assurance', 'Quality Assurance'),
(14, 'Facilities and Maintenance', 'Executive General and Administration'),
(15, 'Shipping and Receiving', 'Inventory Management'),
(16, 'Executive', 'Executive General and Administration');
"""

conn.execute(sql3)

conn.commit()

2. READ : Read data

sql4 = "SELECT * FROM customers;"

cur.execute(sql4)
results = pd.DataFrame(cur.fetchall(), 
                       columns=[desc[0] for desc in cur.description])
results

	DepartmentID	Name	GroupName
0	1	Engineering	Research and Development
1	2	Tool Design	Research and Development
2	3	Sales	Sales and Marketing
3	4	Marketing	Sales and Marketing
4	5	Purchasing	Inventory Management
5	6	Research and Development	Research and Development
6	7	Production	Manufacturing
7	8	Production Control	Manufacturing
8	9	Human Resources	Executive General and Administration
9	10	Finance	Executive General and Administration
10	11	Information Services	Executive General and Administration
11	12	Document Control	Quality Assurance
12	13	Quality Assurance	Quality Assurance
13	14	Facilities and Maintenance	Executive General and Administration
14	15	Shipping and Receiving	Inventory Management
15	16	Executive	Executive General and Administration

3. UPDATE : Update data

sql5 = """
UPDATE customers 
SET Name = 'Updated Engineering' 
WHERE DepartmentID = 1;
"""

cur.execute(sql5)

conn.commit()

cur.execute(sql4)
results = pd.DataFrame(cur.fetchall(), 
                       columns=[desc[0] for desc in cur.description])
results

	DepartmentID	Name	GroupName
0	1	Updated Engineering	Research and Development
1	2	Tool Design	Research and Development
2	3	Sales	Sales and Marketing
3	4	Marketing	Sales and Marketing
4	5	Purchasing	Inventory Management
5	6	Research and Development	Research and Development
6	7	Production	Manufacturing
7	8	Production Control	Manufacturing
8	9	Human Resources	Executive General and Administration
9	10	Finance	Executive General and Administration
10	11	Information Services	Executive General and Administration
11	12	Document Control	Quality Assurance
12	13	Quality Assurance	Quality Assurance
13	14	Facilities and Maintenance	Executive General and Administration
14	15	Shipping and Receiving	Inventory Management
15	16	Executive	Executive General and Administration

4. DELETE : Delete data

sql6 = """
DELETE FROM customers 
WHERE DepartmentID = 1;
"""

cur.execute(sql6)

conn.commit()

cur.execute(sql4)
results = pd.DataFrame(cur.fetchall(), 
                       columns=[desc[0] for desc in cur.description])
results

	DepartmentID	Name	GroupName
0	2	Tool Design	Research and Development
1	3	Sales	Sales and Marketing
2	4	Marketing	Sales and Marketing
3	5	Purchasing	Inventory Management
4	6	Research and Development	Research and Development
5	7	Production	Manufacturing
6	8	Production Control	Manufacturing
7	9	Human Resources	Executive General and Administration
8	10	Finance	Executive General and Administration
9	11	Information Services	Executive General and Administration
10	12	Document Control	Quality Assurance
11	13	Quality Assurance	Quality Assurance
12	14	Facilities and Maintenance	Executive General and Administration
13	15	Shipping and Receiving	Inventory Management
14	16	Executive	Executive General and Administration

Close connection

# close the communication with the SQLite
cur.close()
conn.close()
print('Database connection closed.')

Database connection closed.

References

• SQLite: https://www.sqlite.org/index.html
• SQLite3 Docs: https://docs.python.org/3/library/sqlite3.html

Let's get in touch