SQLite Operations in Python
Python is a popular language for web development, data analysis, and automation. One of the most common tasks in these fields is working with databases. SQLite is a lightweight and fast relational database management system that is easy to use. In this post, we will explore SQLite operations in Python, including explanations, pros and cons, usage, and code examples.
What is SQLite?
SQLite is a software library that provides a relational database management system. It is a serverless database, which means it doesn’t require a separate server process to run. SQLite is also a file-based database, which means it stores data in a single file on disk. SQLite is widely used in embedded systems, mobile devices, and web applications.
SQLite Operations in Python
Python comes with a built-in module called sqlite3 that provides a simple way to interact with SQLite databases. The sqlite3 module provides a set of classes and functions that allow you to create, read, update, and delete data in an SQLite database.
Connecting to an SQLite Database
To connect to an SQLite database, you use the connect() function of the sqlite3 module. The connect() function takes the filename of the database as an argument. If the database doesn’t exist, it will be created automatically.
import sqlite3
conn = sqlite3.connect('example.db')
Creating a Table
To create a table in an SQLite database, you use the execute() method of the Connection object. The execute() method takes an SQL query as an argument.
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('''CREATE TABLE users
(id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL)''')
In the above example, we created a table named “users” with three columns: “id”, “name”, and “email”. The “id” column is the primary key, and it is an auto-incrementing integer.
Inserting Data
To insert data into a table, you use the execute() method with an SQL INSERT statement. You can use placeholders to insert data dynamically.
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('John Doe', '[email protected]'))
conn.commit()
In the above example, we inserted a new row into the “users” table with the name “John Doe” and email “[email protected]”.
Querying Data
To query data from a table, you use the execute() method with an SQL SELECT statement. You can use placeholders to specify the columns and conditions dynamically.
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute("SELECT * FROM users")
rows = c.fetchall()
for row in rows:
print(row)
In the above example, we queried all the rows from the “users” table and printed them to the console.
Updating Data
To update data in a table, you use the execute() method with an SQL UPDATE statement. You can use placeholders to update data dynamically.
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute("UPDATE users SET email = ? WHERE name = ?", ('[email protected]', 'Jane Doe'))
conn.commit()
In the above example, we updated the email address of the user with the name “Jane Doe” to “[email protected]”.
Deleting Data
To delete data from a table, you use the execute() method with an SQL DELETE statement. You can use placeholders to delete data dynamically.
import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute("DELETE FROM users WHERE name = ?", ('John Doe',))
conn.commit()
In the above example, we deleted the user with the name “John Doe” from the “users” table.
Pros and Cons of SQLite
Pros
- SQLite is lightweight and fast, which makes it suitable for small and medium-sized databases.
- SQLite doesn’t require a separate server process to run, which makes it easy to use and deploy.
- SQLite is cross-platform and works on Windows, macOS, and Linux.
- SQLite supports transactions, which makes it reliable and durable.
Cons
- SQLite doesn’t scale well for large databases with high concurrency.
- SQLite doesn’t support some advanced features, such as stored procedures, triggers, and views.
- SQLite doesn’t provide built-in support for encryption or access control.
Conclusion
In this post, we explored SQLite operations in Python, including explanations, pros and cons, usage, and code examples. SQLite is a lightweight and fast relational database management system that is easy to use. Python comes with a built-in module called sqlite3 that provides a simple way to interact with SQLite databases. SQLite is suitable for small and medium-sized databases, but it doesn’t scale well for large databases with high concurrency. Overall, SQLite is a great choice for embedded systems, mobile devices, and web applications that require a simple and fast database solution.