Python Tutorial
Python OOPs
Python MySQL
Python MongoDB
- python-mongodb
Python SQLite
- python-sqlite
Python Questions
Python Tkinter (GUI)
Python Web Blocker
Python MCQ
- python-mcq
Related Tutorials
Python Programs
- python-programs

Connect SQLite with Python

First you have to install 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 completed within seconds.

Install SQLite

Installation steps

type in the following command:

After installation check installation, sqlite terminal will give you a prompt and version info ?

Go to desired folder and create database:

sqlite3 database.db

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

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

Note: To connect SQLite with Python, you do not need to install the connection module separately because its being shipped by default along with Python version 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('w3cschoool.db')

print "Opened database successfully";

Execute the following statement on command prompt:

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

Create a table

Create a table "Employees" within the database "w3cschoool".

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('w3cschoool.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 statement on command prompt:

A table "Employees" is created in the "w3cschoool" 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('w3cschoool.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:

Records are inserted successfully.

Select Records

Now you can fetch and display your records from the table "Employees" by using SELECT statement.

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

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('w3cschoool.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 statement on command prompt:

See all the records you have inserted before.

By same procedures, you can update and delete the table in SQLite database usnig Python.

Next TopicSQLite Interview Questions

← prev next →

Hot Tutorials

Contact US

Email:[email protected]

Python Tutorial

Python OOPs

Python MySQL

Python MongoDB

Python SQLite

Python Questions

Python Tkinter (GUI)

Python Web Blocker

Python MCQ

Related Tutorials

Python Programs