SQL using Python

In this tutorial, we are going to learn how to use SQL with Python using SQLite database. Python has a built-in module to connect with SQLite database. We are going to use sqlite3 module to connect Python and SQLite.

We have to follow the below steps to connect the SQLite database with Python ?

• Import the sqlite3 module.
• Create a connection using the sqlite3.connect(db_name) method that takes a database name as an argument. It creates one file if it doesn't exist with the given name else it opens the file with the given name.
• Get the cursor object from the connection using conn.cursor(). It's the mediator between Python and SQLite database. We have to use this cursor object to execute SQL commands.

Creating a Connection

The above three steps help us create a connection with an SQLite database. See the below code ?

# importing the module
import sqlite3

# creating a connection
conn = sqlite3.connect("tutorialspoint.db")  # db - database

# Cursor object
cursor = conn.cursor()

Creating Tables and Inserting Data

Now we have a connection with a database. Let's create a database table with SQL queries by following the below steps ?

• Write SQL code to create a table with column names and types.
• Execute the code using cursor.execute() to create the table in the database.
• Write SQL code to insert some rows into the table and execute them.
• Commit the changes to save them in the file using conn.commit() method.
• Close the connection using conn.close() method.

# importing the module
import sqlite3

# creating a connection
conn = sqlite3.connect("tutorialspoint.db")

# Cursor object
cursor = conn.cursor()

# code to create a database table
create_table_sql = """
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR(20),
    last_name VARCHAR(30),
    gender CHAR(1)
);
"""

# executing the above SQL code
cursor.execute(create_table_sql)

# inserting data into the students table
insert_student_one_sql = """INSERT INTO students VALUES (1, "John", "Hill", "M");"""
cursor.execute(insert_student_one_sql)

insert_student_two_sql = """INSERT INTO students VALUES (2, "Jessy", "Hill", "F");"""
cursor.execute(insert_student_two_sql)

insert_student_three_sql = """INSERT INTO students VALUES (3, "Antony", "Hill", "M");"""
cursor.execute(insert_student_three_sql)

# saving the changes using commit method of connection
conn.commit()

# closing the connection
conn.close()

Retrieving Data from Database

If you didn't get any error after executing the above code, then you are good to go. How to see the data from the database tables? Let's write the code with the given steps ?

• Connect to the database.
• Create a cursor object.
• Write a SQL query to get the data that you want from the table.
• Execute the query.
• Cursor object will have the data. Get it using the fetchall() method.
• Print the data to see the results.

# importing the module
import sqlite3

# creating a connection
conn = sqlite3.connect("tutorialspoint.db")

# Cursor object
cursor = conn.cursor()

# SQL query to get all students data
fetch_students_sql = """
SELECT * FROM students;
"""

# executing the SQL query
cursor.execute(fetch_students_sql)

# storing the data in a variable using fetchall() method
students = cursor.fetchall()  # a list of tuples

# printing the data
print(students)

# closing the connection
conn.close()

[(1, 'John', 'Hill', 'M'), (2, 'Jessy', 'Hill', 'F'), (3, 'Antony', 'Hill', 'M')]

Working with Individual Records

You can also fetch one record at a time using fetchone() method ?

import sqlite3

conn = sqlite3.connect("tutorialspoint.db")
cursor = conn.cursor()

cursor.execute("SELECT * FROM students WHERE id = 1;")
student = cursor.fetchone()
print(student)

conn.close()

(1, 'John', 'Hill', 'M')

Conclusion

Python's sqlite3 module provides an easy way to work with SQLite databases. Use connect() to establish connection, execute() to run SQL commands, and commit() to save changes. Always remember to close the connection when done.