Database Interaction with Python

Introduction to relational databases

A relational database is a type of database that stores and allows access to data. These types of databases are referred to as “relational” because the data items within them have pre-determined relationships with one another. Data in a relational database is stored in tables 1.

  1. Youtube video link: Here is a link to a video tutorial on the basics of relational databases by freeCodeCamp.org.

Examples

# Example 1: Creating a table in SQLite
import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

# Create a table
conn.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert data into the table
conn.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Commit the changes
conn.commit()

# Close the connection
conn.close()
# Example 2: Querying a table in MySQL
import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')

# Query the database
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
hire_start = '1999-01-01'
hire_end = '1999-12-31'
cursor.execute(query, (hire_start, hire_end))

# Print the results
for (first_name, last_name, hire_date) in cursor:
    print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

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

Exercises

  • Write a Python program to create a table in a relational database.

  • Write a Python program to insert data into a table in a relational database.

  • Write a Python program to query a table in a relational database.

  • Write a Python program to update data in a table in a relational database.

  • Write a Python program to delete data from a table in a relational database.

Connecting to databases (SQLite, MySQL, PostgreSQL)

Databases are an essential part of modern software development. They allow you to store and retrieve data in a structured and efficient way. In this topic, we will explore how to connect to three popular relational databases: SQLite, MySQL, and PostgreSQL. We will cover the basics of connecting to a database, executing SQL queries, and handling errors.

  1. Youtube video link: Here is a link to a video tutorial on connecting to a MySQL database by freeCodeCamp.org.

Examples

# Example 1: Connecting to a SQLite database
import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

# Create a table
conn.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert data into the table
conn.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Commit the changes
conn.commit()

# Close the connection
conn.close()
# Example 2: Connecting to a MySQL database
import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')

# Query the database
cursor = cnx.cursor()
query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
hire_start = '1999-01-01'
hire_end = '1999-12-31'
cursor.execute(query, (hire_start, hire_end))

# Print the results
for (first_name, last_name, hire_date) in cursor:
    print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

# Close the connection
cursor.close()
cnx.close()
# Example 3: Connecting to a PostgreSQL database
import psycopg2

# Connect to the database
conn = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="myusername",
    password="mypassword"
)

# Query the database
cur = conn.cursor()
cur.execute("SELECT * FROM mytable")

# Print the results
rows = cur.fetchall()
for row in rows:
    print(row)

# Close the connection
cur.close()
conn.close()

Exercises

  • Write a Python program to connect to a SQLite database and create a table.

  • Write a Python program to connect to a MySQL database and query a table.

  • Write a Python program to connect to a PostgreSQL database and insert data into a table.

  • Write a Python program to connect to a SQLite database and update data in a table.

  • Write a Python program to connect to a MySQL database and delete data from a table.

Executing SQL queries with Python

Executing SQL queries with Python is a critical component of working with data in a Python environment. To accomplish this, various libraries can be utilized. In this topic, we will explore how to execute SQL queries in Python using two popular libraries, namely sqlite3 and SQLAlchemy.

  1. Youtube video link: Here is a link to a video tutorial on executing SQL queries with Python by freeCodeCamp.org.

Examples

# Example 1: Connecting to a SQLite database and executing a query
import sqlite3

# Connect to the database
conn = sqlite3.connect('example.db')

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

# Execute a query
cursor.execute('SELECT * FROM employees')

# Fetch the results
results = cursor.fetchall()

# Print the results
for row in results:
    print(row)

# Close the connection
conn.close()
# Example 2: Connecting to a MySQL database and executing a query
import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='scott', password='password',
                              host='127.0.0.1',
                              database='employees')

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

# Execute a query
query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")
hire_start = '1999-01-01'
hire_end = '1999-12-31'
cursor.execute(query, (hire_start, hire_end))

# Fetch the results
results = cursor.fetchall()

# Print the results
for (first_name, last_name, hire_date) in results:
    print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

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

Exercises

  • Write a Python program to connect to a SQLite database and execute a query.

  • Write a Python program to connect to a MySQL database and execute a query.

  • Write a Python program to connect to a PostgreSQL database and execute a query.

  • Write a Python program to connect to a SQLite database and insert data into a table.

  • Write a Python program to connect to a MySQL database and update data in a table.

Both sqlite3 and SQLAlchemy are Python libraries that allow you to interact with relational databases. However, they serve different purposes.

sqlite3 is a built-in Python library that provides a simple way to work with SQLite databases. It is a lightweight database engine that is easy to use and requires no additional installation or configuration. You can use sqlite3 to create, read, update, and delete data in an SQLite database.

On the other hand, SQLAlchemy is a Python library that provides a set of tools for working with relational databases. It includes an object-relational mapper (ORM) that allows you to map database tables to Python classes, making it easier to work with data in an object-oriented way. SQLAlchemy supports multiple database engines, including SQLite, MySQL, and PostgreSQL.

If you need to work with a simple SQLite database, sqlite3 is a good choice. However, if you need to work with more complex databases or multiple database engines, SQLAlchemy is a better option.

PreviousWorking with JSON and CSV DataNextWeb Scraping and Data Retrieval

Last updated

Was this helpful?