Introduction to Python SQL Libraries

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:

 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:

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:

$ pip install mysql-connector-python

Note that MySQL is a server-based database management system. One MySQL server can have multiple databases. Unlike SQLite, where creating a connection is tantamount to creating a database, a MySQL database has a two-step process for database creation:

1. Make a connection to a MySQL server.
2. Execute a separate query to create the database.

Define a function that connects to the MySQL database server and returns the connection object:

 1import mysql.connector
 2from mysql.connector import Error
 3
 4def create_connection(host_name, user_name, user_password):
 5    connection = None
 6    try:
 7        connection = mysql.connector.connect(
 8            host=host_name,
 9            user=user_name,
10            passwd=user_password
11        )
12        print("Connection to MySQL DB successful")
13    except Error as e:
14        print(f"The error '{e}' occurred")
15
16    return connection
17
18connection = create_connection("localhost", "root", "")

In the above script, you define a function create_connection() that accepts three parameters:

1. host_name
2. user_name
3. user_password

The mysql.connector Python SQL module contains a method .connect() that you use in line 7 to connect to a MySQL database server. Once the connection is established, the connection object is returned to the calling function. Finally, in line 18 you call create_connection() with the host name, username, and password.

So far, you’ve only established the connection. The database is not yet created. To do this, you’ll define another function create_database() that accepts two parameters:

1. connection is the connection object to the database server that you want to interact with.
2. query is the query that creates the database.

Here’s what this function looks like:

def create_database(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Database created successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

To execute queries, you use the cursor object. The query to be executed is passed to cursor.execute() in string format.

Create a database named sm_app for your social media app in the MySQL database server:

create_database_query = "CREATE DATABASE sm_app"
create_database(connection, create_database_query)

Now you’ve created a database sm_app on the database server. However, the connection object returned by the create_connection() is connected to the MySQL database server. You need to connect to the sm_app database. To do so, you can modify create_connection() as follows:

 1def create_connection(host_name, user_name, user_password, db_name):
 2    connection = None
 3    try:
 4        connection = mysql.connector.connect(
 5            host=host_name,
 6            user=user_name,
 7            passwd=user_password,
 8            database=db_name
 9        )
10        print("Connection to MySQL DB successful")
11    except Error as e:
12        print(f"The error '{e}' occurred")
13
14    return connection

You can see in line 8 that create_connection() now accepts an additional parameter called db_name. This parameter specifies the name of the database that you want to connect to. You can pass in the name of the database you want to connect to when you call this function:

connection = create_connection("localhost", "root", "", "sm_app")

The above script successfully calls create_connection() and connects to the sm_app database.

PostgreSQL

Like MySQL, there’s no default Python SQL library that you can use to interact with a PostgreSQL database. Instead, you need to install a third-party Python SQL driver to interact with PostgreSQL. One such Python SQL driver for PostgreSQL is psycopg2. Execute the following command on your terminal to install the psycopg2 Python SQL module:

$ pip install psycopg2

Like with the SQLite and MySQL databases, you’ll define create_connection() to make a connection with your PostgreSQL database:

import psycopg2
from psycopg2 import OperationalError

def create_connection(db_name, db_user, db_password, db_host, db_port):
    connection = None
    try:
        connection = psycopg2.connect(
            database=db_name,
            user=db_user,
            password=db_password,
            host=db_host,
            port=db_port,
        )
        print("Connection to PostgreSQL DB successful")
    except OperationalError as e:
        print(f"The error '{e}' occurred")
    return connection

You use psycopg2.connect() to connect to a PostgreSQL server from within your Python application.

You can then use create_connection() to create a connection to a PostgreSQL database. First, you’ll make a connection with the default database postgres by using the following string:

connection = create_connection(
    "postgres", "postgres", "abc123", "127.0.0.1", "5432"
)

Next, you have to create the database sm_app inside the default postgres database. You can define a function to execute any SQL query in PostgreSQL. Below, you define create_database() to create a new database in the PostgreSQL database server:

def create_database(connection, query):
    connection.autocommit = True
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        print("Query executed successfully")
    except OperationalError as e:
        print(f"The error '{e}' occurred")

create_database_query = "CREATE DATABASE sm_app"
create_database(connection, create_database_query)

Once you run the script above, you’ll see the sm_app database in your PostgreSQL database server.

Before you execute queries on the sm_app database, you need to connect to it:

connection = create_connection(
    "sm_app", "postgres", "abc123", "127.0.0.1", "5432"
)

Once you execute the above script, a connection will be established with the sm_app database located in the postgres database server. Here, 127.0.0.1 refers to the database server host IP address, and 5432 refers to the port number of the database server.

SQLite

To execute queries in SQLite, use cursor.execute(). In this section, you’ll define a function execute_query() that uses this method. Your function will accept the connection object and a query string, which you’ll pass to cursor.execute().

.execute() can execute any query passed to it in the form of string. You’ll use this method to create tables in this section. In the upcoming sections, you’ll use this same method to execute update and delete queries as well.

Here’s your function definition:

def execute_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print