Move Beyond Flat Files: SQLite and SQLAlchemy in Python

This article explains SQLite and SQLAlchemy in Python. We have also explained features of each of them. Let’s understand!

When we talk about “flat files,” we’re referring to a type of data storage format. A flat file is a file that contains data in a table-like structure, where each row represents a record, and each column represents a field. 

For example, let’s say you have a file called “customers.txt” that contains information about your customers. Each line in the file might represent a different customer, and each piece of information (like their name, address, and phone number) would be separated by a delimiter, like a comma or a tab. 

While flat files can be useful for storing small amounts of data, they’re not always the best choice for more complex data structures. That’s where SQLite and SQLAlchemy come in. 

In this article, we will discuss SQLite and SQLAlchemy in Python and understand how they are used in data management. We will be covering the following sections: 

• Introduction to SQLite in Python 
• Introduction to SQLAlchemy in Python 

Explore Python courses

Introduction to SQLite in Python 

SQLite is a lightweight relational database management system built into Python that allows you to store and manipulate data in a more structured way than a flat file. With SQLite, you can create tables with different data types and relationships between them, making it easier to query and update your data. It’s a great choice for small to medium-sized databases that only require a little concurrency or scalability. 

Explore Online Python Courses

You first need to import the sqlite3 module to use SQLite in Python. This module provides several functions and classes that allow you to interact with SQLite databases. 

Here’s an example of how to create a new SQLite database and add some data to it: 

import sqlite3 

# Connect to the database (creates a new file if it doesn't exist) 

conn = sqlite3.connect('mydatabase.db') 


# Create a new table 

conn.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)') 



# Add some data to the table 

conn.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)") 

conn.execute("INSERT INTO users (name, age) VALUES ('Bob', 30)") 

 
# Commit the changes 

conn.commit() 

# Close the connection 

conn.close() 

In this example, we create a new SQLite database called mydatabase.db. We then create a new table called users with three columns: id, name, and age. We add two rows of data to the table using INSERT statements and then commit the changes using commit(). Finally, we close the connection to the database. 

To retrieve data from the database, you can use the SELECT statement. Here’s an example: 

import sqlite3 

# Connect to the database 

conn = sqlite3.connect('mydatabase.db') 


# Retrieve all users from the table 

cursor = conn.execute('SELECT * FROM users') 

# Loop through the results and print them 

for row in cursor: 

    print(row) 

# Close the connection 

conn.close() 

In this example, we retrieve all user table rows using SELECT * FROM users. We then loop through the results using a for loop and print each row. 

Features of SQLite 

Let’s look at the key features of SQLite in Python: 

• Self-contained: SQLite is a self-contained, serverless database engine that runs within your application. This means that you don’t need to install any additional software or set up a separate server process to use it. 
• Cross-platform: SQLite is a cross-platform database engine that can run on various operating systems, including Windows, macOS, Linux, and many more. 
• Lightweight: SQLite is designed to be lightweight and fast, making it a great choice for small to medium-sized databases that don’t require a lot of concurrency or scalability. 
• Full-featured: Despite its lightweight design, SQLite is a full-featured relational database that supports many of the standard SQL features you expect from a relational database, including transactions, indexes, and views. 
• Small footprint: SQLite has a very small footprint, with the entire database engine contained within a single library file that can be embedded in your application. 
• ACID-compliant: SQLite is ACID-compliant, which means that it ensures data consistency and integrity even in the face of hardware or software failures. 
• High performance: SQLite is known for its high performance, thanks to its efficient design and memory-mapped I/O use. 
• Scalable: While SQLite is not designed for high-concurrency or high-scalability applications, it can still scale to support larger databases using techniques such as sharding or replication. 
• Well-documented: SQLite is very well-documented, with a comprehensive user manual covering all database engine aspects. There’s also a large and active community of developers who use and contribute to SQLite, meaning you can find plenty of resources and support if needed. 
• Free and open-source: SQLite is free and open-source software released under the public domain or permissive licenses, which means that you can use it for any purpose without needing to pay for a license or worry about vendor lock-in.  

