In this tutorial, we will be exploring how to view an SQLite Database using Python. SQLite is a library that provides a disk-based database that doesn’t require a separate server process and allows accessing the database using a non-standard variant of the SQL query language.
Python, being a dynamic and versatile programming language, offers various libraries like SQLite3 and prettytable to work with SQLite databases.
Step 1: Initialization
Before working with SQLite, ensure that Python and SQLite are installed on your workstation. You can install it via pip:
|
1 2 |
pip install db-sqlite3 pip install prettytable |
Step 2: Creating a Connection
To start working with an SQLite database, you must first create a connection to it. In Python, connections are created using the sqlite3 module. Below is a sample connect function:
|
1 2 |
import sqlite3 conn = sqlite3.connect('test.db') |
Step 3: Creating a Cursor Object
After establishing a connection to the database, we need to create a cursor object by calling the cursor() function of the connection object. This cursor will let us execute SQL commands.
|
1 |
cur = conn.cursor() |
Step 4: Fetching Data from SQLite Database
We use a SELECT statement to fetch data from the database. Once this command is executed, we can use fetchall() to get all data.
|
1 2 |
cur.execute('SELECT * FROM students') rows = cur.fetchall() |
Step 5: Viewing the data in a formatted table
To view your SQLite data in a formatted table, you can use the prettytable module. Install it via pip:
|
1 |
pip install prettytable |
Now, we will use the PrettyTable class to create a table for our data.
|
1 2 3 4 5 |
from prettytable import PrettyTable x = PrettyTable(["Name", "Age", "Grade"]) for row in rows: x.add_row(row) print(x) |
Full Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import sqlite3 from prettytable import PrettyTable # Create a connection conn = sqlite3.connect('test.db') cur = conn.cursor() # Create the students table (if it doesn't exist) cur.execute('''CREATE TABLE IF NOT EXISTS students ( name TEXT, age INTEGER, grade INTEGER)''') conn.commit() # Insert sample data (you can replace this with your actual data) cur.execute("INSERT INTO students VALUES ('John', 20, 85)") cur.execute("INSERT INTO students VALUES ('Alice', 22, 90)") conn.commit() # Execute command cur.execute('SELECT * FROM students') rows = cur.fetchall() # View data x = PrettyTable(["Name", "Age", "Grade"]) for row in rows: x.add_row(row) print(x) # Close the connection conn.close() |
Conclusion
In conclusion, Python makes it easy to connect and view SQLite databases. The important part of the process is to setup a connection, execute SQL commands via a cursor object, and use prettytable for better visualization.
Always remember to manage your connections and cursors efficiently to prevent any data leakage or loss.