In this tutorial, we will learn how to print an SQL table in Python. We will use the SQLite3 library, which comes pre-installed with Python, to connect to an SQLite database and print the contents of a table. Let’s get started!
Step 1: Create a Sample SQLite Database and Table
Before we print an SQL table in Python, we need a sample SQLite database and table to work with. You can use a database management tool like DB Browser for SQLite to create a new SQLite database and table.
For the purpose of this tutorial, let’s create a simple table called “students”:
Step 1: Connect to the SQLite Database in Python
To connect to the SQLite database in Python, we will use the sqlite3 library’s connect() method. Replace <path_to_database> with the actual path to your SQLite database.
|
1 2 3 |
import sqlite3 conn = sqlite3.connect('database.db') |
Step 2: Create a table
|
1 2 3 4 5 6 7 8 9 10 |
# Drop table cursor.execute("DROP TABLE IF EXISTS students") # Create the students table cursor.execute('''CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT )''') |
Step 3: Insert data into the table
|
1 2 3 4 5 6 7 |
# Insert sample data into the students table students_data = [ ] cursor.executemany('INSERT INTO students VALUES (?, ?, ?, ?)', students_data) |
Step 4: Fetch Data from the SQL Table
Now that we are connected to the SQLite database, we are ready to fetch the data from the “students” table:
|
1 2 3 |
cursor = conn.cursor() cursor.execute("SELECT * FROM students") rows = cursor.fetchall() |
The cursor.execute() method executes the SQL query, and cursor.fetchall() fetches all the rows and stores them in the rows variable.
Step 5: Print the SQL Table
To print the fetched SQL table, you need to iterate through the rows and print them one by one:
|
1 2 3 |
print("ID | Name | Age | Email") for row in rows: print(f"{row[0]} | {row[1]} | {row[2]} | {row[3]}") |
With this code, we print the column headers followed by the rows.
Step 6: Close the SQLite Connection
After printing the table, it’s important to close the SQLite connection by calling the close() method on the connection object:
|
1 |
conn.close() |
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 33 34 35 36 37 38 |
import sqlite3 # Connect to the SQLite database conn = sqlite3.connect('database.db') # Fetch data from the SQL table cursor = conn.cursor() # Drop table cursor.execute("DROP TABLE IF EXISTS students") # Create the students table cursor.execute('''CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT )''') # Insert sample data into the students table students_data = [ ] cursor.executemany('INSERT INTO students VALUES (?, ?, ?, ?)', students_data) cursor.execute("SELECT * FROM students") rows = cursor.fetchall() # Print the SQL table print("ID | Name | Age | Email") for row in rows: print(f"{row[0]} | {row[1]} | {row[2]} | {row[3]}") # Close the SQLite connection conn.close() |
Output
ID | Name | Age | Email 1 | John Doe | 20 | [email protected] 2 | Jane Smith | 22 | [email protected] 3 | Michael Johnson | 21 | [email protected]
Conclusion
In this tutorial, we learned how to print an SQL table in Python using the SQLite3 library. This method can be easily adapted to print tables from other types of databases by using appropriate Python libraries for the database, such as Psycopg2 for PostgreSQL or PyMySQL for MySQL.