Python OOPs

Python SQLite

Examples

Others

Python OOPs

Python SQLite

Examples

Others



Python SQLite


In this tutorial explain, how to use SQLite with Python using sqlite3 module.

  • SQLite is a serverless, and zero-configuration relational database management system written in the C programming language.
  • SQLite is a popular database for small to medium-sized applications and mobile applications.
  • Python sqlite3 module is used to integrate the SQLite database with Python.

1.Creating a Connection using sqlite3 module

import sqlite3

# Connect to a database
con = sqlite3.connect("sample.db")

The sample.db file is created automatically by sqlite3.connect(), if sample.db does not already exist.

2.Create a Cursor

To execute SQL commands, you need to create a cursor object. Cursors are used to execute SQL statements, fetch data from the database and manage transactions.

We can create a cursor object using the cursor() method 
# Create a cursor object
cur = con.cursor()

3.Create a table

Let’s create students with the following fields: (id, name, rollno, age)

SQL Query for creating a students table 
CREATE TABLE "students" (
  id  INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT,
  rollno  TEXT,
  age   INTEGER
);
We can execute SQL Query using the execute method of the cursor. 
# Create a table
cur.execute("CREATE TABLE students ( id    INTEGER PRIMARY KEY AUTOINCREMENT, name    TEXT, rollno    TEXT, age    INTEGER )")

# Commit the changes
con.commit()

4. Insert data into the table

# Insert data into the table
cur.execute("INSERT INTO students (name,rollno,age) VALUES ('Ram','10001',15)")

# Commit the changes
con.commit()

5. Fetch data from the table

We can retrieve data from the database using fetchone() and fetchall().

# Fetch data from the table
cur.execute("SELECT * FROM students")
data = cur.fetchall()

for row in data:
  print(row)

Here's a complete Code:

data.py 
import sqlite3
    
# Connect to a database
con = sqlite3.connect("sample.db")

# Create a cursor object
cur = con.cursor()
    
# Create a table
cur.execute("CREATE TABLE students ( id    INTEGER PRIMARY KEY AUTOINCREMENT, name    TEXT, rollno    TEXT, age    INTEGER )")

# Insert data into the table
cur.execute("INSERT INTO students (name,rollno,age) VALUES ('Ram','10001',15)")

# Insert data into the table
cur.execute("INSERT INTO students (name,rollno,age) VALUES ('Tom','10002',14)")

# Commit the changes
con.commit()

# Fetch data from the table
cur.execute("SELECT * FROM students")
data = cur.fetchall()

for row in data:
    print("Name    : ",row[1])
    print("Roll No : ",row[2])
    print("Age     : ",row[3])
    print("--------------------")

Output:

C:\Users\HOME\Desktop>demo.py
Name    :  Ram
Roll No :  10001
Age     :  15
--------------------
Name    :  Tom
Roll No :  10002
Age     :  14
--------------------