Reading from SQL databases in Data Analysis Python

Introduction

We read data from SQL databases to use it for analysis and decision making. It helps us get organized data stored in tables easily.

You want to analyze sales data stored in a company database.

You need to get customer information from a database for a report.

You want to combine data from different tables to find trends.

You have data in a database and want to use Python to explore it.

You want to update or check data stored in a database using code.

Syntax

Data Analysis Python

import sqlite3
import pandas as pd

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

# Read SQL query into DataFrame
df = pd.read_sql_query('SELECT * FROM table_name', conn)

# Close connection
conn.close()

Use sqlite3.connect() to connect to your database file.

pd.read_sql_query() runs your SQL command and loads data into a DataFrame.

Examples

This reads all data from the 'customers' table in the 'sales.db' database.

Data Analysis Python

import sqlite3
import pandas as pd

conn = sqlite3.connect('sales.db')
df = pd.read_sql_query('SELECT * FROM customers', conn)
conn.close()

This reads only the name and age of employees older than 30.

Data Analysis Python

import sqlite3
import pandas as pd

conn = sqlite3.connect('company.db')
query = 'SELECT name, age FROM employees WHERE age > 30'
df = pd.read_sql_query(query, conn)
conn.close()

This counts how many orders are in the 'orders' table.

Data Analysis Python

import sqlite3
import pandas as pd

conn = sqlite3.connect('data.db')
query = 'SELECT COUNT(*) AS total FROM orders'
df = pd.read_sql_query(query, conn)
conn.close()

Sample Program

This program creates a small table of fruits in memory, inserts some data, reads it into a DataFrame, and prints it.

Data Analysis Python

import sqlite3
import pandas as pd

# Create a sample database and table
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute('CREATE TABLE fruits (id INTEGER, name TEXT, quantity INTEGER)')
cursor.execute('INSERT INTO fruits VALUES (1, "Apple", 10)')
cursor.execute('INSERT INTO fruits VALUES (2, "Banana", 20)')
cursor.execute('INSERT INTO fruits VALUES (3, "Cherry", 15)')
conn.commit()

# Read data from the table
query = 'SELECT * FROM fruits'
df = pd.read_sql_query(query, conn)

# Close connection
conn.close()

print(df)

Important Notes

Always close the database connection after reading data to free resources.

You can use other database connectors like psycopg2 for PostgreSQL or mysql-connector-python for MySQL.

SQL queries can be customized to select only the data you need.

Summary

Reading from SQL databases lets you get data into Python for analysis.

Use sqlite3 to connect and pandas.read_sql_query() to run SQL and get a DataFrame.

Always close your connection when done.