In today’s data-driven world, a good grasp about database management is vital for any developer. Have you heard about SQLite and Python’s interface to SQLite? This combination offers a self-contained, serverless, and zero-configuration option for managing databases. Sounds attractive? Welcome to an enlightening journey into Python and SQLite!
Table of contents
What is SQLite?
SQLite is an open-source, embedded relational database management system (DBMS). It’s distinct because unlike most other SQL databases, SQLite doesn’t have a separate server process. Instead, it allows you to directly access its storage files.
What is Python SQLite?
Python offers the sqlite3 module in its standard library, which allows us to interface with an SQLite database. This means you can create, read, update, delete and manage an SQLite database from your Python apps.
Why Should I Learn it?
Understanding SQLite and Python’s interface with it is like equipping yourself with a powerful tool in your developer toolkit. Here’s why:
- Flexibility: SQLite database files don’t need a server or installation. You can use them cross-platform.
- Convenience: SQLite is directly accessible from Python, no add-ons needed.
- Industry Relevance: SQLite database is used not only in software applications, but also in browsers, embedded systems, and other scenarios where data management is essential.
By the end of this lesson, you’ll be able to handle SQLite databases using Python the way a knight wields his sword – with precision, skill and confidence. Stay tuned as we take you through every step of this process in an engaging, fun manner. Happy coding!
Getting Started with Python SQLite
Before we dive into creating and managing databases using SQLite in Python, let’s make sure Python SQLite module is available. To check this:
import sqlite3 print(sqlite3.version)
If Python is correctly installed, this code will print out the version of SQLite.
Creating a Database
Simple and straightforward, creating a database in Python is a one-liner:
conn = sqlite3.connect('test.db')This line of code creates a connection object that represents the database ‘test.db’. If the database doesn’t exist, SQLite3 will create it for you.
Creating a Table
Creating a table is equally straightforward:
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute('''
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
age INT NOT NULL);
''')Here, we have opened a connection to our previously created database ‘test.db’, and then opened a cursor, which will serve as our pathway to the database. Once the cursor is open, we have executed a standard SQL command to create a table.
Inserting Data
Inserting data into the database is done with SQL’s INSERT command. Here’s how:
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute("INSERT INTO employees (id, name, age) \
VALUES (1, 'John Doe', 30)")Here, we have inserted an employee record with an ID of 1, name ‘John Doe’, and age 30 into our employees table.
Make sure to save your changes after each operation with:
conn.commit()
And always remember to close the connection with:
conn.close()
That’s all for the basics of SQLite in Python. We have covered how to create a database, a table, and insert data into it. In the next part, we’ll delve into retrieving and manipulating data. Stay with us for more fun and experiential learning!
Retrieving Data
Now that we have data stored in our SQLite database, we can use Python’s SQLite module to retrieve it. This is done using SQL SELECT command.
conn = sqlite3.connect('test.db')
c = conn.cursor()
for row in c.execute('SELECT * FROM employees ORDER BY age'):
print(row)The syntax in the above code will fetch all the records from the ’employees’ table and display it, sorted by ‘age’.
Updating Data
Until now, we have seen how to create and retrieve data from the database. Now let’s see how we can modify the existing data using the UPDATE SQL command.
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute("UPDATE employees set AGE = 32 where ID = 1")
conn.commit()
for row in c.execute('SELECT * FROM employees'):
print(row)In the example, we are updating the age of the employee whose id is 1 to 32. After performing the operation, again we are displaying all the records from the employees table where you can see the updated result.
Deleting Data
To delete data from the database, we use the DELETE SQL command.
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute("DELETE from employees where ID = 1;")
conn.commit()
for row in c.execute('SELECT * FROM employees'):
print(row)In the code snippet above, we are deleting the employee record where the ID is 1. Then, all the remaining records from the employees table are printed.
Applying Filters
Additionally, SQLite provides us with handy filters such as LIKE, AND, OR, and DISTINCT to fetch the desired data.
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute("INSERT INTO employees (id, name, age) \
VALUES (2, 'Jane Doe', 30)")
for row in c.execute('SELECT * FROM employees WHERE name LIKE "Jane%"'):
print(row)The LIKE filter allows us to match patterns in data retrieval. In the above piece of code, we are fetching all employee records whose names start with ‘Jane’.
We hope this tutorial has provided you with an understanding of how to create, fetch, update, and manage SQLite databases using Python’s SQLite module. We believe that you now feel more confident to utilize this efficient and straightforward tool in your future projects. Keep coding and learning with us at Zenva!
Where to Go Next / How to Keep Learning
Feeling excited about all of the new possibilities open to you now that you’ve dipped your toes into working with SQLite in Python? Amazing! This is just the beginning, and there’s an entire sea of knowledge waiting for you. At Zenva, we’ve curated a comprehensive learning pathway to guide you further. We invite you to explore our Python Mini-Degree.
Our Python Mini-Degree is an exhaustive compilation of courses specially designed to teach Python programming. Don’t worry if you’re a beginner, we’ve tailored our courses to suit various proficiency levels.
Learning by doing is what we vouch for. So, through our courses, you’ll find yourself coding basics, crafting algorithms, and developing your own games and applications. As Python is in high demand in the data science sector and beyond, the skills you learn can lead to impressive career opportunities across different industries.
Worried about time commitment? Don’t be. With 24/7 access to our courses on any device, you’ll be perfectly able to learn at your own pace. To keep you abreast of the latest industry trends, our courses are updated regularly. Moreover, to ensure that you are on the right track, we provide the support of expert mentors.
In addition, our Mini-Degree program encompasses a portfolio-building facet. Upon completion, you’ll be proud to showcase your Python projects and demonstrate your competency to potential employers.
For a broader collection of Python-related content, visit our Python courses. Keep the motivation burning, and we at Zenva are here to mentor and guide you along your exciting journey of becoming a proficient Python programmer. Happy coding!
Conclusion
Python SQLite is an incredibly handy tool to have in your developer arsenal. Whether you’re automating tasks, building complex applications, or just handling data – the knowledge and experience of how to create, manipulate, and manage SQLite databases efficiently is undeniably valuable in today’s tech-powered era.
We hope this tutorial sparked your curiosity and you’re now more prepared to venture into the world of Python programming and SQLite databases. We, at Zenva, look forward to accompanying you on your journey to become an expert Python programmer. Get started today and create tomorrow’s solutions with Zenva. Happy learning!
Did you come across any errors in this tutorial? Please let us know by completing this form and we’ll look into it!
FINAL DAYS: Unlock coding courses in Unity, Godot, Unreal, Python and more.
