Programming

How to Use Python SQLite3 Using SQLAlchemy

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

Mahmud Ahsan
Mar 27, 2018 · 5 min read
Image for post
Image for post

sing SQLite3 or any database system in 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.

In this tutorial, I will discuss one of the popular SQL related libraries named SQLAlchemy and will 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 the widely successful file sharing platform were 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 just 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.

Image for post
Image for post

When the processing will finish we will see a window like this

Image for post
Image for post

Then we will click Preferences

Image for post
Image for post

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

Image for post
Image for post

Now we will click the bottom left [+] button

Image for post
Image for post

Then we will see all the packages we can install.

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

Image for post
Image for post

After successfully install the library, we will create a directory named database in 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 create_engine function from 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 a 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 it will 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 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 let us select the file which we want to run. We will select chapter5.py. Then we click OK and then we can run our program in PyCharm.

Image for post
Image for post

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

Image for post
Image for post

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

Image for post
Image for post

STEP 2:

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

Image for post
Image for post

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

Image for post
Image for post

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 other. Then we will run the program again.

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

Image for post
Image for post

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

Level Up Programming

Enhance your programming skills

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store