A Short Guide To Work With SQL Using Python

SQL (Structured Query Language) is a standard programming language for interacting with relational databases. It allows you to create, modify, and query databases, and it is a powerful tool for storing and managing data.

In Python, you can use SQL in several ways:

1. You can use the sqlite3 module to work with SQLite databases. SQLite is a lightweight database engine that is included with Python, and it is perfect for small to medium-sized projects.

2. You can use the psycopg2 module to work with PostgreSQL databases. PostgreSQL is a powerful and feature-rich database engine that is widely used in production environments.

3. You can use the pyodbc module to work with other database engines, such as Microsoft SQL Server or Oracle.

In this article, we will focus on using SQLite with the sqlite3 module in Python. To use SQLite in your Python programs, you first need to import the sqlite3 module, like so:

import sqlite3

To create a connection to a SQLite database, you use the connect() function of the sqlite3 module, like so:

conn = sqlite3.connect("mydatabase.db")

This creates a connection to a database file called "mydatabase.db". If the file does not exist, it will be created for you.

To execute a SQL query, you use the cursor() method of the connection object to create a cursor, and then you use the execute() method of the cursor to execute the query, like so:

cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")

In this example, we have created a table called "users" with three columns: "id", "name", and "age". 

The "id" column is an integer primary key, which means it is a unique identifier for each row in the table.

To insert data into a table, you use the INSERT INTO statement, like so:

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 30))

In this example, we have inserted two rows into the "users" table.

The ? placeholders in the VALUES clause are used to indicate that the values will be provided as arguments to the execute() method.

To select data from a table, you use the SELECT statement, like so:

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
  print(row)

In this example, we have selected all rows from the "users" table and printed them to the console. 

The fetchall() method of the cursor object returns a list of rows, and the for loop iterates over the list and prints each row.

To update data in a table, you use the UPDATE statement, like so:

cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")

In this example, we have updated the "age" of "Alice" from 25 to 26 in the "users" table using the UPDATE statement

Reference Books

Here are the books I’ve used as references for writing this article,
please feel free to read them If you don’t want your knowledge to be
limited to this article alone.