Python SQLite – Working with SQLite Database in Python

In this Python SQLite tutorial we are going to learn Working with SQLite Database in Python, 

we are going to learn how you can insert, select, update and delete data in Python using

SQLite Database.

 

 

 

 

 

What is SQLite ?

 SQLite  is an embedded relational Database engine. the documentation calls it a

self-contained, serverless, zero configuration and transactional SQL database engine.

It is very popular and there are hundreds of millions copies worldwide in use today.

Several programming languages have built-in support for SQLite including Python and

PHP. In Python Sqlite3 is a built in and you don’t need to Install that.

 

 

OK now let’s start our coding, first of all we are going to create a connection to the

Sqlite database using Python.

 

 

 

SQLite Database Connection

We are going to Create SQLite Connection.

 

 

in the above code first we have imported our sqlite3, you don’t need to install this library,

because it is built in with Python. for connecting our python code to sqlite database we

can use sqlite3.connection() function. after runing above code you will have a database in

your working directory at name of geekscoders.db.

 

 

 

Run the code and check your working directory, you will have the

geekscoders.db database.

SQLite Python Tutorial
SQLite Python Tutorial

 

 

 

 

How to Create Table in Python SQLite

OK now we are going to create table for the database, right now i don’t have any table in the

database, you can use this code for creating table.

 

 

 

First we have connected our code to the database that we have already created, you can

use sqlite3.connection() and give the name of the database that you want to connect.

 

 

After that create the object of cursor()  with query, and than execute your query.

 

 

Run the code and you will see that the table is created in our database, now we are going

to use a Pycharm Plugin, that is Database Navigator, first install that plugin because we

want to check our SQLite database. and now you can see that we have our table with three 

fields in the database.

Python SQLite - Working with SQLite Database in Python
Python SQLite – Working with SQLite Database in Python

 

 

 

 Inserting Data in SQLite Database

Now we want to insert some data in our database, you can use this code for inserting data

to sqlite database.

 

 

So for inserting the data first we have created our data, and after that we have connected our

code to the database, than you need to create the object of cursor and at the end  execute

your query. because we are using multiple rows by this reason we use executemany(),

to insert multiple rows into a table, use the executemany() method. The second parameter

of the executemany() method is a list of tuples, containing the data you want to insert:

now we want to use SQLiteStudio software for checking our data in the database.

first you need to download and install that, and after that add your database in to

SQLiteStudio.

Python SQLite Inserting Data
Python SQLite Inserting Data

 

 

 

 

Python Selecting Data in SQLite Database

For selecting data first of all you need to create a cursor() object and after that you can select

all the data from the Sqlite database in Python, this is the code for selecting the data.

 

 

 

Run the code and this is the result.

Python SQLite Selecting Data
Python SQLite Selecting Data

 

 

 

 

Python Updating Data in SQLite Database 

Now let’s update our data, first you need to create object of the cursor(), after that you can

use update query for updating the specific data.

 

 

 

Now check your SQLiteStudio our first data is updated.

Python Sqlite updating data
Python Sqlite updating data

 

 

 

 

Python Deleting Data in SQLite Database 

This is the code for deleting the data, and we are going to delete just one item from

the table, you can just create your cursor object and after that you need to execute the 

query for deleting the data.

 

 

 

Run the code and check SQLiteStudio we don’t have the first row item.

Python SQLite Deleting The Data
Python SQLite Deleting The Data
Thank you for visiting my website! If you enjoyed the free courses, please consider supporting my works on Patreon.

Leave a Comment