Introduction
In today’s world, data lies at the heart of technology, and the management of this data plays a critical role in almost every sector. Database management is vital for organizing, storing, and making data accessible. In this article, we demonstrate how to effectively manage databases using Python, one of the world’s most popular programming languages. Python is an excellent choice for database operations due to its ease of use, extensive library support, and flexibility.
Why Database Management with Python?
Python is one of the preferred languages for database operations, and there are several important reasons for this:
- User-Friendly Syntax: Python’s readable and understandable syntax makes database operations easily executable.
- Comprehensive Library Support: Python has extensive library support for interacting with popular databases like SQLite, MySQL, PostgreSQL.
- Cross-Platform Compatibility: Python can run seamlessly on different operating systems, making it ideal for database operations in various environments.
- Compatibility with Data Science and Analytics: As Python is widely used in data science and analytics, it allows for smooth transitions between analytical processes and database management.
1. Connecting to the Database and Creating Tables
import sqlite3
# Connecting to the SQLite database (creates the database if it doesn't exist)
conn = sqlite3.connect('example_database.db')
# Creating a cursor
cursor = conn.cursor()
# Creating a table
cursor.execute('''
CREATE TABLE people (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)
''')
# Saving the operation and closing the connection
conn.commit()
conn.close()2. Adding Data (INSERT)
conn = sqlite3.connect('example_database.db')
cursor = conn.cursor()
# Adding data
cursor.execute("INSERT INTO people (name, age) VALUES ('Ahmet', 30)")
conn.commit()
conn.close()3. Data Query (SELECT)
conn = sqlite3.connect('example_database.db')
cursor = conn.cursor()
# Querying data from the database
cursor.execute("SELECT * FROM people")
people = cursor.fetchall()
for person in people:
print(person)
conn.close()4. Data Update (UPDATE)
conn = sqlite3.connect('example_database.db')
cursor = conn.cursor()
# Updating an existing record
cursor.execute("UPDATE people SET age = 31 WHERE name = 'Ahmet'")
conn.commit()
conn.close()5. Deleting Data (DELETE)
conn = sqlite3.connect('example_database.db')
cursor = conn.cursor()
# Deleting data
cursor.execute("DELETE FROM people WHERE name = 'Ahmet'")
conn.commit()
conn.close()End of Section Project: Library Management System
At the end of this section, to reinforce what we’ve learned, we are developing our own library management system.
Project Summary
- Database Creation: We will create a library database with SQLite.
- Table Creation: We will create a table to store book information.
- Adding Books: We will add new books to the database.
- Listing Books: We will list all the books in the database.
- Updating Books: We will update existing book information.
- Deleting Books: We will delete books from the database.
- User Interface: We will create a simple command-line interface.
- Project: To access many similar project examples, you can check out my Python Learning in 30 Days learning repo that I prepared for you.
import sqlite3
# First, let's create a table named `books` in a SQLite database.
def connect_to_database():
conn = sqlite3.connect('library.db')
return conn
def create_table():
conn = connect_to_database()
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS books (
id INTEGER PRIMARY KEY,
title TEXT,
author TEXT,
publisher TEXT,
year_of_publication INTEGER
)
''')
conn.commit()
conn.close()
create_table()
# Let's write a function for users to add new books.
def add_book(title, author, publisher, year_of_publication):
conn = connect_to_database()
cursor = conn.cursor()
cursor.execute("INSERT INTO books (title, author, publisher, year_of_publication) VALUES (?, ?, ?, ?)",
(title, author, publisher, year_of_publication))
conn.commit()
conn.close()
# Let's write a function to list all the books in the database.
def list_books():
conn = connect_to_database()
cursor = conn.cursor()
cursor.execute("SELECT * FROM books")
books = cursor.fetchall()
for book in books:
print(book)
conn.close()
# Let's write functions to update and delete books.
def update_book(id, title, author, publisher, year_of_publication):
conn = connect_to_database()
cursor = conn.cursor()
cursor.execute("UPDATE books SET title = ?, author = ?, publisher = ?, year_of_publication = ? WHERE id = ?",
(title, author, publisher, year_of_publication, id))
conn.commit()
conn.close()
def delete_book(id):
conn = connect_to_database()
cursor = conn.cursor()
cursor.execute("DELETE FROM books WHERE id = ?", (id,))
conn.commit()
conn.close()
# Let's create a simple command-line interface so that users can use these functions.
def menu():
while True:
print("\nLibrary Management System")
print("1 - Add Book")
print("2 - List Books")
print("3 - Update Book")
print("4 - Delete Book")
print("5 - Exit")
choice = input("Make your choice: ")
if choice == '1':
title = input("Book title: ")
author = input("Author: ")
publisher = input("Publisher: ")
year_of_publication = int(input("Year of Publication: "))
add_book(title, author, publisher, year_of_publication)
elif choice == '2':
list_books()
elif choice == '3':
id = int(input("ID of the book to update: "))
title = input("New book title: ")
author = input("New author: ")
publisher = input("New publisher: ")
year_of_publication = int(input("New year of publication: "))
update_book(id, title, author, publisher, year_of_publication)
elif choice == '4':
id = int(input("ID of the book to delete: "))
delete_book(id)
elif choice == '5':
break
else:
print("Invalid choice.")
menu()This code will create a simple library management system that will allow users to add, list, update, and delete books.
Conclusion
In this article, we have seen how to perform basic database operations with Python and how to implement this knowledge by creating a simple Library Management System. Python is a powerful and flexible tool for database management. Whether in a simple project or a large-scale application, working with Python and databases will help you establish a solid foundation in data management. I hope this article has helped you understand the basics of working with Python and databases and encourages you to apply this knowledge in your own projects.
I thoroughly enjoyed the work that you have accomplished thus far. The sketch is appealing and your written material is stylish. However, you seem to have a bit of a lingering impatience regarding when you will deliver the following. If you protect this hike, you will almost certainly be required to return sooner rather than later.