Connecting to databases in Python

Connecting to databases in Python involves using appropriate libraries or modules for the specific database system. In this example, I’ll demonstrate connecting to both SQLite and MySQL databases using the sqlite3 and mysql-connector-python libraries, respectively.

Connecting to SQLite Database:

SQLite is a serverless, self-contained, and zero-configuration database engine.

1. Install the sqlite3 library (usually included in Python standard library):

pip install sqlite3

2. Create a simple Python script to connect to an SQLite database:

import sqlite3

# Connect to SQLite database (creates one if not present)
connection = sqlite3.connect('example.db')

# Create a cursor to execute SQL commands
cursor = connection.cursor()

# Execute SQL commands (e.g., create a table and insert data)
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))

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

Connecting to MySQL Database:

MySQL is a popular relational database management system.

1. Install the mysql-connector-python library:

pip install mysql-connector-python

2. Create a simple Python script to connect to a MySQL database:

import mysql.connector

# Connect to MySQL database
connection = mysql.connector.connect(
    host='your_mysql_host',
    user='your_mysql_user',
    password='your_mysql_password',
    database='your_database_name'
)

# Create a cursor to execute SQL commands
cursor = connection.cursor()

# Execute SQL commands (e.g., create a table and insert data)
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT)')
cursor.execute('INSERT INTO users (name, age) VALUES (%s, %s)', ('Bob', 25))

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

Replace the placeholder values in the MySQL connection parameters with your actual database credentials.

These examples provide a basic understanding of connecting to SQLite and MySQL databases using Python. In real-world applications, it’s essential to handle database connections securely, manage connection pooling for better performance, and use ORM frameworks like SQLAlchemy for a higher level of abstraction.