SQLite3 operations with Python

Hi Pythonistas!

Today we will learn an important topic which is “SQLite3 operations using Python”. Let us learn each operation by example

Creating/Connecting a database

import sqlite3
conn = sqlite3.connect('database.db')
print("Opened database successfully");

Once the code is executed a new file with name database.db is created in your current location. This is the sqlite3 database. If we run this code no new file will be created instead it connects to the existing DB.

Creating Table

import sqlite3
conn = sqlite3.connect('database.db')
conn.execute(
'''CREATE TABLE User

     (

         ID INTEGER PRIMARY KEY AUTOINCREMENT,

         NAME       TEXT NOT NULL,

         AGE        INT NOT NULL

     )

''')

print ("Table created successfully");
conn.close()

Once this is executed a table with the name User and having field ID, NAME, and AGE is created.

Inserting to Table

import sqlite3

conn = sqlite3.connect('database.db')

conn.execute("INSERT INTO User (NAME,AGE) VALUES ('Harry Potter', 11)");

conn.execute("INSERT INTO User (NAME,AGE) VALUES ('Ron Weasley', 11)");

conn.execute("INSERT INTO User (NAME,AGE) VALUES ('Hermione Granger', 11)");

conn.execute("INSERT INTO User (NAME,AGE) VALUES ('Vernon Dursley', 50)");

conn.commit()

print("Records created successfully");

conn.close()

Once this code is executed the 4 records will be created in table User.

Reading from Database

import sqlite3

conn = sqlite3.connect('database.db')

cursor = conn.execute("SELECT * from User")

for row in cursor:

   print("Id: ", row[0], "Name: ", row[1], "Age: ", row[2])

print("Operation done successfully");

conn.close()

Output

Id:  1 Name:  Harry Potter Age:  11

Id:  2 Name:  Ron Weasley Age:  11

Id:  3 Name:  Hermione Granger Age:  11

Id:  4 Name:  Vernon Dursley Age:  50

Operation done successfully

Update Operation

import sqlite3

conn = sqlite3.connect('database.db')

conn.execute("UPDATE User set NAME = 'Vernon Uncle' where ID = 4")

conn.commit()

cursor = conn.execute("SELECT * from User where ID=4")

row = cursor.fetchone()

print("Id: ", row[0], "Name: ", row[1], "Age: ", row[2])

print("Operation done successfully");

conn.close()

Output

Id:  4 Name:  Vernon Uncle Age:  50

Operation done successfully

Here we update the name of the record with an id equals 4. The fetchone function is used to fetch one record. 

DELETE a record

import sqlite3

conn = sqlite3.connect('database.db')

conn.execute("DELETE FROM User where ID = 4")

conn.commit()

cursor = conn.execute("SELECT * from User where ID=4")

row = cursor.fetchone()

print(row)

print("Operation done successfully");

conn.close()

Output

None

Operation done successfully

Here row is None which means the record with ID 4 is deleted from the database.

We have discussed the main operations of the database. For more details, you can refer to the Python documentation

I hope you have learned something from this post. Please share your valuable suggestions with afsal@parseltongue.co.in