Year-End Discount: 10% OFF 1-year and 20% OFF 2-year subscriptions!

Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

python
sqlite
sql
database

SQLite database operations in Python

Educative Answers Team

Tired of LeetCode? 😩

Learn the 24 patterns to solve any coding interview question without getting lost in a maze of LeetCode-style practice problems. Practice your skills in a hands-on, setup-free coding environment. 💪

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.

svg viewer

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:

svg viewer

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.

RELATED TAGS

python
sqlite
sql
database
Copyright ©2022 Educative, Inc. All rights reserved

Tired of LeetCode? 😩

Learn the 24 patterns to solve any coding interview question without getting lost in a maze of LeetCode-style practice problems. Practice your skills in a hands-on, setup-free coding environment. 💪

Keep Exploring
Related Courses