Python and SQL
Python and SQL are powerful tools in their own right, and when combined, they form a dynamic duo that can handle a wide range of data-related tasks. Integrating Python with SQL allows developers and data professionals to leverage the strengths of both languages for tasks such as data analysis, manipulation, and storage. In this article, we will explore the various ways to integrate Python and SQL for seamless data management.
Python is a programming language, famous for having an easy-to-learn and English-like syntax.
SQL is a standard programming language, which focuses on processing information in relational databases through the use of queries.
sqllite3
SQLite is a lightweight, serverless, and self-contained database engine that’s widely used in various applications. Python comes with a built-in module called sqlite3 that provides a simple and efficient way to interact with SQLite databases. In this tutorial, we will explore the basics of using SQLite3 in Python, covering topics such as database creation, table creation, data insertion, querying, and more.
Before we dive into the tutorial, make sure you have Python installed on your system. Additionally, since sqlite3 is included in the Python standard library, there’s no need to install any external packages.
Step 1: Connecting to the Database
The first step is to establish a connection to an SQLite database. If the database does not exist, SQLite will create a new one.
import sqlite3
# Connect to the database (or create a new one)
conn = sqlite3.connect('example.db')
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
Step 2: Creating a Table
Let’s create a simple table to store user information.
# Define the table schema
table_creation_query = '''
CREATE TABLE students (
id int PRIMARY KEY,
username TEXT NOT NULL,
age int NOT NULL
)
'''
# Execute the table creation query
cursor.execute(table_creation_query)
# Commit the changes
conn.commit()
Step 3: Inserting Data
Now, let’s insert some data into the users table.
# Insert data into the table
student_data = [
('Adam Smith', '19'),
('John Peterson', '23')
]
insert_query = 'INSERT INTO students (username, age) VALUES (?, ?)'
# Execute the insertion query for each user
cursor.executemany(insert_query, student_data)
# Commit the changes
conn.commit()
Step 4: Querying Data
Retrieve and display the data from the users table.
# Query the database
select_query = 'SELECT * FROM students'
cursor.execute(select_query)
# Fetch all rows
rows = cursor.fetchall()
# Display the results
for row in rows:
print(row)
Step 5: Updating and Deleting Data
Let’s make some changes to the data in the users table.
# Update data
update_query = 'UPDATE students SET age = ? WHERE username = ?'
cursor.execute(update_query, ('20', 'Adam Smith'))
# Commit the changes
conn.commit()
# Delete data
delete_query = 'DELETE FROM students WHERE username = ?'
cursor.execute(delete_query, ('John Peterson',))
# Commit the changes
conn.commit()
Step 6: Closing the Connection
Finally, don’t forget to close the connection once you’re done.
# Close the cursor and connection cursor.close() conn.close()
Congratulations! You’ve now learned the basics of using SQLite3 in Python. This tutorial covered connecting to a database, creating a table, inserting, querying, updating, and deleting data. As you continue to explore SQLite, you’ll discover its versatility and usefulness for various data storage and retrieval tasks in your Python applications.
