SQL with Python: A Comprehensive Guide for Beginners and Intermediates 🎯

Welcome to our SQL with Python tutorial! In this guide, we'll learn how to interact with databases using Python, a popular programming language. By the end, you'll be able to create, read, update, and delete data in a database, all from Python.

Why SQL with Python? 💡

SQL (Structured Query Language) is used to communicate with databases. Python, on the other hand, is a versatile programming language used in various domains, including data analysis, web development, and AI. Combining SQL and Python allows you to perform powerful database operations within your Python code.

Prerequisites 📝

Before we dive in, ensure you have Python installed on your computer. You can download it from here. Additionally, you'll need a SQL database to interact with. We recommend using SQLite, a lightweight database that comes bundled with Python.

Python Libraries ✅

To work with databases in Python, we'll use a library called sqlite3. It's a built-in library, so you don't need to install it separately.

Creating a Database 📝

Let's create a simple SQLite database using Python.

python

import sqlite3

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

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

# Create a table called 'users'
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
)
''')

# Save the changes
conn.commit()

# Close the connection
conn.close()

In this code, we create a new SQLite database called my_database.db, create a table named users, and define three columns: id, name, and email.

Inserting Data 📝

Now, let's insert some data into our users table.

python

# Reopen the connection
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Insert a user
cursor.execute('''
INSERT INTO users (name, email)
VALUES ('John Doe', 'john.doe@example.com')
''')

# Save the changes
conn.commit()

# Close the connection
conn.close()

In this example, we insert a new user with the name 'John Doe' and email 'john.doe@example.com'.

Reading Data 📝

To read data from the database, we'll query the users table.

python

# Reopen the connection
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Query the users table
cursor.execute('SELECT * FROM users')

# Fetch all rows
rows = cursor.fetchall()

for row in rows:
    print(row)

# Close the connection
conn.close()

This code fetches all users from the users table and prints them out.

Updating and Deleting Data 📝

Updating and deleting data in a database follows similar steps. For example, to update John Doe's email:

python

# Reopen the connection
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Update John Doe's email
cursor.execute('''
UPDATE users
SET email = 'john.doe_updated@example.com'
WHERE name = 'John Doe'
''')

# Save the changes
conn.commit()

# Close the connection
conn.close()

To delete John Doe from the users table:

python

# Reopen the connection
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()

# Delete John Doe
cursor.execute('''
DELETE FROM users
WHERE name = 'John Doe'
''')

# Save the changes
conn.commit()

# Close the connection
conn.close()

Quiz 🎯

That's it for this tutorial! As you practice more, you'll become more comfortable working with databases using Python. Happy coding! 💡🎯