Python for SQL: An Introduction to Database Connectivity

Image by author

Overview

Python is a popular and versatile programming language used for a variety of applications such as web development, scientific computing, and data analysis. One of its strengths is its ability to connect and interact with databases. In this article, we will introduce Python for SQL and demonstrate how it can be used to connect to and manipulate databases.

While SQL is a powerful language for working with databases, it has some limitations. For example, it can be difficult to write complex queries or perform data analysis using SQL alone. Python, on the other hand, is a powerful programming language that can be used to perform complex data analysis, machine learning, and web development tasks. By using Python for SQL, you can take advantage of both languages to perform more advanced data analysis and database management tasks.

Python for SQL: Database Connectivity

To connect Python to a database, you need to use a Python library or module that provides a database driver. A database driver is a software component that provides an interface between the Python program and the database management system. There are several popular Python libraries for database connectivity, including:

1. PyMySQL: A pure Python MySQL driver that allows you to connect to a MySQL database and perform SQL queries.
2. psycopg2: A PostgreSQL database adapter that provides access to the PostgreSQL database server.
3. sqlite3: A built-in Python library for working with SQLite databases.
4. SQLAlchemy: A SQL toolkit and Object-Relational Mapping (ORM) library that provides a high-level interface to SQL databases.

In this article, we will use the PyMySQL library to connect to and manipulate a MySQL database. PyMySQL is a Python library used for connecting to MySQL databases. A frequently utilized database management system for web applications is MySQL, which is open-source and based on the relational model. PyMySQL provides a simple interface for connecting to MySQL databases and executing SQL queries.

Connecting to a MySQL Database

To connect to a MySQL database using Python, you need to install the PyMySQL library. Run the following command to install it using the pip package manager:

pip install pymysql

Once you have installed the PyMySQL library, you can create a connection to a MySQL database using the connect() function. The connect() function takes several parameters, including the hostname, username, password, and database name. To connect to a MySQL database using pymysql, we need to create a connection object. To connect to the database, one must utilise the connection object. Here’s an example of how to create a connection object:

import pymysql 
connection = pymysql.connect(host='localhost', user='root', password='passCode', db='your_database')

In this example, we are connecting to a MySQL database running on the local machine. We are using the root user and the password “passCode” to authenticate ourselves. Finally, we are connecting to a database called “your_database”.

Creating a Cursor object

A cursor object is used to execute SQL queries against a database. A cursor object acts as a pointer to a specific location in the database, allowing you to retrieve, insert, update, or delete data. In PyMySQL library, creating a cursor object is an essential step in executing SQL queries.

The database connection object is used to generate the cursor object. To create a cursor object, you need to call the cursor() method on the database connection object. Here is an example:

import pymysql 
# Open database connection 
db = pymysql.connect("localhost","user","password","database_name" ) 

# Create a cursor object 
cursor = db.cursor() 

# Execute SQL query 
cursor.execute("SELECT * FROM table_name") 

# Fetch all rows 
rows = cursor.fetchall()
 
for row in results: 
print(row)
# Close database connection 
db.close()

In this example, we first open a database connection using the PyMySQL library. The connection requires the host, user, password, and database name to connect to a MySQL database. Once the connection is established, we create a cursor object using the cursor() method.

After the cursor object is created, we can execute an SQL query using the execute() method. In this example, we execute a SELECT statement that retrieves all rows from a specific table in the database.

The fetchall() method is then called on the cursor object to retrieve all rows from the SELECT statement. The rows are stored in a variable named rows. We are iterating over the results and printing each row. Then, we close the database connection using the close() method.

It’s important to note that the cursor object does not retrieve any data until a query is executed. The execute() method is used to execute the SQL query, and the fetchall() method retrieves the data from the query.

Insert data in the database

To insert data in a database using pymysql, you will need to establish a connection to the database and execute SQL commands. Here’s an example code snippet to insert data in a MySQL database using pymysql:

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             db='database_name')

# Cursor object creation
cursor = connection.cursor()

# Define the SQL query
sql_query = "INSERT INTO books (author, name, mail, pages) VALUES (%s, %s, %s, %d)"

# Data insertion
data = ("Enid", "Secret Seven", "enid@example.com", 21)


# Execute the query with the data
cursor.execute(sql_query, data)

# Commit the changes
connection.commit()

# Close the cursor and the connection
cursor.close()
connection.close()

The SQL query we define is an INSERT statement that inserts data into the books table. The data that will be inserted is represented by the query’s %s and %d placeholders.

