The world’s leading publication for data science, AI, and ML professionals.

Do You Know Python Has A Built-In Database?

An introduction of Python built-in library – sqlite3

Photo by mcmurryjulie on Pixabay
Photo by mcmurryjulie on Pixabay

If you are a software developer, I believe you must know or even have used an extremely light-weighted Database – SQLite. It has almost all the features you need as a relational database, but everything is saved in a single file. On the official site, here are some scenarios that you could use SQLite.

  • Embedded devices and IoT
  • Data Analysis
  • Data Transferring
  • File archive and/or data container
  • Internal or temporary databases
  • Stand-in for an enterprise database during demos or testing
  • Education, training and testing
  • Experimental SQL language extensions

There are more reasons that you may want to use SQLite, please check out the documentation.

Appropriate Uses For SQLite

Most importantly, SQLite is built-in into a Python library. In other words, you don’t need to install any server-side/client-side software, and you don’t need to keep something running as a service, as long as you imported the library in Python and start coding, then you have a relational database management system!

Import and Usage

Photo by CopyrightFreePictures on Pixabay
Photo by CopyrightFreePictures on Pixabay

When we say "built-in", it means that you don’t even need to run pip install to acquire the library. Simply import it by:

import sqlite3 as sl

Create a Connection to DB

Don’t be bothered with the drivers, connection strings and so on. You can create an SQLite database and have a connection object as simple as:

con = sl.connect('my-test.db')

After we run this line of code, we have created the database and connected it to it already. This is because the database we asked Python to connect to is not existing so that it automatically created an empty one. Otherwise, we can use the same code to connect to an existing database.

Create a Table

Then, let’s create a table.

with con:
    con.execute("""
        CREATE TABLE USER (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            age INTEGER
        );
    """)

In this USER table, we added three columns. As you can see, SQLite is indeed lightweight, but it supports all the basic features a regular RDBMS should have, such as the data type, nullable, primary key and auto-increment.

After running this code, we should have created a table already, although it outputs nothing.

Insert Records

Let’s insert some records into the USER table we just created, which can also prove that we indeed created it.

Suppose we want to insert multiple entries in one go. SQLite in Python can achieve this easily.

sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)
]

We need to define the SQL statement with question marks ? as a placeholder. Then, let’s create some sample data to be inserted. With the connection object, we can then insert these sample rows.

with con:
    con.executemany(sql, data)

It didn’t complain after we’ve run the code, so it was successful.

Query the Table

Now, it’s time to verify everything we have done tangibly. Let’s query the table to get the sample rows back.

with con:
    data = con.execute("SELECT * FROM USER WHERE age <= 22")
    for row in data:
        print(row)

You can see how simple it is.

Also, even though SQLite is light-weighted, but as a widely-used database, most of the SQL clients software support to consume it.

The one I use the most is DBeaver, let’s see how it looks like.

Connect to SQLite DB from SQL Client (DBeaver)

Because I’m using Google Colab, so I’m going to download the my-test.db file to my local machine. In your case, if you run Python on your local machine, you can use your SQL client to connect directly to the databases file.

In DBeaver, create a new connection and select SQLite as DB type.

Then, browse the DB file.

Now, you can run any SQL query on the database. It is nothing different from other regular relational databases.

Seamless Integrate with Pandas

Photo by GraphicMama-team on Pixabay
Photo by GraphicMama-team on Pixabay

Do you think that’s all? No. In fact, as a built-in feature of Python, SQLite can seamlessly integrate with Pandas Data Frame.

Let’s define a data frame.

df_skill = pd.DataFrame({
    'user_id': [1,1,2,2,3,3,3],
    'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning']
})

Then, we can simply call to_sql() method of the data frame to save it into the database.

df_skill.to_sql('SKILL', con)

That’s it! We even don’t need to create the table in advance, the column data types and length will be inferred. Of course, you can still define it beforehand if you want to.

Then, let’s say we want to join the table USER and SKILL, and read the result into a Pandas data frame. It’s also seamless.

df = pd.read_sql('''
    SELECT s.user_id, u.name, u.age, s.skill 
    FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id
''', con)

Super cool! Let’s write the results to a new table called USER_SKILL.

df.to_sql('USER_SKILL', con)

Then, we can also use our SQL client to retrieve the table.

Summary

Photo by Monoar_CGI_Artis on Pixabay
Photo by Monoar_CGI_Artis on Pixabay

Indeed, there are many surprises hidden in Python. They do not mean to be hidden, but just because there are too many out-of-box features existing in Python for one to discover all of them.

In this article, I have introduced how to use the Python built-in library sqlite3 to create and manipulate tables in an SQLite DB. Of course, it also supports updating and deleting but I think you would try it yourself after this.

Most importantly, we can easily read a table from an SQLite DB into a Pandas data frame, or vice versa. This allows us to even more easily to interact with our light-weight relational database.

You may notice that SQLite doesn’t have authentication, that’s its designed behaviour as everything needs to be lightweight. Go discover more surprising features in Python, enjoy it!

All the code in this article can be found in my Google Colab Notebook.

SQLite Pandas Examples

Join Medium with my referral link – Christopher Tao

If you feel my articles are helpful, please consider joining Medium Membership to support me and thousands of other writers! (Click the link above)


Related Articles