How Do I Connect to a SQL Database in Python?

1. Introduction

Connecting Python applications to a MySQL database enables us to interact with relational databases seamlessly. Python offers several libraries to establish this connection, including MySQLdb, PyMySQL, and MySQL Connector.

In this tutorial, we’ll learn how to connect to a SQL database in Python. We’ll begin with the PyMySQL library. After that, we’ll see how to use the official MySQL Connector by Oracle. Lastly, we’ll discuss the MySQLdb library.

Note that we’ll be referring to the Baeldung University database schema for this article.

2. Prerequisites

Before we install the MySQL, we install the Python 3 and MySQL development headers and libraries:

$ sudo apt-get install python3-dev default-libmysqlclient-dev build-essential pkg-config

This ensures a successful connection to a SQL database.

3. Using PyMySQL

To begin with, the first method to connect to a SQL database is by using PyMySQL.

PyMySQL is a pure Python library that supports Python 2 and 3. It’s a simple and reliable choice for connecting to MySQL. Since it’s written entirely in Python, we don’t need additional system-level configurations to get started.

3.1. Installing PyMySQL

To connect Python to a MySQL database, we install a mysql driver. Since Python doesn’t include a built-in MySQL driver, we install one manually.

Let’s use the pip package manager to install PyMySQL:

$ python3 -m pip install PyMySQL

To use sha256_password or caching_sha2_password for authentication, we need to install an additional dependency:

$ python3 -m pip install PyMySQL[rsa]

To use MariaDB’s ed25519 authentication method, we need to install its dependency too:

$ python3 -m pip install PyMySQL[ed25519]

Once installed, PyMySQL is ready to use in the Python scripts without further setup.

3.2. Connecting to PyMySQL

After installing a driver, the next step is establishing a connection to the MySQL server.

We’ll use the Students table from Baeldung University schema to test the database connection:

$ cat pymysql.py
import pymysql.cursors
# Connect to the database
connection = pymysql.connect(host='localhost',
    user='user',
    password='passwd',
    database='db',
    cursorclass=pymysql.cursors.DictCursor)
with connection.cursor() as cursor:
    # Read a single record
    sql = "SELECT `name`, `graduation_date` FROM Student WHERE `gpa` > 3.0"
    cursor.execute(sql, ('[email protected]',))
result = cursor.fetchone()
print(result)

The above code connects to a MySQL database, fetches name and graduation_date records from a table named Student, and prints them. Furthermore, connection establishes the connection and cursor communicates with the database.

Notably, fetchone() retrieves the first row returned by the query and stores it in results. Lastly, we close the connection using the close() method.

Once we execute the script, the script prints the only row in the database:

{'name': 'John Liu', 'graduation_date': 2024-06-15}

This output demonstrates that the script successfully retrieved the row.

4. Using MySQL Connector

Another approach to connecting to a SQL database is MySQL connector. MySQL Connector is the official library provided by Oracle for connecting Python applications to MySQL databases. In addition, it’s frequently updated and supports all major features of MySQL.

4.1. Installing MySQL Connector

We use the pip package to install the MySQL connector:

$ pip install mysql-connector-python

After installation, the library is immediately available for use.

4.2. Connecting With MySQL Connector

The connection process is similar to PyMySQL. We import the library, establish the connection, and execute the required SQL queries. Let’s look at the script for this method:

$ cat mysql.py
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="user",
    password="passwd",
    database="db"
)
cursor = connection.cursor()
cursor.execute("SELECT name, graduation_date FROM Student WHERE gpa > 3.0")
for row in cursor.fetchall():
    print(row)
connection.close()

For a successful connection, we ensure that the provided server address, username, password, and database name are correct. In addition, we ensure the database server is accessible.

Moreover, we create a cursor object is created to execute SQL queries. The fetchall() method retrieves all the results from the executed query. Lastly, after completing all operations, it’s crucial to close the connection to free up resources on the server.

5. Using MySQLdb

MySQLdb is a Python interface for connecting to a MySQL database server. Moreover, it implements the Python Database API v2.0 and is built on top of the MySQL C API. It’s often preferred over the Oracle MySQL client because of its faster performance and ease of use.

In addition, it provides support for older versions of Python. Furthermore, it has strong community support and a history of being widely used, which means there are several resources and examples available for troubleshooting and learning.

5.1. Installing MySQLdb

First, we build the dependencies for python-mysqldb libraries:

$ sudo apt-get build-dep python-mysqldb

After that, we install the Python MySQL libraries using the pip package:

$ sudo pip install MySQL-python

Once the installation is complete, MySQLdb is ready for use.

5.2. Connecting to MySQLdb

To connect to MySQLdb, we establish a connection using the correct credentials, create a cursor object, and execute our desired SQL query. Let’s look at the script for this operation:

$ cat mysqldb.py
import MySQLdb
db = MySQLdb.connect(
    host="localhost",
    user="user",
    passwd="passwd",
    db="db"
)
cursor = db.cursor()
cursor.execute("SELECT name, graduation_date FROM Student WHERE gpa > 3.0")
for row in cursor.fetchall():
    print(row)
db.close()

The above code connects to the User table. After that, it retrieves all rows from that table and prints the results. After completing the operations, we close the connection to avoid resource leaks.

6. Conclusion

In this article, we discussed how to connect to a SQL database in Python.

While MySQLdb is a robust option, PyMySQL is recommended for its simplicity and Python 3 compatibility. On the other hand, MySQL Connector might be a better choice if we want an official, Oracle-supported solution. However, MySQLdb is often chosen for its performance and ease of use.