Introduction to SQLAlchemy in Python 

SQLAlchemy, on the other hand, is a Python library that provides a higher-level interface for working with databases, including SQLite. It allows you to write Python code interacting with your database, making it easier to manipulate data and write complex queries.  

Features of SQLAlchemy 

Let’s look at the key features of SQLAlchemy in Python: 

• Object Relational Mapper (ORM): SQLAlchemy’s ORM allows you to map Python classes to database tables and perform database operations using Python code. This makes it easy to work with databases in a more object-oriented way without needing to write raw SQL queries. 
• SQL expression language: SQLAlchemy’s SQL expression language provides a way to write SQL queries in a more Pythonic way. This makes it easier to write complex queries, and it can also help to prevent SQL injection attacks. 
• Multiple database support: SQLAlchemy supports a wide range of database backends, including PostgreSQL, MySQL, SQLite, and Oracle, among others. 
• Flexibility and control: SQLAlchemy provides much flexibility and control over how you interact with databases. For example, you can use SQLAlchemy to create and manage database schema, handle database transactions, and execute raw SQL queries if needed. 
• Connection pooling: SQLAlchemy includes built-in support for connection pooling, which can help improve database connection performance in multi-threaded or multi-process applications. 
• Optional extensions and plugins: SQLAlchemy provides a wide range of optional extensions and plugins that can add additional functionality, such as support for full-text search or geographic data. 
• Cross-database compatibility: SQLAlchemy supports a consistent API across different database backends, which makes it easier to switch between databases or use multiple databases in the same application. 

Installing SQLAlchemy and Connecting to a Database 

You can install SQLAlchemy using pip, the Python package manager. Open a command prompt or terminal window and enter the following command: 

pip install sqlalchemy  

This will download and install the latest version of SQLAlchemy. 

Once you have installed SQLAlchemy, you can import it into your Python script using the following code: 

from sqlalchemy import create_engine 

To connect to a database using SQLAlchemy, you first need to create an Engine object representing the underlying database connection. You can create an Engine object using the create_engine() function, like this: 

engine = create_engine('postgresql://username:password@host:port/database') 

In this example, we are connecting to a PostgreSQL database with the username “username”, password “password”, and the database name “database”. You’ll need to replace these values with your own database credentials. 

Once you have created the Engine object, you can use it to create a connection to the database using the connect() method, like this: 

conn = engine.connect() 

This will create a connection object that you can use to execute SQL statements and interact with the database. 

That’s it! With these simple steps, you can install SQLAlchemy and connect to a database in your Python script. From there, you can use SQLAlchemy’s powerful ORM and SQL expression language to perform all kinds of database operations.  

How to define a Table object and query a table in your database using SQLAlchemy 

Define a Table object: To access a table in SQLAlchemy, you first need to define a Table object that represents the table in your database. You can create a Table object using the Table() function, like this: 

from sqlalchemy import Table, Column, Integer, String, MetaData 

metadata = MetaData() 


my_table = Table('my_table', metadata, 

                 Column('id', Integer, primary_key=True), 

                 Column('name', String), 

                 Column('age', Integer)) 

In this example, we are defining a Table object called “my_table” that has three columns: “id”, “name”, and “age”. 

Query the table: Once you have defined a Table object, you can query the table using SQLAlchemy’s SQL expression language. For example, you can select all rows from the table using the select() function, like this: 

from sqlalchemy import select 

sel = select([my_table]) 
result = conn.execute(sel) 


for row in result: 

    print(row) 

In this example, we are selecting all rows from the “my_table” table using the select() function. We then execute the select statement by calling the execute() method on the connection object, which returns a ResultProxy object that we can use to iterate over the results. 

We then loop through the results set rows and print each row. 

Endnotes 

So, if you’re working with larger or more complex datasets, it might be worth considering SQLite and SQLAlchemy as alternatives to flat files. They can help you organize your data more effectively and make it easier to work within your Python code.  

Hope this article was helpful for you. If you wish to learn more about Python and practice Python programming, you can explore related articles here. 

Contributed By: Prerna Singh