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

Assume you have a sqlite3 database with student records and the result for reading all the data is,

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

Solution

To solve this, we will follow the steps given below −

• Define a new connection. It is shown below,

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

• Read sql data from the database using below function,

pd.read_sql_query()

• Select all student data from table using read_sql_query with connection,

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

Example

Let us see the complete implementation to get a better understanding −

import pandas as pd
import sqlite3
con = sqlite3.connect("db.sqlite3")
df = pd.read_sql_query("SELECT * FROM student", con)
print(df)

Output

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