Summary

This context provides a comprehensive guide on using SQLAlchemy to interact with SQLite3 databases in Python, detailing the setup process, code examples, and testing procedures.

Abstract

The provided content serves as a tutorial for integrating SQLAlchemy with SQLite3 within a Python project. It begins by highlighting SQLAlchemy's versatility and its use by major companies like Dropbox. The guide then walks through setting up a new project in PyCharm, installing SQLAlchemy, and creating a custom database wrapper. It includes detailed steps for defining database tables, executing queries, and manipulating data. The tutorial also demonstrates how to use DB Browser for SQLite to visually manage the database and verify the results of the Python code. The article concludes by encouraging readers to experiment with SQLAlchemy for database management and promotes an AI service for those interested in cost-effective alternatives to ChatGPT Plus.

Opinions

The author suggests that SQLAlchemy is a popular and powerful tool for database interaction, suitable for both small-scale projects using SQLite3 and large-scale systems requiring scalability.
Dropbox's use of SQLAlchemy is presented as a testament to its reliability and scalability.
The author emphasizes the flexibility of SQLAlchemy, noting its support for various database systems beyond SQLite3, such as MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.
The tutorial is designed for readers with varying levels of expertise, from beginners to advanced users, as indicated by the recommendation of Python learning resources for different skill levels.
The author expresses confidence in the reader's ability to grasp the concepts presented, as evidenced by the encouragement to explore and use SQLAlchemy with any database management system.
An opinion on the cost-effectiveness of alternative AI services is offered, suggesting that readers might prefer a more affordable option over ChatGPT Plus for similar performance and functions.

Programming

How to Use Python SQLite3 Using SQLAlchemy

Learn the easiest way to use SQLite3 with a Python project.

Using SQLite3 or any database system in an application is a common way to store data. Even the Python language has some built-in libraries to access and use SQL-related database systems like SQLite3, MySQL, PostgreSQL, etc.

This tutorial will discuss one of the popular SQL-related libraries named SQLAlchemy and show how to use it to access the SQLite3 database system.

There are some big names who are using SQLAlchemy.

For example, dropbox.com is a widely successful file-sharing platform where early adopters of SQLAlchemy. They successfully scale out to over 50 million users using a custom-built SQLAlchemy Core approach on top of MySQL.

Source code on GitHub

The great thing about SQLAlchemy is that it supports all popular database systems, including SQLite3, MySQL, PostgreSQL, Oracle, Microsoft SQL Server, etc.

So let's start by creating our own wrapper library based on SQLAlchemy. In this post, we are using PyCharm for creating our sample project for SQLAlchemy. But it is not required. You can use any code editor and command line prompt to install the library and run the program.

Create a New Project using PyCharm

After installing PyCharm let's create a new project.

When the processing finishes, we will see a window like this.

Then we will click Preferences.

At this point, we will see the following window. Then we will click our Project name-> Project Interpreter.

Now we will click the bottom left [+] button

Then we will see all the packages we can install.

We will write SQLAlchemy in the search area, then we will click Install Package.

After successfully install the library, we will create a directory named database in the venv directory.

There we will create a python file named mydatabase.py

We will also create another python file in the venv directory named chapter5.py

Recommended Beginner Python Books: Python Crash Course | Automated The Boring Stuff | Python for Everybody (Affiliates)

Let's Write Some Code

First, we import the create_engine function from the SQLAlchemy package. We will also import some other classes like Table, Column, Integer, String, etc. Then we defined 2 global variables named USERS and ADDRESS for table names in the database.

I declared DB_ENGINE as a dictionary and assigned SQLITE a key with a path as value. The {DB} will be replaced with the actual file path. In the future, if we need any other DBMS service, we will put that configuration within DB_ENGINE.

When we will initialize a MyDatabase instance, we will send the configuration through the constructor. We assigned some default values within constructor parameters. Though for SQLITE3, we don’t need a username or password for other DBMS in the future we may need, so we define it as flexible.

create_db_tables() method will create an SQLite3 database using the classes from SQLAlchemy and the configuration we will provide.

We defined an execute_query() method, where we will provide the SQL query as a string and execute the SQL query. So by using this method, we can insert, update, or delete data from the database.

The print_all_data() method will print all the data from a database table we provided as a parameter.

Finally, we defined some methods for testing.

In the chapter5.py file, we wrote the following python code.

Before we run chapter5.py, we will download and install DB Browser. It supports both in mac and in windows to manipulate the SQLite database easily.

Let's Execute Our Program and Test

STEP 1:

First, we change the program chapter5.py and run in PyCharm or Terminal. We commented on all the codes except dbms.create_db_tables()

Then we will click the top right Play button or from the menu Run->Run Chapter5. The first time it will open the following window and select the file we want to run. We will select chapter5.py. Then we click OK, and then we can run our program in PyCharm.

After the first time running the program, we will see the console's following output within PyCharm. Where we will see the message ‘Tables created.’

We will also see a mydb.sqlite file within the Chapter5 directory.

STEP 2:

Then we will open the DB Browser program and open mydb.sqlite file in there.

Then we go through the Execute SQL tab and run the following sample SQL query there.

We will click the play button to execute the queries and finally click Write Changes to apply the change in the mydb.sqlite file.

Recommended Advanced Python Books: Fluent Python | Python Cookbook | Web Scraping (affiliates)

STEP 3:

Finally, we will change the chapter5.py main() function code again. We will now comment on the dbms.create_db_tables() method and uncommented others. Then we will rerun the program.

Now we will see the output data we inserted through DB Browser in the PyCharm console.

So I hope you understood how to use SQLAlchemy. You can use any database management system using the same concept.

👨🏼‍💻 If you liked this post, you could leave up to fifty 👏👏👏 claps. If you have any questions or opinions, let me know in the comments.