AppDividend
Latest Code Tutorials

How to Add and Read Data in SQLite using Python

0

Python comes with an inbuilt package called sqlite3, which we can import in our project and use its API to connect to the database and manipulate the database easily.

Connect Python application to SQLite database

To connect with the SQLite database, use the connect() function.

# app.py

import sqlite3

connection = sqlite3.connect('shows.db')

If shows.db is not there, then it will be created and connected. If it is there, then it will be connected.

To create a table in the relation database, we need to use the cursor object. To create a cursor object, use a connection.cursor() method.

cursor = connection.cursor()

Now, create a database table with some columns.

cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
              (Title TEXT, Director TEXT, Year INT)''')

In this code, we have written the command that will create the table with its column names and datatypes.

Insert data in SQLite database using Python

To add data in SQLite using Python, we use INSERT INTO TABLE query. We will execute the INSERT query using the cursor.

cursor.execute("INSERT INTO Shows VALUES ('Stranger Things', 'Shawn Levy', 2016)")

In this code, we have written the query and the respective column values are Stranger Things, Shawn Levy, 2016.

Read data in the SQLite database using Python.

To check if the data is inserted correctly in the SQLite database using Python, use SELECT Query and print the row using the cursor.fetchone() method.

cursor.execute("SELECT * FROM Shows")

print(cursor.fetchone())

The final step would be to commit these changes to save in the database and close the connection.

So, our complete code is the following.

# app.py

import sqlite3

connection = sqlite3.connect('shows.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
              (Title TEXT, Director TEXT, Year INT)''')

cursor.execute(
    "INSERT INTO Shows VALUES ('Stranger Things', 'Shawn Levy', 2016)")
cursor.execute("SELECT * FROM Shows")

print(cursor.fetchone())

connection.commit()
connection.close()

Run the above file in the terminal.

 python3 app.py
('Stranger Things', 'Shawn Levy', 2016)

You can see that we get our row back, which means the data is successfully saved in the SQLite database.

Insert Multiple data into database using Python

To insert multiple data into the database, first, create data you want to insert and then use the query to add all the data in one go.

moreShows = [('Money Heist', 'Alex Rodrigo', 2017),
            ('Dark', 'Baran bo Odar', 2017),
            ('1992 Scam', 'Hansal Mehta', 2020)]

Here, we have created a Python tuple containing each row in the shows table.

To add multiple data in the SQLite database using Python, use cursor.executemany() method.

cursor.executemany("INSERT INTO Shows VALUES (?, ?, ?)", moreShows)

Here, it will take all the tuples one by one and insert it into the database with their respective fields.

Now, let’s fetch all the data from the database.

To fetch all the records from the SQLite database, use the cursor.fetchall() method.

Our complete code that adds multiple records and shows all the records to the user is following.

# app.py

import sqlite3

connection = sqlite3.connect('shows.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
              (Title TEXT, Director TEXT, Year INT)''')

moreShows = [('Money Heist', 'Alex Rodrigo', 2017),
             ('Dark', 'Baran bo Odar', 2017),
             ('1992 Scam', 'Hansal Mehta', 2020)]

cursor.executemany("INSERT INTO Shows VALUES (?, ?, ?)", moreShows)
records = cursor.execute("SELECT * FROM Shows")

print(cursor.fetchall())

connection.commit()
connection.close()

Run the file and see the output.

python3 app.py
[('Stranger Things', 'Shawn Levy', 2016), 
('Money Heist', 'Alex Rodrigo', 2017), ('Dark', 'Baran bo Odar', 2017), 
('1992 Scam', 'Hansal Mehta', 2020)]

Here, you can see that we have already added the Stranger Things row in the first example, saved in the database. That is why it appears in all records.

That is it for Add and Read Data in SQLite using Python.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.