We then define the data to be inserted as a tuple with three elements: the name, email, and phone number. We execute the query with the data using the execute() method of the cursor object. We then commit the changes using the commit method of the connection object.

Note that if you want to insert multiple rows of data at once, you can use the executemany() method instead of execute(). The executemany() method takes a list of tuples as its second argument, where each tuple represents a row of data to be inserted.

Updating and Deleting Data in the Database

To update and delete data in a database using pymysql, you will need to establish a connection to the database and execute SQL commands. Here’s an example code snippet to update and delete data in a MySQL database using pymysql:

import pymysql 
# Establishing a connection to the database 
connection = pymysql.connect( host='localhost', user='your_username', password='your_password', db='your_database' ) 

# Creating a cursor object 
cursor = connection.cursor() 

# Updating data in a table 
update_query = "UPDATE table_name SET column1=%s WHERE column2=%s" cursor.execute(update_query, (new_value, condition_value)) 
connection.commit() 

# Deleting data from a table 
delete_query = "DELETE FROM table_name WHERE column=%s" cursor.execute(delete_query, (value_to_delete,)) 
connection.commit() 

# Closing the cursor and connection 
cursor.close() 
connection.close()

In the example above, the pymysql.connect() method is used to establish a connection to the database, and the cursor() method is used to create a cursor object to execute SQL commands.

To update data in a table, you need to employ the UPDATE statement and the SET keyword to indicate the fresh value that you wish to assign to the column being updated. The WHERE clause is used to specify the condition for the rows to update.

To delete data from a table, you can use the DELETE statement with the FROM keyword to specify the table you want to delete data from. The WHERE clause is used to specify the condition for the rows to delete.

Finally, you will need to call the commit() method to commit the changes to the database, and close the cursor and connection using the close() method.

Handling Errors and Exceptions

Handling errors and exceptions is an important part of writing reliable code in PyMySQL. Here’s an example of how to handle errors and exceptions in PyMySQL:

import pymysql

# Connect to the database
try:
    connection = pymysql.connect(host='localhost',
                                 user='username',
                                 password='password',
                                 db='database_name')
except pymysql.Error as e:
    print("Error connecting to database:", e)
    exit()

# Create a cursor object
try:
    cursor = connection.cursor()
except pymysql.Error as e:
    print("Error creating cursor:", e)
    exit()

# Define the SQL query
sql_query = "INSERT INTO books (author, name, mail, pages, weight) VALUES (%s, %s, %s, %d, %d)"

# Define the data to be inserted
data = ("Enid", "Secret Seven", "enid@example.com", 21, 2)



# Execute the query with the data
try:
    cursor.execute(sql_query, data)
except pymysql.Error as e:
    print("Error executing query:", e)
    exit()

# Commit the changes
try:
    connection.commit()
except pymysql.Error as e:
    print("Error committing changes:", e)
    exit()

# Close the cursor and the connection
try:
    cursor.close()
    connection.close()
except pymysql.Error as e:
    print("Error closing connection:", e)
    exit()

In the example above, we use try-except blocks to handle errors and exceptions at different stages of the code. When we connect to the database, we use a try-except block to catch any errors that may occur. If an error occurs, we print an error message and exit the program.

We do the same thing when we create a cursor object, execute the query, commit the changes, and close the cursor and connection.

If an error occurs at any of these stages, we print an error message and exit the program.

Note that it’s important to handle errors and exceptions in your code to make it more reliable and to prevent unexpected crashes or behavior. However, it’s also important to handle errors gracefully and provide useful error messages to the user or developer.

Conclusion

Here are some key takeaways about using Python with SQL:

• Python can be used to connect to SQL databases and execute queries using libraries like PyMySQL, sqlite3, and sqlalchemy.
• Using Python with SQL can allow for more powerful and flexible data analysis and manipulation, as well as easier automation of database tasks.
• Python code can be used to insert, update, and delete data in a database, as well as query and retrieve data.
• When using Python with SQL, it’s important to handle errors and exceptions gracefully to ensure reliability and prevent unexpected behavior.
• Python can also be used with other database technologies, such as NoSQL databases and object-relational mappers (ORMs), depending on the needs of your project.
• Learning how to use Python with SQL can be a valuable skill for data analysts, data scientists, and software developers who work with databases.
• There are various resources from where you can master Python in SQL for data analysis. Some of them are — Edx, Scaler, freecodecamp, Kdnuggets, etc.
• Thanks for Reading!

Go from SELECT * to interview-worthy project. Get our free 5-page guide.