How to Connect Python to SQL Databases

Connecting Python to SQL databases allows you to interact with databases directly from your Python scripts. This capability is essential for tasks such as data retrieval, updates, and analysis. In this article, we'll explore how to connect Python to SQL databases using popular libraries such as SQLite, MySQL, and PostgreSQL.

Setting Up Your Environment

To connect Python to SQL databases, you need to install the appropriate database connector libraries. Here are the common libraries for different databases:

• SQLite: No additional installation is needed as SQLite support is built into Python.
• MySQL: Use the mysql-connector-python or PyMySQL library.
• PostgreSQL: Use the psycopg2 library.

Connecting to an SQLite Database

SQLite is a lightweight database that is built into Python's standard library. Here's how to connect to an SQLite database and perform basic operations:

import sqlite3

# Connect to an SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')

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

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

# Insert data into the table
cursor.execute('''
INSERT INTO users (name, age)
VALUES ('Alice', 30)
''')

# Commit the transaction
conn.commit()

# Query the database
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())  # Output: [(1, 'Alice', 30)]

# Close the connection
conn.close()

Connecting to a MySQL Database

To connect to a MySQL database, you'll need to install the mysql-connector-python library. You can install it using pip:

pip install mysql-connector-python

Here's an example of connecting to a MySQL database and performing basic operations:

import mysql.connector

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

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

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

# Insert data into the table
cursor.execute('''
INSERT INTO employees (name, position)
VALUES ('Bob', 'Engineer')
''')

# Commit the transaction
conn.commit()

# Query the database
cursor.execute('SELECT * FROM employees')
print(cursor.fetchall())  # Output: [(1, 'Bob', 'Engineer')]

# Close the connection
conn.close()

Connecting to a PostgreSQL Database

To connect to a PostgreSQL database, you need the psycopg2 library. Install it using pip:

pip install psycopg2

Here's an example of connecting to a PostgreSQL database and performing basic operations:

import psycopg2

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

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

# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL
)
''')

# Insert data into the table
cursor.execute('''
INSERT INTO products (name, price)
VALUES ('Laptop', 999.99)
''')

# Commit the transaction
conn.commit()

# Query the database
cursor.execute('SELECT * FROM products')
print(cursor.fetchall())  # Output: [(1, 'Laptop', 999.99)]

# Close the connection
conn.close()

Conclusion

Connecting Python to SQL databases is a fundamental skill for any data-driven application. By using libraries such as sqlite3, mysql-connector-python, and psycopg2, you can easily interact with various databases. Understanding how to perform basic operations such as creating tables, inserting data, and querying databases will enable you to manage and manipulate your data effectively.