Write a program in Python to read sample data from an SQL Database

Reading data from SQL databases is a common task in data analysis. Python's pandas library provides the read_sql_query() function to easily fetch data from databases and convert it into a DataFrame.

Prerequisites

You need a SQLite database file with sample data. For this example, we assume you have a db.sqlite3 file containing a student table with the following structure ?

  Id   Name
0  1  stud1
1  2  stud2
2  3  stud3
3  4  stud4
4  5  stud5

Steps to Read Data from SQL Database

Step 1: Import Required Libraries

Import pandas for data manipulation and sqlite3 for database connection ?

import pandas as pd
import sqlite3

Step 2: Create Database Connection

Establish a connection to your SQLite database ?

con = sqlite3.connect("db.sqlite3")

Step 3: Read Data Using SQL Query

Use pd.read_sql_query() to execute SQL queries and fetch data directly into a DataFrame ?

df = pd.read_sql_query("SELECT * FROM student", con)

Complete Example

Here's the complete implementation to read sample data from an SQL database ?

import pandas as pd
import sqlite3

# Create connection to database
con = sqlite3.connect("db.sqlite3")

# Read all student data from table
df = pd.read_sql_query("SELECT * FROM student", con)

# Display the data
print(df)

# Close the connection (optional but good practice)
con.close()

The output of the above code is ?

  Id   Name
0  1  stud1
1  2  stud2
2  3  stud3
3  4  stud4
4  5  stud5

Key Parameters

The read_sql_query() function accepts several useful parameters ?

• sql − The SQL query string
• con − Database connection object
• index_col − Column to use as DataFrame index
• params − Parameters for parameterized queries

Alternative: Using read_sql_table()

You can also read entire tables without writing SQL queries ?

# Read entire table directly
df = pd.read_sql_table("student", con)

Conclusion

Use pd.read_sql_query() to execute SQL queries and load results into pandas DataFrames. Always establish a proper database connection and consider closing it when done to free up resources.