Introduction to Python SQL Libraries

Introduction to Python SQL Libraries

by Usman Malik basics databases tools
Share Share Email
Remove ads

All software applications interact with data, most commonly through a database management system (DBMS). Some programming languages come with modules that you can use to interact with a DBMS, while others require the use of third-party packages. In this tutorial, you’ll explore the different Python SQL libraries that you can use. You’ll develop a straightforward application to interact with SQLite, MySQL, and PostgreSQL databases.

In this tutorial, you’ll learn how to:

  • Connect to different database management systems with Python SQL libraries
  • Interact with SQLite, MySQL, and PostgreSQL databases
  • Perform common database queries using a Python application
  • Develop applications across different databases using a Python script

To get the most out of this tutorial, you should have knowledge of basic Python, SQL, and working with database management systems. You should also be able to download and import packages in Python and know how to install and run different database servers locally or remotely.

Understanding the Database Schema

In this tutorial, you’ll develop a very small database for a social media application. The database will consist of four tables:

  1. users
  2. posts
  3. comments
  4. likes

A high-level diagram of the database schema is shown below:

python-sql-database-schema

Both users and posts will have a one-to-many relationship since one user can like many posts. Similarly, one user can post many comments, and one post can also have multiple comments. So, both users and posts will also have one-to-many relationships with the comments table. This also applies to the likes table, so both users and posts will have a one-to-many relationship with the likes table.

Using Python SQL Libraries to Connect to a Database

Before you interact with any database through a Python SQL Library, you have to connect to that database. In this section, you’ll see how to connect to SQLite, MySQL, and PostgreSQL databases from within a Python application.

It’s recommended that you create three different Python files, so you have one for each of the three databases. You’ll execute the script for each database in its corresponding file.

SQLite

SQLite is probably the most straightforward database to connect to with a Python application since you don’t need to install any external Python SQL modules to do so. By default, your Python installation contains a Python SQL library named sqlite3 that you can use to interact with an SQLite database.

What’s more, SQLite databases are serverless and self-contained, since they read and write data to a file. This means that, unlike with MySQL and PostgreSQL, you don’t even need to install and run an SQLite server to perform database operations!

Here’s how you use sqlite3 to connect to an SQLite database in Python:

Python 
 1import sqlite3
 2from sqlite3 import Error
 3
 4def create_connection(path):
 5    connection = None
 6    try:
 7        connection = sqlite3.connect(path)
 8        print("Connection to SQLite DB successful")
 9    except Error as e:
10        print(f"The error '{e}' occurred")
11
12    return connection

Here’s how this code works:

  • Lines 1 and 2 import sqlite3 and the module’s Error class.
  • Line 4 defines a function .create_connection() that accepts the path to the SQLite database.
  • Line 7 uses .connect() from the sqlite3 module and takes the SQLite database path as a parameter. If the database exists at the specified location, then a connection to the database is established. Otherwise, a new database is created at the specified location, and a connection is established.
  • Line 8 prints the status of the successful database connection.
  • Line 9 catches any exception that might be thrown if .connect() fails to establish a connection.
  • Line 10 displays the error message in the console.

sqlite3.connect(path) returns a connection object, which is in turn returned by create_connection(). This connection object can be used to execute queries on an SQLite database. The following script creates a connection to the SQLite database:

Python 
connection = create_connection("E:\\sm_app.sqlite")

Once you execute the above script, you’ll see that a database file sm_app.sqlite is created in the root directory. Note that you can change the location to match your setup.

MySQL

Unlike SQLite, there’s no default Python SQL module that you can use to connect to a MySQL database. Instead, you’ll need to install a Python SQL driver for MySQL in order to interact with a MySQL database from within a Python application. One such driver is mysql-connector-python. You can download this Python SQL module with pip:

Shell 
$ pip install mysql-connector-python