How to Create Database and Store/Retrieve Data from the Database in Python
Share
Condition for Creating and Managing Database in Python
Description:
A database is a structured collection of data that can be easily accessed, managed, and updated.
In Python, you can use SQLite (via the sqlite3 library) to create a lightweight, disk-based database. The steps include creating a database file, defining a table schema, inserting data, and executing queries to retrieve the data.
Steps:
1. Import SQLite library
2. Create a database
3. Define a table schema
4. Insert data
5. Retrieve data
6. Close the connection
Step-by-Step Process
Import SQLite Library: Import the sqlite3 module.
Create a Database: Establish a connection to an SQLite database file.
Define a Table Schema: Use SQL CREATE TABLE statement to define the structure of the table.
Insert Data: Use the SQL INSERT INTO statement to store data.
Retrieve Data: Use the SQL SELECT statement to retrieve data.
Close the Connection: Always close the connection to release resources.
Sample Source Code
# Code for Creating and Retrieving Data from Database
import sqlite3
# Connect to SQLite database (creates a new database file if it doesn't exist)
connection = sqlite3.connect('example.db')
# Create a cursor object to execute SQL commands
cursor = connection.cursor()
# Create a table (if not exists) to store data
cursor.execute('''
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
department TEXT
)
''')
# Insert data into the table
employees = [
(1, 'Antony', 30, 'HR'),
(2, 'Praveen', 25, 'IT'),
(3, 'Kanth', 35, 'Finance'),
(4, 'Dinesh', 22, 'IT')
]
cursor.executemany('INSERT OR IGNORE INTO employees VALUES (?, ?, ?, ?)', employees)
# Commit changes
connection.commit()
# Retrieve data from the table
cursor.execute('SELECT * FROM employees')
rows = cursor.fetchall()
# Display the retrieved data
print("Retrieved Data:")
for row in rows:
print(row)
Research Breakthrough Possible @S-Logix
