Python SQLite

Python SQLite: A Comprehensive Guide to Understanding the Basics

Python is a popular programming language among developers and beginners alike owing to its user-friendly syntax and vast library of modules. One such module that Python offers is SQLite, a lightweight database software that stores data in a file instead of a server. SQLite is an excellent option for small-scale projects and data analysis where scalability is not a significant concern.

What is SQLite, and Why Use It with Python?

SQLite is a software library that provides an SQL database engine to store data. SQLite is a self-contained, serverless, zero-configuration, and transactional database engine, making it an ideal choice for projects with lower scalability requirements. SQLite is lightweight, fast, and requires minimal memory to operate; you can use it on various platforms, including Windows, Linux, and macOS.

Python offers an SQLite module to communicate with the SQLite database. You can use the SQLite module in Python to connect with an existing SQLite database, execute SQL queries, fetch data, and update the database. Additionally, using Python with SQLite allows you to upload data directly into memory, making it useful for programs with limited storage.

Installing Python SQLite

Using SQLite with Python is straightforward. Python doesn’t require any external installation to work with SQLite. SQLite is an in-built Python module and therefore, comes pre-installed with Python.

Connecting to a database

Before performing any operation on an SQLite database, you need to establish a connection. The following code demonstrates how to connect to a database:

import sqlite3

# connects to DB, creates one if it does not exist
# conn object is the connection object
conn = sqlite3.connect('database.db')

In the above code, we have imported the sqlite3 module and connected to a new database named ‘database.db’. If the database file does not exist already, the above code will create a new one.

Creating a Table Using Python SQLite

To create a table in your database, you use the ‘CREATE TABLE’ command along with the column definitions. Here’s the syntax to create a new table:

import sqlite3

# connect to the database
conn=sqlite3.connect('database.db') 

# create table
conn.execute('''CREATE TABLE USERS
                 (ID INT PRIMARY KEY     NOT NULL,
                 NAME           TEXT    NOT NULL,
                 AGE            INT     NOT NULL,
                 ADDRESS        CHAR(50));''')

print ("Table created successfully")

# commit changes and close the connection
conn.commit()
conn.close()

Above code demonstrates how to create a table in SQLite using Python. In the above code, we have created a table named “USERS” that has four columns: ID, NAME, AGE, and ADDRESS.

Inserting Data into a Table in Python SQLite

After creating a table, the next step is to insert data into the table. Here’s the syntax to insert data into an SQLite table:

import sqlite3

# connect to the database
conn = sqlite3.connect('database.db')

# insert data into the table
conn.execute("INSERT INTO USERS (ID, NAME, AGE, ADDRESS) VALUES (1, 'John Doe', 25, 'California')")
conn.execute("INSERT INTO USERS (ID, NAME, AGE, ADDRESS) VALUES (2, 'Jane Doe', 23, 'Washington')")
conn.execute("INSERT INTO USERS (ID, NAME, AGE, ADDRESS) VALUES (3, 'Bob Smith', 21, 'Texas')")
conn.execute("INSERT INTO USERS (ID, NAME, AGE, ADDRESS) VALUES (4, 'Joe Brown', 27, 'New York')")
conn.execute("INSERT INTO USERS (ID, NAME, AGE, ADDRESS) VALUES (5, 'Tom Green', 32, 'Florida')")

# commit changes and close the connection
conn.commit()
conn.close ()

In the above code, we have inserted five rows of data into the table USERS.

Querying Data from a Table Using Python SQLite

To query data from an SQLite table, you use the SELECT statement. Here’s the syntax to retrieve all rows from the USERS table:

import sqlite3

# connect to the database
conn = sqlite3.connect('database.db')

# select data from the table
data = conn.execute("SELECT * FROM USERS")

for row in data:
    print(f"ID: {row[0]}, Name: {row[1]}, Age: {row[2]}, Address: {row[3]}")

# close the connection
conn.close()

Above code retrieves all rows from the table USERS and prints them to the console.

Updating Data in an SQLite Table Using Python SQLite

You can update rows in an SQLite table using the UPDATE statement. Here’s the syntax to update rows in an SQLite table:

import sqlite3

# connect to the database
conn = sqlite3.connect('database.db')

# update rows in the table
conn.execute("UPDATE USERS SET NAME = 'Bob Smith Jr' WHERE ID = 3")
conn.execute("UPDATE USERS SET AGE = 28 WHERE ID = 4")

# commit changes and close the connection
conn.commit()
conn.close()

Above code updates the rows in the USERS table to change Bob Smith’s name and Joe Brown’s age.

Deleting Data from an SQLite Table Using Python SQLite

You can delete rows from an SQLite table using the DELETE statement. Here’s the syntax to delete rows from the USERS table:

import sqlite3

# connect to the database
conn = sqlite3.connect('database.db')

# delete rows from the table
conn.execute("DELETE FROM USERS WHERE ID = 5")

# commit changes and close the connection
conn.commit()
conn.close()

Above code deletes the row where ID=5 from the USERS table.

Conclusion

In conclusion, SQLite in Python is a great option for developers who want to work with small-scale projects and data analysis. It is straightforward to use and comes pre-installed with Python. You can use the SQLite module provided by Python to communicate with the SQLite database, execute SQL statements, and manage the data. Overall, if your project demands a lightweight, serverless database engine, SQLite is the way to go.

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top