CRUD SQL Operations with Python using SQLite

SQL Operations with Python using SQLite

 

SQLite is a software library that implements a self-contained, server less, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

Install SQLite on Windows

Step 1 − Go to https://www.sqlite.org/download.html , and download precompiled binaries from Windows section.

Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.

Step 3 − Create a folder C:\>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.

Step 4 − Add C:\>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result.

Creating Database

·         Import the sqlite3 library.

·         Create a connection to the database by giving name of the created or to create database.

·         Create a cursor object- Which allows us to interact with database and other record.

Let’s build on this code and insert some data into our new table!

Figure 1-creating database

Inserting Values to it

·         Use the INSERT INTO SQL command to insert a record into our database.

·         Save the record to the database, we have to commit it.

·         We’re using question marks (?) instead of string substitution (%s) to insert the values.

Figure 2-inserting multiple values

Updating Records

·          Use SQL’s UPDATE command to update out albums table. 

·         If you don’t commit the changes, then your changes won’t be written out to the database.

Figure 3-updating record

Figure 4-updated record

Deleting Record

 Tell SQLite which table to delete from (albums) and which records to delete using the WHERE clause

.

Figure 5-deleting value

Figure 6-deleted value