SQLite database operations in Python

Python has a built-in module named sqlite3 to work with SQL databases. The built-in module allows all operations to be performed on it that can be performed on a normal SQLite database.

To perform basic SQL functions using python, we first need to import the SQL module into our code. This is done using the command:

import sqlite3

Let’s look at how to perform basic SQL functions in Python:

Connecting to SQLite database

To connect to a local database on your computer use:

import sqlite3
conn = sqlite3.connect('database.sqlite')
cursor = conn.cursor()
print("Opened database successfully")

Creating a table

cursor.execute('''CREATE TABLE EMPLOYEE
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL);''')
cursor.close()

Inserting values in tables

import sqlite3
conn = sqlite3.connect('database.sqlite')
cursor = conn.cursor()
cursor.execute("INSERT INTO EMPLOYEE (ID,NAME,AGE) \
      VALUES (1, 'Razi', 14')");
cursor.execute("INSERT INTO EMPLOYEE (ID,NAME,AGE,ADDRESS,MARKS) \
      VALUES (2, 'Jon', 19, 'Bangalore', 150 )");
cursor.execute("INSERT INTO EMPLOYEE (ID,NAME,AGE) \
      VALUES (3, 'Martha', 35)");
conn.commit()
conn.close()

The .commit function writes the changes to the database.

Deleting table values

import sqlite3
conn = sqlite3.connect('my_database.sqlite')
cursor = conn.cursor()
conn.execute("DELETE from  SCHOOL where ID = 2")
conn.commit()
conn.close()

To execute SQL commands using Python, simply pass the SQL statement to the conn.execute() function. Any SQL statement can be executed in a similar manner.