Database Access | Data Collection | Datascience Tutorial

Introduction

In the realm of Data Science, accessing and manipulating databases is a fundamental skill. This tutorial will guide you through the basics of database access, using SQL and Python. By the end of this tutorial, you will have a solid understanding of how to connect to a database, execute queries, and handle the results.

Setting Up a Database

Before we can interact with a database, we need to set one up. We'll use SQLite for this tutorial as it is a lightweight, file-based database that is easy to set up and use.

To create a new SQLite database, you can use the following command:

sqlite3 mydatabase.db

Creating Tables

Once the database is created, the next step is to create tables. Here is an example of how to create a table named users with columns id, name, and email.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

Inserting Data

After creating tables, you can insert data into them. Here is an example of how to insert a new record into the users table:

INSERT INTO users (name, email) 
VALUES ('John Doe', 'john.doe@example.com');

Querying Data

To retrieve data from the database, you use the SELECT statement. Here is an example of how to select all records from the users table:

SELECT * FROM users;

Database Access Using Python

Python provides various libraries for database access. We'll use the sqlite3 module to interact with the SQLite database we created earlier. Here is an example of how to connect to the database and fetch data:

import sqlite3

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

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

# Fetch and display results
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
conn.close()

Updating Data

You can update existing records in the database using the UPDATE statement. Here is an example:

UPDATE users 
SET email = 'new.email@example.com' 
WHERE name = 'John Doe';

Deleting Data

To delete records from the database, use the DELETE statement. Here is an example:

DELETE FROM users 
WHERE name = 'John Doe';

Conclusion

In this tutorial, we covered the basics of database access, including creating a database, creating tables, inserting, querying, updating, and deleting data, and accessing the database using Python. These skills are essential for any Data Scientist working with large volumes of data stored in databases.

Database Access Tutorial