Connect SQLite with Python

Created with Sketch.

Connect SQLite with Python

First you have to install Python and SQLite on your syatem.

Install Python

Use the following code:

  1. sudo apt-get update
  2. sudo apt-get upgrade python

SQLite Connect sqlite with python 1

Press y and installation will be completed within seconds.

Install SQLite

Installation steps

type in the following command:

  1. sudo apt-get install sqlite3 libsqlite3-dev

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

  1. sqlite3

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:

  1. .databases

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:

  1. #!/usr/bin/python
  2. import sqlite3
  3. conn = sqlite3.connect(‘javatpoint.db’)
  4. print “Opened database successfully”;

Execute the following statement on command prompt:

  1. python connect.py

SQLite Connect sqlite with python 2

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:

  1. #!/usr/bin/python
  2. import sqlite3
  3. conn = sqlite3.connect(‘javatpoint.db’)
  4. print “Opened database successfully”;
  5. conn.execute(”’CREATE TABLE Employees
  6.        (ID INT PRIMARY KEY     NOT NULL,
  7.        NAME           TEXT    NOT NULL,
  8.        AGE            INT     NOT NULL,
  9.        ADDRESS        CHAR(50),
  10.        SALARY         REAL);”’)
  11. print “Table created successfully”;
  12. conn.close()

Execute the following statement on command prompt:

  1. python createtable.py

SQLite Connect sqlite with python 3

A table “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:

  1. #!/usr/bin/python
  2. import sqlite3
  3. conn = sqlite3.connect(‘javatpoint.db’)
  4. print “Opened database successfully”;
  5. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  6.       VALUES (1‘Ajeet’27‘Delhi’20000.00 )”);
  7. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  8.       VALUES (2‘Allen’22‘London’25000.00 )”);
  9. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  10.       VALUES (3‘Mark’29‘CA’200000.00 )”);
  11. conn.execute(“INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \
  12.       VALUES (4‘Kanchan’22‘Ghaziabad ‘65000.00 )”);
  13. conn.commit()
  14. print “Records inserted successfully”;
  15. conn.close()

Execute the following statement on command prompt:

  1. python connection.py

SQLite Connect sqlite with python 4

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:

  1. #!/usr/bin/python
  2. import sqlite3
  3. conn = sqlite3.connect(‘javatpoint.db’)
  4. data = conn.execute(“select * from Employees”);
  5. for row in data:
  6.    print “ID = “, row[0]
  7.    print “NAME = “, row[1]
  8.    print “ADDRESS = “, row[2]
  9.    print “SALARY = “, row[3], “\n”
  10. conn.close();

Execute the following statement on command prompt:

  1. python select.py

SQLite Connect sqlite with python 5

See all the records you have inserted before.

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

Leave a Reply

Your email address will not be published. Required fields are marked *