1+ ########## SQLITE3 MODULE ##########
2+
3+
4+ # The sqlite3 module is a built-in Python library that allows you to work with SQLite databases
5+ # It provides an interface compliant with the DB-API 2.0 specification described by PEP 249
6+ # The purpose of the DB-API 2.0 specification is to define a common standard for creating modules to work with databases in Python
7+ # sqlite3 module has been available in Python since version 2.5
8+
9+ # Following SQL standards have been created: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016, SQL:2019
10+ # Detailed information on each of the standards can be found in the Internet resources
11+ # SQLite generally implements the SQL-92 standard, with some exceptions
12+ # ==> https://www.sqlite.org/lang.html
13+
14+
15+ ## importing the sqlite3 module
16+
17+ import sqlite3
18+
19+
20+ ## creating a connection to a database
21+
22+ import sqlite3
23+
24+ conn = sqlite3 .connect ('hello.db' ) # creates or opens a database file named 'hello.db' in the current directory
25+ conn = sqlite3 .connect ('C:\PythonLearning\FILE_PROCESSING\\ navet.db' )
26+
27+
28+ ## creating a database in RAM
29+
30+ import sqlite3
31+
32+ conn = sqlite3 .connect (':memory:' ) # creates a database in RAM
33+
34+
35+ ## inserting data into a table ==> INSERT INTO
36+
37+ '''
38+ INSERT INTO table_name (column1, column2, column3, ..., columnN)
39+ VALUES (value1, value2, value3, ..., value4);
40+ '''
41+
42+ # INSERT INTO tasks (id, name, priority) VALUES (1, 'My first task', 1);
43+ # if we omit the id column, it will be automatically incremented if it is defined as INTEGER PRIMARY KEY
44+
45+
46+ # simple add
47+
48+ import sqlite3
49+
50+ conn = sqlite3 .connect ('todo.db' )
51+ c = conn .cursor ()
52+
53+ # executes the CREATE TABLE statement in our database
54+ c .execute ('''CREATE TABLE IF NOT EXISTS tasks (
55+ id INTEGER PRIMARY KEY,
56+ name TEXT NOT NULL,
57+ priority INTEGER NOT NULL
58+ );''' )
59+ c .execute ('INSERT INTO tasks (name, priority) VALUES (?,?)' , ('My first task' , 1 ))
60+ conn .commit () # mandatory to save the changes
61+ conn .close () # close the connection
62+
63+
64+ # multiple add ==> executemany()
65+
66+ import sqlite3
67+
68+ conn = sqlite3 .connect ('todo.db' )
69+ c = conn .cursor ()
70+ c .execute ('''CREATE TABLE IF NOT EXISTS tasks (
71+ id INTEGER PRIMARY KEY,
72+ name TEXT NOT NULL,
73+ priority INTEGER NOT NULL
74+ );''' )
75+ tasks = [
76+ ('My first task' , 1 ),
77+ ('My second task' , 5 ),
78+ ('My third task' , 10 ),
79+ ]
80+ c .executemany ('INSERT INTO tasks (name, priority) VALUES (?,?)' , tasks )
81+ conn .commit ()
82+ conn .close ()
83+
84+
85+
86+ ## application refactoring
87+
88+ # consists of creating a function containing repetitive fragments. As a result, the code’s volume is reduced, and it becomes more readable
89+
90+ import sqlite3
91+
92+ class Todo :
93+ def __init__ (self ):
94+ self .conn = sqlite3 .connect ('todo.db' ) # connect to the database
95+ self .c = self .conn .cursor () # create a cursor object
96+ self .create_task_table () # create the table when initializing the class
97+
98+ def create_task_table (self ): # method to create the tasks table
99+ self .c .execute ('''CREATE TABLE IF NOT EXISTS tasks (
100+ id INTEGER PRIMARY KEY,
101+ name TEXT NOT NULL,
102+ priority INTEGER NOT NULL
103+ );''' )
104+
105+ def add_task (self ): # method to add a new task
106+ name = input ('Enter task name: ' )
107+ priority = int (input ('Enter priority: ' ))
108+
109+ self .c .execute ('INSERT INTO tasks (name, priority) VALUES (?,?)' , (name , priority ))
110+ self .conn .commit ()
111+
112+ app = Todo ()
113+ app .add_task () # call the add_task method to add a new task
114+
115+
116+
117+ ## reading data
118+
119+ # iterator
120+ import sqlite3
121+
122+ conn = sqlite3 .connect ('todo.db' )
123+ c = conn .cursor ()
124+ for row in c .execute ('SELECT * FROM tasks' ):
125+ print (row )
126+ conn .close ()
127+ '''
128+ (1, 'My first task', 1)
129+ (2, 'My second task', 5)
130+ (3, 'My third task', 10)
131+ (4, 'python chap 1', 1)
132+ (5, 'kubernetes 4', 2)
133+ (6, 'faire travailler les gosses', 3)
134+ '''
135+
136+ # fetchall()
137+ import sqlite3
138+
139+ conn = sqlite3 .connect ('todo.db' )
140+ c = conn .cursor ()
141+ c .execute ('SELECT * FROM tasks' )
142+ rows = c .fetchall ()
143+ for row in rows :
144+ print (row )
145+ conn .close ()
146+ '''
147+ (1, 'My first task', 1)
148+ (2, 'My second task', 5)
149+ (3, 'My third task', 10)
150+ (4, 'python chap 1', 1)
151+ (5, 'kubernetes 4', 2)
152+ (6, 'faire travailler les gosses', 3)
153+ '''
154+
155+ # fetchone()
156+ import sqlite3
157+
158+ conn = sqlite3 .connect ('todo.db' )
159+ c = conn .cursor ()
160+ c .execute ('SELECT * FROM tasks' )
161+ row = c .fetchone ()
162+ print (row )
163+ # (1, 'My first task', 1)
164+ row = c .fetchone ()
165+ print (row )
166+ # (2, 'My second task', 5)
167+ conn .close ()
0 commit comments