Learn Python – Connect SQLite with Python- Basic and advance

First you have to set up Python and SQLite on your syatem.

Install Python

Use the following code:

sudo apt-get update  
sudo apt-get upgrade python  

Press y and installation will be executed inside seconds.

Install SQLite

Installation steps

type in the following command:

sudo apt-get install sqlite3 libsqlite3-dev  

After set up test installation, sqlite terminal will supply you a prompt and version data ?

sqlite3  

Go to desired folder and create database:

sqlite3 database.db

It’ll create database.db in the folder you’ve got given the command.

To test if your database is created, use the following command in sqlite3 terminal:

.databases  

Note: To connect SQLite with Python, you do no longer need to installation the connection module one at a time because its being shipped by means of default along with Python model 2.5.x onwards.

SQLite with Python

Create a python file “connect.py”, having the following code:

#!/usr/bin/python  
  
import sqlite3  
  
conn = sqlite3.connect('javatpoint.db')  
  
print "Opened database successfully";  

Execute the following announcement on command prompt:

python connect.py  

Now connection is created with the javatpoint database. Now you can create a table.

Create a table

Create a table “Employees” within the database “javatpoint”.

Create a python file “createtable.py”, having the following code:

#!/usr/bin/python  
  
import sqlite3  
  
conn = sqlite3.connect('javatpoint.db')  
print "Opened database successfully";  
  
conn.execute('''''CREATE TABLE Employees 
       (ID INT PRIMARY KEY     NOT NULL, 
       NAME           TEXT    NOT NULL, 
       AGE            INT     NOT NULL, 
       ADDRESS        CHAR(50), 
       SALARY         REAL);''')  
print "Table created successfully";  
  
conn.close()  

Execute the following declaration on command prompt:

python createtable.py  

A desk “Employees” is created in the “javatpoint” database.

Insert Records

Insert some records in “Employees” table.

Create a python file “connection.py”, having the following code:

#!/usr/bin/python  
  
import sqlite3  
  
conn = sqlite3.connect('javatpoint.db')  
print "Opened database successfully";  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (2, 'Allen', 22, 'London', 25000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (3, 'Mark', 29, 'CA', 200000.00 )");  
  
conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \  
      VALUES (4, 'Kanchan', 22, 'Ghaziabad ', 65000.00 )");  
  
conn.commit()  
print "Records inserted successfully";  
conn.close()  

Execute the following statement on command prompt:

python connection.py  

Records are inserted successfully.

Select Records

Now you can fetch and show your files from the table “Employees” via the usage of SELECT statement.

Create a python file “select.py”, having the following code:

#!/usr/bin/python  
  
import sqlite3  
  
conn = sqlite3.connect('javatpoint.db')  
  
data = conn.execute("select * from Employees");  
  
for row in data:  
   print "ID = ", row[0]  
   print "NAME = ", row[1]  
   print "ADDRESS = ", row[2]  
   print "SALARY = ", row[3], "\n"  
  
conn.close();  

Execute the following assertion on command prompt:

python select.py   

See all the records you have inserted before.

By equal procedures, you can replace and delete the table in SQLite database usnig Python.