How to Use SQL in Python?

SQL In Python

Most modern-day applications interact with databases on a very frequent basis. And SQL or Structured Query Language makes it much easier to access and manipulate the data stored inside those databases.

Python, being one of the popular languages, has support for both built-in and third-party SQL libraries.

In the following section, we discuss how you can use SQL in Python using one of the most popular libraries called SQLite.

An Introduction to SQLite

The reason why we choose SQLite for our tutorial is because of its serverless architecture. SQLite is fast, lightweight, and stores the whole database within a file or even your PC’s internal memory (RAM). It is highly used by developers in testing environments.

The module SQLite comes natively with Python. So you won’t have to install it externally using pip.

But the simplicity of SQLite shouldn’t be taken lightly as it can also handle big databases for a production-ready environment.

All of these traits make SQLite a perfect module for both Beginners and Intermediate developers.

Steps to Use SQL in Python

Follow our instructions below to use SQL within your python script.

1. Import SQLite

The first step to using any module in python is to import it at the very top of the file. In Python3, the module is known as “sqlite3”

import sqlite3 #Importing the module

2. Create a Connection with the Database

Once the module is imported, We need to create a database object using the “connect()” method and pass the database file path as an argument.

If we currently don’t have any databases, the same command will create a new database with the file path/name we specify.

import sqlite3 #Importing the module

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

""" Here, conn is the database object and 'databasename.db' is the actual database we're trying to connect with. 
If there is no database available then the same command will trigger to create a new database of the same name in our current directory."""

3. Creating the Cursor Object

Once our database object is created, we need to set up another object that is able to execute native SQL commands to the database object using Python.

To accomplish that, all we need to do is call the “cursor()” method on our database object. All SQL commands must be executed with the cursor object.

curr = conn.cursor() #Here 'curr' is our new cursor object. 

4. Use SQL Command to Create Tables

In this section, we set up a basic table on our current database and learn how to commit them so the table actually gets stored on the file.

# SQL command that creates a table in the database

createTableCommand = """ CREATE TABLE NSA_DATA (
username VARCHAR(50),
phonenumber VARCHAR(15),
password VARCHAR(50),
baddeedcount INT,
secrets VARCHAR(250)
);"""

# Executing the SQL command
curr.execute(createTableCommand)

# Commit the changes
conn.commit()

As we see, first, we need to put the SQL commands into a string form. Then we call the “execute()” method on our cursor object and pass the string as our argument.

Finally, we need to call the “commit()” method on our database object. Else, the changes won’t be reflected in our actual database. Hence, we mustn’t forget to commit the changes.

5. Adding Data to Database

The next thing we do after creating the database schema is adding the data. Follow the next commands to learn how:

# First, we write our SQL command within a string and assign it to a variable addData
addData = """INSERT INTO NSA_DATA VALUES('abcd', '0123456789', 'Password1o1', 23, 'None Yet');"""
print("The data has been added!")

# Then we execute the command
curr.execute(addData)

# And finally commit
conn.commit()

Output:

INSERT INTO NSA_DATA VALUES('abcd', '0123456789', 'Password1o1', 23, 'None Yet')
The data has been added!

However, if you have a list of data that you want to import inside the database without going through them one by one, Here’s an approach, how you can import data from 2D Arrays into the database.

# The 2D array containing required data
data = [['abcd', '0123456789', 'Password1o1', 23, 'None Yet'],
        ['oswald', '0123456888', 'SunnyDay', 0, 'None Yet'],
        ['nobitanobi', '3216548876', 'ilovedoracake', 357, 'many of them']]

# A for loop to iterate through the data and add them one by one. 
for i in data:
    addData = f"""INSERT INTO NSA_DATA VALUES('{i[0]}', '{i[1]}', '{i[2]}', '{i[3]}', '{i[4]}')"""
    print(addData) # To see all the commands iterating
    curr.execute(addData)
print("Data added successfully!")

conn.commit()

Output:

INSERT INTO NSA_DATA VALUES('abcd', '0123456789', 'Password1o1', '23', 'None Yet')
INSERT INTO NSA_DATA VALUES('oswald', '0123456888', 'SunnyDay', '0', 'None Yet')
INSERT INTO NSA_DATA VALUES('nobitanobi', '3216548876', 'ilovedoracake', '357', 'many of them')
Data added successfully!

6. Fetching the Data

And finally, we also need to extract data from databases to process them on our day to day technical needs. The process for this is quite similar to what we’ve been doing in the above section with just a slight change.

Once we execute our search query using the cursor object, it doesn’t return the results right away. Instead, we need to use the method “fetchall()” on our Cursor to get the data.

# Our search query that extracts all data from the NSA_DATA table.  
fetchData = "SELECT * from NSA_DATA"

# Notice that the next line of code doesn't output anything upon execution. 
curr.execute(fetchData)

# We use fetchall() method to store all our data in the 'answer' variable
answer = curr.fetchall()

# We print the data
for data in answer:
    print(data)

Output:

('abcd', '0123456789', 'Password1o1', 23, 'None Yet')
('abcd', '0123456789', 'Password1o1', 23, 'None Yet')
('oswald', '0123456888', 'SunnyDay', 0, 'None Yet')
('nobitanobi', '3216548876', 'ilovedoracake', 357, 'many of them')
('abcd', '0123456789', 'Password1o1', 23, 'None Yet')
('oswald', '0123456888', 'SunnyDay', 0, 'None Yet')
('nobitanobi', '3216548876', 'ilovedoracake', 357, 'many of them')

Conclusion

Hopefully, you’ve learned how you can carry out the basic SQL operations using Python. You should also note, SQLite is not the only library available. For production level work, higher level databases like PostgreSQL and MySQL are much recommended. Although the usage within python is mostly the same.

The Completed Code:

The following section contains the complete code used in this tutorial.

Creating a Table Using SQL in Python

import sqlite3

conn = sqlite3.connect("database.db")
curr = conn.cursor()

createTableCommand = """CREATE TABLE NSA_DATA (
username VARCHAR(50),
phonenumber VARCHAR(15),
password VARCHAR(50),
baddeedcount INT,
secrets VARCHAR(250)
);"""

try: 
    curr.execute(createTableCommand)
    print("Table Successfully Created!")
except:
    print("There was an error with Table creation")
finally:
    conn.commit()

Output:

Table Successfully Created!

Adding Data via SQL in Python

import sqlite3

conn = sqlite3.connect("database.db")
curr = conn.cursor()

# The 2D array containing required data
data = [['abcd', '0123456789', 'Password1o1', 23, 'None Yet'],
        ['oswald', '0123456888', 'SunnyDay', 0, 'None Yet'],
        ['nobitanobi', '3216548876', 'ilovedoracake', 357, 'many of them']]

# A for loop to iterate through the data and add them one by one. 
for i in data:
    addData = f"""INSERT INTO NSA_DATA VALUES('{i[0]}', '{i[1]}', '{i[2]}', '{i[3]}', '{i[4]}')"""
    print(addData) # To see all the commands iterating
    curr.execute(addData)
print("Data added successfully!")

conn.commit()

Output:

INSERT INTO NSA_DATA VALUES('abcd', '0123456789', 'Password1o1', '23', 'None Yet')
INSERT INTO NSA_DATA VALUES('oswald', '0123456888', 'SunnyDay', '0', 'None Yet')
INSERT INTO NSA_DATA VALUES('nobitanobi', '3216548876', 'ilovedoracake', '357', 'many of them')
Data added successfully!

Fetching Data Using SQL in Python

import sqlite3

conn = sqlite3.connect("database.db")
curr = conn.cursor()

fetchData = "SELECT * from NSA_DATA"

curr.execute(fetchData)

# We use fetchall() method to store all our data in the 'answer' variable
answer = curr.fetchall()

# We print the data
for data in answer:
    print(data)

Output:

('abcd', '0123456789', 'Password1o1', 23, 'None Yet')
('oswald', '0123456888', 'SunnyDay', 0, 'None Yet')
('nobitanobi', '3216548876', 'ilovedoracake', 357, 'many of them')

References

Python sqlite3 official Documentation