Introduction to Database Connectivity with SQLite
Published: 2024-05-17 15:10:03

# Introduction to Database Connectivity with SQLite

SQLite is a powerful and lightweight relational database management system that allows you to store and retrieve data efficiently. It is widely used in various applications and provides an easy way to manage your data.

In this article, we will explore how to establish a connection with SQLite using Python. We'll cover the basics of SQLite, including creating databases, performing CRUD (Create, Read, Update, Delete) operations, executing queries, and working with transactions.

## Prerequisites
Before getting started with database connectivity in Python using SQLite, make sure you have the following:

- Python installed on your computer
- The `sqlite3` module installed (This should come bundled by default with Python)

## Setting Up the Connection
To begin working with a database in SQLite from your Python codebase, you first need to establish a connection. To do this, follow these steps:

1. Import the `sqlite3` module by adding the following line at the beginning of your script:
```python
import sqlite3
```

2. The next step is to create a connection object that represents the database itself:
```python
connection = sqlite3.connect('database_name.db')
```
Replace `'database_name.db'` with your desired name for the database file.

Note: If the specified file does not exist already, it will be created automatically.

## Creating Tables
Once connected to an existing or newly created database using SQLite via Python code running on our machine successfully; we can start creating tables inside it as per our requirements.

Here's how you can create tables using SQL statements through Python:

1. Define an SQL statement that creates a table.
```python
create_table_query = '''CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);'''
```
Replace `'table_name'`, `'column1'`, `'column2'`, etc., with your desired table and column names, along with their respective data types and constraints.

2. Execute the SQL statement using the `execute()` method of the connection object.
```python
connection.execute(create_table_query)
```

## Performing CRUD Operations
SQLite supports all basic CRUD operations: Create, Read, Update, and Delete. In this section, we will discuss how to perform these operations using Python.

### Inserting Data
To insert data into tables in SQLite from Python code:

1. Define an SQL statement that inserts data into the table.
```python
insert_data_query = '''INSERT INTO table_name(column1,column2,...) 
                      VALUES(value1,value2,...);'''
```
Replace `'table_name', 'column1', 'column2', ..., `,`'value1','value2','...', etc.,` with your respective table name, column names, and values to be inserted.

2. Execute the SQL statement using `execute()` method of the connection object.
```python
connection.execute(insert_data_query)
```
Note: Don't forget to commit changes after executing each INSERT statement by calling `commit()` on your connection object:
```python
connection.commit()
```

### Reading Data

To retrieve data from a SQLite database in Python:

1. Define an SQL SELECT query that specifies which columns you want to retrieve or use * (asterisk) for all columns:
```python
select_query = '''SELECT column_name(s) FROM table_name WHERE condition;'''
```
Replace `'column_name(s)'`, `'table_name'`, and `'condition'` with specific values according to your requirements.


3. Fetch records retrieved by executing the select query using `fetchall()`.
```python
result_set = cursor.fetchall()
for row in result_set:
    print(row)
```

4. Close the cursor when done working with it.
   ``` python   
   cursor.close() 
   ```

### Updating Data

To update existing data in a SQLite database from Python:

1. Define an SQL statement that updates records in the table.
```python
update_query = '''UPDATE table_name SET column1=new_value WHERE condition;'''
```
Replace `'table_name'`, `'column1'`, `'new_value'`, and `'condition'` with specific values.

2. Execute the SQL statement.
```python
connection.execute(update_query)
```

3. Commit the changes by calling `commit()` on your connection object:
```python
connection.commit()
```

### Deleting Data

To delete data from SQLite tables using Python:

1. Define an SQL DELETE statement to remove records from a table.
```python
delete_query = '''DELETE FROM table_name WHERE condition;'''
```
Replace `'table_name'` and `'condition'` with specific values for your scenario.

2. Execute the SQL statement.
```python
connection.execute(delete_query)
```

3.Commit the changes by calling `commit()` on your connection object:
``` python  
connection.commit() 
```

## Working with Transactions

SQLite supports transactions, which allow you to perform multiple operations as a single unit of work. This ensures that either all operations succeed or none of them do.

Here's how you can use transactions in SQLite with Python:

Start a transaction using the `begin()` method of the connection object:
``` python  
connection.begin() 
 ```

Execute all desired CRUD operations within this transaction.

Commit or rollback based on whether everything ran successfully:
 ``` python   
transaction_status = True # Set it to true whenever there is no error while running all crud operations within the transaction block if any exception occurs before setting the status as True we will get a new value of False otherwise None.
try:     
    # All queries go here    
    if transaction_status:       
        connection.commit()    
except Exception as e:  
    print(e)
finally:
    if not transaction_status: 
        connection.rollback()  
```
If everything ran successfully, call `commit()` to persist the changes. If any exceptions occur, call `rollback()` to undo all the changes made within the transaction.

## Closing the Connection

Once you are done using SQLite with Python, make sure to close the connection by calling `close()` on your connection object:
```python
connection.close()
```

Closing a connection is important as it releases any system resources associated with it and ensures data integrity.

## Conclusion

In this article, we have learned how to establish a database connection with SQLite using Python. We have covered creating tables and performing CRUD operations such as insertions, deletions,
and updates. Additionally, we explored transactions and closing connections for proper resource management.

SQLite provides an efficient way of managing data in your Python applications. With these fundamental concepts under your belt,
you can now dive deeper into advanced topics and build robust database-backed applications using SQLite and Python. Happy coding!