How to use Python with databases?

Maria
Python
04 May, 2024

Python is a versatile language that is often used for backend development, data analysis, and scripting. One of its powerful capabilities is interfacing with databases. This article will guide you through the process of using Python with databases, covering essential libraries, basic operations, and advanced techniques.

1. Setting Up Your Environment

To interact with databases in Python, you need to install appropriate libraries. Some of the commonly used libraries include:

SQLite: sqlite3 (comes with Python)
MySQL: mysql-connector-python
PostgreSQL: psycopg2
SQLAlchemy: An ORM that works with multiple databases

You can install these libraries using pip:

pip install mysql-connector-python psycopg2-binary sqlalchemy

2. Connecting to Databases

SQLite

SQLite is a lightweight database that is included with Python. It is great for small projects or for use as an embedded database.

import sqlite3

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

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users
                  (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

# Commit changes and close connection
conn.commit()
conn.close()

MySQL

To connect to a MySQL database, you can use the mysql-connector-python library.

import mysql.connector

# Connect to MySQL database
conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)
cursor = conn.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users
                  (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)''')

# Commit changes and close connection
conn.commit()
conn.close()

PostgreSQL

To connect to a PostgreSQL database, you can use the psycopg2 library.

import psycopg2

# Connect to PostgreSQL database
conn = psycopg2.connect(
    dbname='yourdatabase',
    user='yourusername',
    password='yourpassword',
    host='localhost'
)
cursor = conn.cursor()

# Create a table
cursor.execute('''CREATE TABLE IF NOT EXISTS users
                  (id SERIAL PRIMARY KEY, name VARCHAR(255), age INTEGER)''')

# Commit changes and close connection
conn.commit()
conn.close()

3. Performing Basic Operations

Inserting Data

SQLite:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Insert data
cursor.execute('''INSERT INTO users (name, age) VALUES (?, ?)''', ('Alice', 25))

# Commit changes and close connection
conn.commit()
conn.close()

MySQL:

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)
cursor = conn.cursor()

# Insert data
cursor.execute('''INSERT INTO users (name, age) VALUES (%s, %s)''', ('Alice', 25))

# Commit changes and close connection
conn.commit()
conn.close()

PostgreSQL:

import psycopg2

conn = psycopg2.connect(
    dbname='yourdatabase',
    user='yourusername',
    password='yourpassword',
    host='localhost'
)
cursor = conn.cursor()

# Insert data
cursor.execute('''INSERT INTO users (name, age) VALUES (%s, %s)''', ('Alice', 25))

# Commit changes and close connection
conn.commit()
conn.close()

Querying Data

SQLite:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Query data
cursor.execute('''SELECT * FROM users''')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
conn.close()

MySQL:

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)
cursor = conn.cursor()

# Query data
cursor.execute('''SELECT * FROM users''')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
conn.close()

PostgreSQL:

import psycopg2

conn = psycopg2.connect(
    dbname='yourdatabase',
    user='yourusername',
    password='yourpassword',
    host='localhost'
)
cursor = conn.cursor()

# Query data
cursor.execute('''SELECT * FROM users''')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
conn.close()

Updating Data

SQLite:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Update data
cursor.execute('''UPDATE users SET age = ? WHERE name = ?''', (30, 'Alice'))

# Commit changes and close connection
conn.commit()
conn.close()

MySQL:

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)
cursor = conn.cursor()

# Update data
cursor.execute('''UPDATE users SET age = %s WHERE name = %s''', (30, 'Alice'))

# Commit changes and close connection
conn.commit()
conn.close()

PostgreSQL:

import psycopg2

conn = psycopg2.connect(
    dbname='yourdatabase',
    user='yourusername',
    password='yourpassword',
    host='localhost'
)
cursor = conn.cursor()

# Update data
cursor.execute('''UPDATE users SET age = %s WHERE name = %s''', (30, 'Alice'))

# Commit changes and close connection
conn.commit()
conn.close()

Deleting Data

SQLite:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Delete data
cursor.execute('''DELETE FROM users WHERE name = ?''', ('Alice',))

# Commit changes and close connection
conn.commit()
conn.close()

MySQL:

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)
cursor = conn.cursor()

# Delete data
cursor.execute('''DELETE FROM users WHERE name = %s''', ('Alice',))

# Commit changes and close connection
conn.commit()
conn.close()

PostgreSQL:

import psycopg2

conn = psycopg2.connect(
    dbname='yourdatabase',
    user='yourusername',
    password='yourpassword',
    host='localhost'
)
cursor = conn.cursor()

# Delete data
cursor.execute('''DELETE FROM users WHERE name = %s''', ('Alice',))

# Commit changes and close connection
conn.commit()
conn.close()

4. Using SQLAlchemy for ORM

SQLAlchemy is a powerful Object Relational Mapper (ORM) that allows you to interact with databases using Python objects. It abstracts away the complexities of SQL queries and provides a more Pythonic way to work with databases.

Setup:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create an engine
engine = create_engine('sqlite:///example.db')

# Define a base class
Base = declarative_base()

# Define a User class
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create the table
Base.metadata.create_all(engine)

# Create a session
Session = sessionmaker(bind=engine)
session = Session()

Inserting Data:

# Create a new user
new_user = User(name='Alice', age=25)

# Add the user to the session and commit
session.add(new_user)
session.commit()

Querying Data:

# Query all users
users = session.query(User).all()
for user in users:
    print(user.name, user.age)

Updating Data:

# Update a user's age
user_to_update = session.query(User).filter(User.name == 'Alice').first()
user_to_update.age = 30
session.commit()

Deleting Data:

# Delete a user
user_to_delete = session.query(User).filter(User.name == 'Alice').first()
session.delete(user_to_delete)
session.commit()

Python FAQ

SQLAlchemy is a powerful and flexible SQL toolkit and Object Relational Mapper (ORM) for Python. It allows you to interact with databases using Python objects instead of writing raw SQL queries. The benefits of using SQLAlchemy include:

Abstraction: Simplifies complex SQL queries with Pythonic code.
Portability: Supports multiple database backends, making it easier to switch databases.
Productivity: Provides high-level ORM capabilities, reducing the amount of boilerplate code.
Maintenance: Improves code readability and maintainability.

To prevent SQL injection attacks, always use parameterized queries or prepared statements. Avoid concatenating SQL queries with user input directly. Here’s an example with SQLite:

import sqlite3

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

# Parameterized query to prevent SQL injection
user_id = 1
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

# Fetch and process results...
results = cursor.fetchall()
print(results)

# Close the connection
conn.close()

Using parameterized queries ensures that user input is properly escaped and handled safely by the database driver.

To handle transactions in Python, you can use the transaction management features provided by database libraries. Here's an example with PostgreSQL using psycopg2:

import psycopg2

# Connect to PostgreSQL database
conn = psycopg2.connect(
    dbname='yourdatabase',
    user='yourusername',
    password='yourpassword',
    host='localhost'
)
cursor = conn.cursor()

try:
    # Start a transaction
    cursor.execute("BEGIN")

    # Perform multiple database operations
    cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Alice', 25))
    cursor.execute("UPDATE users SET age = %s WHERE name = %s", (26, 'Alice'))

    # Commit the transaction
    conn.commit()
except Exception as e:
    # Rollback the transaction in case of error
    conn.rollback()
    print(f"Transaction failed: {e}")
finally:
    # Close the connection
    conn.close()

Conclusion

Python provides robust support for interacting with various types of databases, from lightweight SQLite to powerful relational databases like MySQL and PostgreSQL. By using libraries such as sqlite3, mysql-connector-python, psycopg2, and SQLAlchemy, you can perform a wide range of database operations including creating tables, inserting data, querying, updating, and deleting records.

With this guide, you have a solid foundation for working with databases in Python. As you become more familiar with these tools, you can explore more advanced features and techniques to optimize and scale your database interactions.

Tags :