SQLite is a library written in C for the management of a lightweight dbms based on a .sqlite file that contains a database with all the data of a user, including tables, indexes and definitions saved as data tables, not requiring the use of a server, using a non-standard variant of the SQL language. Among the advantages it can boast lightness, compactness, speed and the need for a server to work.
Let’s now look through a series of small examples such as SQLite with Python.
Example 1: connecting to the database and creating a table.
First we create a .py extension file and import the sqlite3 library that we will need to use the SQLite features with Python.
Now we need to create a connection to the database using the connect () method, if the database does not exist it will be created automatically.
db = sqlite3.connect("myDataBase.db")
Once a connection is established, we create a Cursor object that calls its execute () method to execute SQL commands.
cursor = db.cursor()
Now we can create a simple table.
#we write a query in SQL in a variable createTable = "create table persons(id integer primary key autoincrement, surname varchar (30), name varchar (30))" #let's run the query cursor.execute(createTable )
To delete a table instead we will write the following query
deleteTable= "drop table persons" cursor.execute(deleteTable)
Below is the complete code of the example.
import sqlite3 db = sqlite3.connect("myDataBase.db") cursor = db.cursor() createTable= "create table persons(id int primary key not null, surname varchar(30), name varchar(30))" cursor.execute(createTable)
CRUD is the acronym for Create, Read, Update, Delete, which are nothing more than the four fundamental operations that are done on the data, i.e. create indicates the insertion of data, read the reading, update the modification and delete the deletion .
Esempio 2: inserimento dati in una tabella (create).
Prendiamo il codice scritto in precedenza e teniamo le prime tre righe che sono l’importazione di sqlite3, la connessione al database, e la creazione del cursore, poi vi aggiungeremo la query dell’inserimento dei dati in tabella, l’allocazione in memoria delle modifiche e per finire useremo un comando che non abbiamo ancora visto, il metodo commit() che rende definitive le modifiche sul database.
import sqlite3 db = sqlite3.connect("myDataBase.db") cursor = db.cursor() insertTable = """insert into persons(surname, name) values ('Mazzini', 'Giuseppe'), ('Marconi', 'Guglielmo'), ('Benso', 'Camillo'), ('Verdi', 'Giuseppe'), ('Mameli', 'Goffredo') """ cursor.execute(insertTable) db.commit()
We can verify the changes made using the SQLite browser.
Example 3: displaying the data in the table (read).
To view the data in the table, in the query we will use the SELECT construct, this form is in its very broad structure providing several features to be able to return the most varied results even in complex forms, here we will however limit ourselves to using the most basic form .
Let’s take the previous example and replace the insert query with the selection query, at the end we will print the data on the screen by cycling the array returned by the cursor.fetchall() method.
import sqlite3 db = sqlite3.connect("myDataBase.db") cursor = db.cursor() viewElements = "select * from persons" cursor.execute(viewElements) for i in cursor.fetchall(): print(i, i, i)
Fig 2 shows the content of the query.
Example 4: modification of data in the table (update).
For data modification we will use the update command as we will see in the following example.
import sqlite3 db = sqlite3.connect("myDataBase.db") cursor = db.cursor() editData = "update persons set surname='Volta', name='Alessandro' where id=2" cursor.execute(editData) db.commit() viewElements = "select * from persons" cursor.execute(viewElements) for i in cursor.fetchall(): print(i, i, i)
in Fig 3. you can see the updated database.
Example 5: deleting data in the table (delete).
To delete the data we will use the delete command as we will see in the following example.
import sqlite3 db = sqlite3.connect("myDataBase.db") cursor = db.cursor() deleteData = "delete from people where id=3" cursor.execute(delete Data) db.commit()
viewElements = “select * from people”
for i in cursor.fetchall():
print (i, i, i)
It should be noted that the commit() method is used only for those operations of inserting, modifying and deleting, that is, those operations where the actions of modifying the table are performed.
In this short article we have seen how it is possible to manage data even in large quantities with extreme simplicity using the binomial Python / SQLite.